Подбор параметра

Лабораторная работа № 10EXCEL 7.0.Оптимизация решений в Excel

Цель работы: приобретение навыков работы по оптимизации решений в Excel.

Программа работы

1. Изучение метода оптимизации решений в Excel «Подбор параметра».

2. Изучение диспетчера сценариев в. Excel

3. Изучение линейной оптимизации в Excel

Оптимизация решений в Excel

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

Подбор параметра

Подбор параметра - простейший метод нахождения оптимального желаемого решения за счет изменения одного из параметров. Суть этого метода лучше всего рассмотреть на примере.

Пример. Пусть предприятие выпускает один вид продукции, например столы. Данные о реализации продукции и затратах на ее производство имеют значения, представленные на рис. 10.1.

При этом прибыль составит 712 млн рублей. Допустим, что мы поставили себе цель повысить уровень прибыли до 2 млрд руб. Это можно осуществить тремя способами:

· повысить цену;

· увеличить объем производства;

· снизить затраты и прежде всего заработную плату.

Попробуем найти такую цену реализации, чтобы прибыль была 2 млрд руб.

Воспользуемся меню Сервис-Подбор параметра (Tools-Goal Seek...). В результате откроется диалог Подбор параметра (Goal Seek...) (рис. 10.2).

Рис. 10.1. Исходные данные для оптимизации решений
методом подбора параметра

Рис.10.2. Диалоговое окно Подбор параметра

В первом поле необходимо установить адрес целевой ячейки, во втором - ее значение. В третьем поле необходимо указать адрес изменяемой ячейки, то есть той ячейки, в которой будет осуществляться подбор параметра. Вставим в соответствующие поля адрес С10, 2000 и С3 и нажмем ОК. В результате мы получим сообщение, что решение найдено и, нажав еще раз OK, мы в исходной таблице получим значение прибыли в 2 млрд руб. в случае, если цену реализации повысим до 871.556 тыс. руб.

Однако не все задачи могут быть решены путем подбора параметра. Решение не будет найдено, если изменяемая и целевая ячейки логически связаны. При такой сходимости в окне Результат подбора параметра (рис. 10.3) можно установить Шаг (Step) и Паузу (Pause) и с их помощью осуществлять процесс подбора параметра.

Подбор параметра можно выполнять и на диаграмме. Для этого необходимо:

· на основе табличных данных построить диаграмму;

· выделить один из столбиков на диаграмме, в результате чего он будет обрамлен восемью маленькими квадратиками;

· поставить курсор на верхний средний квадратик, и при помощи левой клавиши мыши увеличивать или уменьшать высоту столбика.

Как только клавиша будет отпущена, то при первом выполнении данной операции откроется диалоговое окно Подбор параметра (см. рис. 10.2), а затем - Результат подбора параметра (см. рис. 10.3). При этом данные в целевой ячейке также будут изменяться.

В последующем подбор параметра с помощью диаграммы будет происходить без открытия диалоговых окон.

Рис.10.3. Диалоговое окно Результат подбора параметра