Расчет оптимальной производственной программы в среде MS Excel

Решение данной задачи можно решить, используя вычислительные средства в среде MS Excel. Создадим новую книгу Excelи сохраним ее под определенным именем на жестком диске компьютера. Расположим исходные и искомые данные модели (рис. 3.19) на отдельном рабочем листе созданной книги.

Рис. 3.19. Исходные и искомые данные модели.

Прежде всего в ячейку А1 введем заголовок рабочего листа "Модель расчета оптимальной производственной программы фирмы" (рис. 3.20). В ячейке В2:С2 запишем названия переменных модели (рис.3.20). В ячейке В3:С3 введем выпуски продукции, равные единицам, чтобы легко было проверить правильность работы формул, которые будут вводиться в ячейки компьютерного аналога модели (рис.3.20). В ячейках В4:С4 запишем нули как нижние границы возможных значений переменных.

Ввиду особенности представления нижних индексов в Excelна рисунках они будут представлены не в подстрочной форме.

В строке 5 запишем заголовки "Ограничения модели", "Левая часть", "Знак", "Правая часть". В ячейке А6:А8 дадим наименования ресурсов, с которыми связаны ограничения модели. В ячейки В6:С8 вводим матрицу коэффициентов ограничений модели (рис.3.20), которые выражают нормы затрат соответствующих ресурсов на первый продукт, связанный с переменной , и на второй продукт, связанный с переменной .

В строке 9 зададим заголовки "Целевая функция", "Целевая ячейка". В ячейке А10 укажем, что целевая функция связана с выручкой от реализации продукции. В ячейках В10:С10 введем цены, соответствующие переменной и переменной , т.к первому и второму продуктам. Наконец, в ячейке Е10 будет подсчитываться выручка, соответствующая текущей программе выпуска продукции, меняющейся в ячейках В3:С3.

В ячейках Е6:Е8 введем формулы подсчета расходов ресурсов на текущую программу выпусков продукции, которые могут меняться в ячейках В3:С3. Эти формулы будут представлять левые части ограничений модели (рис.3.20).

Рис.3.20. Расположение искомых и исходных данных математической модели на рабочем листе Excel.

Для того, чтобы ввести такую формулу, например для левой части первого ограничения, устанавливаем курсор на ячейку Е6 и вызываем диалоговое окно Мастер функций, активизировав значок , находящийся рядом с окном ввода формул (рис.3.21). Выбрав функцию СУМПРОИЗВ и нажав ОК, вызовем окно Аргументы функции (рис.3.22).

Рис.3.21. Вид окна Мастер функций.

Рис.3.22. Задание аргументов функции ExcelСумма произведений.

Для получения абсолютной адресации с целью дальнейшего копирования в поле Массив 1 (рис. 3.22) после вводы с помощью мыши диапазона В3:С3 была нажата функциональная клавиша (F4). В поле Массив 2 сохранена относительная адресация диапазона В6:С6. Нажав кнопку ОК, в ячейке Е6 (рис. 3.20) увидим контрольное значение, равное 3, которое явилось результатом вычисления по формуле Excel=СУММПРОИЗВ($B$3:$C$3;B6:C6) при заданных нами значениях = 1и = 1.

Теперь для ввода аналогичных формул в ячейки Е7, Е8 и Е10 достаточно скопировать в них формулу ячейки Е6.

Только что введенные в ячейки электронной таблицы формулы позволяют теперь для любой программы выпуска продукции, помещенной в ячейке В3:С3, оперативно отвечать:

- в ячейке Е6 - на вопрос, сколько нужно для этой программы сырья;

- в ячейке Е7 - на вопрос, сколько нужно для этой программы оборудования;

- в ячейке Е8 - на вопрос, сколько нужно для этой программы живого труда;

- в ячейке Е10 - на вопрос, какую выручку ожидать после реализации продукции, выпущенной по этой программе.

В ячейках F6:F8 (см.рис. 3.20) напечатаны знаки <=, показывающие, что расходы ресурсов, вычисляемые в ячейках Е6:Е8, не должны превысить заданных моделью (рис. 3.19) лимитов на эти расходы, которые введены в ячейки G6:G8.

Далее необходимо выбрать раздел "Поиск решения". Во многих версиях MSExcelтакая функция не установлена, поэтому необходимо установить эту функцию. Выбираем раздел "Кнопка офис" >"Параметры Excel" > "Надстройки" > "Поиск решения" > "Перейти", далее MSExcelначнет установку разделе "Поиск решений".

