Транспортні тарифи

Цегельний завод	Будівельний майданчик
Б1	Б2	Б3	Б4	Б5
З1
З2
З3

Потрібно визначити оптимальний за критерієм мінімізації загальних транспортних витрат план постачання цеглою будівельних майданчиків. Вся вихідна інформація до цієї задачі наведена на рис. 2.1.

Цегельні заводи, їх виробнича потужність	Маршрути, транспортні тарифи	Будівельні майданчики, їх попит
	З–1 18							Б–1
								Б–2
	З–2
							Б–3
								Б–4
	З–3
							Б–5

Рис. 2.1. Інформація до задачі про оптимізацію плану перевезень цегли

Економіко–математична модель. Передусім зазначимо, що кількість постачальників цегли – цегельних заводів – дорівнює 3, а кількість споживачів цегли – будівельних майданчиків – 5. Загальні щодобові виробничі потужності постачальників дорівнюють 18+24+28=70 тис. цеглин, а попит споживачів – 8+10+12+15+20=65 тис. цеглин. Отже, виробничі потужності достатні для забезпечення наявного попиту, тобто розв’язок задачі існує.

Далі уведемо позначення невідомих: – обсяг перевезень продукції (у тисячах цеглин) від -го постачальника до -го споживача (; ); – загальні транспортні витрати (у гривнях), що відповідають певному плану перевезень продукції. Саме такі змінні ми використовували в попередньому підрозділі для побудови економіко–математичної моделі транспортної задачі.

Щоб у подальшому полегшити підготовку робочого аркушу Excel для розв’язування задачі на комп’ютері за допомогою інструменту “ Поиск решения ”, уведемо допоміжні змінні , , які показуватимуть обсяги вивезеної від відповідного -го постачальника продукції, а також змінні , , – обсяги продукції, ввезеної кожному споживачу.

За наведених позначень та наявних вихідних даних економіко–математична модель задачі, що розглядається, набере вигляду:

Мінімізувати загальні витрати на перевезення цегли:

при обмеженнях:

а) щодо потужностей постачальників:

б) щодо попиту споживачів:

в) щодо невід’ємності основних змінних, які показують обсяги перевезень за відповідними маршрутами:

Зазначимо, що вимога невід’ємності допоміжних змінних () є надлишковою через невід’ємність кожної з основних змінних (; ), оскільки загальний обсяг вивезеної від постачальника продукції є сумою обсягів перевезень продукції від цього постачальника до кожного із споживачів.

Підготовка робочого аркуша Excel. Домовимося кожну задачу опрацьовувати в окремій книзі Excel, яку називатимемо відповідно до назви задачі, що в ній розв’язується. Отже, створимо файл “Cegla.xls” та розпочнемо роботу на першому аркуші цієї книги, який має назву “ Лист 1 ”.

Виконаємо такі дії.

1. Об’єднаємо клітинки A1:D1 та внесемо туди назву задачі: “Оптимізація плану перевезень цегли”.

2. В масив клітинок A3:B7 занесемо таблицю з інформацією про потужності цегельних заводів, причому для обчислення загальної виробничої потужності в клітинку B7 уведемо формулу: =СУММ(B4:B6). Ця таблиця матиме такий вигляд:

Завод	Потужність
З-1
З-2
З-3
Разом

3. В масив клітинок A9:G10 занесемо таблицю з інформацією про попит цегли на кожному з будівельних майданчиків. Для обчислення сукупного попиту у клітинку G10 уведемо формулу: =СУММ(B10:F10). Таблиця матиме вигляд:

Буд-майданчик	Б-1	Б-2	Б-3	Б-4	Б-5	Всього
Попит

4. В масив клітинок A12:F17 занесемо таблицю з інформацією про транспортні тарифи:

Транспортні тарифі
Завод	Будівельний майданчик
Б-1	Б-2	Б-3	Б-4	Б-5
З-1
З-2
З-3

5. В клітинках A19:D19 передбачимо інформацію про загальні транспортні витрати. Щоб побудувати цю таблицю, клітинки A19:C19 об’єднаємо та впишемо слова: “Загальні транспортні витрати”, а клітинку D19 оберемо за цільову. Пізніше в цю клітинку потрібно буде ввести формулу для обчислення загальних транспортних витрат. Ось як виглядатимуть зараз клітинки A19:D19:

Загальні транспортні витрати

6. Для таблиці з інформацією про план перевезень цегли оберемо масив клітинок A21:F26. Частина клітинок цього масиву міститиме необхідні написи, а для значень змінних, що безпосередньо означають обсяги відповідних перевезень, потрібен лише фрагмент B24:F26 – зараз ці клітинки порожні:

План перевезень цегли
Завод	Будівельний майданчик
Б-1	Б-2	Б-3	Б-4	Б-5
З-1
З-2
З-3

7. Уведемо в цільову клітинку D19 формулу для обчислення загальних транспортних витрат. Зараз зручно скористатися спеціальною функцією, яка дозволяє знаходити суму добутків відповідних елементів числових масивів – функцією СУММПРОИЗВ, а також врахувати, що транспортні тарифи містяться в масиві клітинок B15:F17, а обсяги перевезень міститимуться в масиві клітинок B24:F26. Отже, в клітинку D19 вводимо формулу:

=СУММПРОИЗВ(B15:F17;B24:F26)

