Поиск решения. Поиск решений является частью блока задач анализа что если

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

Процедуру поиска решения можно использовать для определения значения влияющей ячейки, которое соответствует экстремуму зависимой ячейки — например, фирма производит два вида продукции (товар А и товар Б) по определенной цене, на их производство требуется 4 вида ресурсов (ресурс 1, ресурс 2, ресурс 3, ресурс 4), которые есть в наличие на фирме в определенном количестве (Запас), также имеется информация сколько нужно каждого ресурса на производство единицы продукции, соответственно товара А и товара Б. Нужно найти, то количество товара А и товара Б, которое максимизирует доход (выручку) (см. рис.57).

Функция «Поиск решения» расположена на вкладке Данные в группе Анализ щелкнитеПоиск решения.

Если команда Поискрешения или группа Анализ отсутствует, необходимо загрузить надстройку " Поискрешения".

1. На вкладке Файл выберите команду Параметры, а затем — категорию Надстройки.

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

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

Порядок действий:

1. Составить исходную таблицу и ввести зависимые ячейки Е2: Е6 соответствующие формулы (рис. 57)

2. Выделить оптимизируемую ячейку$Е$6

Рисунок 57. Поиск решения

3. Вызвать функциюПоиск решения.

В диалоговом окнеПараметры поиска решения ( см. рис. 58 )

Указать в окнеОптимизировать целевую функцию адрес ячейки содержащей целевую функцию (Е6).

Указать тип взаимосвязи между целевой ячейкой и решением, установив один из переключателей группыДо: равной: 8 максимум (минимум или указанному значению)

В полеИзменяя ячейки переменнойуказать ячейки $В7: $С$7 -параметры, которые могут изменяться в процессе поиска решения

4. В окне в Соответствии с ограничениями: - ввести ограничения, используя кнопкиДобавить (Изменить, Удалить)

5. Для вычисления нажать кнопкуВыполнить.

КнопкаЗакрыть– сохранение параметров без запуска процесса поиска решения.

6. По окончании поиска решения появляется диалоговое окно Результаты поиска решения. Выбрать один из переключателейСохранить найденные решения или Восстановить исходные значения.

7. ОК

Задание36

Бригада приняла заказ на изготовление 50 единиц продукции П1, 30 единиц продукции П2 и 545 единиц продукции П3. Продукция производится на станках А и В. Для изготовления на станке А единицы продукции П1 требуется 4 единицы времени, А единицы продукции П2 - 4 единицы времени, А единицы продукции П3 –10 единицы времени.; на станке В соответственно–6, 8, 20 единиц времени. Составить математическую модель задачи и на ее основе найти план использования оборудования, т.е. указать, сколько продукции и какого вида следует изготовить на станках А и В, чтобы заказ был выполнен в минимальное время (табл. 31)

Таблица31