Создание модели в Excel
Лабораторная работа 6
Инструментальные средства MS Excel в задачах оптимизации
Цель: освоить правила работы с надстройкой Excel – Поиск решения
Постановка задачи:
Требуется определить оптимальные размеры объемов выпуска ассортимента продукции, обеспечивающие максимальную суммарную прибыль, с учетом имеющихся ресурсов на складе, которые вовлечены в производство.
Формализация постановки задачи начинается с ввода обозначений для будущей модели
- пусть намечается выпуск 3-х видов продукции: П1, П2, П3, (j=1, …, 3);
- для производства продукции используется шесть видов деталей (ресурсов) (i=1, … , 6);
- известны нормы расхода деталей (ресурсов) на изготовление единицы каждого вида продукции aij [ед.детал./ед.прод.];
- каждый вид деталей (ресурса) ограничен bi [ед.детал.] наличием на складе;
- предполагается, что от реализации единицы продукции, предприятие получает прибыль Pj [ден.ед./ед.прод.].
Исходные данные оптимизационной задачи сведены в таблице 1.
Таблица 1 с исходными данными
| Наименование деталей на складе | Наличие на складе | Вид продукции | ||
| Телевизор | DVD плейер | Компьютер | ||
| Шасси | ||||
| Динамик | ||||
| Блок питания | ||||
| Монитор | ||||
| Электронная плата | ||||
| Процессор | ||||
| Прибыль от реализации единицы продукции (руб.): |
Введем обозначения для построения математической модели:
ЧерезXj – обозначим неизвестное, которое показывает возможное количество выпускаемой продукции j-ого вида, при использовании (aij) существующих норм единицы деталей (ресурса) на выпуск определенного вида продукции, при ограничениях на общее использование ресурса bi. Добавим дополнительное условие, которое показывает, что изготовляемая продукция должна быть выпущена всех видов. Это значит, что все переменные Xj должны быть не менее 1 (X1 ? 1, X2 ? 1,X3 ? 1). Отметим, что, если не имеет значение, какие виды продукции следует выпускать при поиске оптимального использования ресурсов, то в ограничениях следует указать не 1, а 0. Обозначив через Z величину суммарной прибыли при организации выпуска продукции, можно записать следующую систему уравнений:
Z = 450·X1 + 125·X2 + 340·X3 ? max
| 1?X1 + 0?X2 + 1?X3 ? 450 | Шасси |
| 4?X1 + 2?X2 + 1?X3 ? 340 | Динамик |
| 1?X1 + 1?X2 + 1?X3 ? 500 | Блок питания |
| 1?X1 + 0?X2 + 1?X3 ? 125 | Монитор |
| 2?X1 + 1?X2 + 1?X3 ? 270 | Электронная плата |
| 0?X1 + 1?X2 + 1?X3 ? 470 | Процессор |
| X1 ? 1, X2 ? 1, X3 ? 1 | Ограничения для выпуска продукции |
Создание модели в Excel
1. Открыть табличный процессор Excel.
Подготовить начальную таблицу для размещения описаний задачи, исходных данных, ограничений, коэффициентов целевой функции, место для проведения вычислений и сохранения результатов. Начальная таблица, как она будет выглядеть в Excel, представлена на рис. 1. Столбец D введен для занесения результатов в ячейки D5:D10. В этих ячейках будут отображаться результаты вычислений, т.е. сколько на самом деле будет задействовано единиц каждого вида ресурса для выпуска всей номенклатуры продукции. Добавить в таблицу новые обозначения, которые понадобятся для ввода начальных значений и вывода результатов. Для этой цели:
· в строке 11 (ячейки E11:G11), ввести коэффициенты для целевой функции ее название - «Прибыль от реализации единицы продукции»;
· в строке 12 создать заголовок – «Значения Xj при решении задачи», ячейки E12:G12 понадобятся для ввода формул;
· ячейку E13 можно выделить, в которой будет формироваться результат, поэтому в строке 13 сделана запись – «Конечная прибыль от реализации продукции», это и есть значение целевой функции.
Рис. 2. Начальная таблица в Excel
1. Ввести формулы в таблицу с начальными данными. Для решения оптимизационной задачи методом линейного программирования необходимо математическую модель записать в терминах Excel, т.е. в определенные ячейки предварительно необходимо ввести формулы. В таблице 2 указаны координаты ячеек для рассматриваемого примера, математическая запись уравнений и формулы, которые введены в соответствующие ячейки в табличном процессоре Excel.
Таблица 2. Перечень формул для установки в ячейках таблицы Excel
| Ячейка | Математическая запись | Формула |
| D5 | 1?X1 + 0?X2 + 1?X3 | =$E$13*E5+$F$13*F5+$G$13*G5 |
| D6 | 4?X1 + 2?X2 + 1?X3 | =$E$13*E6+$F$13*F6+$G$13*G6 |
| D7 | 1?X1 + 1?X2 + 1?X3 | =$E$13*E7+$F$13*F7+$G$13*G7 |
| D8 | 1?X1 + 0?X2 + 1?X3 | =$E$13*E8+$F$13*F8+$G$13*G8 |
| D9 | 2?X1 + 1?X2 + 1?X3 | =$E$13*E9+$F$13*F9+$G$13*G9 |
| D10 | 0?X1 + 1?X2 + 1?X3 | =$E$13*E10+$F$13*F10+$G$13*G10 |
| E11 | X1 ? 1 | >=1 |
| G11 | X2 ? 1 | >=1 |
| F11 | X3 ? 1 | >=1 |
| E14 | 450·X1 + 125·X2 + 340·X3 | =E12*E13+F12*F13+G12*G13 |
2. Работа с надстройкой Excel – Поиск решения
· Подключение: Меню – Сервис – Поиск решения, если такая строка отсутствует, то необходимо нажать на строку с командой: Надстройки, и поставить отметку против строки Поиск решения (рис. 2).
Рис. 2. Подключение инструмента для решения задач оптимизации
· Ввести начальные значения Xj в ячейки E13:G13. Например, по двадцать единиц каждого вида изделия.
· Выделить курсором ячейку E14с целевой функцией.
· Выбрать команду в менюСервис-Поиск решения.
· В открывшемся диалоговом окне– Поиск решения, заполнить окна: Изменяя ячейки и Ограничения (можно проверить в диалоговом окне факт установки курсора на целевой ячейке, если требуется, то ее положение можно изменить). На рис. 3 показано всплывающее диалоговое окно Поиск решения, в котором проведены все перечисленные подготовительные действия.
Рис. 3. Всплывающее диалоговое окно с введенными данными
· Установить диапазон ячеек в строке всплывающего окна: Изменяя ячейки, в которых будет отображаться результат с количеством номенклатуры Xj изделий. В рассматриваемом примере, это будут ячейки E13:G13, которые должны быть фиксированными (перед координатами ячее ставится знак $).
· Отметить селекторную кнопку: Равной максимальному значению, т.к. определяется максимальное использование ресурсов.
· В окно с наименованием Ограничения последовательно ввести все ограничения для уравнений модели. В данном примере ячейки C5:C10 содержат количество деталей на складе, которые потребуются для выпуска всей номенклатуры продукции, а в ячейки D5:D10 были внесены формулы модели по каждому виду комплектующей, следовательно, суммарное количество используемых деталей не должно превышать величину, указанную в правой части уравнения. Для ввода ограничений, необходимо нажать на кнопку 
. После выполненного действия будет открыто диалоговое окно с наименованием Добавление ограничений, которое показано на рис. 4. В окне видно, что вычисляемое значение в ячейке D5 должно быть меньше или равно установленной величины в ячейке C5.
Рис. 4. Диалоговое окно для ввода ограничений
· Если требуется ввести еще ограничения, то нажать на кнопку , в противном случае нажать на кнопку 
.
· Ввести ограничения на выпуск номенклатуры продукции в ячейки E13:G13 (в примере всего три вида продукции). Так, в качестве примера, на рис. 5 показано диалоговое окно для добавления ограничений, в котором указано, что вычисляемое значение в ячейке G13 должно быть более 1 единицы (это условие записано в исходной таблице для изделия – Компьютеры).
Рис. 5. Диалоговое окно с ограничением на выпускаемую продукцию
· Ввести ограничения на форму представление результатов. В данной постановке задачи подразумевается, что количество изделий не может быть дробной величиной, а должны отображаться только целыми числами, следовательно, при выполнении расчетов, это обстоятельство необходимо учитывать. На рис. 6 показано диалоговое окно для добавления ограничений, в котором для ячейки E13 (в ней отображается количество единиц изделия), установлено условие ‘цел’, что означает целочисленное решение. В раскрывающемся списке выбирается необходимое условие.
Рис. 6. Установка условия целочисленного решения в диалоговом окне
· Установить параметры для оптимизационной задачи, для чего в диалоговом окне: Поиск решения, нажать на кнопку 
. После того, как откроется диалоговое окно с наименованием Параметры поиска решения, представленное на рис. 7.
Рис. 7. Диалоговое окно для установки параметров поиска решения
· В окне установить пометку: Линейная модель, и закрыть кнопкой ОК.
· Провести вычисления, для чего в диалоговом окне Поиск решения, нажать на кнопку 
. В том случае, если все данные введены правильно, а в вычисляемых ячейках существуют формулы, описанные в данной методике, то появится диалоговое окно с наименованием: Результаты поиска решения, которое представлено на рис. 8.
Рис. 8. Диалоговое окно с сообщением о завершении поиска оптимального решения по поставленной задаче
Программа формирует три типа отчетов: Результаты, Устойчивость и Пределы. Если отметить любой из них или все вместе, а затем вызвать, то можно провести анализ исходных данных и конечных результатов, об этом будет сказано ниже. В том случае, если в окне Результаты поиска решения появится сообщение: «Ошибка в модели. Проверьте правильность значений в ячейках и ограничениях», то отчеты получить невозможно, а следует открыть окно Поиск решения (рис. 4), и проверить правильность установки знаков ограничений, наименования ячеек, в которых должна быть вычислена целевая функция и установлены начальные значения изменяемых ячеек. На рисунке 9 показан лист Excel, который содержит исходные данные и результаты решения оптимизационной задачи методом линейного программирования по заданным начальным значениям и тем условиям, которые были установлены, в соответствии с постановкой задачи.
Рис. 9. Результаты решения оптимизационной задачи методом линейного программирования