Решение транспортной задачи в MS Excel. Одной из самых распространенных проблем в экономике является транспортировка груза с минимальными материальными или временными затратами

Одной из самых распространенных проблем в экономике является транспортировка груза с минимальными материальными или временными затратами. Применение математических методов в планировании перевозок даёт большой экономический эффект. Такие транспортные задачи можно легко решать с помощью электронных таблиц MS Excel.

Рассмотрим следующую транспортную задачу:

Для строительства четырёх объектов используется кирпич, изготавливаемый на трёх заводах. Ежедневно каждый из заводов изготавливает 100, 150 и 50 тонн кирпича соответственно (предложение поставщиков). Потребности в кирпиче на каждой из строек ежедневно составляют 75, 80, 60 и 85 тонн соответственно (спрос потребителей). Тарифы перевозок одной тонны кирпича с каждого из заводов к каждому из строящихся объектов задаются матрицей транспортных расходов С.

В произвольной клетке (i, j) стоит коэффициент затрат (затраты на перевозку единицы груза от i-го поставщика к j-му потребителю). Необходимо найти объёмы перевозок от всех поставщиков к потребителям так, чтобы: мощности всех поставщиков были реализованы, спросы всех потребителей были удовлетворены, а суммарные затраты на общую перевозку были бы минимальны.

Обозначим x_ij– объём перевозки от i-го поставщика к j-му потребителю. Заданные мощности поставщиков и спросы потребителей накладывают ограничения на значения неизвестных x_ij. Чтобы мощность каждого из поставщиков была реализована, необходимо составить уравнения баланса для каждой строки таблицы поставок:

Аналогично, для удовлетворения спроса каждого потребителя, подобные уравнения баланса составляются для каждого столбца таблицы поставок:

Объём перевозимого груза не может быть отрицательным, поэтому следует ввести ограничение не отрицательности переменных: x_ij ≥0.

Суммарные затраты F на перевозку вычисляются через коэффициенты затрат следующим образом:

Для математической постановки транспортной задачи обозначим: с_ij – коэффициенты затрат; M_i – мощности поставщиков, N_j – мощности потребителей, (i = 1,2,…,m), (j = 1,2,…,n), m – число поставщиков, n – число потребителей. В этих обозначениях система ограничений примет вид:

(1)

Система (1) включает в себя уравнения баланса по строкам и по столбцам.

При этом суммарная мощность поставщиков равна суммарной мощности потребителей, т.е.

Целевая функция в данном случае следующая:

(2)

Таким образом, на множестве неотрицательных решений системы ограничений (1) найти такое решение, при котором значение целевой функции (2) будет минимально.

Рабочий лист EXCEL с введенными исходными данными для решения транспортной задачи показан на рис 1.

Рис.1

Затем настраиваем программу «Поиск решения» как показано на рис. 2

Рис.2

В появившемся окне «Поиск решения» установите курсор на кнопку «Выполнить» и щелкните левой клавишей мыши.

После того как на рабочем листе появилось решение (рис.3) в появившемся диалоговом окне «Результаты поиска решения» (рис.4) установите курсор на переключатель «Восстановить исходные значения» и щелкните левой клавишей мыши. Для завершения расчетов щелкните на кнопке «ОК».