Общие принципы решения оптимизационных моделей процессором MS Excel

Общие принципы решения оптимизационных моделей процессором MS Excel В настоящее время все популярные версии табличных процессоров включают встроенные средства решения задач оптимизации. Не является исключением и пакет прикладных программ MS Excel, представляющий пользователю специальную надстройку Поиск решения. С ее помощью можно быстро определить оптимальный план производства продукции при ограниченных ресурсах, обеспечивающий максимизацию одних величин (например, прибыли) или минимизацию других (например, расходов). Таким образом, надстройка Поиск решения позволяет анализировать задачи трех типов: линейные, нелинейные и целочисленные. Принцип решения оптимизационных задач рассмотрим на примере. Пример 1. Кондитерская фабрика в Покрове выпустила пробную партию новых видов шоколада «Восхищение» и «Лакомка». Для изготовления этого шоколада было закуплено высококачественное сырье, расход которого на производства шоколада приведен в таблице Ресурсы Норма расхода ресурсов на 1кг шоколада Запас ресурсов Восхищение Лакомка Какао-бобы 4 2 60 Масло 4 1 40 Сахар 2 1 60 Стоимость 1 кг (ден.ед.) 100 45 Проанализировав количество заявок на покупку пробной партии шоколада, было решено шоколада «Лакомка» не выпускать более 25 кг. Определить в каком объеме следует выпустить шоколад, чтобы доход от его реализации был максимальным. Построим математическую модель задачи. Для этого надо определить переменные задачи, целевую функцию и ограничения, которым удовлетворяют переменные. Обозначим через и – количество шоколада каждого вида, планируемое к продаже. Тогда математическая модель задачи будет иметь вид Создадим аналог этой модели в среде MS Excel. Для этого на лист, который назовем «Оптимальное производство» занесем все исходные данные, сформировав две таблицы, как показано на рисунке В ячейки В2, С2 введем начальный план выпуска шоколада, здесь он задан единицами, поскольку при решении задач от начальных значений переменных не зависит ни оптимальное решение, ни время его поиска. В ячейки В3, С3 запишем стоимость одного килограмма шоколада и на основании этих двух строк подсчитаем значение целевой функции, а именно суммарную прибыль от реализации килограмма шоколада «Восхищение» и килограмма шоколада «Лакомка». Для этого воспользуемся функцией СУММПРОИЗВ. Для того чтобы воспользоваться этой функций, установим курсор в ячейку D2 и выберем в меню вкладку Формулы, а далее Математические. В появившемся окне выбираем искомую функцию СУММПРОИЗВ. Для формирования суммарной прибыли заполним поле Массив 1, выделив ячейки B2, C2 курсором, а для поля Массив 2, ячейки B3, C3, как показано на рисунке. После нажатия кнопки ОК, в ячейке D2 увидим суммарную прибыль, полученную от реализации килограмма шоколада «Восхищение» и килограмма шоколада «Лакомка». Другими словами, значение целевой функции при и . Теперь аналогичным образом, отразим во второй таблице суммарный расход ингредиентов для производства шоколада. Таким образом, получим следующие формулы: – в ячейке D7 реализована функция СУММПРОИЗВ(В2:С2;В7:С7); – в ячейке D8 реализована функция СУММПРОИЗВ(В2:С2;В8:С8); – в ячейке D9 реализована функция СУММПРОИЗВ(В2:С2;В9:С9). В этих ячейках указан суммарный расход какао-бобов, масла и сахара для любого набора шоколада. Отдельной строкой запишем максимальный спрос на шоколад «Лакомка» для того, чтобы отразить эту информацию при построении компьютерной модели задачи. Для поиска оптимального решения в меню выберем вкладку Данные и в этой вкладке активизируем надстройку Поиск решения. Рассмотрим поподробнее процесс создания компьютерной модели задачи. В поле Оптимизировать целевую ячейку должен находиться адрес ячейки содержащие суммарную прибыль, а именно D2. Содержимое этой ячейки можно максимизировать, минимизировать или для нее можно задать какое-либо постоянное значение. В рамках рассматриваемой задачи на поиск максимальной прибыли, выделяем значение Максимум. Активизировав поле Изменяя ячейки переменных, с помощью курсора вводим ячейки В2:С2, которые отвечают за оптимальное количество шоколада. Значения этих ячеек будут изменяться в процессе поиска оптимального решения. Следующим этапом опишем основные ограничения рассматриваемой задачи. Для этого активизируем поле В соответствии с ограничениями, нажав кнопку Добавить. Эта кнопка служит для отображения окна Добавление ограничения. В поле Ссылка на ячейки запишем ячейку содержащую расход какао-бобов на килограмм шоколада «Восхищение» и килограмм шоколада «Лакомка», то есть ячейку D7. В поле Ограничение запишем общие запасы какао-бобов на фабрике, то есть ячейку Е7. Согласно построенной модели расход ресурса не превосходит его запас, следовательно, неравенство формирует знак «». Аналогично опишем ограничения соответствующие маслу и сахару. Отдельно создадим условие, связанное с ограничением спроса на шоколад «Лакомка». Активизируем условие Сделать переменные без ограничений неотрицательными и выберем метод решения задачи. Для решения линейных задач программа предлагает воспользоваться симплекс-методом. Для выбора этого метода нужно воспользоваться опцией висячего меню. В результате заполнения всех полей и внесения всех ограничений в диалоговое окно Поиск решений задача примет вид Запустим вычислительный процесс поиска оптимального решения с помощью кнопки Найти решение. Результаты поиска решения приведены на рисунке. В ячейках В2, С2 найдено оптимальное производство шоколада , , то есть фабрике необходимо реализовать, с целью получения максимальной прибыли в размере 1400 ден.ед. (ячейка D2), 5 кг шоколада «Восхищения» и 20 кг шоколада «Лакомка».