5

Оптимизация ассортимента продукции хлебокомбината

Предприятие выпускает хлебобулочную продукцию различных видов. Выпуск некоторых видов не может быть меньше заданного количества (табл. 2). Каждый вид продукции может производиться с использованием различного сырья. Объемы сырья заданы в таблице 3.

В таблице 1 приведены нормативы затрат сырья на производство продукции и прибыль (общие для всех вариантов).

Таблица 1

Сырье	ед. изм.	Виды продукции
		Хлеб пшен в.с.	Хлеб пшен 1с	Хлеб Красносельский	Хлеб Украинский	Батон нарез 1с 0,4	Хлеб Столичный	Батон нарез в.с. 0,5	Батон Особый 0,45	Батон столовый в.с.0,3	Плетенка с маком
Мука пшеничная в.с.	ц	1						1	1	1	1
Мука пш. 1 с.	ц		1	1		1	0,5
Мука пш. 2 с.	ц				0,6
Мука ржаная	ц				0,4		0,5
Дрожжи	кг	10	15	10	5	10	5	15	20	10	5
Масло растительное	кг	20	20	20	20	13,4	20	13,4	13,4	13,4	13,4
Сахарный песок	кг			20		40		40	10	20	40
Прибыль	ден. ед.	400	316	366	388	250	122	455	205	345	229

В таблице 2 приведены программы выпуска: Хлеба пшеничного 1с и Батона нарез в.с. 0,5,

по вариантам:

Вариант	Буханка	Батон	Вариант	Буханка	Батон
0	500	120	8	690	130
1	510	90	9	680	150
2	520	50	10	700	110
3	530	150	11	750	90
4	540	100	12	800	140
5	560	170	13	850	130
6	570	185	14	900	120
7	580	90	15	990	100

В таблице 3 приведены объемы сырья (по вариантам):

Вариант	Мука пшеничная в.с.	Мука пш. 1 с.	Мука пш. 2 с.	Мука ржаная	Дрожжи	Масло растительное	Сахарный песок
0	4600	2036	709	623	41540	74838	35800
1	2700	1500	200	500	40500	75000	35500
2	2800	2000	220	520	40000	74500	35400
3	2300	1150	340	540	40900	74100	35200
4	2000	1950	350	550	41050	73800	35500
5	2100	1030	480	600	41790	74600	36000
6	2200	1910	450	610	42300	73900	36150
7	2300	1000	580	630	43800	73900	35650
8	1900	2040	550	640	45000	72700	35200
9	1500	2060	590	650	49000	71650	36700
10	1600	2080	630	660	48050	70150	36450
11	1700	2090	600	700	47500	68260	37000
12	1800	2050	650	580	49200	69100	36950
13	1900	2000	790	550	48500	69850	38000
14	2400	1990	950	640	50000	68650	36540
15	2800	1900	850	620	50100	68000	35000

Требуется определить оптимальный план производства продукции, который приносил бы максимальную прибыль.

Решение задачи линейного программирования с помощью Поиска решения в среде EXCEL

На примере - оптимизации ассортимента продукции хлебокомбината.

Для решения задачи необходимо:

1. Создать форму для ввода условий задачи.

2. Указать адреса ячеек, в которые будет помещен результат решения (изменяемые ячейки).

3. Ввести исходные данные.

4. Ввести зависимость для целевой функции.

5. Ввести зависимости для ограничений.

6. Указать назначение целевой функции (установить целевую ячейку).

7. Ввести ограничения.

8. Ввести параметры для решения задачи линейного программирования.

1. Подготовим форму для ввода условий (см. рис 1).

Рисунок 1 Форма для ввода условий.

2. В нашей задаче оптимальные значения вектора Х = (Х₁, Х₂, Х₃, Х₄, Х₅, Х₆, Х₇) (другими словами – ответ задачи) будут помещены в ячейках D4:M4, оптимальное значение целевой функции – в ячейку N15.

