Цель задания: освоить правила работы с надстройкой Excel – Поиск решения
Постановка задачи:
Требуется определить оптимальные размеры объемов выпуска ассортимента продукции, обеспечивающие максимальную суммарную прибыль, с учетом имеющихся ресурсов на складе, которые вовлечены в производство.
Исходные данные оптимизационной задачи сведены в таблице.
Создание модели в Excel
Открываем табличный процессор Excel.
Подготавливаем начальную таблицу для размещения описаний задачи, исходных данных, ограничений, коэффициентов целевой функции, место для проведения вычислений и сохранения результатов. Столбец D введен для занесения результатов в ячейки D5:D10. В этих ячейках будут отображаться результаты вычислений, т.е. сколько на самом деле будет задействовано единиц каждого вида ресурса для выпуска всей номенклатуры продукции. Добавляемв таблицу новые обозначения, которые понадобятся для ввода начальных значений и вывода результатов. Для этой цели:
- в строке 12 (ячейки E12:G12), вводим коэффициенты для целевой функции ее название - «Прибыль от реализации единицы продукции»;
- в строке 13 создём заголовок – «Значения Xj при решении задачи», ячейки E13:G13 понадобятся для ввода формул;
- ячейку E14 выделяем, в ней будет формироваться результат, поэтому в строке 14 делаем запись – «Конечная прибыль от реализации продукции», это и есть значение целевой функции.
Вводим формулы в таблицу с начальными данными. Для решения оптимизационной задачи методом линейного программирования необходимо математическую модель записать в терминах Excel, т.е. в определенные ячейки предварительно необходимо ввести формулы.
2. Работа с надстройкой Excel – Поиск решения
- Подключение: Кнопка «Office» в левом верхнем углу окна программы – Параметры Excel – Надстройки – Перейти, и ставим отметку против строки Поиск решения.
- Выделяем курсором ячейку E14 с целевой функцией.
- Выбираем команду в меню Данные - Поиск решения.
- В открывшемся диалоговом окне – Поиск решения, заполняем окна: Изменяя ячейки и Ограничения (можно проверить в диалоговом окне факт установки курсора на целевой ячейке, если требуется, то ее положение можно изменить). На рисунке ниже показано всплывающее диалоговое окно Поиск решения, в котором проведены все перечисленные подготовительные действия.
- Установливаем диапазон ячеек в строке всплывающего окна: Изменяя ячейки, в которых будет отображаться результат с количеством номенклатуры Xj изделий. В рассматриваемом примере, это будут ячейки E13:G13, которые должны быть фиксированными (перед координатами ячеек ставится знак $).
- Отмечаем селекторную кнопку: Равной максимальному значению, т.к. определяется максимальное использование ресурсов.
- В окно с наименованием Ограничения последовательно вводим все ограничения для уравнений модели. В данном примере ячейки C5:C10 содержат количество деталей на складе, которые потребуются для выпуска всей номенклатуры продукции, а в ячейки D5:D10 были внесены формулы модели по каждому виду комплектующей, следовательно, суммарное количество используемых деталей не должно превышать величину, указанную в правой части уравнения. Для ввода ограничений, необходимо нажать на кнопку 
. После выполненного действия будет открыто диалоговое окно с наименованием Добавление ограничений. В окне видно, что вычисляемое значение в ячейке D5 должно быть меньше или равно установленной величины в ячейке C5.
- Вводим ограничения на выпуск номенклатуры продукции в ячейки E13:G13 (в примере всего три вида продукции). Показано диалоговое окно для добавления ограничений, в котором указано, что вычисляемое значение в ячейке G13 должно быть более 1 единицы (это условие записано в исходной таблице для изделия – Компьютеры).
Вводим ограничения на форму представления результатов. В данной постановке задачи подразумевается, что количество изделий не может быть дробной величиной, а должны отображаться только целыми числами, следовательно, при выполнении расчетов, это обстоятельство необходимо учитывать.
- Проводим вычисления, для чего в диалоговом окне Поиск решения, нажать на кнопку 
. В том случае, если все данные введены правильно, а в вычисляемых ячейках существуют формулы, описанные в данной методике, то появится диалоговое окно с наименованием: Результаты поиска решения.
Программа формирует три типа отчетов: Результаты, Устойчивость и Пределы. Если отметить любой из них или все вместе, а затем вызвать, то можно провести анализ исходных данных и конечных результатов.
Результат проведенных вычислений по заданным параметрам с добавлением дополнительных граф, в которых отражается информация о тех потерях, которые получит предприятие производитель.
Отчет с наименованием Результаты, который формируется системой, состоит из трех таблиц. При анализе результатов достаточно взглянуть на столбец Статус третьей таблицы. В том случае, если ресурс был использован полностью, тогда система записывает в соответствующую ячейку текстовое сообщение «Связанное», в противном случае появляется текст «Не связан». Следовательно, получен ответ о недоиспользованных ресурсах, что позволяет внести новые ограничения и решить задачу заново.
