Потребители

https://exceltable.com/otchety/reshenie-transportnoy-zadachi

РЕШЕНИЕ ТРАНСПОРТНОЙ ЗАДАЧИ В EXCEL С ПРИМЕРОМ И ОПИСАНИЕМ

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

Планирование перевозок с помощью математических и вычислительных методов дает хороший экономический эффект.

ВИДЫ ТРАНСПОРТНЫХ ЗАДАЧ

Условия и ограничения транспортной задачи достаточно обширны и разнообразны. Поэтому для ее решения разработаны специальные методы. С помощью любого из них можно найти опорное решение. А впоследствии улучшить его и получить оптимальный вариант.

Условия транспортной задачи можно представить двумя способами:

· в виде схемы;

· в виде матрицы.

В процессе решения могут быть ограничения (либо задача решается без них).

По характеру условий различают следующие типы транспортных задач:

· открытые открытые транспортные задачи (запас товара у поставщика не совпадает с потребностью в товаре у потребителя);

· закрытые (суммарные запасы продукции у поставщиков и потребителей совпадают).

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

ПРИМЕР РЕШЕНИЯ ТРАНСПОРТНОЙ ЗАДАЧИ В EXCEL

Предприятия А1, А2, А3 и А4 производят однородную продукцию а1, а2, а3 и а4, соответственно. В условных единицах – 246, 186, 196 и 197. Затем товар поступает в пять пунктов назначения: В1, В2, В3, В4 и В5. Это потребители продукции. Они готовы ежедневно принимать 136, 171, 71, 261 и 186 единиц товара.

Стоимость перевозки единицы продукции с учетом удаленности от пункта назначения:

Производители

Потребители

Объем производства

В1

В2

В3

В4

В5

А1

4,2

3,35

4,65

А2

3,85

3,5

4,9

4,55

А3

4,75

3,5

3,4

4,5

4,4

А4

3,1

5,1

4,4

Объем потребления

Задача: минимизировать транспортные расходы по перевозке продукции.

1. Проверим, является ли модель транспортной задачи сбалансированной. Для этого все количество производимого товара сравним с суммарным объемом потребности в продукции: 246 + 186 + 196 + 197 = 136 + 171 + 71 + 261 + 186. Вывод – модель сбалансированная.

2. Сформулируем ограничения: объем перевозимой продукции не может быть отрицательным и весь товар должен быть доставлен к пунктам назначения (т.к. модель сбалансированная).

3. Введем стоимость перевозки единицы продукции в рабочие ячейки Excel.

4. Введем формулы для расчета суммарной потребности в товаре. Это будет первое ограничение.

5. Введем формулы для расчета суммарного объема производства. Это будет второе ограничение.

6. Вносим известные значения потребности в товаре и объема производства.

7. Вводим формулу целевой функции СУММПРОИЗВ(B3:F6; B9:F12), где первый массив (B3:F6) – стоимость единицы перевозки товаров. Второй (B9:F12) – искомые значения транспортных расходов.

8. Вызываем команду «Поиск решения» на закладке «Данные» (если там нет данного инструмента, то его нужно подключить в настройках Excel, а как это сделать описано в статье: расширенные возможности финансового анализа). Заполняем диалоговое окно. В графе «Установить целевую ячейку» - ссылка на целевую функцию. Ставим галочку «Равной минимальному значению». В поле «Изменяя ячейки» - массив искомых критериев. В поле «Ограничения»: искомый массив >=0, целые числа; «ограничение 1» = объему потребностей; «ограничение 2» = объему производства.

9. Нажимаем «Выполнить». Команда подберет оптимальные переменные при заданных ограничениях.

Так выглядит «сырой» вариант работы инструмента. Экспериментируя с полученными данными, находим подходящие значения.

РЕШЕНИЕ ОТКРЫТОЙ ТРАНСПОРТНОЙ ЗАДАЧИ В EXCEL

При таком типе возможны два варианта развития событий:

· суммарный объем производства превышает суммарную потребность в товаре;

· суммарная потребность больше суммы запасов.

Открытую транспортную задачу приводят к закрытому типу. В первом случае вводят фиктивного потребителя. Его потребности равны разнице всего объема производства и суммы существующих потребностей.