8. Масив клітинок A28:B31 відведемо для інформації про обсяги вивезеної з заводів цегли. Для цього, крім необхідних надписів, уведемо:

у клітинку B19 – формулу: =СУММ(B24:F24)

у клітинку B20 – формулу: =СУММ(B25:F25)

у клітинку B21 – формулу: =СУММ(B26:F26)

/Бачимо, що для опрацювання клітинок B20 і B21 формулу з клітинки B19 потрібно просто “протягнути” на клітинки B20, B21./

Отримаємо поки що таке:

Завод	Вивезено
З-1
З-2
З-3

9. У масиві клітинок A33:F34 передбачимо інформацію про обсяги ввезень цегли на кожний з будівельних майданчиків:

Буд-майданчик	Б-1	Б-2	Б-3	Б-4	Б-5
Ввезено

Для автоматичного заповнення цієї таблиці уведемо потрібні формули:

у клітинку B34: =СУММ(B24: B26)

у клітинку C34: =СУММ(C24: C26)

у клітинку D34: =СУММ(D24: D26)

у клітинку E34: =СУММ(E24: E26)

у клітинку F34: =СУММ(F24: F26)

/Можна “протягнути” формулу з клітинки B34 на клітинки C34, D34, E34 та F34./

Підготовку робочого аркушу закінчено. Він матиме такий вигляд, як це показано на рисунку 2.2.

	A	B	C	D	E	F	G
	Оптимізація плану перевезень цегли
	Завод	Потужність
	З-1
	З-2
	З-3
	Разом:
	Буд-майданчик	Б-1	Б-2	Б-3	Б-4	Б-5	Всього
	Попит
	Транспортні тарифи
	Завод	Будівельний майданчик
	Б-1	Б-2	Б-3	Б-4	Б-5
	З-1
	З-2
	З-3
	Загальні транспортні витрати
	План перевезень цегли
	Завод	Будівельний майданчик
	Б-1	Б-2	Б-3	Б-4	Б-5
	З-1
	З-2
	З-3
	Завод	Вивезено
	З-1
	З-2
	З-3
	Буд-майданчик	Б-1	Б-2	Б-3	Б-4	Б-5
	Ввезено

Рис. 2.2. Вид робочого листа, підготовленого для розв’язування
задачі про оптимізацію плану перевезень цегли

Пошук розв’язку.

1. Оберемо команду “ Поиск решения ” в меню “ Сервис ”.

2. У діалоговому вікні “ Поиск решения ”, яке з’явиться на екрані, в полі “ Установить целевую ячейку ” вкажемо на адресу цільової клітинки: D19.

3. Перемикач вибору оптимізаційного спрямування цільової функції ввімкнемо у положення “ минимальному значению ”.

4. У полі “ Изменяя ячейки ” вкажемо на адреси клітинок з основними незалежними змінними, які відповідають шуканим обсягам перевезень цегли за кожним з маршрутів: B24:F26.

5. У поле “ Ограничения ” введемо обмеження задачі. Для цього натиснемо кнопку “ Добавить ” та введемо обмеження щодо виробничих потужностей заводів:

$B$29:$B$31

<=

$B$4:$B$6

та щодо попиту на цеглу з боку будівельних майданчиків:

$B$34:$F$34

=

$B$10:$F$10

/Зараз ми скористалися можливістю використовувати в Excel відношення “<=” та “=” не лише щодо двох чисел, а також і щодо двох числових масивів однакової розмірності – у такому разі зазначене відношення мають задовольняти усі пари відповідних елементів вказаних у відношенні масивів./

6. Введемо параметри пошуку рішення: “ Линейная модель ” та “ Неотрицательные переменные ”.

7. Натиснемо на кнопку “ Выполнить ”.

8. У вікні “ Результаты поиска решения ”, яке через мить з’явиться на екрані, увімкнемо перемикач “ Сохранить найденное решение ” та натиснемо ОК.

9. Прочитаємо на робочому аркуші Excel знайдений розв’язок (рис. 2.3). Оптимальному плану перевезень відповідають мінімальні загальні транспортні витрати у розмірі 2215 гривень.

Розв’язок задачі знайдено.

	A	B	C	D	E	F	G
	Загальні транспортні витрати
	План перевезень цегли
	Завод	Будівельний майданчик
	Б-1	Б-2	Б-3	Б-4	Б-5
	З-1
	З-2
	З-3
	Завод	Вивезено
	З-1
	З-2
	З-3
	Буд-майданчик	Б-1	Б-2	Б-3	Б-4	Б-5
	Ввезено

Рис. 2.3. Фрагмент робочого листа з розв’язком задачі

про оптимізацію плану перевезень цегли

Задачу про оптимізацію плану перевезень цегли розв’язано.

2.4. Завдання для самостійного опрацювання

З п’яти цегельних заводів З₁, З₂, З₃, З₄ та З₅ цегла щоденно відвантажується на сім будівельних майданчиків М₁,..., М₇. Щодобові виробничі потужності заводів, щоденна потреба будівельників у цеглі та транспортні тарифи на перевезення однієї тисячі цеглин наведено в таблицях 2.2 – 2.4.

Таблиця 2.2

Виробничі потужності цегельних заводів, тис. цеглин на добу

Цегельний завод	З-1	З-2	З-3	З-4	З-5	Разом
Потужність

Таблиця 2.3