4.Решение задачи линейного программирования средствами excel
Покажем решение задачи, имеющую следующую математическую формулировку.
Найти неотрицательные значения переменных Х1, Х2, Х3, Х4, Х5, при которых целевая функция
f = 9,3Х1 + 8,5Х2 +5,1Х3 + 1,4Х4 + 3,7Х5 min.
При этом выполняются ограничения на переменные:
0,93Х1 – 0,31Х2 + 0,42Х3 + 0,29Х4 + 0,3Х5 ≥ 7,9;
27Х1 + 53Х2 + 49Х3 + 7Х4 – 10Х5 ≥ 830;
– 3Х2 + 3Х3 + Х5 = 20;
0,87Х1 – 0,51Х2 + 0,53Х3 – 0,26Х4 – 0,42Х5 ≤ 7.
Запускаем EXCEL. Создаем в рабочем листе таблицу с коэффициентами ограничений и целевой функции. Дополняем таблицу строками и столбцами, показанными на рисунке 3, где граница исходной таблицы выделена жирной линией.
В ячейках столбца G вычисляются выражения, стоящие в левых частях ограничений и в целевой функции. Вводим в ячейку G4 формулу =СУММПРОИЗВ(B4:F4;B$9:F$9). Растягиваем ее вниз до ячейки G8, содержащей значение целевой функции.
Рис.3
Символы в столбце «Знак» не будут участвовать в вычислениях, они помогают в дальнейшем при задании ограничений. В столбце «Объем ограничений» указываем значения ограничений из математической модели.
В строке «Значения переменных», ячейках В9 – F9, содержатся значения управляемых переменных. На данном этапе их можно не заполнять, там по умолчанию стоят нули.
Все подготовлено для решения задачи.
Выбираем команду Сервис Поиск решения. Откроется диалоговое окно, показанное на рис.4.
Рис.4
Заполняем поля Установить целевую ячейку и Изменяя ячейки, щелкнув на соответствующей ячейке таблицы или выделив диапазон ячеек. Устанавливаем переключатель в области Равной на Минимальному значению. Чтобы заполнить поле Ограничения, нажимаем клавишу Добавить. В результате откроется диалоговое окно Добавление ограничения на рис.5.
Рис.5
В поле Ссылка на ячейку вводим адреса ячеек из столбца «Формула», содержащего левые части ограничений. В поле Ограничение вводим соответствующие значения из столбца «Объем ограничения». В центральном поле ставим знак ограничения из раскрывающегося списка. Введя очередное ограничение, нажимаем Добавить, а введя последнее – ОК. В результате вернемся к заполненному диалоговому окну Поиск решения. С помощью клавиш Изменить и Удалить можем ввести коррективы в список ограничений.
Далее нажимаем клавишу Параметры. Откроется диалоговое окно Параметры поиска решения на рис.6. Здесь обязательно ставим флажок в поле Неотрицательные значения. Для ускорения работы программы ставим флажок в поле Линейная модель. Остальные поля можно не трогать. Нажимаем ОК.
Рис.6
Снова откроется диалоговое окно Поиск решения. Нажимаем клавишу Выполнить. В результате получим заполненную таблицу и откроется диалоговое окно Результаты поиска решения.
Рис.7
В поле Тип отчета активируем нужные нам типы отчета и нажимаем ОК. Полученное решение будет сохранено и создадутся три новых листа с указанными типами отчетов.
В Отчете по результатам будут показаны все результаты.
В Отчете по устойчивости показано, как могут изменяться параметры задачи, чтобы найденное оптимальное решение осталось без изменения.
В Отчете по пределам показано, как могут меняться значения переменных (каждое в отдельности, когда остальные зафиксированы), чтобы ограничения задачи продолжали выполняться, и как изменится при этом целевая функция.