Транспортная задача. Имеется 4 склада продукции и 5 заводов по переработке этой продукции

 

Имеется 4 склада продукции и 5 заводов по переработке этой продукции. Запасы продукции на складах составляют 150, 180, 145 и 200 тонн соответственно. Возможности заводов по переработке продукции составляют 100, 125, 140, 160 и 150 тонн соответственно.

Затраты на перевозку одной тонны продукции представлены в таблице 9.

 

Таблица 9. Затраты на перевозку одной тонны продукции

 

	Завод 1	Завод 2	Завод 3	Завод 4	Завод 5
Склад 1	14, 32	18, 24	16, 22	24, 12	19, 56
Склад 2	18, 33	16, 42	13, 75	21, 48	11, 85
Склад 3	13, 45	14, 26	17, 15	18, 95	15, 45
Склад 4	15, 55	15, 95	15, 12	19, 42	16, 44

 

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

Построим экономико-математическую модель. Для этого введем следующие обозначения:

Х_ij – количество продукции, перевозимой с i -го склада на
j -ый завод.

C_ij – стоимость перевозки единицы продукции с i -го склада на j -ый завод,

Y – затраты на перевозку всей продукции.

Затраты на перевозку всей продукции можно определить по формуле:

Y = SS X_ij× С_ij

Это и будет наша целевая функция. Ее значение должно быть минимальным. Введем теперь ограничения. Обозначим:

N_i – запасы продукции на i -ом складе,

M_j – возможности j -го завода по переработке продукции.

Тогда ограничения можно записать в следующем виде:

N_i = S X_i;

M_j = S X_j;

X_ij ≥ 0;

X_ij – целые.

 

Для X_ij задается ограничение на целые значения, чтобы программа тратила меньше времени на перебор всех возможных значений. Хотя это ограничение не является обязательным.

Теперь решим задачу в MS EXCEL. Решение будет состоять из следующих этапов:

1-й этап. На этом этапе создадим таблицу с исходными данными.

2-й этап.. На этом этапе создадим таблицу, в которой EXCEL будет находить оптимальные значения объемов продукции, которую надо перевести со складов на заводы.

3-й этап. Создадим таблицу, в которой EXCEL будет вести расчет стоимости перевозимой продукции со складов на заводы. С помощью этой таблицы мы получим общие затраты на перевозку.

4-й этап. Решим задачу, используя функцию Поиск решения.

 

1. Создайте таблицу в EXCEL по образцу (Рис. 53).

 

	A	B	C	D	E	F	G	H
		Завод 1	Завод 2	Завод 3	Завод 4	Завод 5		Имеется
	Склад 1	14, 32	18, 24	16, 22	24, 12	19, 56
	Склад 2	18, 33	16, 42	13, 75	21, 48	11, 85
	Склад 3	13, 45	14, 26	17, 15	18, 95	15, 45
	Склад 4	15, 55	15, 95	15, 12	19, 42	16, 44
	Требуется

 

Рис. 53. Исходная таблица

 

В ячейках B2: F5 расположены значения стоимости перевозок 1 тонны продукции. В ячейках Н2: Н5 находятся значения запасов продукции на складах в тоннах. В ячейках В7: F7 находятся значения возможностей заводов по переработки продукции.

В ячейке H7 находится формула =СУММ(H2: H5).

2. Создайте ниже следующую таблицу (Рис. 54).

 

	A	B	C	D	E	F	G	H
	Оптимальный план перевозок
		Завод 1	Завод 2	Завод 3	Завод 4	Завод 5
	Склад 1
	Склад 2
	Склад 3
	Склад 4

 

Рис. 54. Таблица для расчетов

 

В этой таблице в ячейках B11: F14 EXCEL будет искать значения, удовлетворяющие условиям задачи.

3. В ячейку Н11 введите формулу: = СУММ(В11: F11)

4. Скопируйте эту формулу в ячейки Н12: Н14.

5. В ячейку В16 введите формулу: = СУММ(В11: В14)

6. Скопируйте эту формулу в ячейки С16: F16.

7. В ячейку Н16 ввести формулу: = СУММ(Н11: Н14)

Таким образом, в ячейках Н11: Н14 будут находиться значения объемов продукции, вывезенной со складов, а в ячейках В16: F16 будут находиться значения объемов продукции, доставленной на заводы. В ячейке Н16 будет определено общее количество перевезенной продукции.

Перед решением задачи в этих ячейках EXCEL поместит нули (Рис. 55).

 

	A	B	C	D	E	F	G	H
	Оптимальный план перевозок
		Завод 1	Завод 2	Завод 3	Завод 4	Завод 5
	Склад 1
	Склад 2
	Склад 3
	Склад 4

 

Рис. 55. Таблица объемов перевозок

 

8. Теперь определим затраты на перевозку продукции с каждого склада на каждый завод. Составим следующую таблицу (ее можно скопировать с предыдущей таблицы):

 

	A	B	C	D	E	F	G	H
	Затраты на перевозку
		Завод 1	Завод 2	Завод 3	Завод 4	Завод 5
	Склад 1
	Склад 2
	Склад 3
	Склад 4

 

Рис. 56. Таблица затрат на перевозку продукции

 

9. В ячейку В20 введите формулу: =В2*В11.

10. Скопируйте эту формулу в блок ячеек В20: F23. В таблице в указанных ячейках появятся нули.

11. В ячейку Н20 введите формулу: =СУММ(В20: F20).

12. Скопируйте эту формулу в ячейки Н21: Н23.

13. В ячейку В25 введите формулу: =СУММ(В20: В23).

14. Скопируйте эту формулу в ячейки С25: F25.

15. В ячейку Н25 введите формулу: =СУММ(Н20: Н23).

Таким образом, в ячейках Н20: Н23 будут находиться значения затрат на перевозку продукции, вывезенной со складов, а в ячейках В25: F25 будут находиться значения затрат на перевозку продукции, доставленной на заводы. В ячейке Н25 будут определены общие затраты на перевозку продукции. Перед решением задачи в этих ячейках EXCEL также поместит нули. Тогда общий вид рабочего листа будет следующим (Рис. 57):

 

 

Рис. 57. Вид рабочего листа перед решением

 

Ячейка Н25 будет целевой ячейкой. EXCEL в процессе решения задачи будет перебирать значения в ячейках В11: F14 и вычислять значение общих затрат на перевозку. Затем он выберет тот вариант, при котором значение в целевой ячейке будет минимальным.

16. Установите курсор на ячейку Н25 и выполните команду Сервис - Поиск решения.

17. Задайте параметры Поиска решения (Рис. 58).

 

 

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

 

После выполнения поиска решения получим следующий результат (Рис. 59):

Из полученного решения видно, что на первый завод с первого склада нужно перевезти 100 тонн продукции. На второй завод необходимо перевезти 125 тонн с третьего склада и т.д.

 

 

Рис. 59. Результат поиска решения