Цель: изучение возможностей МS Ехсеl по решению оптимизационных задач и практическое освоение соответствующих умений и навыков.
Вы уже знакомы со многими функциями, которые имеются в программе электронных таблиц МS Ехсеl. Но следует отметить, что возможности современных электронных таблиц не ограничиваются вычислениями по формулам и построением диаграмм и графиков. С помощью надстроек ЭТ можно решать самые разнообразные задачи.
Некоторые из надстроек не инсталлируются по умолчанию и требуют дополнительной установки. Так, для установки надстройки Поиск решения необходимо:
- выбрать команду Разработчик, Надстройки;
- установить флажок около пункта Поиск решения;
- щелкнуть на кнопке ОК.
Поиск решения является надстройкой, которая позволяет решать задачи оптимизированного моделирования. Процедура поиска решения дает возможность найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, искомый результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут содержать ссылки на другие влияющие ячейки,
Рассмотрим решение задачи линейного программирования с использованием указанной возможности.
Пусть в колхозе требуется распределить площадь пашни между двумя культурами в соответствии со следующими данными:
| Культура | Площадь, га | Урожай, ц/га | Затраты, руб./га | Цена за 1 ц, руб. | Затраты, человеко-дней на 1 га |
| x | |||||
| y |
Кроме того, заданы ресурсы производства:
земли — 1800 га, человеко-дней — 8000.
Величины х и у являются неизвестными и подлежат определению.
Построение математической модели задачи включает в себя:
- задание целевой функции (ее надо максимизировать или минимизировать);
- задание системы ограничений в форме линейных уравнений и неравенств;
- требование неотрицательности переменных.
Решим задачу по оптимизации критерия, а именно по максимуму прибыли.
Ограничения задачи имеют следующий вид:
ограничение по площади: х + у < 1800; (1)
ограничение по человеко-дням: 2х + 5у < 1800, или х + 5у < 4000. (2)
Кроме того, ясно, что х > 0, у > 0. (3)
Для прибыли (согласно данным таблицы) имеем формулу:
П = 6 • 10 x + 8 • 15у - 50 x - 80у = 10(x + 4 y). (4)
Учтя все условия задачи, приходим к ее математической модели: среди неотрицательных целочисленных решений системы линейных неравенств
найти такое, которое соответствует максимуму линейной функции F =10(х +4 y).
Теперь заполним расчетную форму в табличном процессоре Ехсеl.
Введем в столбец А подписи к величинам и расчетным формулам,
в В — расчетные формулы (отображаются вычисленные по этим формулам значения),
в С — текстовое обозначение этих формул,
в D — значения ограничений.
На вкладке Данные выбираем Поиск решения и заполняем открывшееся диалоговое окно следующим образом:
Далее щелкаем в этом окне на кнопке Параметры и в открывшемся диалоговом окне Параметры поиска решения устанавливаем флажки Линейная модель и Неотрицательные значения. Щелкаем на кнопке ОК.
В окне Поиск решения щелкаем на кнопке Выполнить.
Оптимальное решение найдено.
Таким образом, х = 1250 (га), у = 550 (га) — искомый оптимальный вариант распределения площади пашни между двумя культурами, при котором достигается максимальная прибыль и выполняются все условия задачи.
Решение задачи может быть выполнено и другими способами, например графически, что приводит к тому же результату: