Сделай Сам Свою Работу на 5

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





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

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

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

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

.

Если функции и являются линейными, т. е.

,

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

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

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

2. В отдельную ячейку ввести формулу для целевой функции . Эта ячейка называется целевой.

3. отдельные ячейки ввести формулы для левой части ограничений .

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

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

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

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

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

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



- в поле Ссылка на ячейку ввести ссылку на ячейку с формулой, определяющей первое ограничение ( );

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

- в поле Ограничение ввести значение ограничения

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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



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

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

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

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

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

Практическая работа 8

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

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

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

при следующих ограничениях:

Ответ: при

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

при следующих ограничениях:

Ответ: при

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

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

Ответ: при ;

2) найти максимальное и минимальное значение функции при условии .

Ответ: при ; при ;

3) найти максимальное и минимальное значение функции при условии .

Ответ:

при при

4) найти максимальное значение функции при условиях и .

Ответ: при .

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

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

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

Таблица 1

 

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

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

В этой задаче переменные и – количество товаров каждого вида; целевая функция

;

ограничения на объем каждого вида ресурсов:

Ответ:

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

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

Таблица 2

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

 

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

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

ограничения на объем готовой продукции:

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

кроме того ‑ ограничения, определяемые физическим смыслом задачи.

Ответ:

 








Не нашли, что искали? Воспользуйтесь поиском по сайту:



©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.