Как решить транспортную задачу в excel

Решение транспортной задачи в Excel (сбалансированная задача)

как решить транспортную задачу в excel

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

Цитата взята с википедии.

Решение транспортной задачи рассматривается практически на всех специальностях, где хоть как-то присутствует курс математики. Решить транспортную задачу можно различными способами и программными средствами. Причем если решение такой задачи в математических пакетах типа Mathcad или MATLAB обыденное дело, то решение такой задачи в программе 1С:Предприятие 8.2 уже интересная диковинка.

Смотрите также видео версию статьи «Решение транспортной задачи в Excel (сбалансированная задача)».

Сегодня мы рассмотрим решение сбалансированной транспортной задачи в табличном процессоре MS Excel.

Постановка задачи

Есть запасы однотипной продукции у поставщиков A1, A2, A3, A4.

Существует потребность в этой продукции B1, B2, B3

Стоимость доставки единицы продукции от поставщиков к потребителям представлена в таблице.

Поставщик Потребитель Запас
В1 В2 В2
А1 6 5 2 250
А2 3 7 4 100
А3 7 8 1 80
А4 2 2 3 120
Потребность 150 150 250

Необходимо составить такой план перевозок, который бы удовлетворил все потребности и имел минимальную стоимость.

Решение задачи

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

Для решения транспортной задачи потребуются функции: СУММПРОИЗВ, СУММ и надстройка «Поиск решения».

