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

Лабораторная работа №11

Для решения задач оптимизации в Microsoft Excel предназначена надстройка Поиск решения.

Постановка задачи

В общем виде задача оптимизации ставится следующим образом: найти оптимальное (максимальное или минимальное) значение функции

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

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

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

Если функции Решение задач оптимизации средствами Microsoft Excel - student2.ru и Решение задач оптимизации средствами Microsoft Excel - student2.ru являются линейными, т. е.

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

Решение задач оптимизации средствами Microsoft Excel - student2.ru ,

то такая задача называется задачей линейного программирования.

Подготовка блока данных

1. На рабочем листе отвести блок данных под изменяемые ячейки, т. е. ячейки для хранения переменных Решение задач оптимизации средствами Microsoft Excel - student2.ru . В результате решения задачи в этих ячейках появятся искомые значения Решение задач оптимизации средствами Microsoft Excel - student2.ru .

2. В отдельную ячейку ввести формулу для целевой функции Решение задач оптимизации средствами Microsoft Excel - student2.ru . Эта ячейка называется целевой.

3. отдельные ячейки ввести формулы для левой части ограничений Решение задач оптимизации средствами Microsoft Excel - student2.ru .

Запуск программы «Поиск решения»

1. На вкладке Данные в группе Анализ выбрать команду Поиск решения.

2. В поле Установить целевую ячейку ввести ссылку на целевую ячейку.

3. В поле Изменяя ячейки ввести ссылку на диапазон изменяемых ячеек.

4. Для задания ограничений в группе Ограничениящелкнуть по кнопке Добавить.

В открывшемся диалоговом окне выполнить следующие действия:

- в поле Ссылка на ячейку ввести ссылку на ячейку с формулой, определяющей первое ограничение ( Решение задач оптимизации средствами Microsoft Excel - student2.ru );

- во втором поле выбрать оператор ограничения (>, <, = и т.д);

- в поле Ограничение ввести значение ограничения Решение задач оптимизации средствами Microsoft Excel - student2.ru

5. Для задания следующего ограничения щелкнуть по кнопке Добавить и повторить операции пункта 4.

6. Когда все ограничения будут заданы, щелкнуть по кнопке ОК, чтобы вернуться в диалоговое окно Поиск решения.

7. Изменять и удалять ограничения можно с помощью кнопок Изменить и Удалить.

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

Если известно, что решаемая задача линейная, то следует включить режим Линейная модель: процесс решения значительно ускорится.

Для возврата в диалоговое окно Поиск решения щелкнуть по кнопке ОК.

9. Для инициализации процедуры поиска решения щелкнуть по кнопке Выполнить.

Полученные результаты будут выведены на рабочий лист.

После завершения процедуры решения в диалоговом окне Результаты поиска решения можно выполнить один из следующих вариантов:

- сохранить найденное решение или восстановить исходные значения на рабочем листе;

- сохранить параметры поиска решения в виде модели;

- сохранить решение в виде сценария;

- просмотреть любой из встроенных отчетов.

Сохранение модели

Текущие установочные параметры для поиска решения можно сохранить в виде модели.

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

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

Чтобы впоследствии загрузить модель, надо щелкнуть по кнопке Загрузить модель в диалоговом окне Параметры поиска решения.

Сохранение сценария

Найденные решения (значения изменяемых ячеек) можно сохранить в качестве сценария. Для этого нужно:

1. В диалоговом окне Результаты поиска решения выбрать Сохранить сценарий.

2. В поле Название сценария ввести имя.

Просмотреть сценарии можно с помощью команды Диспетчера сценариев (на вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Диспетчер сценариев).

Создания отчета по результатам поиска решения

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

Для создания отчета надо в диалоговом окне Результаты поиска решения выбрать нужный тип в поле Тип отчета. Можно выбрать сразу несколько типов (при выделении нескольких строк используется клавиша Ctrl).

Типы отчетов:

- результаты – отчет содержит целевую ячейку, список изменяемых ячеек, их исходные и конечные значения, ограничения и сведения о них;

- устойчивость – отчет содержит сведения о степени зависимости модели от изменений величин, входящих в формулы, применяемые в задаче (формулы модели и формулы ограничений);

- пределы – выводится целевая ячейка и ее значение, а также список изменяемых ячеек, их значений, нижних и верхних пределов и целевых результатов.

Практическое задание

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

1. Решить задачи линейного программирования:

1) найти максимальное значение функции

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

при следующих ограничениях: Решение задач оптимизации средствами Microsoft Excel - student2.ru

