Рішення задачі розподілу ресурсів в EXCEL.

1) Введення данних примера 1 в таблицю EXCEL (рис.4).

Рис.4

На рис.4 «краска 1» обозначает краску для внутренних работ, «краска 2» – краску для наружных работ.

Для переменных задачи x₁ и x₂ отведены ячейки B3 и C3. Эти ячейки называются рабочими или изменяемыми ячейками. В изменяемые ячейки ничего не заносится и в результате решения задачи в этих ячейках будет оптимальные значения переменных.

В ячейку D4 вводится формула для вычисления целевой функции задачи (дохода) Z=2x₁+x₂. Чтобы сделать это надо выполнить следующие действия:

курсор в D4;

курсор на кнопку f_x (мастер функций);

В появившемся окне выбрать “Математические” и “СУММПРОИЗВ” (рис. 5).

 

Рис.5.

В окне мастера функций нажать Далее>, в появившемся окне (рис.6) в поле “массив 1” ввести (протаскивая курсор мыши по ячейкам) адреса изменяемых ячеек B3:C3. В поле “массив 2” вводятся адреса ячеек содержащих цены на краски B4:C4, после нажать Готово.

Рис.6

В ячейку D7 вводится формула для вычисления израсходованного количества продукта А: x₁+2x₂, а в ячейку D8 вводится формула для израсходованного количества продукта B: 3x₁+x₂. Обе формулы вводятся аналогично целевой функции (рис.7 и 8).

 

Рис.7

Рис.8

 

Проверить результаты ввода можно следующим образом: при установке курсора в ячейку D4 в строке ввода должно появиться: “=СУММПРОИЗВ(B3:C3; B4:C4)”; в ячейки D7: “=СУММПРОИЗВ(B3:C3; B7:C7)”; в ячейки D8: “=СУММПРОИЗВ(B3:C3; B8:C8)”.

Окончательно после ввода формул и данных экран имеет вид (рис.9):

 

Рис.9

2) Работа в окне “Поиск решения”

В меню “Сервис” выбираем процедуру “Поиск решения”

В появившемся окне (рис.10) нужно установить адрес целевой ячейки D4, значение целевой ячейки: максимальное, адреса изменяемых ячеек B3:C3.

 

Рис.10

Чтобы ввести ограничения задачи, нажать кнопку «Добавить». В появившемся диалоговом (рис.11) окне слева ввести адрес D7 (израсходованное количество продукта А), затем выбрать знак <= и в правой части количество продукта А на складе, равное 3 (или адрес ячейки E7).

 

 

Рис.11

После ввода нажать кнопку «Добавить» и аналогично ввести второе ограничение: D8 <= 3. Снова нажать кнопку «Добавить» и ввести ограничение: B3:C3 >= 0 (соответствующее ограничению x₁, x₂ >= 0). После ввода последнего ограничения нажать ОК. После ввода ограничений окно «Поиска решений имеет» будет иметь вид (рис. 12):

 

 

Рис.12

 

3) Настройка параметров решения задачи.

В окне «Поиск решения» нажать «Параметры» в появившемся окне (рис. 13) установить флажок в пункте «Линейная модель». В этом случае при решении задачи будет использоваться симплекс - метод. Остальные значения можно оставить без изменения. После нажать кнопку ОК

 

 

Рис.13

 

Для решения задачи в окне «Поиск решения» нажать кнопку «Выполнить». Если решение найдено появляется окно (рис.14):

 

 

Рис.14

Для просмотра результатов выбираем тип отчета: «Результаты» и нажимаем кнопку ОК. В появившихся трех таблицах (рис.15) приводятся результаты поиска. Из этих таблиц видно, что в оптимальном решении:

производство краски 1 = B3 = 0.6;

производство краски 2 = С3 = 1.2;

при этом доход = D4 = 2.4;

расход ресурса A = D7 = 3;

расход ресурса B = D8 = 3;

таким образом, оба ресурса дефицитные (соответствующие ограничения называются связанными).

 

Целевая ячейка (Макс)
	Ячейка	Имя	Исходно	Результат
	$D$4	Доход	2,4	2,4
Изменяемые ячейки
	Ячейка	Имя	Исходно	Результат
	$B$3	Краска 1	0,6	0,6
	$C$3	Краска 2	1,2	1,2
Ограничения
	Ячейка	Имя	Значение	Формула	Состояние	Разница
	$D$7	A Расход		$D$7<=$E$7	связанное
	$D$8	B Расход		$D$8<=$E$8	связанное
	$B$3	Краска 1	0,6	$B$3>=0	не связан.	0,6
	$C$3	Краска 2	1,2	$C$3>=0	не связан.	1,2

Рис.15

«Отчет по результатам» состоит из трех таблиц (рис.15):

в таблице 1 приводятся сведения о целевой функции;

в таблице 2 приводятся значения переменных задачи;

в таблице 3 показаны результаты поиска для ограничений задачи.

