ЕXCEL. Поиск решения

В MS Excel существует надстройка Поиск решения (Solver), используя которую можно, в частности, решать задачи линейной и нелинейной оптимизации. Для запуска надстройки Поиск решения в версиях Microsoft Office 2003 и более ранних нужно воспользоваться меню Сервис ® Надстройки ® Поиск решения. В версиях Microsoft Office 2007 и 2010 надстройка Поиск решения находится во вкладке Данные в группе Анализ. Загрузка надстройки для Поиска решения в последних версиях Excel выполняется следующим образом:

1) Выбрать Настройка панели быстрого доступа, выделяем Другие команды и входим в Параметры Excel.

2) Выбираем команду Надстройки, а затем в окне Управление надстройками Microsoft Office из Неактивных надстроек приложений выделяем Поиск решений. ОК.

Если в окне Надстройки в Excel 2007-2010 (или в меню Сервис ® Надстройки в более ранних версиях Excel) нет элемента Поиск решения, то необходимо доустановить эту надстройку Excel (при установке MS Office по умолчанию надстройка Поиск решения не устанавливается).

Далее мы будем приводить примеры решения задач в надстройке Поиск решения из MS Excel 2010.

Основные параметры диалогового окна Поиск решения:

· Установить целевую ячейку;

· Изменяя ячейки;

· Ограничения.

Для решения задачи необходимо:

1. указать адреса ячеек, в которые будет помещен результат решения (установить Изменяемые ячейки);

2. ввести исходные данные;

3. ввести зависимость для целевой функции;

4. ввести зависимости для ограничений;

5. запустить команду Поиск решения;

6. назначить ячейку для целевой функции (установить целевую ячейку);

7. ввести ограничения;

8. ввести параметры для решения задачи ЛП.

Пример 3. Планирование выпуска продукции пошивочного предприятия (задача о костюмах).

Намечается выпуск двух видов костюмов - мужских и женских. На женский костюм требуется 1 м шерсти, 2 м лавсана и 1 человеко-день трудозатрат, на мужской – 3,5 м шерсти, 0,5 м лавсана и 1 человеко-день трудозатрат. Всего имеется 350 м шерсти, 240 м лавсана и 150 человеко-дней трудозатрат.

Требуется определить, сколько костюмов каждого вида необходимо сшить, чтобы обеспечить максимальную прибыль. Если прибыль от реализации женского костюма составляет 10 ден. ед., а от мужского – 20 ден. ед. При этом следует иметь в виду, что необходимо сшить не менее 60 мужских костюмов.

Решение. Обозначим:

х₁ - число женских костюмов;

х₂ - число мужских костюмов.

Математическая модель задачи примет вид:

Целевая функция:

Е = 10x₁ + 20x₂

при ограничениях:

1. Указать адреса ячеек, в которые будет помещен результат решения. В ячейки А2:В2 будут помещены значения х₁ и х₂, оптимальное значение целевой функции - в ячейке С3.

2. Введем исходные данные. Исходные данные задачи показаны в таблице.