Excel10УКР

										81
3.9.
Z min 7x11 1x12						4x13		2x14	2x21	15x22 4x23 3x24 7x31 10x32 5x33 8x34 2x41
4x42 11x43 10x44
x11 x12 x13					x14		14
	x22		x23		x24		94
x21
	x32		x33		x34		154
x31
	x42 x43				x44		28
x41
x11 x21 x31					x41		10
	x22		x32		x42		25
x12
	x23 x33				x43		215
x13
x	x	24	x	34	x	44	40
14
	xij	0
3.10.
Z min 6x11 5x12 2x13								4x14	15x21 14x22 13x23 10x24 7x31 11x32 8x33 5x34
x	x		x		x		110
11	12		13		14
x21 x22 x23					x24		110
	x32 x33				x34		110
x31
x11 x21 x31					97
	x22		x32		144
x12
	x23 x33				66
x13
x	x	24	x	34	23
14
	xij	0
3.11
Z min 3x11 8x12						5x13		7x21	5x22	6x23 2x31 7x32 4x33 9x41 8x42 4x43

x11 x12 x13 400

x21 x22 x23 550x31 x32 x33 700x41 x42 x43 300

x11 x21 x31 x41 500

x12 x22 x32 x42 850x13 x23 x33 x43 600 xij 0

3.12.

Z min 15x11 7x12 11x13 4x14 6x21 4x22 12x23 8x24 7x31 11x32 5x33 10x34

x11 x12 x13 x14 900x21 x22 x23 x24 1200

x31 x32 x33 x34 900

x11 x21 x31 1000x12 x22 x32 550x13 x23 x33 490

x14 x24 x34 960

xij 0

82

3.13.

Z min 21x11 14x12 27x13 15x14 7x21 20x22 13x23 11x24 10x31 11x32 14x33 12x34

x	x		x		x	125
11	12		13		14
x21 x22			x23		x24	145
	x32		x33		x34	25
x31
x11 x21			x31		115
	x22		x32		65
x12
	x23		x33		75
x13
x	x	24	x	34	40
14

xij 0

3.14.

Z min 8x11 11x12						1x13 4x14 5x21 2x22 7x23 3x24 10x31 4x32 3x33 5x34
x	x		x		x	120
11	12		13		14
x21 x22			x23		x24	31
	x32		x33		x34	69
x31
x11 x21			x31		115
	x22		x32		65
x12
	x23		x33		25
x13
x	x	24	x	34	15
14

xij 0

3.15.

Z min 11x11 21x12 13x13 8x14 4x21 7x22 10x23 13x24 8x31 6x32 11x33 7x34

x11 x12 x13 x14 1210x21 x22 x23 x24 1100

x31 x32 x33 x34 730

x11 x21 x31 950x12 x22 x32 525x13 x23 x33 765

x14 x24 x34 800

xij 0

3.16.

Z min 5x11 2x12 1x13 1x14 3x21 7x22 5x23 5x24 6x31 5x32 4x33 4x34

x		x		x		x	100
	11	12		13		14
x21 x22 x23						x24	110
				x33		x34	90
x31 x32
x11		x21 x31				25
		x22		x32		135
x12
		x23		x33		40
x13
x		x	24	x	34	100
	14
xij	0

83

3.17.

Z min 9x11 5x12 3x13 10x14 6x21 3x22 8x23 2x24 3x31 8x32 4x33 5x34

x11 x12 x13 x14 25x21 x22 x23 x24 55

x31 x32 x33 x34 22x11 x21 x31 45

x12 x22 x32 15x13 x23 x33 22

x14 x24 x34 20

xij 0

3.18.

Z min 4x11 x12 2x13 5x14 3x15 2x21 1x22 8x23 3x24 5x25 4x31 8x32 7x33 1x342x35 6x41 2x42 5x43 7x44 4x45

x11 x12		x13	x14	x15	200
	x22	x23	x24	x25	100
x21
	x32	x33	x34	x35	150
x31
	x42	x43	x44	x45	50
x41

x11 x21				x31		x41		150
x		x	22	x	32	x	42	150
	12
		x23		x33		x43		50
x13
x		x		x		x		60
	14		24		34		44
x		x	25	x	35	x	45	90
	15
xij	0

3.19.

Z min 3x11 8x12 5x13 7x21 5x22 6x23 2x31 7x32 4x33 9x41 8x42 4x43

x11 x12 x13 400

x21 x22 x23 550x31 x32 x33 700x41 x42 x43 300

x11 x21 x31 x41 500

x12 x22 x32 x42 850x13 x23 x33 x43 600 xij 0

84

3.20.