3. Введите исходные данные модели по вашему варианту в созданную форму. Результат ввода по данным примера (вариант 0), показан на рисунке 2.

Рисунок 2 Введенные данные по варианту "0".

4. Введем зависимость для целевой функции (обозначим через «М1» – «один щелчок левой кнопкой мыши»):

• Курсор в ячейку N15.

• Курсор на кнопку Мастер функций М1.

• На экране диалоговое окно Мастер функций шаг 1 из 2.

• Курсор в окно Категория на категорию Математические. М1.

• Курсов в окно Функции на СУММПРОИЗВ, М1.

• В массив 1 ввести $D$4:$M$4 - (адреса ячеек во все диалоговые окна удобно вводить не с клавиатуры, а протаскивая мышь по заданному интервалу ячеек. Знак $ обязателен, ввести с клавиатуры).

• В массив 2 ввести D15:M15. ОК. (рис.3).

• Таким образом, мы ввели функцию в ячейку N15, где должен появится «0».

Рисунок 3 Пример ввода функции.

4. Введем ту же функциональную зависимость для левых частей ограничений:

• Скопируем формулу из ячейки N15 в ячейки - Объемов левой части ограничений;

• Курсор в N15, подведя курсор к правому нижнему углу ячейки появится маркер в форме черного крестика , нажав левую кнопку мыши протащить курсор по заданным ячейкам) вверх до ячейки N6 включительно.

На этом ввод зависимостей закончен.

Запуск Поиска решения.

После выбора команд Сервис  Поиск решения появится диалоговое окно Поиск решения.

4. Назначение целевой функции (установить целевую ячейку).

• Курсор в поле «Установить целевую ячейку».

• Ввести адрес $N$15, с помощью мышки;

• Ввести направление целевой функции: Максимальному значению.

Ввести адреса искомых переменных:

• Курсор в поле «Изменяя ячейки»;

• Ввести адреса $D$4:$M$4, с помощью мышки.

4. Ввод ограничений.

• Нажать на кнопку «Добавить». Появится диалоговое окно - Добавление ограничения Рис 4.

Рисунок 4 Добавление ограничения.

• В поле «Ссылка на ячейку» ввести адрес $N$6.

• Ввести знак ограничения (<=).

• Курсор в правое окно.

• Ввести адрес $P$6.

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

• Ввести, внимательно, остальные ограничения. (Более быстрый и рациональный способ введения зависимостей – массивами, протаскивая мышь по нужным ячейкам). Для левой части (ограничений по сырью, знак “<=”) массив: $N$6:$N$12, правой части: $P$6:$P$12 (рис 5). По остальным ограничениям (типа “>=”) получим массив $N$13:$N$14 >= $P$13:$P$14.

Рисунок 5 Добавление ограничения - массивом.

Для решения задачи, достаточно, введенных ограничений, но добавим еще одно: $N$15 = $N$15, его необходимость вызвана тем, что расчетное значение целевой функции (кое, нам - важно) не отображается в отчете по Устойчивости … После данного приема, значение явится в виде последнего ограничения, что, затем можно увидеть во второй таблице отчета по Устойчивости.

• После ввода последнего ограничения ОК.

На экране диалоговое окно Поиск решения с введенными условиями, рисунок 6.

Рисунок 6 Диалоговое окно Поиск решения.

8. Ввод параметров для решения задачи линейного программирования (рис. 7).

• Открыть окно Параметры поиска решения;

• Установить флажок Линейная модель, что обеспечит применение симплекс-метода;

• Установить флажок Неотрицательные значения;

• ОК. (На экране диалоговое окно Поиска решения);

• Выполнить.(На экране диалоговое окно Результаты поиска решения рис. 8).

Рисунок 7 Параметры поиска решения

Рисунок 8 Результаты решения

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

Для более углубленного анализа, имеет смысл изучить остальные отчеты.