Транспортная задача в Excel. Поиск лучшего способа транспортировки от продавца к покупателю

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

Описание транспортной задачи

Итак, у нас есть два контрагента, которые постоянно взаимодействуют друг с другом. В нашем случае это покупатель и продавец. Нам нужно придумать, как перевозить грузы так, чтобы затраты были минимальными. Для этого необходимо представить все данные в схематическом или матричном виде. В Excel мы используем последний вариант. В целом существует два типа транспортных задач:

1. Закрыто. В этом случае спрос и предложение находятся в равновесии.
2. Открыть. Здесь нет равенства между спросом и предложением. Чтобы получить решение этой проблемы, необходимо сначала привести ее к первому типу, уравнивающему спрос и предложение. Для этого нужно ввести дополнительный показатель – наличие условного покупателя или продавца. Кроме того, необходимо внести определенные изменения в таблицу затрат.

Как включить функцию «Найти решение» в Excel

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

1. Откройте меню «Файл», которое находится в левом верхнем углу окна программы.
2. После этого нажмите на кнопку с параметрами.
3. Далее находим подраздел «Настройки» и переходим в меню управления дополнениями. Это небольшие программы, работающие в среде Microsoft Excel. Видим, что сначала мы кликнули по меню «Надстройки», а затем в правой нижней части установили пункт «Надстройки Excel» и нажали кнопку «Перейти». Все необходимые действия выделены красными прямоугольниками и стрелками.
4. Далее включаем надстройку «Поиск решения», после чего подтверждаем свои действия нажатием кнопки ОК. Исходя из описания настройки, мы видим, что она предназначена для анализа сложных данных, например научных и финансовых.
5. После этого переходим на вкладку «Данные», где видим новую кнопку, которая называется так же, как и надстройка. Его можно найти в группе инструментов «Анализ».

Осталось только нажать на эту кнопку, и мы приступим к решению транспортной задачи. Но перед этим нам следует поговорить еще немного об инструменте «Поиск решения» в Excel. Это специальное дополнение Excel, позволяющее найти максимально быстрое решение проблемы. Характерной особенностью является учет ограничений, которые устанавливает пользователь на этапе подготовки. Говоря простым языком, это подпрограмма, позволяющая определить наилучший способ достижения определенной задачи. К таким задачам могут относиться следующие:

1. Инвестирование, загрузка склада или любая другая подобная деятельность. В том числе доставка товара.
2. Лучший путь. Сюда входят такие цели, как достижение максимальной прибыли при минимальных затратах, как добиться наилучшего качества с использованием имеющихся ресурсов и так далее.

Помимо транспортных задач, данное дополнение также используется для следующих целей:

1. Разработка плана производства. То есть, сколько единиц продукта необходимо произвести, чтобы получить максимальный доход.
2. Найдите распределение труда по различным видам работ так, чтобы общие затраты на производство продукта или услуги были наименьшими.
3. Установите минимальное время, которое потребуется для выполнения всей работы.

Как видите, задачи самые разные. Универсальное правило применения этой надстройки заключается в том, что перед решением задачи необходимо создать модель, которая бы соответствовала ключевым характеристикам поставленной задачи. Модель — это набор функций, которые используют переменные в качестве аргументов. То есть ценности, которые могут измениться.

Важно отметить, что оптимизация набора значений осуществляется исключительно по одному показателю, который называется целевой функцией.

Надстройка Solver перечисляет различные значения переменных, передаваемых в целевую функцию, таким образом, чтобы оно было максимальным, минимальным или равным определенному значению (именно в этом и состоит ограничение). Есть еще одна функция, несколько схожая по принципу работы, и которую часто путают с «Поиском решения». Это называется «Выбор варианта». Но если копнуть глубже, разница между ними огромна:

1. Функция поиска цели не работает более чем с одной переменной.
2. В нем не предусмотрена возможность устанавливать ограничения на переменные.
3. Он способен определить лишь равенство целевой функции определенному значению, но не позволяет найти максимум и минимум. Поэтому для нашей задачи он не подходит.
4. Эффективно рассчитывать можно только в том случае, если модель линейного типа. Если модель нелинейная, то она находит значение, наиболее близкое к исходному значению.

Задача транспорта гораздо сложнее по своей структуре, поэтому дополнения «Подбор параметров» для этого недостаточно. Рассмотрим подробнее, как реализовать функцию «Поиск решения» на практике на примере транспортной задачи.

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

Чтобы наглядно продемонстрировать, как на практике решать транспортные задачи в Excel, приведем пример.

Условия задачи

Предположим, у нас есть 6 продавцов и 7 покупателей. Спрос и предложение между ними распределяются соответственно следующим образом: 36, 51, 32, 44, 35 и 38 единиц являются продавцами, а 33, 48, 30, 36, 33, 24 и 32 единицы являются покупателями. Если просуммировать все эти значения, то окажется, что спрос и предложение находятся в равновесии. Поэтому данная проблема имеет закрытый тип и решается очень просто.

