Решение транспортных задач в MS Excel 2013

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

Транспортная задача

Для строительства четырех объектов используется кирпич, изготавливаемый на трех заводах. Транспортные издержки задаются матрицей $$C:$$
$$C=\begin{pmatrix} 2 & 6 & 3 & 5\\ 9 & 4 & 2 & 7\\ 3 & 2 & 5 & 4 \end{pmatrix}$$
Ежедневно каждый из заводов может изготовить 100, 400 и 200 условных единиц кирпича (так называемые мощности поставщиков). Потребности в кирпиче на каждом из строящихся объектов ежедневно составляют 250, 300, 100 и 200 условных единиц (спрос потребителей). Т.к. суммарная мощность поставщиков не совпадает с суммарным спросом потребителей:
$$100+400+200=700\neq 850=250+300+100+200,$$
то имеем дело с открытой транспортной задачей. Т.к. спрос превышает предложение, то для обеспечения баланса необходимо ввести условного поставщика с мощностью в 150 у.е. и нулевыми транспортными издержками. В противном случае нужно было бы ввести условного потребителя. Для транспортных задач закрытого типа (есть баланс) ничего вводить не нужно.
Транспортная задача заключается в распределении груза для каждой пары «поставщик – потребитель» таким образом, чтобы спрос был удовлетворен и суммарные транспортные издержки были бы минимальными.
Введем исходные данные транспортной задачи в MS Excel.

Рабочий лист EXCEL с введенными исходными данными

Далее обращаемся к надстройке Поиск решения.

Подключение надстройки Поиск решения в Excel 2013

ФайлПараметры Надстройки – в выпадающем списке выбрать Надстройки Excel и нажать на кнопку Перейти… – поставить галочку напротив Поиск решения и нажать OK.

Вызов надстройки Поиск решения

Данные — Анализ — Поиск решения

Заполняем диалоговое окно Параметры поиска решения

Диалоговое окно Параметры поиска решения

Оптимизировать целевую функцию: $B$17 (суммарные транспортные издержки)

До: Минимуму (суммарные транспортные издержки должны быть минимальными)

Изменяя ячейки переменных: $C$13:$F$16 (распределение грузов)

В соответствии с ограничениями (нажимаем добавить):

  • Соответствующие предложения поставщиков из 1 и 2 таблиц равны между собой
  • Соответствующий спрос потребителей из 1 и 2 таблиц равны между собой
  • Распределенные грузы должны быть целыми и неотрицательными


Выберите метод решения: (Поиск решения нелинейных задач методом ОПГ, Поиск решения лин. задач симплекс-методом, Эволюционный поиск решения). Выбираем Поиск решения лин. задач симплекс-методомВ итоге получится следующее:

В диалоговом окне Параметры поиска решения нажимаем на кнопку Найти решение, отмечаем сохранить найденное решение, нажимаем ОК и получаем оптимальное распределение грузов (диапазоне ячеек C13:F16) с минимальными суммарными транспортными издержками (ячейка B17).

Оптимальное решение

Поделиться

Больше материалов

Запросы в Access 2013 с помощью элементов формы

Предлагаем вспомнить теоретические материалы по теме Access: Основні поняття реляційних БД: нормалізація, зв’язок та ключі; Об’єкти СУБД Access; Запити в СУБД MS...

ОС Windows 7. Робота з об’єктами. Стандартні програми.

Основні поняття: Файл — це впорядкований набір даних, який має унікальне ім’я  і зберігається на одному з пристроїв....

ДПА 2013 по инфоматике. 11 класс. Задание 11 (В.5)

Продолжаем готовиться к ДПА по информатике. Предлагаем Вам изучить выполнение практического задания № 11 пятого варианта из сборника заданий ДПА 2013 по...

Надстройка Maple в Excel

С каждым годом увеличивается применение универсальных математических систем (Maple, Mathematica, MATLAB, MatCad и других). Одной из самых лучших математических систем является Maple...

Програмне забезпечення ПК

Під програмним забезпеченням (Software) розуміється сукупність програм, які використовуються обчислювальною системою. До програмного забезпечення (ПЗ) відноситься також вся область діяльності по проектуванню і розробці ПЗ: ...

Материалы по теме

ДПА 2013 по инфоматике. 11 класс. Задание 11 (В.5)

Продолжаем готовиться к ДПА по информатике. Предлагаем Вам изучить выполнение практического задания...

ДПА 2013 по инфоматике. 11 класс. Вариант 1. Задание 11

Задание № 11 первого варианта ДПА 2013 по информатике

Надстройка Maple в Excel

С каждым годом увеличивается применение универсальных математических систем (Maple, Mathematica, MATLAB, MatCad...