Во втором случае вводят фиктивного поставщика. Объем его производства равен разнице суммарной потребности и суммарных запасов.

Единица перевозки груза для фиктивного участника равняется 0.

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

https://abuzov.ru/reshenie-transportnoj-zadachi-excel/

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

В этом материале попробуем разобраться, как решить транспортную задачу в Excel. Среда решения – Excel. Данный материал подходит для версий программы: 2007, 2010, 2013, 2016.

Постановка задачи и подготовка таблиц

Цель задачи сводится к математическому моделированию минимизации грузопотоков. Довольно часто студенты пишут рефераты на тему поиска решения транспортной задачи. Этот пример можно взять за основу реферата. Рассмотрим решение на конкретном примере.

Задача

В хозяйстве имеются 5 складов минеральных удобрений и 4 пункта, в которые необходимо доставить удобрения. Потребность каждого пункта в удобрениях различна, а так же запасы на каждом складе ограничены. Требуется определить, с какого склада, в какой пункт поставлять, сколько удобрений для минимализации грузооборота перевозок.

Исходные данные:

Наличие минеральных удобрений (либо иной продукции) на складах.

Склады	Наличие удобрений, т.
Склад № 1
Склад № 2
Склад № 3
Склад № 4
Склад № 5

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

Пункты	Потребность в удобрениях
1 пункт
2 пункт
3 пункт
4 пункт

Расстояние между складами и пунктами доставки

	Пункт 1	Пункт 2	Пункт 3	Пункт 4
Склад № 1
Склад № 2
Склад № 3
Склад № 4
Склад № 5

Данные в таблицах. На пересечении столбца конкретного пункта доставки со строкой склада находится информация о расстоянии между этим пунктом доставки и складом. Например, расстояние между 3 пунктом и складом № 3 равно 10 километрам.

Пошаговое решение в Excel

Подготовим таблицы для решения задачи.

Рисунок 1. Изменяемые ячейки.

Значения ячеек в столбце B с третьей по седьмую определяют сумму значения соответствующих строк со столбца C до столбца F.

Например, значение ячейки B3=СУММ(C4:F4)

Аналогично значения в восьмой строке, складываются из суммы соответствующих столбцов. Далее создадим еще одну таблицу.

Рисунок 2. Исходная информация в Excel

В строке 16 по столбцам C-F определим грузооборот по каждому пункты доставки. Например, для пункта 1 (ячейка С16) это рассчитывается по формуле:

C16==C3*C11+C4*C12+C5*C13+C6*C14+C7*C15

Либо, это можно рассчитать с помощью функции СУММПРОИЗВ:

C16 =СУММПРОИЗВ(C3:C7;C11:C15)

В ячейке B4 находится количество минеральных удобрений, перевозимых со склада № 1 в 1 пункт доставки, а в ячейке C11 — расстояние от склада №1 до 1 пункта доставки. Соответственно первое слагаемое в формуле означает полный грузооборот по данному маршруту. Вся же формула вычисляет полный грузооборот перевозок минеральных удобрений в 1 пункт доставки.

В ячейке B16 по формуле =СУММ(C16:F16) будет вычисляться общий объем грузооборота минеральных удобрений. Рабочий лист примет следующий вид.

Рисунок 3. Рабочий лист, приготовленный для решения транспортной задачи.

Для решения транспортной задачи воспользуемся процедурой Поиск решения, которая находится на вкладке Данные. Если у вас нет процедуры Поиск решения, необходимо зайти в Параметры Excel -> Надстройки — > Поиск решения.

После выбора данной процедуры на вкладке Данные откроется диалоговое окно.

Рисунок 4. Диалоговое окно Поиск решения.

Выберем целевую ячейку $B$16, установим ее равной минимальному значению, что бы минимизировать значение конечной ячейки, путем изменения влияющих ячеек, изменяя ячейки, выберем диапазон с единицами $C$3:$F$7.

Рисунок 5. Условия для решения транспортной задачи.

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

1. $B$3:$B$7 <= $B$11:$B$15

2. $C$3:$F$7 >= 0

3. $C$8:$F$8 >= $C$10:$F$10

После всех установок нажмем «Выполнить» и получаем результат.