Первоначальная таблица EXCEL заполняется результатами, полученными при решении (на рис.16 появившиеся значения в темных ячейках).

Рис.16

 

Варіанти завдань.

1. Продукция может производиться двумя технологическими способами Т1 и Т₂. На производство продукции затрачиваются ресурсы трех видов К₁; К₂; К₃, запасы которых равны: 15; 18; 8. Расход ресурсов на производство всей продукции по первому технологическому способу составляет 2; 4; 0, а по второму - 3; 2; 2. Выход продукции по способу Т1 равняется 10 еди­ницам, по Т2 - 8. Определить с какой интенсивностью нужно применять каждый тех. способ, чтобы при этих запасах иметь максимум продукции.

2. Из двух сортов бензина составляют две смеси А и Б. Смесь А содержит 60% бензина первого сорта и 40% - второго. Смесь Б содержит 80% бен­зина первого сорта, 20% - второго. Продажная цена 1 кг смеси А - 10 к.; смеси Б - 12 к. Составить план образования смесей, при котором будет получен максимальный доход, если в наличии 50 т бензина 1-го сорта и 30 т - второго.

3. Предприятие выпускает два вида изделий П1 и П2, на изготовление кото­рых идет 3 вида сырья: 8₁; 8₂; 8₃, запасы которых равны 200, 110, 120 ед. Расход сырья на 1000 ед. продукции составляет: 81 - 20; 10; 82 - 20; 5; 83 -10; 10. Оптовая цена за 1000 шт. изделий составляет: 15; 17 тыс. рублей. Себестоимость производства 1000 шт. изделий составляет 12 и 15 тыс. рублей. Составить план выпуска продукции, обеспечивающий макси­мальную прибыль, предполагая, что сбыт неограничен

4. Предприятие имеет три производственных фактора в количестве 6; 5; 2 тыс. единиц и может организовать производство двумя различными спо­собами. Расход производственных факторов по первому способу произ­водства составляет 1; 1; 3 тыс. единиц, по второму - 3; 1; 2 тыс. По перво­му способу предприятие выпускает в месяц 3 тыс. изделий, в по второму -2 тыс. изделий. Сколько времени предприятие должно работать каждым способом, чтобы получить максимум продукции?

5. На каждую автоколонну из 10 машин, направленных для вывоза груза из района А, выделяется 4 передвижных мастерских, 3 машины тех помощи, 2 мотоцикла. На такую же автоколонну для вывоза груза из района В вы­деляется 3 передвижные мастерские, 1 машина тех помощи. Одна колонна из района А вывозит 2 тыс. тонн груза, из района Б - 1 тыс. тонн груза. Какое количество автоколонн следует направить в каждый район, чтобыобеспечить максимальный вывоз груза, если имеется 200 машин, 20 авто- ремонтных мастерских, 10 машин тех помощи, 16 мотоциклов?

6. Предприятие выпускает два вида изделий П₁ и П₂, используя 4 группы станков (А, Б, В, Г), фонды рабочего времени которых (час.) составляют 10; 30; 20; 12 часов. На производство одного изделия П₁ каждая группа станков тратит (соответственно): 4; 0; 1; 3 ч. Для П₂ - 2; 3; 2; 2 ч. Прибыль от реализации каждого изделия П1 равна 2 рубля; П2 - 3 рубля. Найти план производства, дающий максимальную прибыль.

7. В животноводческом совхозе на производство одного центнера молока тратится 25 рублей, из них на трудовые затраты - 10 рублей, на матери­альные - 15 рублей; производство 1 центнера мяса обходится в 180 руб­лей, из которых 100 рублей - трудовые затраты, 80 рублей - материаль­ные. Государственные закупочные цены за 1 центнер молока - 35 рублей, а за 1 центнер мяса - 200 рублей. Определить оптимальный план произ­водства молока и мяса, если на животноводство выделено 190000 рублей. Фонд зарплаты - 100000 рублей, остальное - на оборудование

8. Из Минска в Гродно необходимо перевезти оборудование трех типов. I типа - 84 ед.; II - 80 ед.; III - 150 ед., для чего используют два вида транс­порта А и Б. Количество оборудования каждого типа на транспорт А со­ставляет: 3; 4; 3 ед., - транспорт Б: 2; 1; 13 ед. Затраты на перевозку транспортом А равны 8 ед., Б - 12 ед. Составить такой план перевозок, чтобы транспортные расходы были минимальными

9. Трикотажная фабрика производит свитеры и кофточки, используя шерсть, силон и нитрон, запасы которых соответственно равны 900; 400; 300 кг. Количество которых соответственно равны 900; 400; 300 кг. Количество каждой пряжи на изготовление 10 свитеров составляет: 4; 2; 1 кг, а 10 кофточек: 2; 1; 1 кг. Прибыль от реализации 10 ед. продукции: 6 и 5 рублей. Найти план выпуска, максимизирующий прибыль.

Лабораторна робота 1.