Z min 7x11 x12 4x13 2x14 2x21 15x22 4x23 3x24 7x31 10x32 8x34 2x414x42 11x43 10x44

x11 x12 x13 x14 133x21 x22 x23 x24 94x31 x32 x33 x34 154

x41 x42 x43 x44 28

x11 x21 x31 x41 10x12 x22 x32 x42 25x13 x23 x33 x34 215

x14 x24 x34 x44 100

xij 0

8.6. ЛАБОРАТОРНА РОБОТА № 9. РОЗВ’ЯЗАННЯ ОПТИМІЗАЦІЙНИХ ЗАДАЧ

8.6.1. Транспортна задача

У господарстві при заготівці силосу необхідно перевезти 4000т зеленої маси з п’яти полів до 4 ферм, зокрема з 1–го поля 600т, з 2–го – 240т, з 3–го – 1360т, з 4–го – 1000т і з 5–го – 800т. Для 1–ої ферми потрібний 600т зеленої маси, для 2–ої – 800т, для 3–ої – 1400т, для 4–ої – 1200т.

Відстані (у км.) перевезення зеленої маси з полів до ферм приведено в таблиці 8.6.1.1.

Таблиця 8.6.1.1 Відстані перевезення зеленої маси із полів до ферм (у км)

	№ поля				№ ферми
		1		2			3		4
	1	5		6			2		2
	2	9		7			4		6
	3	7		1			4		5
	4	5		2			2		4
	5	6		4			3		4

Мета – мінімізація транспортних витрат.

Транспортні задачі розділяють на два типи, якщо ресурси, яки споживаються, рівні ресурсам, які вироблені, то такі задачі називають закритими, інакше їх називають відкритими. Ми розглянемо тільки закритий

85

тип транспортних задач. У даній дачі для 4 ферм необхідно 4000т леної маси, таку саму кількість зеленої маси вироблено на 5 полях.

На прикладі вказаної задачі введемо загальну, універсальну схему вирішення оптимізаційних задач.

1 крок. Ввести змінні для оптимізації.

Рішенням даної задачі є таблиця перевезень, у якій вказана кількість тонн маси, що перевозиться, із кожного поля на кожну ферму. По структурі вона співпадає з таблицею 8.6.1. Інтуїтивно зрозуміло, що багато елементів таблиці повинні бути заповнені нулями, оскільки здійснювати перевезення між полем і фермою далеко розташованими один від одного невигідно.

Для наочності представимо задачу у графічному вигляді (рис.

8.6.1.1).

Рис. 8.6.1.1 Схема перевезень з полів на ферми

І так, xij – кількість зеленої маси (т), яка перевозиться із i–го поля на j–у ферму ( i 1...5, j 1...4) .

2 крок. Записати вираз для цільової функції через введені змінні і задані параметри.

Мета оптимізації завжди вказується у постановці задачі. У даній задачі це мінімізація транспортних витрат. Транспортні витрати на перевезення вантажів з одного об’єкту на іншій встановлюються як множення

86

маси вантажу на відстань, у нашому випадку одиниця транспортних витрат т*км.

Позначимо cij – відстань від i-–го поля до j–ої ферми – матриця 5*4 (таблиця 8.5.1), тоді:

5 4

Z cij xij min i 1 j 1

3 крок. Записати вирази для всіх обмежень. Обмеження, як правило, розбиваються на категорії. а) За вивозом з полів:

З1–го поля: x11 x12 x13 x14 600

З2–го поля: x21 x22 x23 x24 240

.....................

З 5–го поля: x51 x52 x53 x54 800

б) За доставкою на ферми:

На 1–у ферму: x11 x21 x31 x41 x51 600

На 2–гу ферму: x12 x22 x32 x42 x52 800

.........................

На 4–у ферму: x14 x24 x34 x44 x54 1200

При записі обмежень необхідно звертати увагу на те, що одиниці вимірювання лівої і правої частин обмеження повинні співпадати. Часто це дозволяє уникнути грубих помилок.

Після того, як всі вимоги, що фігурують в умовах задачі, записані у вигляді обмежень, необхідно записати так звані, природні обмеження, тобто обмеження які не фігурують в умовах задачі, але слідують із її сенсу. Більшість задач, як і ця транспортна задача, це, перш за все, невід’ємність змінних, що оптимізуються:

xij 0,i 1...5, j 1...4.

Часто це може бути цілочисельність частини змінних. Без обліку природних обмежень, як правило, не можливо отримати правильне рішення.

87
Вище приведені математичні формули	часто	називають
тичною моделлю задачі.

