7. Решение задачи линейного программирования с по­мощью excel

Рассмотрим задачу линейного программирования, сформулированную в п. З настоящей работы.

2x₁ + х₂ ≤ 400

3x₁ + 4x₂ ≤ 900

х₁ + Зх₂ ≤ 600

x₁ ≥ 0, х₂ ≥ 0

f = 60x₁ + 40x₂  max

Войдя в MS EXCEL, создадим форму для ввода условий задачи. Для сформулированной задачи форма будет иметь вид:

	A	B	C	D	E	F	G
1		Переменные
2	Имя	пер. 1	пер.2
3	Значение
4	Ниж. гр.
5	Верх. гр.
6	Коэф. Ф.Ц				Макс.
7		Ограничения
8	Вид			Лев. час.	Знак	Прав. час.
9	Огр. 1
10	Огр. 2
11	Огр. 3

Введенный текст является комментарием и на решение задачи не влияет. Введем исходные данные:

60 в В6; 40 в С6;

2 в В9; 1 в С9; 400 в F9;

3 в В10; 4 в С10; 900 в F10;

1 в В11; З в С11; 600 в F11.

Знак ≤ в Е9; Е10; Е11.

В результате получим:

	A	B	C	D	E	F	G
1		Переменные
2	Имя	пер. 1	пер.2
3	Значение
4	Ниж. гр.
5	Верх. гр.
6	Коэф. Ф.Ц	60	40		Макс.
7		Ограничения
8	Вид			Лев. час.	Знак	Прав. час.
9	Огр. 1	2	1		≤	400
10	Огр. 2	3	4		≤	900
11	Огр. 3	1	3		≤	600

Введем зависимость для целевой функции. Установим курсор в D6. Курсор на кнопку Мастер функций. Один щелчок по левой кнопке мыши (M1). На экране появится диалоговое окно Мастер функций. Курсор в окно Категория на категорию Математические М1. Курсор в окно на СУММПРОИЗВ. М1.

На экране появится диалоговое окно СУММПРОИЗВ

В массиве 1 следует ввести В3: С3 (во все диалоговые окна адреса ячеек удобно вводить не с клавиатуры, а протаскивая мышь по ячейкам, чьи адреса следует ввести). В массив 2 введем В6:С6. Курсор в Готово М 1.

На экране в D6 прочитаем введенное значение целевой функции (=СУММПРОИЗВ (В3: СЗ; В6 : С6)).

Введем зависимости для левых частей ограничений: курсор в D6; копиро­вать в буфер, курсор в D9; вставка из буфера. На экране в D9 будет введена функция (=СУММПРОИЗВ (ВЗ:СЗ; В9:С9)). Затем копируем D9 в D10 и в D11. На этом ввод данных закончен.

Теперь приступаем к поиску решения. Для этого используем функцию Сервис, Поиск решения … . На экране появится окно Поиск решения.

Назначим целевую функцию: курсор в окно Установить целевую ячейку; ввести адрес D6; ввести направление целевой функции - максимальное значе­ние.

Для ввода адресов искомых переменных: курсор в поле Изменяя ячейки; ввести адреса ВЗ: СЗ; курсор на функцию Добавить …; M1.

На экране появится диалоговое окно Добавить ограничения.

Введем граничные условия на переменные (пер.1 ≥ 0, пер.2 ≥ 0); ВЗ ≥ В4; СЗ ≥ С4 (в окне ссылка на ячейку ввести ВЗ: курсор на стрелку; М1; курсор на знак ≥; M1; курсор в правое окно; ввести В4; Добавить …. Аналогично вводится граничное условие СЗ ≥ С4; D9 ≥ F9; D10 ≥ F10; D11 ≥ F11. После 2 ввода последнего ограничения вместо Добавить … вводим ОК. На экране появится диалоговое окно Поиск решения с введенными условиями.

Если при вводе задачи возникает необходимость в изменении или удале­нии внесенных ограничений или граничных условий, то это делается с помо­щью команд Изменить …, Удалить … .

С помощью команд, находящихся в окне Параметры поиска решения, мож­но вводить условия для решения задач оптимизации всех классов. Команды, используемые по умолчанию, подходят для решения большинства практиче­ских задач. (В поле Максимальное время можно ввести время, не превышаю­щее 32767 с. (более 9 часов). В поле Предельное число итераций 100 подходит для решения большинства задач. Установление флажка Линейные модели обеспечивает применение симплекс-метода.) ОК. На экране появится диалого­вое окно Поиск решения.

Устанавливаем курсор на Выполнить. M1. После выполнения на экране появится диалоговое окно Результаты поиска решения. Решение найдено и результат оптимального решения задачи приводится в таблице, после в Типе отчета опции Результаты. ОК. По умолчанию найденное решение сохраняется.

	A	B	C	D	E	F	G
1		Переменные
2	Имя	пер. 1	пер.2
3	Значение	140	120
4	Ниж. гр.
5	Верх. гр.
6	Коэф. Ф.Ц	60	40		Макс.
7		Ограничения
8	Вид			Лев. час.	Знак	Прав. час.
9	Огр. 1	2	1		≤	400
10	Огр. 2	3	4		≤	900
11	Огр. 3	1	3		≤	600

Из таблицы видно, что в оптимальном решении пер.1=140, пер.2=120. При этом максимальная выручка будет составлять D6=13200 ден. ед., а количество используемых ресурсов равно: pec.1=D9=400; pec.2=D10=900, pec.3=D11=500.

Если условия задачи будут несовместными, решение задачи не будет най­дено, о чем будет сообщено.

Анализ оптимального решения выполняется на основании применения по­ложений симплекс-метода, которые были изложены в пп. 3, 5, 6 и начинается после появления диалогового окна Результат поиска решения. С помощью этого диалогового окна можно вызвать отчеты трех типов: результаты, устойчивость, пределы.

Отчет по результатам начинается с установления курсора на результат ОК. На экране вызванный отчет на новом листе, на ярлычке которого указано название отчета M1. На экране высвечивается вызванный отчет.