Оптимизационные задачи в microsoft excel
Microsoft Excel обеспечивает решение оптимизационных задач методами задач линейного и нелинейного программирования. Модель имеет ограниченную размерность – 200 переменных.
Для оптимизационных расчетов с помощью команды меню СервисНадстройки устанавливается надстройка Microsoft Excel «Поиск решения». Модель задачи линейного программирования:
где – коэффициенты линейной функции.
– искомые оптимальные значения переменных, которые могут принимать любое, в том числе неотрицательное или целое значение;
линейная функция переменных и их коэффициентов может стремиться к минимуму, максимуму или принимать фиксированное значение.
–норма расхода «ресурса» j–го вида на выпуск «единицы» .
–ограничение на суммарный расход «ресурса» j–го вида.
Информационная технология решения оптимизационной задачи рассматривается на примере определения оптимального выпуска продукции по критерию максимума прибыли:
Целевая функция – линейная, стремится к максимуму.
Коэффициенты целевой функции – прибыль на единицу продукции.
Нормативы затрат ресурсов на выпуск единицы продукции считаются заданными.
Ограничение – объем ресурсов различного вида (трудовые, материальные ресурсы, оборудование).
Дополнительное ограничение – выпуск каждого вида продукции – целые числа.
Схема размещения информации на рабочем листе для оптимизационных задач приведена на рис. 13.
КОЭФФИЦИЕНТЫ |
| ||||||||
|
|
|
|
|
| ||||
РЕЗУЛЬТАТЫ | |||||||||
|
|
|
|
|
| ||||
ЦЕЛЬ |
|
|
|
|
| ||||
|
|
|
|
|
| ||||
|
|
|
|
|
|
|
|
|
|
НОРМАТИВЫ |
| ПОТРЕБНОСТИ |
| ЗАПАСЫ | |||||
|
| ||||||||
|
| ||||||||
|
| ||||||||
|
| ||||||||
|
| ||||||||
|
|
Рисунок 13
Порядок подготовки листа рабочей книги:
Вставить новый лист – команда меню ПравкаЛист (если необходимо).
Переименовать лист в Поиск решения 1 – команда меню ФорматЛистПереименовать.
Создать имя блока ячеек для коэффициентов целевой функции. Имя блока – Коэффициенты.
Ввести значения коэффициентов целевой функции (прибыль для единицы продукции).
Создать имя блока ячеек для переменных. Имя блока – Результаты.
Ввести 0 в ячейки блока Результаты для проверки правильности вводимых формул.
Размерности блоков РЕЗУЛЬТАТЫ и КОЭФФИЦИЕНТЫ совпадают.
Ввести в свободную ячейку формулу для вычисления целевой функции: =СУММПРОИЗВ(КОЭФФИЦИЕНТЫ;РЕЗУЛЬТАТЫ).
Создать имя блока для ячейки целевой функции. Имя блока – ЦЕЛЬ.
Ввести нормативы затрат ресурсов на выпуск единицы продукции.
Создать имя блока нормативов затрат ресурсов на выпуск единицы продукции. Имя блока – НОРМАТИВЫ.
Размерность блока НОРМАТИВЫ по горизонтали (число столбцов) совпадает с размерностью блоков РЕЗУЛЬТАТЫ и КОЭФФИЦИЕНТЫ.
Создать блок ячеек для расчета потребности в ресурсах на выпуск продукции. Имя блока – ПОТРЕБНОСТИ.
Ввести формулу расчета потребности первого ресурса: =СУММПРОИЗВ($A8:$F8;РЕЗУЛЬТАТ).
A8:F8 – ячейки первой строки блока НОРМАТИВЫ.
Для тиражирования формулы расчета потребности ресурсов следует использовать абсолютные ссылки только по координате столбцов, так как номера строк ячеек при копировании формул будут изменяться.
Тиражировать формулу потребности для первого ресурса во все ячейки блока ПОТРЕБНОСТИ.
Создать имя блока ячеек для задания ограничений расхода ресурсов. Имя блока – ЗАПАСЫ.
Размерности блоков ПОТРЕБНОСТИ и ЗАПАСЫ совпадают. Высота этих блоков соответствует по вертикали размеру блока НОРМАТИВЫ.
Ввести ограничения на расход ресурсов в ячейки блока ЗАПАСЫ.
В табл. 11 приведены исходные данные для решения оптимизационной задачи.
Таблица 11
1 | 1 | 2 | 5 | 4 | 3 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
0 | 0 | 0 | 0 | 0 | 0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 | 3 | 4 | 5 | 6 | 0 |
| 0 |
| 12 |
2 | 3 | 4 | 1 | 1 | 2 |
| 0 |
| 22 |
1 | 3 | 4 | 5 | 2 | 0 |
| 0 |
| 23 |
1 | 0 | 3 | 4 | 5 | 6 |
| 0 |
| 44 |
0 | 0 | 1 | 3 | 4 | 0 |
| 0 |
| 55 |
3 | 4 | 5 | 1 | 2 | 3 |
| 0 |
| 66 |
5 | 3 | 4 | 6 | 3 | 2 |
| 0 |
| 77 |
2 | 3 | 4 | 3 | 2 | 4 |
| 0 |
| 89 |
Выделить блок ячеек КОЭФФИЦИЕНТЫ и снять защиту ячеек – команда ФорматЯчейки, вкладка Защита.
Выделить блок ячеек НОРМАТИВЫ и снять защиту ячеек – команда ФорматЯчейки, вкладка Защита.
Выделить блок ячеек ЗАПАСЫ и снять защиту ячеек – команда ФорматЯчейки, вкладка Защита.
Установить защиту на лист – команда меню СервисЗащитаЗащитить лист.
Установка защиты обеспечивает сохранение всех формул (ячейки блоков ПОТРЕБНОСТЬ, ЦЕЛЬ).
Выполнить команду меню СервисПоиск решения для построения модели задачи оптимизации производственной программы.
В диалоговом окне Поиск решения сформировать модель оптимизационной задачи вида – рис. 14.
Рисунок 14
Задать параметры для оптимизации – кнопка Параметры в диалоговом окне Поиск решения – рис. 15.
Рисунок 15
Нажать кнопку ОК и запустить задачу на решение.
Результат решения – значения ячеек блока Результаты – табл. 12. Оптимальный план: продукции Х4 – 4, продукции Х5 – 1 единица. Максимум целевой функции – 24.
Сформировать отчет Результаты для анализа ограничений на расход ресурсов.
Для задач с ограничением на целые значения переменных не формируются отчеты Устойчивость и Пределы.
Таблица 12
1 | 1 | 2 | 5 | 4 | 3 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
0 | 0 | 0 | 4 | 1 | 0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
24 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 | 3 | 4 | 5 | 6 | 0 |
| 5 |
| 12 |
2 | 3 | 4 | 1 | 1 | 2 |
| 22 |
| 22 |
1 | 3 | 4 | 5 | 2 | 0 |
| 21 |
| 23 |
1 | 0 | 3 | 4 | 5 | 6 |
| 16 |
| 44 |
0 | 0 | 1 | 3 | 4 | 0 |
| 6 |
| 55 |
3 | 4 | 5 | 1 | 2 | 3 |
| 27 |
| 66 |
5 | 3 | 4 | 6 | 3 | 2 |
| 14 |
| 77 |
2 | 3 | 4 | 3 | 2 | 4 |
| 0 |
| 89 |
Отчет Результаты содержит целевую ячейку и список влияющих ячеек модели, их исходные и конечные значения, формулы для расчета ограничений. Пример отчета – табл. 13.
Отчет Устойчивость содержит сведения о чувствительности решения к малым изменениям в формуле целевой ячейки или в формулах ограничений. Для нелинейных моделей отчет содержит данные для градиентов и множителей Лагранжа (ограниченные затраты, фиктивные цены, объективный коэффициент с некоторым допуском, а также диапазоны ограничений справа).
Отчет Ограничения состоит из целевой ячейки и списка влияющих ячеек модели, их значений, а также нижних и верхних границ. Нижним пределом является наименьшее значение, которое может содержать влияющая ячейка, в то время как значения остальных влияющих ячеек фиксированы и удовлетворяют наложенным ограничениям. Соответственно, верхним пределом называется наибольшее значение.
Таблица 13
Microsoft Excel 9.0 Отчет по результатам |
|
| ||||
Рабочий лист: [Анализ и моделирование.xls]Решение | ||||||
Отчет создан: 17.09.00 23:10:18 |
|
| ||||
Целевая ячейка (Максимум) |
|
|
| |||
| Ячейка | Имя | Исходно | Результат |
|
|
| $A$5 | ЦЕЛЬ | 24 | 24 |
|
|
Изменяемые ячейки |
|
|
| |||
| Ячейка | Имя | Исходно | Результат |
|
|
| $A$3 |
| 0 | 0 |
|
|
| $B$3 |
| 0 | 0 |
|
|
| $C$3 |
| 0 | 0 |
|
|
| $D$3 |
| 0 | 4 |
|
|
| $E$3 |
| 0 | 1 |
|
|
| $F$3 |
| 0 | 0 |
|
|
Ограничения |
|
|
|
| ||
| Ячейка | Имя | Значение | формула | Статус | Разница |
| $H$8 |
| 5 | $H$8<=$J$8 | не связан. | 7 |
| $H$9 |
| 22 | $H$9<=$J$9 | Связанное | 0 |
| $H$10 |
| 21 | $H$10<=$J$10 | не связан. | 2 |
| $H$11 |
| 16 | $H$11<=$J$11 | не связан. | 28 |
| $H$12 |
| 6 | $H$12<=$J$12 | не связан. | 49 |
| $H$13 |
| 27 | $H$13<=$J$13 | не связан. | 39 |
| $H$14 |
| 14 | $H$14<=$J$14 | не связан. | 63 |
| $H$15 |
| 0 | $H$15<=$J$15 | не связан. | 89 |
| $A$3 |
| 0 | $A$3>=0 | Связанное | 0 |
| $B$3 |
| 0 | $B$3>=0 | Связанное | 0 |
| $C$3 |
| 0 | $C$3>=0 | Связанное | 0 |
| $D$3 |
| 4 | $D$3>=0 | не связан. | 4 |
| $E$3 |
| 1 | $E$3>=0 | не связан. | 1 |
| $F$3 |
| 0 | $F$3>=0 | Связанное | 0 |
| $A$3 |
| 0 | $A$3=целое | Связанное | 0 |
| $B$3 |
| 0 | $B$3=целое | Связанное | 0 |
| $C$3 |
| 0 | $C$3=целое | Связанное | 0 |
| $D$3 |
| 4 | $D$3=целое | Связанное | 0 |
| $E$3 |
| 1 | $E$3=целое | Связанное | 0 |
| $F$3 |
| 0 | $F$3=целое | Связанное | 0 |
- Информационные технологии анализа данных в microsoft office
- Введение
- Аналитическая обработка данных в microsoft office
- Интеграция данных в microsoft office
- Математические методы анализа данных
- Информационные технологии встроенных функций microsoft excel
- Функция бз
- Функция пз
- Функция норма
- Функция пплат
- Функция кпер
- Функции оснплат, плпроц
- Подбор параметра модели в microsoft excel
- Сценарный подход в microsoft excel
- Таблицы подстановки microsoft excel
- Оптимизационные задачи в microsoft excel
- Статистическая обработка данных в microsoft excel
- Описательная статистика
- Прогнозирование значений
- Агрегирование данных в microsoft office
- Сводная таблица Microsoft Excel
- Структурная таблица Microsoft Excel
- Консолидация данных в Microsoft Excel
- Графические методы анализа данных microsoft office
- Типы диаграмм и их свойства
- Построение диаграммы
- Линии тренда
- Примеры информационных технологий анализа данных
- Задача «Факторный анализ»
- Задача «Количественный анализ хозяйственной деятельности»
- Задача «Анализ рентабельности»
- Задача «Анализ производственной программы»
- Задача «Оптимальное размещение денежных средств на депозите»
- Задача «Анализ продаж ценных бумаг»
- Приложение. Некоторые встроенные функции microsoft excel
- Литература
- Оглавление
- 191023, Санкт–Петербург, Садовая ул., д. 21