Решение задач оптимизации в среде Microsoft Excel

8.1. Цель лабораторной работы

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

8.2. Задания к выполнению лабораторной работы

Решить предложенную преподавателем задачу оптимизации средствами Microsoft Excel 2013.

8.

8.3. Подготовка к работе

Для выполнения работы следует ознакомиться с примером действий для выполнения заданий, рассмотренным в разделе 8.4

8.4. Примеры действий по выполнению заданий к лабораторной работе.

В качестве примера рассмотрим решение следующей задачи оптимизации.

Задача: завод производит электронные приборы трех видов (прибор А, прибор В, прибор С).

Для сборки используются микросхемы трех типов (тип1, тип2, тип3)

Стоимость изготовления приборов одинакова. Ежедневно на склад завода завозят по 500 микросхем типа1 и по 400 типа2 и типа3.

Дано (см. таблицу ниже) количество деталей каждого типа, требуемое для создания одного прибора класса A, класса B и класса C:

	Прибор А	Прибор В	Прибор С
Тип1
Тип 2
Тип 3

Найти максимальное суммарное количество приборов различного вида (А, В и С), которое может произвести завод в день, если производственная мощность завода позволяет использовать запас поступающих микросхем полностью.

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

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

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

1. Запустите программу Excel (Пуск -> Все программы ->Microsoft Office->Microsoft Excel 2013) и откройте книгу examples, созданную ранее.

2. Создайте новый лист, дважды щелкните на его ярлычке и присвойте ему имя «Решение задачи оптимизации».

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

4. В ячейки С1, D1 и Е1 занесите нули — в дальнейшем значения этих ячеек будут подобраны автоматически. В ячейках С1, D1, E1 будут находиться значения, равные количеству приборов соответственно типов А, В, С, производимых в день.

5. В ячейках диапазона С2:Е4 разместите таблицу расхода комплектующих.

	Прибор А	Прибор В	Прибор С
Тип1
Тип 2
Тип 3

6. В ячейках В2:В4 нужно указать формулы для расчета расхода комплектующих по типам. В ячейке В2 формула будет иметь вид =$C$1*C2+$D$1*D2+$E$1*E2, а остальные формулы можно получить методом автозаполнения (обратите вни­мание на использование абсолютных и относительных ссылок).

7. В ячейку F1 занесите формулу, вычисляющую общее число произведенных при­боров: для этого выделите диапазон С1:Е1 и щелкните на кнопке «Автосумма» (Формулы ->Автосумма).

Для последующих действий нам потребуется команда «Поиск решения», но по умолчанию она отключена. Чтобы включить эту команду, необходимо проделать следующие действия:

àНа главной панели инструментов щелкнуть правой кнопкой мыши -> настройка панели быстрого доступа -> надстройки -> поиск решения (нажмите “Перейти”) ->(поставьте галочку напротив “поиск решения”) ->OK.

8. Выберите команду: Данные ->Поиск решения — откроется диалоговое окно Поиск решения.

9. В поле «Установить целевую ячейку» укажите ячейку, содержащую оптимизируемое значе­ние (F1). Установите переключатель Равной максимальному значению (требуется максимальный объем производства).

10. В поле Изменяя ячейки задайте диапазон подбираемых параметров — С1 :Е1.

11. Чтобы определить набор ограничений, щелкните на кнопке «Добавить». В диало­говом окне «Добавление ограничения» в поле «Ссылка на ячейку» укажите диапазон В2:В4. В качестве условия задайте «<=». В поле «Ограничение» задайте диапазон А2:А4. Это условие указывает, что дневной расход комплектующих не должен превосходить запасов. Щелкните на кнопке «ОК».

12. Снова щелкните на кнопке «Добавить». В поле «Ссылка на ячейку» укажите диапазон С1:Е1. В качестве условия задайте >=. В поле «Ограничение» задайте число 0. Это условие указывает, что число производимых приборов неотрицательно. Щелк­ните на кнопке «ОК».

13. Снова щелкните на кнопке «Добавить». В поле «Ссылка» на ячейку укажите диапазон «С1:Е1». В качестве условия выберите пункт «цел». Это условие не позволяет про­изводить доли приборов. Щелкните на кнопке «ОК».

14. Щелкните на кнопке «Найти решение». По завершении оптимизации откроется диало­говое окно «Результаты поиска решения».

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

16. Сохраните рабочую книгу examples.

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

1. Как формулируется задача оптимизации в табличном виде?

2. Как формулируются критерий эффективности и ограничения, которым должно удовлетворять решение?

3. Какие программы Excel используются для нахождения оптимальных значений независимых(управляемых) переменных и соответствующего значения целевой функции?

Литература

1. «Информатика. Базовый курс» под ред. Симоновича С.В. Питер, 2010 г.

2. «Понятный самоучитель Excel 2013» Лебедев А.Н, Питер, 2014г.