Для отображения формул необходимо на вкладке «Формулы» в группе «Зависимости формул» выбрать «Показать формулы» либо горячее сочетание клавиш «Ctrl+` (тильда)».

Дальше выбираем команду «Поиск решения» на вкладке «Данные»

Кстати, если дать имена диапазонам ячеек, то окно поиска решения будет выглядеть следующим образом:

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

Источник: https://msoffice-prowork.com/reshenie-transportnojj-zadachi-v-excel-sbalansirovannaya-zadacha/

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

как решить транспортную задачу в excel

Транспортная задача представляет собой задачу поиска наиболее оптимального варианта перевозок однотипного товара от поставщика к потребителю. Её основой является модель, широко применяемая в различных сферах математики и экономики. В Microsoft Excel имеются инструменты, которые значительно облегчают решение транспортной задачи. Выясним, как их использовать на практике.

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

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

Если общий объем товара на складах поставщика равен величине спроса, транспортная задача именуется закрытой. Если эти показатели не равны, то такую транспортную задачу называют открытой. Для её решения условия следует привести к закрытому типу.

Для этого добавляют фиктивного продавца или фиктивного покупателя с запасами или потребностями равными разнице между спросом и предложением в реальной ситуации.

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

Инструменты для решения транспортной задачи в Эксель

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

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

Урок: функция «Поиск решения» в Экселе

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

Теперь давайте разберем конкретный пример решения транспортной задачи.

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

Имеем 5 поставщиков и 6 покупателей. Объёмы производства этих поставщиков составляют 48, 65, 51, 61, 53 единиц. Потребность покупателей: 43, 47, 42, 46, 41, 59 единиц. Таким образом, общий объем предложения равен величине спроса, то есть, мы имеем дело с закрытой транспортной задачей.

Кроме того, по условию дана матрица затрат перевозок из одного пункта в другой, которая отображена на иллюстрации ниже зеленым цветом.

Решение задачи

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

  1. Для того, чтобы решить задачу, строим таблицу с точно таким же количеством ячеек, как и у вышеописанной матрицы затрат.
  2. Выделяем любую пустую ячейку на листе. Кликаем по значку «Вставить функцию», размещенному слева от строки формул.
  3. Открывается «Мастер функций». В списке, который предлагает он, нам следует отыскать функцию СУММПРОИЗВ. Выделяем её и жмем на кнопку «OK».
  4. Открывается окно ввода аргументов функции СУММПРОИЗВ. В качестве первого аргумента внесем диапазон ячеек матрицы затрат. Для этого достаточно выделить курсором данные ячейки. Вторым аргументом выступит диапазон ячеек таблицы, которая была приготовлена для расчетов. Затем, жмем на кнопку «OK».
  5. Кликаем по ячейке, которая расположена слева от верхней левой ячейки таблицы для расчетов. Как и в прошлый раз вызываем Мастер функций, открываем в нём аргументы функции СУММ. Кликнув по полю первого аргумента, выделяем весь верхний ряд ячеек таблицы для расчетов. После того, как их координаты занесены в соответствующее поле, кликаем по кнопке «OK».
  6. Становимся в нижний правый угол ячейки с функцией СУММ. Появляется маркер заполнения. Жмем на левую кнопку мыши и тянем маркер заполнения вниз до конца таблицы для расчета. Таким образом мы скопировали формулу.
  7. Кликаем по ячейке размещенной сверху от верхней левой ячейки таблицы для расчетов. Как и в предыдущий раз вызываем функцию СУММ, но на этот раз в качестве аргумента используем первый столбец таблицы для расчетов. Жмем на кнопку «OK».
  8. Копируем маркером заполнения формулу на всю строку.
  9. Переходим во вкладку «Данные». Там в блоке инструментов «Анализ» кликаем по кнопке «Поиск решения».
  10. Открываются параметры поиска решения. В поле «Оптимизировать целевую функцию» указываем ячейку, содержащую функцию СУММПРОИЗВ. В блоке «До» устанавливаем значение «Минимум». В поле «Изменяя ячейки переменных» указываем весь диапазон таблицы для расчета. В блоке настроек «В соответствии с ограничениями» жмем на кнопку «Добавить», чтобы добавить несколько важных ограничений.
  11. Запускается окно добавления ограничения. Прежде всего, нам нужно добавить условие того, что сумма данных в строках таблицы для расчетов должна быть равна сумме данных в строках таблицы с условием. В поле «Ссылка на ячейки» указываем диапазон суммы в строках таблицы расчетов. Затем выставляем знак равно (=). В поле «Ограничение» указываем диапазон сумм в строках таблицы с условием. После этого, жмем на кнопку «OK».
  12. Аналогичным образом добавляем условие, что столбцы двух таблиц должны быть равны между собой. Добавляем ограничение, что сумма диапазона всех ячеек в таблице для расчета должна быть большей или равной 0, а также условие, что она должна быть целым числом. Общий вид ограничений должен быть таким, как представлен на изображении ниже. Обязательно проследите, чтобы около пункта «Сделать переменные без ограничений неотрицательными» стояла галочка, а методом решения был выбран «Поиск решения нелинейных задач методом ОПГ». После того, как все настройки указаны, жмем на кнопку «Найти решение».
  13. После этого происходит расчет. Данные выводятся в ячейки таблицы для расчета. Открывается окно результатов поиска решения. Если результаты вас удовлетворяют, жмите на кнопку «OK».

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

Помогла ли вам эта статья?

ДА НЕТ

Источник: https://lumpics.ru/the-solution-of-the-transportation-problem-in-excel/

Метод симплекса для чайников — описание с примером подробного решения — Помощник для школьников Спринт-Олимпиады

как решить транспортную задачу в excel

В математике взаимосвязь элементов множества, определяемая правилом, при котором каждое значение зависит от другого бесконечного числа величин, называется функцией. Для определения её крайних значений применяется линейное программирование. Решить поставленную задачу можно несколькими способами. Один из них — симплекс-метод. Для чайников разработаны специальные онлайн-сервисы, помогающие с нахождением ответа, но лучше всё же научиться рассчитывать вручную.

Понятие и алгоритм

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

Метод был разработан в 1947 году математиком из США Бернардом Данцигом. Предложенный способ оказался весьма эффективным для решения задач, связанных с оптимизацией использования ограниченных ресурсов. То есть он позволяет оценить и откорректировать параметры системы, а также получить качественные аналитические результаты.

Существует два подхода решения задачи:

  • графический;
  • симплексный.

Первый можно использовать для оптимизационного решения двухмерных задач. Например, существует два производственных цикла по сборке ящиков. Выпуск товара характеризуется ограничением в поставках древесины и временем формовки изделия. Для одного необходимо 30 досок, а для другого — 40.

Поставщики доставляют в неделю 2 тыс. единиц материала. Первый ящик собирается за 15 минут, а второй — за 30. Нужно определить, какое количество ящиков необходимо производить за неделю на первом конвейере и на втором. При этом первое изделие приносит 10 рублей прибыли, а второе — пять.

ЭТО ИНТЕРЕСНО:  Как активировать бортовой компьютер на рено логан

Время изготовление ограничено 160 часами.

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

Это типовая двухмерная задача, условия неотрицательности которой определяются границами прямых: 30*Х1 + 4 0*Х 2 ≤ 2000 (для досок) и 20*Х 1 ≤ 50*Х 2 = 1600 (для сборки). Отложив по оси ординат Х1, а Х2 по абсцисс, и указав на них точки соответствующие уравнениям, можно будет подобрать оптимальное решение для использования сырья и времени.

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

Симплекс-метод при базисном решении

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

Алгоритм решения задачи линейного программирования симплекс методом следующий:

  • Свести поставленную задачу к канонической форме путём переноса свободных членов в правую часть и ввода дополнительных переменных. В случае отрицательных переменных неравенство умножается на -1. Если в записи используется знак «меньше или равно», переменная используется положительная, в противном случае — отрицательная.
  • В зависимости от количества вводимых значений все переменные принимаются за основные. Их необходимо выразить через неосновные и перейти к базовому решению.
  • Через неосновные переменные выражается функция цели.
  • Если при решении отыскивается ответ с максимумом или минимумом линейной формы и все неосновные переменные получаются только положительными, то задача считается выполненной.
  • Если найденный максимум (минимум) линейной формы в функции имеет одну или несколько неосновных переменных с отрицательными коэффициентами, необходимо перейти к новому базисному решению.
  • Из переменных, входящих в форму с отрицательными или положительными коэффициентами, выбирается наибольшая (по модулю) и переводится в основные.
  • Другими словами, указывается оптимальное опорное решение, способ перехода от одного нахождения ответа к другому, варианты улучшения расчётов. После нахождения первоначального решения с «единичным базисом» вычисляется оценка разложения векторов по базису и заполняется симплексная таблица.

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

    Двухфазный способ

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

    Например, существует ограниченность, описываемая функцией:

    F = C 1 X 1+ C 2 X 2++ CnXn. Используется условие, что Х1Р1+Х2Р2++Х(m +1) P (m +1)+ + XnPn = Р0, где Х j больше либо равно 0 (j =1, n). Принимается, что среди чисел bi (i =1, m) имеются отрицательные.

    Решением будет выражение: х= (b1; b2;; bm ;0;;0), однако этот ответ не будет разрешать задание, так как к нему могут относиться и отрицательные числа. Так как векторы Р1, Р2 Рм единичные, то каждый из них можно описать линейной областью, состоящей из них же. При этом коэффициентами разложения векторов Рj по области будут числа: Xij = aij (i =1, m; j =1, n) по модулю.

    Выражение х= ( b1; b2;; bm ;0;;0) определяется базисом. Называют его псевдоплан. Считается, что если дельта j больше либо равна нулю, то для любого: j ( j =1, n ) по модулю. В то же время если в псевдоплане с находимым базисом существует хотя бы одно отрицательное число, то тогда задача вообще не будет иметь планов. Но когда для этих отрицательных чисел верно, что аij меньше нуля, то можно будет перейти к новому псевдоплану.

    Нужна помощь в подготовке к ЕГЭ по математике? Наши профессиональные репетиторы помогут вам сдать ЕГЭ на 80+ баллов!

    Объяснение псевдоплана помогает построить алгоритм двойственного метода.

    Если взять за основу х = (b1; b2;; bm ;0;;0) и представить это выражение псевдопланом, то, учитывая исходные данные, можно составить симплекс-таблицу. В ней часть элементов будет отрицательная.

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

    Принцип решения задачи включает следующее:

    • нахождение псевдоплана;
    • проверка его на оптимальность;
    • выбор разрешающей строки путём нахождения абсолютной величины отрицательного числа, отношения элементов (m+1) и соответствующей им строке;
    • нахождение нового псевдоплана.

    Если анализ оптимален, считается, что найдено верное решение. В другом случае устанавливается неразрешимость задачи либо составляется новый псевдоплан. Делается это в результате пересчёта табличных данных, например, методом Жордана-Гаусса.

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

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

    Задачи линейного программирования (ЗЛП) позволяют выбрать оптимальную загрузку при перемещении какого-либо товара из одних мест в другие. Во вводных данных указывается число пунктов отправления (м) и количество мест назначения (n). Первые обозначаются как А1, А2Ам, а вторые – В1, В2Вn. За аi принимается объём продукции на складе, а bi – потребность. Затраты на перевозку с i пункта в j обозначаются Сij.

    задача — составить план таким образом, чтобы общая стоимость была минимальна. Пусть дано четыре песчаных карьера, с которых необходимо поставить песок на четыре склада. При этом осуществляться перевозки должны за определённую стоимость. Составляем таблицу.

    Записываем уравнение ограничения. Сумма всего перевезённого песка с первого карьера должна быть меньше или равна 140. Поэтому можно записать: x11+x12+x12+x14+T1 = 140, где Т1 переменная для хранения остатка. Сумма ограничений будет записана как х11+х21+х31 =115. Аналогичные уравнения составляют и для оставшихся карьеров.

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

    • A0 – последний столбец из матрицы;
    • Сб – стоимость перевозок;
    • Х11, Т3 – данные из полученной матрица.

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

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

    Расчёт в Excel

    Для включения пакета анализа в программе необходимо перейти в раздел «Параметры» и выбрать строчку «Перейти». В новом окне найти строчку «Пакет анализа», кликнуть по ней и нажать кнопку ОК.

    Затем понадобится загрузить и открыть шаблон для проверки в Excel. Используя манипулятор типа «мышь» или клавиатуру, выбрать ячейку G4 и выполнить команду «Сервис/Поиск решения». Далее указать исходные данные, а после нажать кнопку «Выполнить».

    Полученное решение можно представить в форме отчёта, содержащего:

  • Результаты – содержит информацию об исходных и конечных значениях целевой и влияющих ячеек, дополнительные сведения об ограничениях.
  • Устойчивость — отчёт, включающий данные о чувствительности решения к малым изменениям.
  • Пределы – включают исходные и конечные значения, а также верхние и нижние границы значений, которые принимают влияющие ячейки при введённых ограничениях.
  • Онлайн-сервис для чайников

    Метод решения относится к высшей математике, поэтому в нём довольно трудно разобраться даже подготовленному человеку, не говоря уже о чайнике. Существует некоторое количество сайтов с подробным онлайн-решением методом симплекса.

    На таких сервисах предлагается ввести количество переменных и строк (ограничений). А далее просто заполнить симплекс-таблицу и нажать расчёт.

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

    Удобным является ещё и то, что обычно на сайтах предлагается создать шаблон решения в Excel или Maple. Решаться любая задача будет почти мгновенно. Подробно можно выполнить расчёт онлайн-калькулятор по методу симплекса на следующих сайтах:

  • «Семестр» (semestr.ru).
  • «Мир математики» (matworld.ru).
  • «Высшая математика» (math-pr.com).
  • «Матзона» (mathzone.ru).
  • «Контрольная работа» (kontrolnaya-rabota.ru).
  • Выполнить расчёт с помощью онлайн-сервисов сможет любой. При этом вероятность ошибки в ответе стремится к нулю. Тем более что для решения задачи даже необязательно знать принцип симплекс-метода.

    ПредыдущаяСледующая

    Источник: https://sprint-olympic.ru/uroki/matematika-uroki/92787-metod-simpleksa-dlia-chainikov-opisanie-s-primerom-podrobnogo-resheniia.html

    Как закрепить столбец в Excel — подробная инструкция

    Вы работали с большими таблицами в Excel. В них названия столбцов удалены от ячеек, которые нужно отредактировать. Это неудобно. Приходится прокручивать страницу. Что предпринять? Рассмотрим, как закрепить столбец в Excel

    Как это работает

    Табличный редактор предлагает такие варианты закрепления областей листа:

    1. Верхняя строка (шапка таблицы). Используйте, когда данные находятся вверху таблицы. При прокрутке вниз можно запутаться;
    2. Первый столбец;
    3. Область сверху и слева листа (строки и столбцы).

    Как это сделать рассмотрим подробнее.

    Первый столбец

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

    ЭТО ИНТЕРЕСНО:  Как прошить оплетку руля

    Несколько столбцов

    Отметьте столбец правее тех, которые закрепить. Например, чтобы сделать неподвижными столбцы А и В, выделите столбец С:
    При горизонтальной прокрутке выделенные столбцы А и В будут неподвижные.

    Закрепление начинается с крайнего левого столбца А. Сделать неподвижными области в середине листа не получится.

    Как закрепить строку в Excel чтоб не двигалась

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

    Как закрепить две строки в Excel

    Выберите строку ниже тех, что закрепить. Например, чтобы сделать неподвижными первые две строки, выделите третью, перейдите:
    Теперь при вертикальной прокрутке строка 1 и 2 неподвижна.

    Как закрепить в Excel строку и столбец

    Это возможно если начать из первой строки и столбца. Отметьте ячейку ниже последней строки и правее столбца. Например, чтобы сделать неподвижными первый столбец и две строки, перейдите в ячейку В3, далее:

    Вывод

    Мы рассмотрели, как закрепить в Экселе (Excel) строку и столбец. Используйте рассмотренные способы чтобы нужный диапазон данных в таблице был неподвижным. Это сделает работу с большими таблицами быстрее и удобнее.

    Источник: https://public-pc.com/kak-zakrepit-stolbecz-v-excel/

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

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

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

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

    Задача

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

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

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

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

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

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

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

    Пункт 1 Пункт 2 Пункт 3 Пункт 4
    Склад № 1 6 4 5 11
    Склад № 2 12 6 4
    Склад № 3 15 7 10 4
    Склад № 4 9 5 12 5
    Склад № 5 3 7 12 11

    Данные в таблицах. На пересечении столбца конкретного пункта доставки со строкой склада находится информация о расстоянии между этим пунктом доставки и складом. Например, расстояние между 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 = 0
    2. $C$8:$F$8 >= $C$10:$F$10

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

    Источник: https://abuzov.ru/reshenie-transportnoj-zadachi-excel/

    Методы оптимальных решений. Транспортная задача в MS Excel

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

    Условие

     Есть некие предприятия и склады с грузом. Каждое предприятие, нуждается в определённом объёме нашего груза. Каждый склад доставляет тонну груза по собственному тарифу. Таким образом, нужно составить маршрут, по которому мы развезём объём груза, удовлетворяющий каждое предприятие, и при этом затратим меньше всего средств.

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

    Вот нам и нужно как-то распределить всё так, чтобы потратить меньше всего денег. 
     

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

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

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

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

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

     

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

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

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

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

     

     

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

     

     

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

    Шаг 1

     Дублируем нашу таблицу в 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

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

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

      

    Если вы всё сделали правильно, то у вас должно быть всё точно так же.

    Заключение

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

    Источник: https://Reshatel.org/reshenie-zadach/transportnaya-zadacha-v-excel/

    Презентация на тему

    • Скачать презентацию (2.02 Мб)
    • 31 загрузок
    • 0.0 оценка

    ВКонтакте

    Твиттер

    Телеграм

    Ваша оценка презентации

    Оцените презентацию по шкале от 1 до 5 баллов

    Презентация для школьников на тему «Решение транспортной задачи в среде Excel» по математике. pptCloud.ru — удобный каталог с возможностью скачать powerpoint презентацию бесплатно.

    • Форматpptx (powerpoint)
    • Количество слайдов41
    • Слова
    • КонспектОтсутствует
    • Слайд 2 Транспортная задача относится к двухиндексным задачам, т. к. ее математическая модель сводится к минимизации целевой функции, выражающей суммарные затраты на перевозку всего груза при ограничениях
    • Слайд 3 Значит, в результате решения задачи необходимо получить матрицу с компонентами .
    • Слайд 4 Три фермерских хозяйства ежедневно могут доставлять в город соответственно 60, 60 и 50 ц молока для обеспечения пяти торговых точек : Стоимость перевозки 1ц молока и потребности торговых точек в молоке указаны в таблице
    • Слайд 5

      Таблица

    • Слайд 6 Переменные : — количество молока , поставляемое i-м фермерским хозяйством в j-ю торговую точку. Целевая функция –суммарные транспортные издержки, которые необходимо минимизировать
    • Слайд 7 По поставщикам (их 3)
    • Слайд 8 И по потребителям (их 5)
    • Слайд 9 Постановка этой задачи была рассмотрена выше . Теперь мы решим эту задачу средствами Excel.1) Указать адреса ячеек, в которые будет помещен результат решения задачи, т. е. изменяемые ячейки . Эти ячейки можно размещать либо в первых строках массива, либо в нижних, как в нашей задаче.2)Ввести исходные данные , как в транспортной таблице.
    • Слайд 10
    • Слайд 11 3)Ввести зависимости для ограничений. Сначала введем условия реализации мощностей поставщиков, т.е. ограничения по запасам: , где -запас поставщика. Количество потребителей равно 5. Поместим курсор в ячейку G11. Выберем функцию СУММ. Выделим для суммирования ячейки B11:F11.
    • Слайд 12
    • Слайд 13
    • Слайд 14 Это мы введем левые части неравенств (1). Обратим внимание : здесь суммирование идет по строке без каких –либо коэффициентов. Теперь введем условия по потребителям:
    • Слайд 15 Нам сейчас нужно просуммировать ячейки по потребителям. Поместим курсор в ячейку В14.Выберем функцию сумм.Выделим для суммирования ячейки В11:В13, где находятся запасы молока. Нажмем кнопку ОК. Эту же последовательность действий повторим для ячеек С14, D14,Е14,F14.
    • Слайд 16
    • Слайд 17 4) Ввести зависимость для целевой функции. Целевую функцию поместим в ячейку G14. Сюда надо ввести формулу . Это двойная сумма, где суммируются произведения. Здесь надо учесть, что перемножаются все коэффициенты из транспортной таблицы и все соответствующие им переменные , стоящие в изменяемых клетках.
    • Слайд 18 Поместим курсор в ячейку G14.Запустим мастер функций .ВыберемСУММПРОИЗВ.Нажмем ОК.
    • Слайд 19 В окне укажем адреса массивов .В нашей задаче это произведение затрат на доставку (ячейки B3:F5)и объемов поставок к каждому потребителю (ячейки B11:F13).В поле Массив1укажем адреса B3:F5, поместив курсор в указанные ячейки.В поле Массив2укажем адресаB11:F13, поместив курсор в эти ячейки.Нажмем ОК.В данной задаче в ячейке G14 появится число 0.
    • Слайд 20
    • Слайд 21
    • Слайд 22 5) Запустить командуПоиск решения. 6) Назначить ячейку для целевой функции. Для этого поместить курсор в целевую ячейку. Адрес $G$14 введется при этом сам. Ввести тип целевой функции –отметить –Минимальное значение
    • Слайд 23
    • Слайд 24 7) Ввести ограничения Первое ограничение –по уровню потребления:B14:F14=B6:F6 второе –по уровню запасовG11:G13≤G3:G5 После ввода ограничений нажмем кнопку ОК.
    • Слайд 25 8)Ввести параметры. Установить Неотрицательные значения и Линейная модель Нажмем ОК. В появившемся окне Поиск решения нажать Выполнить.
    • Слайд 26
    • Слайд 27 Ответ. Распределение товара по торговым точкам приведено на рисунке. Общие затраты на перевозку продукции составят 785 д.е.Спрос торговых точек удовлетворен полностью — они получат 150ц молока. У первого фермерского хозяйства останется нереализованным 20ц молока.
    • Слайд 28

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

    • Слайд 29
    • Слайд 30 Требуется распределить самолеты трех типов по авиалиниям так, чтобы при минимальных суммарных эксплуатационных расходах перевезти по каждой из четырех авиалиний соответственно не менее 300,200,1000 и 500 единиц груза.
    • Слайд 31 Переменные: -количество самолетов i-го типа, назначаемых на j-ю авиалинию.Целевая функция- суммарные транспортные издержки, которые необходимо минимизировать:
    • Слайд 32 По плану перевозок
    • Слайд 33 Если нет необходимости использовать все самолеты, то эти ограничения будут иметь вид неравенств типа ≤.Все переменные должны быть неотрицательными и целочисленными, т.к. число самолетов не может быть не целым.
    • Слайд 34

      Вид электронной таблицы

    • Слайд 35 Ограничения по количеству используемых самолетов вводим с помощью функции СУММ. Эксплуатационные расходы вводим с помощью функции СУММПОИЗВ. Ячейку ЦФ заполняем с помощью функции СУММПОИЗВ.
    • Слайд 36
    • Слайд 37
    • Слайд 38
    • Слайд 39
    • Слайд 40
    • Слайд 41 Общая стоимость перевозок составит 2224 д.е. Из 20 самолетов второго типа будет использовано 8, из 30 самолетов третьего типа будет использовано 20. Месячный объем перевозок выполнен полностью. Оптимальное распределение самолетов приведено на последнем слайде.
    ЭТО ИНТЕРЕСНО:  Как открыть заслонку печки ваз 2110 вручную

    Посмотреть все слайды

    Источник: https://pptcloud.ru/matematika/reshenie-transportnoy-zadachi-v-srede-excel

    Как решить транспортную задачу в Excel

    Эксель можно использовать для решения широкого спектра задач, в том числе, для нахождения наилучшего способа осуществления перевозок от производителя (продавца) к потребителю (покупателю). Давайте посмотрим, каким образом это можно реализовать в программе.

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

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

    Транспортные задачи бывают двух типов:

    • Закрытая – совокупное предложение продавца равняется общему спросу.
    • Открытая – спрос и предложение не равны. Чтобы решить такую задачу, нужно сначала привести ее к закрытому типу. В этом случае добавляется условный покупатель или продавец с недостающим количеством спроса или предложения. Также в таблицу издержек следует внести соответствующую запись (с нулевыми значениями).

    Подготовительный этап: включение функции “Поиск решения”

    Чтобы решить транспортную задачу в Эксель, нужно воспользоваться функцией “Поиск решения”, которую нужно предварительно активировать, т.к. изначально она не включена. Алгоритм действий следующий:

    1. Открываем меню “Файл”.
    2. В перечне слева выбираем пункт “Параметры”.
    3. В параметрах кликаем по подразделу “Надстройки”. Затем в правой части окна в самом низу, выбрав значение “Надстройки Excel” для параметра “Управление”, щелкаем по кнопке “Перейти”.
    4. В открывшемся окне ставим галочку напротив надстройки “Поиск решения” и жмем OK.
    5. В результате, если мы перейдем во вкладу “Данные”, то увидим здесь кнопку “Поиск решения” в группе инструментов “Анализ”.

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

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

    Алгоритм решения

    Итак, приступи к решению нашей задачи:

    1. Для начала строим таблицу, количество строк и столбцов в которой соответствует числу продавцов и покупателей, соответственно.
    2. Перейдя в любую свободную ячейку щелкаем по кнопке “Вставить функцию” (fx).
    3. В открывшемся окне выбираем категорию “Математические”, в списке операторов отмечаем “СУММПРОИЗВ”, после чего щелкаем OK.
    4. На экране отобразится окно, в котором нужно заполнить аргументы:
      • в поле для ввода значения напротив первого аргумента “Массив1” указываем координаты диапазона ячеек матрицы затрат (с желтым фоном). Сделать это можно, используя клавиши на клавиатуре, или просто выделив нужную область в самой таблице с помощью зажатой левой кнопки мыши.
      • в качестве значения второго аргумента “Массив2” указываем диапазон ячеек новой таблицы (либо вручную, либо выделив нужные элементы на листе).
      • по готовности жмем OK.
    5. Щелкаем по ячейке, расположенной слева от самого верхнего левого элемента новой таблицы, после чего снова жмем кнопку “Вставить функцию”.
    6. На этот раз нам нужна функция “СУММ”, которая также, находится в категории “Математические”.
    7. Теперь нужно заполнить аргументы. В качестве значения аргумента “Число1” указываем верхнюю строку созданной для расчетов таблицы (целиком) – вручную или методом выделения на листе. Жмем кнопку OK, когда все готово.
    8. В ячейке с функцией появится результат, равный нулю. Наводим указатель мыши на ее правый нижний угол, и когда появится Маркер заполнения в виде черного плюсика, зажав левую кнопку мыши тянем его до конца таблицы.
    9. Это позволит скопировать формулу и получить аналогичные результаты для остальных строк.
    10. Выбираем ячейку, которая находится сверху от самого верхнего левого элемента созданной таблицы. Аналогично описанным выше действиям вставляем в нее функцию “СУММ”.
    11. В значении аргумента “Число1” теперь указываем (вручную или с помощью выделения на листе) все ячейки первого столбца, после чего кликаем OK.
    12. С помощью Маркера заполнения выполняем копирование формулы на оставшиеся ячейки строки.
    13. Переключаемся во вкладку “Данные”, где жмем по кнопке функции “Поиск решения” (группа инструментов “Анализ”).
    14. Перед нами появится окно с параметрами функции:
      • в качестве значения параметра “Оптимизировать целевую функцию” указываем координаты ячейки, в которую ранее была вставлена функция “СУММПРОИЗВ”.
      • для параметра “До” выбираем вариант – “Минимум”.
      • в области для ввода значений напротив параметра “Изменяя ячейки переменных” указываем диапазон ячеек новой таблицы (без суммирующей строки и столбца).
      • нажимаем кнопку “Добавить” в блоке “В соответствии с ограничениями”.
    15. Откроется небольшое окошко, в котором мы можем добавить ограничение – сумма значений первых столбцов исходной и созданной таблицы должны быть равны.
      • становимся в поле “Ссылка на ячейки”, после чего указываем нужный диапазон данных в таблице для расчетов.
      • затем выбираем знак “равно”.
      • в качестве значения для параметра “Ограничение” указываем координаты  аналогичного столбца в исходной таблице.
      • щелкаем OK по готовности.
    16. Таким же способом добавляем условие по равенству сумм верхних строк таблиц.
    17. Также добавляем следующие условия касательно суммы ячеек в таблице для расчетов (диапазон совпадает с тем, который мы указали для параметра “Изменяя ячейки переменных”):
      • больше или равно нулю;
      • целое число.
    18. В итоге получаем следующий список условий в поле “В соответствии с ограничениями”. Проверяем, чтобы обязательно была поставлена галочка напротив опции “Сделать переменные без ограничений неотрицательными”, а также, чтобы в качестве метода решения стояло значение “Поиск решения нелинейных задач методов ОПГ”. Когда все готово, нажимаем “Найти решение”.
    19. В результате будет выполнен расчет и отобразится окно с результатами поиска решения. Оцениваем их, и в случае, когда они нас устраивают, нажимаем OK.
    20. Все готово, мы получили таблицу с заполненными данными и транспортную задачу можно считать успешно решенной.
    Понравилась статья? Поделиться с друзьями:
    Авто-мастер
    Как запрессовать сайлентблок в рычаг

    Закрыть