Транспортная задача представляет собой задачу поиска наиболее оптимального варианта перевозок однотипного товара от поставщика к потребителю. Её основой является модель, широко применяемая в различных сферах математики и экономики. В Microsoft Excel имеются инструменты, которые значительно облегчают решение транспортной задачи. Выясним, как их использовать на практике.
Общее описание транспортной задачи
Главной целью транспортной задачи является поиск оптимального плана перевозок от поставщика к потребителю при минимальных затратах. Условия такой задачи записываются в виде схемы или матрицы. Для программы Excel используется матричный тип.
Если общий объем товара на складах поставщика равен величине спроса, транспортная задача именуется закрытой. Если эти показатели не равны, то такую транспортную задачу называют открытой. Для её решения условия следует привести к закрытому типу. Для этого добавляют фиктивного продавца или фиктивного покупателя с запасами или потребностями равными разнице между спросом и предложением в реальной ситуации. При этом в таблице издержек добавляется дополнительный столбец или строка с нулевыми значениями.
Инструменты для решения транспортной задачи в Эксель
Для решения транспортной задачи в Excel используется функция . Проблема в том, что по умолчанию она отключена. Для того, чтобы включить данный инструмент, нужно выполнить определенные действия.
- Делаем перемещение во вкладку .
- Кликаем по подразделу .
- В новом окне переходим по надписи .
- В блоке , который находится внизу открывшегося окна, в выпадающем списке останавливаем выбор на пункте . Делаем клик по кнопке .
- Запускается окно активации надстроек. Устанавливаем флажок возле пункта . Кликаем по кнопке .
- Вследствие этих действий во вкладке в блоке настроек на ленте появится кнопка . Она нам и понадобится при поиске решения транспортной задачи.
Пример решения транспортной задачи в Excel
Теперь давайте разберем конкретный пример решения транспортной задачи.
Условия задачи
Имеем 5 поставщиков и 6 покупателей. Объёмы производства этих поставщиков составляют 48, 65, 51, 61, 53 единиц. Потребность покупателей: 43, 47, 42, 46, 41, 59 единиц. Таким образом, общий объем предложения равен величине спроса, то есть, мы имеем дело с закрытой транспортной задачей.
Кроме того, по условию дана матрица затрат перевозок из одного пункта в другой, которая отображена на иллюстрации ниже зеленым цветом.
Решение задачи
Перед нами стоит задача при условиях, о которых было сказано выше, свести транспортные расходы к минимуму.
- Для того, чтобы решить задачу, строим таблицу с точно таким же количеством ячеек, как и у вышеописанной матрицы затрат.
- Выделяем любую пустую ячейку на листе. Кликаем по значку , размещенному слева от строки формул.
- Открывается «Мастер функций». В списке, который предлагает он, нам следует отыскать функцию . Выделяем её и жмем на кнопку .
- Открывается окно ввода аргументов функции . В качестве первого аргумента внесем диапазон ячеек матрицы затрат. Для этого достаточно выделить курсором данные ячейки. Вторым аргументом выступит диапазон ячеек таблицы, которая была приготовлена для расчетов. Затем, жмем на кнопку .
- Кликаем по ячейке, которая расположена слева от верхней левой ячейки таблицы для расчетов. Как и в прошлый раз вызываем Мастер функций, открываем в нём аргументы функции . Кликнув по полю первого аргумента, выделяем весь верхний ряд ячеек таблицы для расчетов. После того, как их координаты занесены в соответствующее поле, кликаем по кнопке .
- Становимся в нижний правый угол ячейки с функцией . Появляется маркер заполнения. Жмем на левую кнопку мыши и тянем маркер заполнения вниз до конца таблицы для расчета. Таким образом мы скопировали формулу.
- Кликаем по ячейке размещенной сверху от верхней левой ячейки таблицы для расчетов. Как и в предыдущий раз вызываем функцию , но на этот раз в качестве аргумента используем первый столбец таблицы для расчетов. Жмем на кнопку .
- Копируем маркером заполнения формулу на всю строку.
- Переходим во вкладку . Там в блоке инструментов кликаем по кнопке .
- Открываются параметры поиска решения. В поле указываем ячейку, содержащую функцию . В блоке устанавливаем значение . В поле указываем весь диапазон таблицы для расчета. В блоке настроек жмем на кнопку , чтобы добавить несколько важных ограничений.
- Запускается окно добавления ограничения. Прежде всего, нам нужно добавить условие того, что сумма данных в строках таблицы для расчетов должна быть равна сумме данных в строках таблицы с условием. В поле указываем диапазон суммы в строках таблицы расчетов. Затем выставляем знак равно (=). В поле указываем диапазон сумм в строках таблицы с условием. После этого, жмем на кнопку .
- Аналогичным образом добавляем условие, что столбцы двух таблиц должны быть равны между собой. Добавляем ограничение, что сумма диапазона всех ячеек в таблице для расчета должна быть большей или равной 0, а также условие, что она должна быть целым числом. Общий вид ограничений должен быть таким, как представлен на изображении ниже. Обязательно проследите, чтобы около пункта стояла галочка, а методом решения был выбран . После того, как все настройки указаны, жмем на кнопку .
- После этого происходит расчет. Данные выводятся в ячейки таблицы для расчета. Открывается окно результатов поиска решения. Если результаты вас удовлетворяют, жмите на кнопку .
Как видим, решение транспортной задачи в Excel сводится к правильному формированию вводных данных. Сами расчеты выполняет вместо пользователя программа.
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 13048 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.