7_Задача оптимизации_Поиск решения

ПРАКТИЧЕСКАЯ РАБОТА 5

Решение задач оптимизации с помощью Excel

Поиск решения

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

Целевая ячейка - это ячейка, для которой нужно найти максимальное, минимальное или заданное значение. Она должна содержать формулу, зависящую от изменяемых ячеек (прямо или косвенно). Пока не будет найдено решение, программа подбирает значения изменяемых ячеек.

Изменяемые ячейки - это ячейки, от которых зависит значение целевой ячейки.

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

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

Средство Поиск решения относится к надстройкам. Надстройка - вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей и может отсутствовать в меню. Чтобы использовать эту надстройку в Excel, необходимо сначала загрузить ее.

1. Щелкните значок Кнопка Microsoft Office , а затем щелкните Параметры Excel.

2. Выберите команду Надстройки, а затем в окне Управление выберите пункт Надстройки Excel.

3. Нажмите кнопку Перейти.

4. В окне Доступные надстройки установите флажок Поиск решения и нажмите кнопку ОК.

Если Поиск решения отсутствует в списке поля Доступные надстройки, чтобы найти надстройку, нажмите кнопку Обзор.

В случае появления сообщения о том, что надстройка для поиска решения не установлена на компьютере, нажмите кнопку Да, чтобы установить ее.

5. После загрузки надстройки для поиска решения на вкладке ДАННЫЕ в группе Анализ становится доступна команда Поиск решения.

Задача

Завод производит электронные приборы трех видов (прибор А, прибор В и прибор С), используя при сборке микросхемы трех типов (1, 2 и 3). Стоимость изготовленных приборов одинакова.

Ежедневно на склад завода поступает микросхемы 1 – 400 штук, микросхемы 2 – 500 штук, микросхемы 3 - 500 штук.

Каково оптимальное соотношение дневного производства приборов различного типа, если производственные мощности завода позволяют использовать запас поступивших микросхем полностью?

Расход микросхем задается следующей таблицей:

1. Запустите программу Excel (ПУСК - Программы - Microsoft Office - Excel 2007), откроется рабочая книга. Сохраните, пока еще пустую книгу, на рабочем диске под именем Поиск решения.

2. Переименуйте Лист1. Для этого щелкните правой клавишей мыши по ярлычку Лист1, выберите команду Переименовать и присвойте ему имя Организация производства.

3. Заполните таблицу по образцу. В названиях столбцов введите перенос текста по словам, выбрав команду ГЛАВНАЯ - группа Выравнивание - Перенос текста .

В ячейки В3, В4 и В5 занесите дневной запас комплектующих - числа 400, 500 и 500 соответственно.

4. В ячейки D2, Е2, F2 занесите нули, в дальнейшем значения этих ячеек будут подобраны автоматически.

5. В ячейках диапазона D3:F5 разместите таблицу расхода комплектующих (какое количество микросхем используется для каждого прибора).

6. В ячейках C3:C5 введите формулы для расчета расхода комплектующих по типам микросхем. В ячейке C3 формула будет иметь вид =$D$2*D3+$E$2*E3+$F$2*F3 (количество приборов умножается на количество микросхем, которое используется для производства этого вида прибора). Остальные формулы можно получить методом автозаполнения, то есть протягиванием маркера заполнения по ячейкам (обратите внимание на использование абсолютных и относительных ссылок).

7 В ячейку G2 занесите формулу, вычисляющую общее число произведенных приборов: для этого выделите диапазон D2:F2 и щелкните на кнопке автосуммирования  ГЛАВНАЯ - группа Редактирование - Сумма.

8. Поиск решения. Выберите команду ДАННЫЕ - группа Анализ - Поиск решения. (Solver Parameters) - откроется диалоговое окно.

9. В поле Установить целевую ячейку (Set Target Cell) укажите ячейку, содержащую оптимизируемое значение, которое мы ищем (G2). Установите переключатель Равной максимальному значению (Equal To Мах), так как требуется максимальный объем производства.

10. В поле Изменяя ячейки (By Changing Cells) задайте диапазон (протягиванием мышки) подбираемых параметров – D2:F2.

11. Чтобы определить набор ограничений, щелкните на кнопке Добавить (Add). В диалоговом окне Добавление ограничения (Add Constraint) в поле Ссылка на ячейку (Cell Reference) укажите диапазон C3:C5. В качестве условия задайте <=. В поле Ограничение (Constraint) задайте диапазонB3:B5. Это условие указывает, что дневной расход комплектующих не должен превосходить запасов. Щелкните на кнопке ОК.

12. Снова щелкните на кнопке Добавить (Add). В поле Ссылка на ячейку (Cell Reference) укажите диапазон D2:F2. В качестве условия задайте >=. В поле Ограничение (Constraint) задайте число 0. Это условие указывает, что число производимых приборов неотрицательно. Щелкните на кнопке ОК.

13. Снова щелкните на кнопке Добавить (Add). В поле Ссылка на ячейку (Cell Reference) укажите диапазон D2:F2. В качестве условия выберите пункт цел (int). Это условие не позволяет производить доли приборов. Щелкните на кнопке ОК.

14. Щелкните на кнопке Выполнить (Solve). По завершении оптимизации откроется диалоговое окно Результаты поиска решения (Solver Results).

15. Установите переключатель Сохранить найденное решение (Keep Solver Solution), после чего щелкните на кнопке ОК.

16. Проанализируйте полученное решение. Оказывается, чтобы практически полностью реализовать дневной запас микросхем нужно отказаться от выпуска Прибора 2.

17. Проверьте оптимальность, экспериментируя со значениями ячеек D2:F2. Вы увидите, что расход микросхем каждый день будет неоптимальным. Чтобы восстановить оптимальные значения, можно в любой момент повторить операцию поиска решения (или просто отменить последние операции).

17. Сохраните рабочую книгу Поиск решения.xlsx.

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

_________________________________

4