В этой статье мы пошагово рассмотрим, как решить транспортную задачу посредством функций MS Excel. Задачи данного типа изучаются студентами на таких дисциплинах, как исследование операций и методы оптимальных решений.

Условие

 
Есть некие предприятия и склады с грузом. Каждое предприятие, нуждается в определённом объёме нашего груза. Каждый склад доставляет тонну груза по собственному тарифу. Таким образом, нужно составить маршрут, по которому мы развезём объём груза, удовлетворяющий каждое предприятие, и при этом затратим меньше всего средств.
 
Так транспортная задача выглядит в своём наиболее общем и типовом виде.
 
транспортная задача выглядит
 
С – это цена за тонну. X – это то, сколько мы привезём тонн со склада на предприятие. Например, если мы примем X11 равным 5, это будет значить, что со склада А1 к потребителю B1 мы повезём 5 тонн по цене C11. Вот нам и нужно как-то распределить всё так, чтобы потратить меньше всего денег.
 
сколько мы привезём тонн со склада на предприятие
 

Варианты решения

 
Транспортную задачу можно решить «вручную». Существует несколько подходов к её решению на бумаге. Среди них:
 

  • Метод опорного плана;
  • Метод минимального элемента;
  • Метод Фогеля.

Как правило, решая задачу одним из этих способов, вы получаете решение, находите потенциалы для него и понимаете, что в числе потенциалов есть положительные значения. Соответственно, это говорит о том, что вы нашли неверное решение. Далее вам нужно действовать, что называется, наугад. Вы переставляете различные цифры в таблице, пробуете разные варианты, словом, ищите решение методом «научного тыка». Далее снова пересчитываете потенциалы, и снова ничего не срастается.
 
Однозначного алгоритма, работающего безотказно в любых условиях, к сожалению, пока не придумали.
 
Однако для решения транспортной задачи или проверки полученного нами на бумаге результата, мы можем воспользоваться функционалом MS Excel.

Транспортная задача в Экселе

 
Для решения нам потребуется надстройка «Поиск решения». Возможно, она не будет активирована в вашем редакторе по умолчанию, поэтому, проделываем следующую очередность действий:
 

  • Жмём «Файл»;
  • В появившемся меню нажимаем по предпоследней кнопке «Параметры»;
  • Вновь находим предпоследний пункт «Надстройки» и переходим в «Управление»:

 
Поиск решения
 

  • Ставим галочку в появившемся окне рядом с пунктов «Поиск решения» и жмём «ОК».

 
Ставим галочку в появившемся окне
 

Поиск решения активирован. Далее он будет нами использован.
 

Пример задачи

 
Поиск решения активирован
 
На складах A1 — A4 есть суммарно 100 тонн зерна, и их нужно развести по текущим расценкам в пункты B1 – B3, потратив как можно меньше средств на доставку. Тарифы на доставку указаны в центре таблицы.
 

Шаг 1

 
Дублируем нашу таблицу в Excel.
 
Дублируем нашу таблицу в Excel
 

Шаг 2

 
Рисуем другую таблицу.
 
Рисуем эксель таблицу
 
Диапазон ячеек D12 – F15 заполняем единицами. Эти значения мы впоследствии будем изменять, чтобы найти самый дешёвый вариант перевозки. В диапазоне H12 – H15 должна быть сумма трёх единиц таблицы в строке D12 – F12, а в D17 – F17 – сумма четырёх единиц в столбце. Так напротив каждой строки и каждого столбца
 
Диапазон ячеек
 
самый дешёвый вариант перевозки
 

Шаг 3

Рисуем третью таблицу, которая перемножит соответствующие ячейки первых двух таблиц.
 
перемножит соответствующие ячейки первых двух таблиц
 
Для этого выделяем диапазон 3 на 4 клетки, жмём на кнопку « = », выделяем диапазон D3-F6, жмём на клавиатуре « * », выделяем D12 – F15 и зажимаем сочетание клавиш Ctrl + Shift + Enter. Всё, вы перемножили значения.

 

Шаг 4

 
Теперь суммируем все значения последней таблицы. Для этого просто выберите произвольную свободную ячейку в MS Excel. Введите в неё « =СУММ( » и выделите третью таблицу. Нажмите Enter.
 

Шаг 5

 
Переходим во вкладку «Данные» и находим там «Поиск решения».
 
Переходим во вкладку Данные
 
Щелкаем по данной кнопке. Далее всё делаем, как представлено на рисунке.
 
методы оптимизации в excel
 
Описываю сверху вниз всё окно. Выберите целевую ячейку ту, которую мы сделали в 4-ом шаге нашего решения. Далее выберите минимум. В поле «Изменяя ячейки переменных» выберите диапазон, где мы проставили единицы. Выставляем ограничения. Значения, которые будут находиться вместо единиц, должны быть больше нуля и целыми, а потребности не должны превысить запасов. Жмём «Найти решение».
 
Получаем следующий результат.
 
Изменяя ячейки переменных
 
Если вы всё сделали правильно, то у вас должно быть всё точно так же.

Заключение

 
По второй таблице сверху вы видите, сколько тонн и куда мы повезём. В третьей таблице вы видите, сколько это будет стоить. Например, мы повезём 30 тонн в B1 со склада A1 и 10 тонн со склада A3, так как спрос у пункта B1 равен 40. Аналогично и с другими пунктами.