Лекция №15. Решение транспортной задачи помощью MS Excel

 

Решение транспортной задачи в Excel

Решение транспортной задачи в Excel — условное название для методов нахождения решения транспортной задачи с применением электронных таблиц Microsoft Excel. Надстройка «Поиск решения» в Microsoft Excel позволяет напрямую находить оптимальное решение транспортной задачи. В MS Excel также можно организовать поиск начального допустимого плана и пошаговое решение транспортной задачи симплеккс-методом.

Рабочий лист

При решении в качестве начального этапа можно подготовить рабочий лист как показано на рисунке:

	A	B	C	D	E	F	G
	Цены перевозки, руб./кг	Потребитель 1	Потребитель 2	Потребитель 3	Потребитель 4
	Поставщик 1
	Поставщик 2
	Поставщик 3
	Объёмы перевозки, кг:	Потребитель 1	Потребитель 2	Потребитель 3	Потребитель 4		Запасы
	Поставщик 1		-		-
	Поставщик 2			-
	Поставщик 3	-	-		-
	Спрос:
	Целевая функция:

 

Формулы в таблице

Ячейки рядом с серыми (на изображении — строка 12 и столбец F) содержат формулы суммирования по строке и столбцу.

· F9: =СУММ(B9:E9)

· F10: =СУММ(B10:E10)

· F11: =СУММ(B11:E11)

· B12: =СУММ(B9:B11)

· C12: =СУММ(C9:C11)

· D12: =СУММ(D9:D11)

· E12: =СУММ(E9:E11)

В отмеченной красным цветом итоговой ячейке использована формула =СУММПРОИЗВ(B4:E6;B9:E11), которая вычисляет сумму произведений цены на объем для каждого из путей перевозки груза. Другие ячейки на этом рабочем листе формул не содержат.

Изменение числа поставщиков и потребителей

Если число строк и столбцов (поставщиков и потребителей) не совпадает с примером, их добавляют, "не задевая" первую и последнюю колонку из диапазона, чтобы не испортились настройки. Например, чтобы добавить еще одну колонку, добавляйте ее после столбца B, а нового поставщика — после строки Поставщик 1 в двух местах), после чего нужно «размножить» соответствующие формулы и оформление из имеющихся ячеек на вновь вставленные.

Ввод исходных данных

В отмеченные зеленым цветом клетки затем надо ввести цены, в отмеченные серым — объем спроса и предложения. Желтые ячейки (объемы перевозки) при вызове надстройки «Поиск решения» программа посчитает сама.

Сбалансированность задачи

Сумма спроса и сумма запасов (в этом примере = 90) должны совпадать, в противном случае требуется ввести фиктивного отправителя или поставщика с нулевыми ценами доставки.

Установка надстройки

Чтобы начать расчет, нужно убедиться, что в меню Сервис есть пункт меню «Поиск решения»:

Если его там нет, то нужно зайти в пункт «Надстройки» и установить соответствующую надстройку:

Выполнение вычислений

Затем необходимо вызвать пункт меню «Сервис — Поиск решения»:

В этом примере наложено целочисленное ограничение, если оно не требуется, то его можно убрать (выделить в настройках строку со словом «целое» и нажать кнопку «Удалить»).

Для начала поиска решения нужно нажать кнопку «Выполнить», затем в появившемся окне — «Сохранить найденное решение».

Округление

В итоговом решении могут оказаться числа наподобие 19.99999 или 1E-6 — для их форматирования до чисел с нужной разрядностью следует использовать кнопку «Формат с разделителями» на панели инструментов.