Порядок и методика выполнения работы

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

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. Диалоговое окно Результат подбора параметра

Диспетчер сценариев

При проведении вариантных расчетов полезно сохранять промежуточные результаты и выводить их в виде структурной или сводной таблицы для последующего ее анализа. Это можно проделать с помощью Диспетчера сценариев (Scensrio Manager), диалоговое окно которого вызывается командами меню Сервис-Сценарии (Tools- Scenarios)(рис. 10.4).

Нажав кнопку Добавить (Add), мы переходим в диалоговое окно Добавление сценария (Add Scenario) (Рис. 10.5), в котором указываются:

· имя сценария;

· изменяемые ячейки (одна или диапазон);

· комментарии;

· запрет изменений;

· скрыть или нет сценарий.

В следующем окне Значения ячеек сценария (Scenario Values) указываются значения изменяемых клеток (рис. 10.6).

Рис. 10.4. Диалоговое окно Диспетчер сценариев

Рис. 10.5. Диалоговое окно Добавление сценария

Рис. 10.6. Диалоговое окно Значения ячеек сценария

Нажав кнопку Отчет (Summary), попадаем в окно Отчет по сценарию, в котором необходимо указать тип отчета: Структура или Сводная таблица (о сводных таблицах смотрите следующую главу), а также Ячейки результата (рис. 10.7). Нажатие клавиши OK позволяет выдать структурированную итоговую или сводную таблицу (рис. 10.8).

Рис. 10.7. Диалоговое окно Отчет по сценарию

В столбце D таблицы структуры сценария представлены текущие значения цены и объема производства. При этом прибыль составит 712 млн руб. В следующем столбце раскрыт сценарий «Цена». Если увеличить цену за один стол до 870 тыс. рублей, то прибыль составит 1972 млн рублей.

Рис. 10.8. Рабочий лист со сценариями решения задачи

В столбце F приведен сценарий «Объем производства». Если увеличить объем производства столов с 18000 до 20000, то прибыль составит 2312 млн руб.

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

Линейная оптимизация

Пусть предприятие (например, мебельная фабрика) производит столы и стулья. Расход ресурсов на их производство и прибыль от их реализации представлены ниже.

	СТОЛЫ	СТУЛЬЯ	ОБЪЕМ РЕСУРСОВ
Расход древесины на изделие, м**3	0.5	0.04
Расход труда, чел-час		0.6
Прибыль от реализации единицы изделия, тыс. руб.

Кроме того, на производство 80 столов заключен контракт с муниципалитетом, который, безусловно, должен быть выполнен. Необходимо найти такую оптимальную производственную программу, чтобы прибыль от реализации продукции была максимальной.

Пусть x₁ - количество столов;

х₂ - количество стульев.

Тогда система ограничений и целевая функция запишутся следующим образом:

0.5x₁+0.04х₂ <= 200 (ограничения по древесине);

12x₁+0.6x₂<=1800 (ограничения по труду);

180x₁+20x₂®max (целевая функция);

x₁>=0; x₂>=0;

x₁, x₂ - целые числа.

Для решения задачи в Excel запишем ее виде, представленном на рис. 10.9.

Для решения задачи вызовем меню Сервис-Поиск решения (Tools-Salver).

В открывшемся диалоговом окне Поиск решения (рис. 10.10) укажем:

· адрес целевой ячейки (в нашем примере D5);

· диапазон искомых ячеек (А2:А3);

· ограничения: А2>=80

А2:А3=целое

А2:АЗ>=0

B2<=D2

B3<=D3

Рис. 10.9. Запись исходных данных для решения
задачи линейной оптимизации

Рис. 10.10. Диалоговое окно Поиск решения

Добавления, изменения и удаления ограничений производятся с помощью кнопок Добавить, Изменить, Удалить (Add, Change, Delete).

Для нахождения оптимального решения нажмем кнопку Выполнить (Solve). В результате в таблице по лучим значение целевой функции - 42400 млн руб. при x1 = 80 и х2= 1400 (рис. 10.11).

Рис. 10.11. Рабочий лист с найденным оптимальным решением

Диалоговое окно Результаты поиска решения позволяет (рис. 10.12):

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

· восстановить первоначальные значения;

· сохранить сценарий;

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

Рис. 10.12. Диалоговоеокно Результаты поиска решения

Если щелкнуть по кнопке OK, то на месте исходной таблицы получим таблицу с найденными оптимальными значениями (см. рис. 10.11).

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

Порядок и методика выполнения работы

1. Изучить метод «Подбор параметра» на приведённом примере.

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

3. Изучить назначение и использование Диспетчера сценариев.

4. Подготовить сценарий в соответствии с вариантом задания, выданным преподавателем.

5. Изучить метод Линейной оптимизации.

6. Провести оптимизацию таблицы в соответствии с вариантом задания, выданным преподавателем.

Содержание отчета

1. Цель работы.

2. Привести распечатки первоначальных таблиц и таблиц с результатами оптимизации.

Контрольные вопросы

1. В чём заключается суть метода «Подбор параметра»?

2. Какими способами можно определить подбор параметра?

3. Назначение и использование Диспетчера сценариев.

4. Как осуществить линейную оптимизацию?