Реализация расчета оптимальной производственной программы.
Задача определения оптимальной производственной программы предприятия решается с использованием табличного процессора EXCEL операционной среды WINDOWS"95 и WINDOS”2013.
А). Для решения задач, связанных с вычислениями, Microsoft Office’95(97), XP содержит программу Microsoft Excel. Файлы, используемые для обработки и хранения данных этой программой, называются рабочими книгами. Рабочая книга Microsoft Excel состоит из одного или нескольких листов, которые представляют собой таблицы (см. приложение).
Каждая ячейка листа может содержать данные следующих типов:
Число. Числа могут иметь различный формат представления (просто число, процент, денежная сумма и т.п.), что не влияет на вычисления.
Текст. Текст одной ячейки может содержать до 32 000 символов; в одной ячейке может располагаться несколько строк текста.
Дата или время суток. В Microsoft Excel существуют несколько форматов отображения дат и времени.
Формула. Все формулы начинаются с символа «=» (знак равенства). Кроме обычных математических знаков (+, -, *, /, ^, скобки и т.п.) в формулах могут быть использованы функции, в том числе строковые и логические (описания некоторых функций приведены в приложении).
Чтобы ввести число, текст, дату или время суток в ячейку, необходимо:
Выделить ячейку, в которую необходимо ввести данные (указать курсором мыши и нажать левую клавишу мыши или с помощью клавиш-стрелок на клавиатуре).
Набрать данные и нажать клавишу «ENTER» или клавишу табуляции.
|
|
|
При вводе даты следует использовать точку или дефис в качестве разделителя, например: 09.05.96 или Янв-96.
Для отображения времени суток в 12-часовом формате следует ввести букву a или p, отделенную пробелом от значения времени, например: 9:00 p. В противном случае время будет интерпретировано на основе 24-часового формата.
Чтобы ввести формулу, необходимо:
Указать ячейку, в которую необходимо ввести формулу.
Ввести символ «=» (знак равенства).
Если нажать кнопку «вставка функции», знак равенства вставляется автоматически.
Ввести формулу.
Нажать клавишу «ENTER».
В следующем примере представлена формула, вычисляющая разность между числами 5 и 1. Результат выполнения отобразится в ячейке, в которой указана формула.
=5-1
В формуле может быть указана ссылка на ячейку. Если необходимо, чтобы в ячейке содержалось значение другой ячейки, введите знак равенства, после которого укажите ссылку на эту ячейку. Ячейка, содержащая формулу, называется зависимой ячейкой, и ее значение зависит от значения другой ячейки. Формула может вернуть другое значение, если изменить ячейку, на которую формула ссылается. Следующая формула умножает значение ячейки B15 на число 5. Формула будет пересчитываться при изменении значения ячейки B15.
|
|
|
=B15*5
Если формула ссылается на ячейки, расположенные на другом листе рабочей книги, имя листа отделяется символом «!» (восклицательный знак). В следующем примере формула ссылается на ячейку B4 листа с именем «ОПП1»:
=ОПП1!В4
Формулы можно копировать в другие ячейки. Относительные ссылки, например, А1 или С20, всегда изменяются так, чтобы отобразить правило их вхождения в формулу относительно ее нового местоположения. Абсолютные ссылки $А$1 или $С$20 при копировании всегда остаются неизменными.
В Microsoft Excel содержится большое количество стандартных формул, называемых функциями. Функции используются для простых или сложных вычислений. В качестве примера можно привести функцию SUM («СУММ» в русскоязычной версии пакета), суммирующую диапазоны ячеек, например:
=SUM(A1:А10;B12), где А1:А10 - начало и конец блока ячеек.
На листе, который назовем ОПП1 будут произведены расчет общей трудоемкости изготовления изделий, включенных в госзаказ, и определена пропускная способность основных цехов предприятия без учета времени на выполнение госзаказа (табл.1.3). Лист ОПП1 имеет следующий вид:
| Выбор оптимальной производственной программы | ||||||||||||||
| Таблица 1.1 | ||||||||||||||
| Исходные данные по изделиям для расчета оптимальной | ||||||||||||||
| производственной программы | ||||||||||||||
| Наимено- вание изделия | Обозна- чение ко- личества изделий | Ограничение на количество выпускаемых изделий | Станкоемкость изготовления изделий по цехам | Прибыль на единицу изделия | ||||||||||
| по госзаказу | рынок | сбороч- ный | механи- ческий | заготови- тельный | всего | |||||||||
| А | XА | XА=0 | XА>=0 | |||||||||||
| Б | XБ | XБ=0 | XБ>=0 | |||||||||||
| В | XВ | XВ=0 | XВ>=0 | |||||||||||
| Г | XГ | XГ=6000 | XГ>=0 | |||||||||||
| Д | XД | XД=7765 | XД=0 | |||||||||||
| Таблица 1.2 | ||||||||||||||
| Эффективный фонд времени работы оборудования основных цехов | ||||||||||||||
| Наименование | Наименования основных цехов | Всего | ||||||||||||
| показателей | сборочный | механический | заготовительный | |||||||||||
| Количество единиц оборудования, шт. | ||||||||||||||
| Эффективный фонд работы оборудования цеха, часов | 3121861,5 | 578122,5 | 7349864,1 | |||||||||||
| Эффективный фонд работы оборудования (Фэф) рассчитан по формуле: | ||||||||||||||
| Фэф=(Др*Ксм*Тсм-Дпр*Тсокр)*(1-ррем/100),где | ||||||||||||||
| Др = | -число рабочих дней | |||||||||||||
| Ксм = | -число смен в сутки | |||||||||||||
| Тсм = | -продолжительность смены, час. | |||||||||||||
| Дпр = | -число предпраздничных дней | |||||||||||||
| Тсокр = | -время сокращения работы в предпраздничные дни, час. | |||||||||||||
| ррем = | -прцент простоя в планово-предупредительном ремонте,% | |||||||||||||
| Таблица 1.3 | ||||||||||||||
| Расчет пропускной способности основных цехов предприятия без учета времени на выполнение госзаказа | ||||||||||||||
| Наименование цеха | Станкоемкость изготовления изделий, включенных в госзаказ, час. | Фэф, тыс.час. | Пропуск- ная спо- | |||||||||||
| Изделие Г | Изделие Д | Всего на | собность | |||||||||||
| на одно изделие | на госзаказ | на одно изделие | на госзаказ | госзаказ | цеха, тыс. час. | |||||||||
| сборочный | 2123011,5 | |||||||||||||
| механический | 2375730,1 | |||||||||||||
| заготовительный | 578122,5 | 370587,5 | ||||||||||||
| итого | 4869329,1 | |||||||||||||
|
|
|
|
|
|
Расчет оптимальной производственной программы с помощью табличного процессора ЕХСEL 7.0 Для определения оптимальной производственной программы используем Solver (поиск решения), который позволяет решать оптимизационные задачи линейного программирования. Для этого все расчеты ведутся на следующем листе ОПП2, выделенная ячейка имеет адрес $В$5. Именно сюда запишется результат - полученная сумма покрытия 105387550. Выполним команду СЕРВИС, поиск решения. В диалоговом окне набираем адреса целевой ячейки, изменяемых ячеек и ограничения, сформулированные в виде формул к соответствуюжим ячейкам. Например:
| Целевая ячейка (Макс) | ||||||
| Ячейка | Имя | Исходно | Результат | |||
| $B$5 | Сумма прокрытия А | |||||
| Изменяемые ячейки | ||||||
| Ячейка | Имя | Исходно | Результат | |||
| $B$6 | Решения А | |||||
| $C$6 | Решения Б | 3,79252E-13 | ||||
| $D$6 | Решения В | |||||
| $E$6 | Решения Г | |||||
| $F$6 | Решения Д | |||||
| Ограничения | ||||||
| Ячейка | Имя | Значение | Формула | Состояние | Разница | |
| $B$2 | Сборочный А | $B$2<=$C$22 | не связан. | |||
| $B$3 | Механический А | $B$3<=$E$22 | не связан. | 3695814,4 | ||
| $B$4 | Заготовительный А | $B$4<=$G$22 | не связан. | |||
| $B$6 | Решения А | $B$6>=0 | связанное | |||
| $C$6 | Решения Б | 3,79252E-13 | $C$6>=0 | связанное | ||
| $D$6 | Решения В | $D$6>=0 | не связан. | |||
| $E$6 | Решения Г | $E$6>=6000 | не связан. | |||
| $F$6 | Решения Д | $F$6=7765 | связанное | |||
| $C$6 | Решения Б | 3,79252E-13 | $C$6=целое | связанное | ||
| $B$6 | Решения А | $B$6=целое | связанное | |||
| $D$6 | Решения В | $D$6=целое | связанное | |||
| $E$6 | Решения Г | $E$6=целое | связанное | |||
Содержимое листа ОПП2:
| A | B | C | D | E | F | G | |
| Обозначения | А | Б | В | Г | Д | ||
| Сборочный | |||||||
| Механический | |||||||
| Заготовительный | |||||||
| Сумма покрытия | |||||||
| Решения | |||||||
В ячейках В2,В3,В4 листа ОПП2 должна быть соответственно следующая информация (обязательно формула):
=В6*200+С6*200+D6*400+E6*50+F6*90 (сборочный)
=B6*250+C6*200+D6*450+E6*70+F6*110 (механический)
=B6*50+C6*30+D6*100+E6*10+F6*19 (заготовительный),
в ячейке B5 строки СУММА ПОКРЫТИЯ должна быть целевая функция:
=B6*960+C6*700+D6*2000+E6*250+F6*12200.
В строке РЕШЕНИЯ в ячейках B6,C6,D6,E6,F6 должны быть нули.
После установления всех необходимых данных в диалоговом окне нажмем кнопку ОК и в строках СУММА ПОКРЫТИЯ и РЕШЕНИЯ получаем результаты на листе ОПП2:
| A | B | C | D | E | F | G | |
| Обозначения | А | Б | В | Г | Д | ||
| Сборочный | |||||||
| Механический | |||||||
| Заготовительный | |||||||
| Сумма покрытия | |||||||
| Решения | |||||||
Полученные данные формируются в таблицу 1.4.
Б). Расчет оптимальной производственной программы с помощью табличного процессора ЕХСEL 2013 Для определения оптимальной производственной программы используем вкладку Данные, Поиск решения (в группе Анализ), который позволяет решать оптимизационные задачи линейного программирования. Для этого все расчеты ведутся на следующем листе ОПП2, выделенная ячейка имеет адрес $В$5. Именно сюда запишется результат - полученная сумма покрытия 105387550. Выполним команду ПОИСК РЕШЕНИЯ. В диалоговом окне набираем адреса целевой ячейки, изменяемых ячеек и ограничения, сформулированные в виде формул к соответствуюжим ячейкам. Например:
Дата добавления: 2015-12-17; просмотров: 67; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!