Активизируем курсором целевую ячейку Е10 и вызовем диалоговое окно Поиск решения, которое заполним так, чтобы Excelсоздал адекватный компьютерный аналог математической модели (рис.3.19), окончательный вариант его представлен на рис. 3.23.

Рис. 3.23. Вид окна Поиск решений с окончательной записью компьютерного аналога математической модели (3.20).

Рассмотрим подробный процесс создания компьютерного аналога. При заполнении окна Поиск решения абсолютную адресацию ссылок на ячейки компьютер устанавливает автоматически.

В поле Установить целевую ячейку должен находиться адрес целевой ячейки Е10. Если это не произошло автоматически, необходимо, находясь с курсором в этом окне, щелкнуть мышью на целевой ячейке. Так как содержимое целевой ячейки максимизируется, то ключ должен соответствовать надписи Равной: максимальному значению.

Активизировав поле Изменяя ячейки, с помощью мыши вводим в него интервал ячеек В3:С3 с рабочего листа, изображенного на рис. 3.20.

После этого активизируем после Ограничения и нажимаем кнопку Добавить, после чего на экране появляется диалоговое окно Добавление ограничения (рис. 3.24). В поле Ссылка на ячейку: щелкнем мышью на ячейке Е6 рабочего листа (см. рис. 3.20), которая представляет левую часть ограничения по сырью. В поле Ограничение: щелкнем мышью по ячейке G6, которая представляет правую часть ограничения по сырью. Так как знак между левой и правой частью отражает требование модели 3.0, то нажимаем кнопку добавить и аналогичным образом формируем ограничения по оборудованию, а затем по труду.

Рис. 3.24. Составление первого ограничения компьютерной модели.

Кроме сформированных компьютерных ограничений по ресурсам, необходимо создать компьютерный аналог граничных условий, в данном случае они представляют требование не отрицательности искомых переменных. Решатель позволяет записать эти требования в виде векторного неравенства, т.е содержимое ячеек В3:С3 должно быть больше или равняться содержимому соответствующих ячеек В4:С4, в которых мы предусмотрительно записали нули. Поэтому после формирования ограничения по труду нужно нажать кнопку Добавить и в появившемся окне ввести левую и правую части этого векторного неравенства (рис. 3.25). Для смены знака неравенства <= на противоположный знак >= нужно воспользоваться опцией висячего меню.

Рис. 3.25. Составление последнего ограничения компьютерной модели.

Так как формирование ограниченной компьютерной модели на этом завершается, то на панели окна Добавление ограничения (рис. 3.26) следует нажать кнопку ОК. В результате мы получим окончательно заполненное данными компьютерной модели диалоговое окно Поиск решения (см. рис. 3.23). Прежде чем запустить вычислительный процесс для составленной компьютерной модели кнопкой Выполнить на панели окна Поиск решения (см. рис. 3.23), необходимо нажать кнопку Параметры, чтобы вызвать диалоговое окно Параметры поиска решения (рис. 3.27).

Рис. 3.27. Настройка параметров вычислительного процесса.

Как мы видим, в этом окне мы можем менять любые параметры поиска решения, влияющие на характер прохождения вычислительного процесса.

Нажав кнопку ОК на панели окна Параметры поиска решения, снова вернемся в окно Поиск Решения (см.рис. 3.23), откуда, наконец, запустим вычислительный процесс поиска оптимальной производственной программы кнопкой Выполнить. После удачного завершения вычислительного процесса на экране появится окно Результаты поиска решения с текстом сообщения, который можно прочитать на рис. 3.28.

Рис. 3.28. Вид окна Результаты поиска решения.

Текст сообщения может быть другим только по двум причинам: либо для текущих исходных данных задача не имеет оптимального решения, либо компьютерный аналог математической модели составлен с ошибками.

Далее сохраняем найденное решение и получаем ответ. (рис 3.29).

Рис. 3.29. Найденное оптимальное решения прямой задачи, сохраненное на рабочем листе.

Так же можно сделать данное решение в виде отчета, только в этом случае в окне Результаты поиска решения (см. рис. 3.28) выбираем раздел Устойчивость и получаем документ с отчетом "Отчет по устойчивости 1" (рис. 3.30).

рис.3.30. Вид окна "Отчет по устойчивости 1".

Таким образом, на основании проведенного компьютерного анализа можно составить полное табличное описание функции предельной эффективности сырья для данной фирмы, которое полностью совпадает с таблицей, полученной ручными расчетами.

Date: 2015-07-27; view: 2521; Нарушение авторских прав