Решение задачи оптимизации с помощью Excel
Автор: Boris89 • Апрель 4, 2018 • Лабораторная работа • 885 Слов (4 Страниц) • 915 Просмотры
Лабораторная работа №1
Решение задачи оптимизации с помощью Excel
Процесс решения оптимизационной задачи в Excel 2010 включает следующие этапы:
- Подготовка исходных данных
- Формирование модели
- Настройка параметров расчетов
- Нахождение оптимального решения
- Анализ устойчивости решения
- Сохранение результатов
Пример. Для производства трех видов изделий фирма располагает следующими видами ресурсов: сырье , оборудование , труд. Расход ресурсов, на производство одной единицы изделий каждого вида, и удельная прибыль от их продажи приведены в таблице.
Расчет оптимального плана фирмы | ||||
Нормы расхода ресурсов | ||||
Изделие 1 | Изделие 2 | Изделие 3 | Наличие | |
Ресурс | ||||
Сырье, кг | 5 | 6 | 4 | 400 |
Оборудование, ед. | 4 | 7 | 6 | 350 |
Труд, чл-ч. | 6 | 8 | 5 | 480 |
Удельная прибыль, у.д.е. | 25 | 40 | 30 |
Подготовка исходных данных
Размещение информации на рабочем листе. Для проведения расчетов необходимо определить изменяемые ячейки, которые отводятся под переменные. В одну из ячеек рабочего листа заносится формула, вычисляющая значение целевой функции. В специально отведенные ячейки вводятся формулы, которые задают вычисление функций, стоящих в левой части ограничений задачи.
Разместите на рабочем листе исходную информацию так, как показано на рисунке.
[pic 1]
Диапазон B10:D10 отведем под изменяемые ячейки. В этих ячейках, после выполнения Поиска решения, будет записано оптимальное количество выпускаемых изделий, необходимое для получения максимальной прибыли.
Диапазон E7:E9 отведем под левые части ресурсных ограничений. Он будет содержать формулы, подсчитывающие расход ресурсов.
Ячейку E10сделаем целевой. Так как целевая функция и все соотношения в модели линейны, для ввода формул удобно использовать функцию СУММПРОИЗВ.
Ввод формул.
Установите курсор в целевую ячейку Е10 и, с помощью Мастера функций, введите формулу СУММПРОИЗВ.
В Массив1 введите ссылки на ячейки B11:D11. Нажатием клавиши F4 сделайте ссылки данного диапазона абсолютными.
В Массив2 введите ссылки на ячейки B10:D10.
[pic 2]
После нажатия клавиши ОК значение целевой ячейки равно нулю, так как значения ячеек B10:D10, отведенных под оптимальное количество выпускаемых изделий, пока не найдены.
Скопируйте формулу из ячейки E10 в массив ячеек E7:E9.
[pic 3]
Поиск решения.
Установите курсор в целевую ячейку E10.
Перейдите на вкладку Данные, выберете Поиск решения.
Установите курсор в строке Изменяемые ячейки переменных и выделите диапазон B11:D11. При этом абсолютные ссылки на ячейки появятся автоматически.
Установите курсор в окно ввода ограничений и нажмите кнопку Добавить. Запишите ограничения так, как показано на рисунке.
[pic 4]
Проверьте правильность введенных данных (см. рис. ниже)
[pic 5]
Внимание. Если для нахождения решения используется версия Excel 2003-2007, то после ввода ограничений необходимо нажать кнопку Параметры и в открывшемся диалоговом окне выбрать Линейная модель и Неотрицательные значения.
...