Природно, сформульована задача лінійного програмування може бути вирішена в Excel за схемою, яку приведена у розділі 8.5, використовуючи матричні функції. Таке рішення є першою частиною даної лабораторної роботи. Оскільки обмеження для транспортної задачі мають специфіку, а саме всі коефіцієнти при змінних дорівнюють 1 або 0, існує більш просте рішення, наведено на рис. 8.6.1.2.

Рис. 8.6.1.2. Більш просте рішення транспортної задачі

Тут у стовпці F вписані формули підсумовування клітинок відповідного рядка матриці перевезень, поряд у стовпці G задані необхідні об’єми перевезень. У 17 і 18 рядках аналогічно за фермами. Цільова функція об-

числена як =СУММПРОИЗВ(B4:E8;B12:E16).

8.6.2. Оптимізація структури посівних площ

Потрібно визначити оптимальну структуру посівних площ у господарстві, що забезпечує отримання максимуму валового доходу. У господарстві є 2377 га ріллі. Ресурси праці складають 98700 ос./год.

Перелік культур, які вирощуються, та їх економічні характеристики наведено у таблиці 8.6.2.1.

88

Таблиця 8.6.2.1 Перелік культур, які вирощуються, та їх економічні характеристики

			Вихід з 1 га
										Вартість ва-
					На		Витрати праці	Матеріально–
					корм					лової проду-
	Культура						на 1 га	грошові витра-
			На про-							кції з 1га
					(ц		(ос./год.)	ти на 1 га (грн.)
			даж (ц)							(грн.)
					корм.
					од.)
	Озима		36		–		19	214		461
	пшениця
	Озиме жи-		28		–		15	226		435
	то
	Картопля		132		10		81	781		4780
	Однорічні
	трави на		–		32		10	227		266
	зелений
	корм

План із продажу продукції: зерна – 18080 ц, картоплі – 56120 ц. Для забезпечення тваринництва необхідно кормів: картоплі – 2000 ц корм. од., зелених – 8760 ц корм. од.

Побудуємо математичну модель.

1. Змінні для оптимізації.

Структура посівних площ – це площі, що відводяться під різні культури. Таким чином:

x1 – площа, що відводиться під озиму пшеницю (га),

x2 – площа, що відводиться під озиме жито (га),

x3 – площа, що відводиться під картоплю (га),

x4 – площа, що відводиться під однорічні трави на зелений корм (га).

2. Цільова функція – максимум валового доходу:

Vd (валовий дохід)=V p (вартість валової продукції)– Z (загальні витра-

ти).

89

Vp 461 x1 435 x2 4780 x3 266 x4

Z 214 x1 226 x2 781 x3 227 x4

3. Обмеження:

а) За площею:

x1 x2 x3 x4 2377

б) По ресурсах праці:

19 x1 15x2 81x3 10 x4 98700

в) За планом продажу:

36 x1 28 x2 18030 (пшениця і жито)

132 x3 56120 (картопля)

г) Для забезпечення тваринництва: 10 x3 2000 (картопля)

32 x4 8760 (зелені корми)

4. Природні обмеження: xi 0,i 1...4.

Зауваження по оформленню оптимізаційних задач в Excel

Всі початкові дані задачі повинні розташовуватися в окремих клітинках, у заголовках таблиць або у суміжних клітинках повинно бути наведено назви величин із обов’язковою вказівкою одиниць вимірювання. При введенні формул математичної моделі необхідно використовувати посилання на відповідні клітинках із даними, досягаючи того, що при зміні початкових даних для отримання нового рішення достатньо було заново виконати поставлене у пошуку рішення задачі.

8.6.3. Оптимізація розподілу мінеральних добрив

Розрахувати оптимальний план розподілу мінеральних добрив у господарстві під посіви продовольчих культур з метою максимізації загальної вартості надбавки урожаю.

Початкові дані наведені у таблиці 8.6.3.1.

90

															Таблиця 8.6.3.1
						Початкові дані
			№ ділян-			Врожайність		Дози внесення добрив (ц/га)						Надбавка
	Культура				Площа	без внесення								урожаю з 1 га
			ки
						добрив (ц/га)
								Азотні		Фосфорні		Калійні		Ц			Грн.
	Озима		1		100	12		0,6		0,8		0,4		7			105
	пшениця
			2		55	16		0,6		0,9		0,5		8			112
	Озиме жито		1		87	14		0,5		0,7		0,4		6			120
			2		48	15		0,6		0,7		0,4		7			126
	Картопля		1		54	136		0,3		0,7		0,5		34			444
			2		39	137		0,3		0,7		0,5		38			494

Наявність добрив: Азотних – 170 ц Фосфорних – 250 ц Калійних – 150 ц

Потрібно виробити : Зерна не менше 5000 ц

Картоплі не менше 13000 ц