Лекция №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 — для их форматирования до чисел с нужной разрядностью следует использовать кнопку «Формат с разделителями» на панели инструментов.