Кроме того, у нас есть информация о том, сколько вам нужно потратить на перевозку из точки А в точку Б (в примере они выделены желтыми ячейками).

Решение – пошаговый алгоритм

Теперь, после того как мы ознакомились с таблицами с исходными данными, для решения этой задачи мы можем использовать следующий алгоритм:

1. Сначала составляем таблицу, состоящую из 6 строк и 7 столбцов.
2. После этого переходим к любой ячейке, не содержащей никаких значений и в то же время лежащей за пределами вновь созданной таблицы, и вставляем функцию. Для этого нажмите на кнопку fx, которая находится слева от строки ввода функции.
3. У нас есть окно, в котором нам нужно выбрать категорию «Математика». Какая функция нас интересует? Тот, что выделен на этом скриншоте. Функция SUMPRODUCT умножает диапазоны или массивы между собой и суммирует их. Именно то, что нам нужно. После этого нажмите клавишу ОК.
4. Далее на экране появится окно, в котором необходимо указать параметры функции. Они следующие:
   1. Массив 1. Это первый аргумент, в который мы записываем диапазон, выделенный желтым цветом. Задать параметры функции можно как с помощью клавиатуры, так и выделив соответствующую область левой кнопкой мыши.
   2. Массив 2. Это второй аргумент, который представляет собой вновь созданную таблицу. Действия выполняются аналогично.

Подтвердите свои действия, нажав кнопку ОК.

1. После этого делаем щелчок левой кнопкой мыши по ячейке, которая во вновь созданной таблице служит верхней левой. Теперь снова нажмите функциональную кнопку «Вставить».
2. Выбираем ту же категорию, что и в предыдущем случае. Но на этот раз нас интересует функция SUM.
3. Теперь наступает этап заполнения аргументов. В качестве первого аргумента мы пишем верхнюю строку таблицы, которую мы создали вначале. Как и раньше, это можно сделать, выделив эти ячейки на листе, или вручную. Подтверждаем свои действия нажатием кнопки ОК.
4. Результаты мы увидим в ячейке с функцией. В данном случае это ноль. Далее переместите курсор в правый нижний угол, после чего появится маркер автозаполнения. Выглядит как маленький черный плюш. Если он появился, зажмите левую кнопку мыши и подведите курсор к последней ячейке нашей таблицы.
5. Это дает нам возможность перенести формулу во все остальные ячейки и получить правильные результаты без необходимости выполнения дополнительных вычислений.
6. Следующий шаг — выбрать верхнюю левую ячейку и вставить функцию SUM в нее. После этого вводим аргументы и с помощью маркера автозаполнения заполняем все оставшиеся ячейки.
7. После этого приступаем непосредственно к решению проблемы. Для этого мы будем использовать дополнение, которое мы включили ранее. Переходим на вкладку «Данные» и там находим инструмент «Поиск решения». Нажимаем на эту кнопку.
8. Теперь перед нашими глазами появилось окно, через которое можно настроить параметры нашего дополнения. Давайте рассмотрим каждый из этих вариантов:
   1. Оптимизируйте целевую функцию. Здесь нам нужно выделить ячейку, содержащую функцию SUMPRODUCT. Мы видим, что эта опция дает возможность выбрать функцию, для которой будет искаться решение.
   2. До. Здесь мы устанавливаем опцию «Минимум».
   3. Путем изменения ячеек переменных. Здесь мы указываем диапазон, соответствующий той таблице, которую мы создали в самом начале (за исключением суммирующей строки и столбца).
   4. С учетом ограничений. Здесь нам нужно добавить ограничения, нажав кнопку «Добавить».
   5. Мы помним, какое ограничение нам нужно создать – сумма значений требований покупателей и предложений продавцов должна быть одинаковой.
9. Задача ограничений выполняется следующим образом:
   1. Ссылка на ячейки. Здесь вводим диапазон таблицы для расчетов.
   2. Условия. Это математическая операция, по которой проверяется диапазон, указанный в первом поле ввода.
   3. Значение условия или ограничения. Здесь мы вводим соответствующий столбец в исходную таблицу.
   4. После того, как все действия выполнены, нажимаем кнопку ОК, тем самым подтверждая свои действия.

Точно такие же операции проделываем и с верхними строками, задав следующее условие: они должны быть равны.

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

После этого все данные будут рассчитаны автоматически, а затем Excel покажет окно с результатами. Это необходимо для того, чтобы перепроверить работу ЭБУ, так как возможны ошибки, если ранее условия были заданы неправильно. Если все верно, то нажмите кнопку «ОК» и увидите готовую таблицу.

Если окажется, что наша задача стала открытого типа, то это плохо, потому что нужно отредактировать исходную таблицу, чтобы задача превратилась в закрытую. Однако когда это будет сделано, оставшийся алгоритм останется прежним.

Заключение

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