Ответ: Решение задач оптимизации средствами Microsoft Excel - student2.ru при Решение задач оптимизации средствами Microsoft Excel - student2.ru

2) найти минимальное значение функции

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

при следующих ограничениях: Решение задач оптимизации средствами Microsoft Excel - student2.ru

Ответ: Решение задач оптимизации средствами Microsoft Excel - student2.ru при Решение задач оптимизации средствами Microsoft Excel - student2.ru

2. Решить задачи нелинейного программирования:

1) найти максимальное значение функции Решение задач оптимизации средствами Microsoft Excel - student2.ru при условии Решение задач оптимизации средствами Microsoft Excel - student2.ru

Ответ: Решение задач оптимизации средствами Microsoft Excel - student2.ru при Решение задач оптимизации средствами Microsoft Excel - student2.ru ;

2) найти максимальное и минимальное значение функции Решение задач оптимизации средствами Microsoft Excel - student2.ru при условии Решение задач оптимизации средствами Microsoft Excel - student2.ru .

Ответ: Решение задач оптимизации средствами Microsoft Excel - student2.ru при Решение задач оптимизации средствами Microsoft Excel - student2.ru ; Решение задач оптимизации средствами Microsoft Excel - student2.ru при Решение задач оптимизации средствами Microsoft Excel - student2.ru ;

3) найти максимальное и минимальное значение функции Решение задач оптимизации средствами Microsoft Excel - student2.ru при условии Решение задач оптимизации средствами Microsoft Excel - student2.ru .

Ответ:

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

4) найти максимальное значение функции Решение задач оптимизации средствами Microsoft Excel - student2.ru при условиях Решение задач оптимизации средствами Microsoft Excel - student2.ru и Решение задач оптимизации средствами Microsoft Excel - student2.ru .

Ответ: Решение задач оптимизации средствами Microsoft Excel - student2.ru при Решение задач оптимизации средствами Microsoft Excel - student2.ru .

3. Найти оптимальное решение следующих экономических задач:

1) Задача планирования производства

Фирма реализует товары двух видов: Товар 1 и Товар 2, используя при этом четыре типа ресурсов – технические, трудовые, финансовые и транспортные. Норма затрат ресурсов на единицу товара, общий объем каждого ресурса и прибыль от реализации каждого товара заданы в таблице 1.

Таблица 1

Ресурсы Норма затрат ресурсов на товары Общее количество ресурсов
Товар 1 Товар 2
Технические
Трудовые
Финансовые
Транспортные
Прибыль на единицу продукции, руб. 2,00 3,00  

Требуется определить количество товаров каждого вида, обеспечивающее фирме максимальную прибыль.

В этой задаче переменные Решение задач оптимизации средствами Microsoft Excel - student2.ru и Решение задач оптимизации средствами Microsoft Excel - student2.ru – количество товаров каждого вида; целевая функция

Решение задач оптимизации средствами Microsoft Excel - student2.ru ;

ограничения на объем каждого вида ресурсов: Решение задач оптимизации средствами Microsoft Excel - student2.ru

Ответ: Решение задач оптимизации средствами Microsoft Excel - student2.ru

2) Транспортная задача

Предприятия, выпускающие одинаковую продукцию, могут реализовать ее в трех различных магазинах. Стоимость транспортировки единицы объема продукции от каждого предприятия до каждого магазина, а также объем готовой продукции на каждом предприятии и пропускная способность каждого магазина приведены в таблице 2.

Таблица 2

  Магазин 1 Магазин 2 Магазин 3 Магазин 4 Объем готовой продукции
Предприятие 1 1 000
Предприятие 2 1 100
Предприятие 3
Пропускная возможность магазинов 1 100  

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

Здесь переменные Решение задач оптимизации средствами Microsoft Excel - student2.ru − объем продукции, поставляемый из i-го предприятия в j-й магазин; целевая функция

Решение задач оптимизации средствами Microsoft Excel - student2.ru ограничения на объем готовой продукции: Решение задач оптимизации средствами Microsoft Excel - student2.ru

ограничения, накладываемые пропускной возможностью магазинов: Решение задач оптимизации средствами Microsoft Excel - student2.ru

кроме того Решение задач оптимизации средствами Microsoft Excel - student2.ru Решение задач оптимизации средствами Microsoft Excel - student2.ru ‑ ограничения, определяемые физическим смыслом задачи.

Ответ: Решение задач оптимизации средствами Microsoft Excel - student2.ru

Наши рекомендации