CC BY
71
ТАБЛИЧНЫЙ ПРОЦЕССОР MS EXCEL: РЕШЕНИЕ ОПТИМИЗАЦИОННЫХ ЗАДАЧ
© Абрамов Е.В.*
Волжский институт строительства и технологий (филиал) Волгоградского государственного архитектурно-строительного университета, г. Волжский
В работе отражено применение электронных таблиц Excel для решения класса задач на оптимизацию. Приведен подробный план графического и программного решения с помощью инструмента «Поиск решения» на примере задачи о распределении ресурсов. Данная технология используется на занятиях со студентами экономической специальности ВИСТех при изучении курса «Информатика».
Раздел «Линейное программирование», в рамках которого рассматривают задачи на оптимизацию, входит в государственный стандарт большинства специальностей технических, гуманитарных и педагогических вузов страны.
Оптимизационными задачами называются такие задачи, в которых требуется найти экстремальное (наибольшее или наименьшее) значение некоторой функции при заданных ограничениях. Эту функцию принято называть целевой. Если целевая функция и ограничения линейны, то данная задача относится к линейному программированию. Многие экономические задачи решаются в рамках линейного программирования, представляющего собой совокупность методов решения линейных задач.
На практике всегда имеются некоторые ограничения, которым должно удовлетворять искомое решение. Эти ограничения могут быть просто границами переменных или функциями этих переменных. В экономических задачах во многих случаях имеются ограничения на используемые ресурсы для производства различных видов продукции. Если удается снять или ослабить эти ограничения, то полученное решение часто удается улучшить.
В основном, решение таких задач влечет за собой трудоемкость расчетов, но при этом главным остается освоение алгоритма решения задач, поэтому программа MS Excel выступает как инструмент для выполнения требуемых операций.
Решение оптимизационных задач в Excel осуществляется программой Поиск решения меню Сервис, которая позволяет решать сложные линейные и нелинейные задачи со многими переменными и ограничениями. После того, как определить задачу и выбрать Выполнить, программа изменит значения переменных и выполнит необходимые вычисления, а затем основываясь на полученных результатах, будет повторять эту проце-
* Доцент кафедры Высшей математики, к.п.н.
дуру до тех пор, пока не получит решение, удовлетворяющее условиям задачи. Каждый цикл изменения переменных называется итерацией. Иногда для правильного решения может понадобиться выполнить несколько сотен итераций.
Автор статьи не претендует на представление новых научных результатов в области информатики, главная цель - это систематизация материала по приемам использования электронных таблиц при решении студентами технических, гуманитарных и педагогических вузов задач линейного программирования.
Рассмотрим пример решения задачи о распределении ресурсов.
Предположим, цех предприятия производит два вида продукции (I и II). Следует рассчитать оптимальные недельные объемы производства этой продукции с точки зрения максимизации прибыли. Прибыль (целевая функция Б) от первого продукта составляет 5 ед., от второго - 5,5 ед.
На производстве действуют ограничения по сырью, трудовым ресурсам и транспортным расходам:
Для I требуется 3 ед. сырья, для II - 6 ед. Всего цех располагает 18 ед. сырья.
Для изготовления I требуется 6 рабочих, для II - 4 рабочих. В цехе 24 рабочих.
Транспортные расходы на перевозку I составляют 2 ед., а II - 1 ед. Эти затраты могут быть не менее 2 ед. (цена аренды одного автомобиля минимальной грузоподъёмности в течение дня). Предполагаем, что вся дневная продукция цеха может быть вывезена на одном автомобиле.
Кроме того, очевидно, что количество единиц продукции не может быть меньше нуля.
Приведем два способа решения данной задачи с помощью MS Excel: графический и программный.
1. Графический способ решения.
Чтобы найти графическое решение задачи линейного программирования, а затем проверить его, пользуясь средствами Excel, следует определить максимальное (или минимальное) значение целевой функции F и значения аргументов, при которых оно получено. Графические построения можно вести вручную, а можно, при желании, и с помощью средств деловой графики MS Excel.
3x1 + 6x2 < 18, 6x1 + 4x2 < 24,
(1)
(2)
(3)
Запишем математическую модель: <! 2x1 + x2 > 2,
F = 5x1 + 5,5x2 ^ max, x1 > 0, x2 > 0.
(4)
Для графического решения данной задачи средствами Excel построим табл. 1.
В первом столбце табл. 1 размещаем аргумент х1 с шагом, равным, например, единице. Используя механизм автозаполнения, заполним первый столбец, начиная с ячейки A2 числами от 0 до числа, например, семь.
Следующие три столбца B, Си D табл. 1 заполним значениями ограничений (1)-(3), в которых знаки неравенств заменены знаком «=« и они разрешены относительно переменной х2:
3xj + 6 x2 = 18,
6 x2 = 18 - 3xj,
x2 = (18 - 3 Xj)/6
Таблица 1
A В С D E F
1 X1 x2=(18-3xi)/6 x2=(24-6xi)/4 x2=2-2xi F Max
2 0 3 6 2
3 1 2,5 4,5 0
4 2 2 3 -2
5 3 1,5 1,5 -4
6 4 1 0 -6
7 5 0,5 -1,5 -8
8 Б 0 -3 -10
9 7 -0,5 -4,5 -12
Для этого в ячейку В2 введем формулу =(18 - 3*А2) / 6 и выполним автозаполнение столбца В таблицы 1. Затем в ячейку С2 введем формулу = (24 - 6*А2) / 4 и автоматически заполним столбец С. И наконец, в ячейку Б2 введем формулу =2-2*А2 и автоматически заполним столбец Б.
Поскольку известно, что переменные х1 и х2 не могут быть отрицательными, то после заполнения таблицы удалим из расчета все ячейки с отрицательными значениями.
Теперь разрешим целевую функцию Е также относительно переменной х2:
Е = 5х1 + 5,5х2, 5,5х2 = (Е - 5х1), х2 = (Е - 5х1)/5,5.
Введем в ячейку Е2 эту формулу: = ($Е$2 - 5*А2) / 5,5 (у ячейки Е2 абсолютный адрес). В ячейке E2 появится значение 0, так как искомый максимум не задан и ячейка Р2, на которую ссылается эта формула, пуста. В этом случае можно пока указать любую константу. Для этого и будет служить ячейка Е2. Далее мы сможем изменять её значение произвольным образом, добиваясь нужного положения целевой функции Е на графике.
Для начала введем в ячейку Е2 число 5. Значение ячейки E2 сразу изменится. Выполним теперь автозаполнение столбца E табл. 1. В послед-
6xj + 4x2 = 24, 4x2 = 24 - 6xj, x2 = (24 - 6 x,) / 4;
2x, + x2 = 2, x2 = 2 - 2 x,.
ней строке все яченки имеют отрицательное значение, поэтому эта строка не должна участвовать в построении графика и, вообще, должна быть удалена из таблицы. В результате получим табл. 2.
Таблица 2
А В С 0 Е
1 *1 х^СШ-Зи^/б хг=(24-6хі)/4 х?=2-2хі Р Мах
2 0 3 6 2 0,909091 5
3 1 2,5 4,5 0 0
4 2 2 3 -0,90909
5 3 1,5 1,5 -1,81818
6 4 1 0 -2,72727
7 5 0,5 -3,63636
8 6 0 -4,54545
Теперь приступим к созданию графика. В качестве диапазона построения возьмем область А1:Е8. Используя Мастер диаграмм, выполним следующие действия:
Шаг 1 ^ Стандартные ^ Тип диаграммы ^ Точечная; Вид ^ Точечная диаграмма со значениями, соединенными отрезками без маркеров (рис. 1) ^ Далее >.
Шаг 2 ^ Диапазон данных ^ Ряды в: столбцах (рис. 2) ^ Далее >.
Рис. 1. Окно мастера диаграмм Рис. 2. Окно мастера диаграмм
Шаг 3 ^ Заголовки ^ Ось X (категорий): ^ ввести х1, Ось У (значений): ^ ввести х2 (рис. 3а); Линии сетки ^ убрать все флажки (рис. 36) ^ Далее >.
Рис. За. Окно мастера диаграмм Рис. 36. Окно мастера диаграмм
Шаг 4 ^ имеющемся: (рис. 4) ^ Готово.
Рис. 4. Окно мастера диаграмм По завершении построения следует отформатировать график. Удалим цветовой фон: наведем курсор мыши на Область построения диаграммы и щелкнем правой кнопкой мыши ^ Формат области построения... ^ Вид ^ Заливка ^ прозрачная (рис. 5) ^ ОК. Установим шаг изменения меток, равным 0,5: наведем курсор мыши на Ось X (категорий) и щелкнем правой кнопкой мыши ^ Формат оси. ^ Шкала ^ снять флажок цена основных делений: ^ ввести значение 0,5 (рис. 6) ^ ОК.
Рис. 5. Окно редактирования формата области построения
Рис. 6. Окно редактирования формата оси
Аналогичные действия повторить для другой оси. В завершении увеличим размер диаграммы для ее легкого чтения и получим график (рис. 7).
6.5 6
5.5 5
4.5 4
3.5 3
2.5 2
1.5
см 1
* 0,5
0
-0,5 -1 -1,5 -2 -2,5 -3 -3,5 -4 -4,5 -5 -5,5
х1
Рис. 7. Диаграмма ограничений задачи
Теперь нужно вручную по графику определить область поиска решений. Для этого в каждом из ограничений (1)-(3) меняем знак неравенства на знак равенства. На рис. 7 графики ограничений построены именно в таком виде. Решение ищется в той полуплоскости, все точки которой удовлетворяют исходному неравенству. Чтобы определить эту полуплоскость для каждого из ограничений (1)-(3), следует приравнять нулю значения х\ и Х2. Если получено соотношение вида 0 < const для прямой, лежащей над началом координат, то это значит, что начало координат входит в полуплоскость и не входит в полуплоскость, если для этой прямой выполняется 0 > const. Таким образом, можно определить область, удовлетворяющую всем ограничениям. Такая область поиска решений называется многоугольником решений.
Многоугольник решений удобно обвести линией, используя Автофигуры ^ Линии ^ Полилиния и закрасить его в какой-нибудь цвет, используя Цвет заливки (рис. 8).
Известно, что оптимальное решение обязательно находится на границе многоугольника решений, обычно в одной из его вершин. Первоначально график целевой функции получается произвольным относительно области решений (в ячейке F2 бралось произвольное значение функции, равное 5). Для нахождения максимально возможного допустимого значения целевой функции ее следует перемещать (поднимать или опускать) параллельно самой себе до пересечения с точкой на границе многоугольника решений, где ее значение максимально. В нашем случае, очевидно, это точка пересечения графиков прямых, полученных из ограничений (1) и (2). Чтобы найти ее
координаты, нужно решить систему уравнений, полученных из ограничений
есть оптимальное решение. Легко найти прибыль цеха, то есть максимальное значение целевой функции Е = 5 • 3+5,5 • 1,5 = 23,25.
il 0,5 1 1,5 2 2,5 3 3,5 4 4,5 5 5,5 6 6 5
-1,5
-2
■2,5
-3
-3,5
-4
-4,5
-5
-5,5
График целевой функции проходит снизу многоугольника решений, поэтому для графического решения задачи его нужно перемещать вверх, постепенно увеличивая значение ячейки F2 до тех пор, пока график не пройдет через точку (3; 1,5). Результат достигается при значении ячейки F2 равной 23,25, что подтверждается аналитическим решением.
Аналогично решаются задачи о распределении ресурсов при нахождении минимума.
2. Программный способ решения.
Найдем решение задачи с помощью инструмента Поиск решения, который располагается в меню Сервис. Если команда Поиск решения отсутствует, нужно сначала выполнить Сервис ^ Надстройки. ^ включить флажок в строке Поиск решения. Для решения задачи составим табл. 3.
Ограничения внесем в верхнюю часть табл. 3. Коэффициенты отношений записываем в область С2:Б4, правые части уравнений - в область F2:F4, коэффициенты целевой функции - в С6:Б6. В процессе расчётов в области Е2:Е4 отображаются вычисляемые (фактические) значения правой части неравенств. Сюда вводятся необходимые для решения формулы. В ячейки Е2, Е3 и Е4 введем соответственно формулы = С2*С$7 + Б2*Б$7, = С3*С$7 + Б3*Б$7 и = С4*С$7 + Б4*Б$7. В этих ячейках появляются пока нулевые
Получим точку с координатами (3; 1,5). Это и
7 7
6,5 -
----х2-(18-3x1 ]/6
— х2=(24-6х1)/4 х2-2-2х1
х1
Рис. 8. Многоугольник решений
значения. Результат формируется в области С7:Б7. Ячейку Е6 зарезервируем для получения оптимизируемого значения. Для вычислений введем в эту ячейку формулу = С6*С$7 + Б6*Б$7. В ней появится пока значение 0.
Таблица 3
А В С D Е F
1 Но Вид ресурса х-1 *2 Вычисленные значения Заданные ограничения
2 1 1-е уравнение 3 6 0 18
3 2 2-е уравнение 6 4 0 24
4 3 3-є уравнение 2 1 0 2
5 Экстремум
6 Целевая функция 5 5,5 0
7 Результаты
Теперь перейдем к работе с инструментом Поиск решений. Выполним действия: Сервис ^ Поиск решения. В открывшемся окне Поиск решений в поле Установить целевую ячейку: укажем предназначенную для этого ячейку E6. Так как требуется в задаче найти максимум целевой функции, то выполним: Равной: ^ максимальному значению. Можно задать не только максимальное / минимальное значения, но и любую произвольную величину, введя её в поле значению:
Зададим диапазон подбираемых параметров. В поле Изменяя ячейки: введем ячейки C7:D7. Удобнее всего это сделать не вручную, а выделяя соответствующие ячейки в табл. 3 курсором мыши.
Заполняем поле Ограничения:. Ограничения устанавливаются с помощью кнопки Добавить. Щелкнем по кнопке Добавить. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку: укажем диапазон C2:D4. В качестве условия зададим <=. В поле Ограничение: зададим диапазон F2:F3. Это условие описывает условия (1) и (2).
Снова щелкнем по кнопке Добавить. В поле Ссылка на ячейку: укажем ячейку E4. В качестве условия зададим >=. В поле Ограничение: зададим ячейку F4. Это условие описывает условие (3).
Снова щелкнем по кнопке Добавить. В поле Ссылка на ячейку: укажем диапазон C2:D4. В качестве условия зададим >=. В поле Ограничение: введем с клавиатуры число 0. Это условие описывает условия (4).
На рис. 9 изображен результат заполнения параметров инструмента Поиск решений для нашей задачи. Щелкнув по кнопке Параметры, можно просмотреть параметры поиска оптимального решения.
После ввода всех ограничений и других условий следует нажать кнопку Выполнить для решения поставленной задачи.
Если вычисления оказались успешными, Excel представит окно итогов Результаты поиска решения (рис. 10). Их можно сохранить или отказаться (Восстановить исходные значения). Кроме того, можно получить один из трёх видов отчётов: Результаты, Устойчивость, Пределы, позволяющих лучше осознать полученные результаты, в том числе, оценить их достоверность.
Рис. 9. Окно «Поиск решения»
Рис. 10. Окно «Результаты поиска решения»
Параллельно с появлением на экране окна итогов, табл. 3 измениться и примет вид (табл. 4).
Таблица 4
Вывод: в табл. 4 в ячейках E2:E4 получен оптимальный недельный «расход» сырья, трудовых ресурсов и транспортных перевозок соответственно. В ячейках C7:D7 получен оптимальный недельный объем производства продукции двух видов с точки зрения максимизации прибыли, а в ячейке Е6 получена максимальная прибыль.
Список литературы:
1. Лунгу К.Н. Линейное программирование. Руководство к решению задач. - М.: ФИЗМАТЛИТ, 2009. - 132 с.
2. Минько A.A. Принятие решений с помощью Excel. Просто как дважды два. - М.: Эксмо, 2006. - 240 с.
