Транспортната задача е задачата да се търси най-оптималният вариант на превоза на същия вид стоки от доставчика до потребителя. Нейната основа е модел, широко използван в различни области на математиката и икономиката. В Microsoft Excel съществуват инструменти, които значително улесняват решаването на транспортната задача. Ще разберем как да ги използваме на практика.

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

Основната цел на транспортната задача е да се намери оптимален план за транспортиране от доставчика до потребителя при минимални разходи. Условията на такъв проблем са написани под формата на схема или матрица. За Excel се използва матрицата.

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

Инструменти за решаване на транспортния проблем в Excel

За да се реши проблемът с транспорта в Excel, се използва функцията "Намиране на решения" . Проблемът е, че по подразбиране е деактивиран. За да активирате този инструмент, трябва да извършите определени действия.

  1. Преминаваме към раздела "Файл" .
  2. Отворете раздела Файл в Microsoft Excel

  3. Кликваме върху подраздел "Настройки" .
  4. Отидете на опциите в Microsoft Excel

  5. В нов прозорец отидете на надписа "Добавки".
  6. Отидете в добавките в Microsoft Excel

  7. В блока "Управление" , който се намира в долната част на отворения прозорец, в падащия списък спираме избора на елемента "Добавки в Excel" . Кликнете върху бутона "Отиди ..." .
  8. Преминаване към добавки в Excel в Microsoft Excel

  9. Прозорецът за активиране на добавките започва. Поставете отметка в квадратчето до "Намиране на решение" . Кликнете върху бутона "OK" .
  10. Активиране на инструмента за търсене на решения в Microsoft Excel

  11. Поради тези действия в раздела "Данни" в блока за настройки "Анализ" на лентата се появява бутон "Намиране на решения" . Ще се нуждаем от това в търсене на решение на проблема с транспорта.

Търсене в Microsoft Excel

Урокът: Намерете решение в Excel

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

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

Условия на задачите

Разполагаме с 5 доставчици и 6 купувачи. Обемът на производство на тези доставчици е 48, 65, 51, 61, 53 единици. Необходимостта от купувачи: 43, 47, 42, 46, 41, 59 единици. Така общият обем на предлагането е равен на търсената стойност, т.е. ние имаме работа със затворена транспортна задача.

Таблица на обемите на търсене и предлагане в Microsoft Excel

Освен това, при условие, се дава матрицата на разходите за транспортиране от една точка до друга, което е показано на илюстрацията по-долу в зелено.

Матрица на разходите в Microsoft Excel

Решаване на проблема

Преди нас е задачата при условията, споменати по-горе, да се намалят транспортните разходи до минимум.

  1. За да решим проблема, изграждаме таблица с точно същия брой клетки, както описаната по-горе матрица на разходите.
  2. Разпределение на таблицата за решаване на проблем в Microsoft Excel

  3. Изберете всяка празна клетка в листа. Кликнете върху иконата "Вмъкване на функция" , разположена отляво на формулата.
  4. Отидете в съветника за функции в Microsoft Excel

  5. Ще се отвори "Помощ за функциите". В списъка, който той предлага, трябва да намерим функцията SUMPRODUCT . Изберете го и кликнете върху бутона "OK" .
  6. Съветникът за функции на Microsoft Excel

  7. Отваря входния прозорец за функцията SUMPROSE . Като първи аргумент въвеждаме диапазона от клетки в матрицата на разходите. За да направите това, достатъчно е да маркирате данните за клетката с курсора. Вторият аргумент е диапазона от клетки в таблицата, подготвена за изчисления. След това кликнете върху бутона "OK" .
  8. Аргументи на функцията SUMPROSE в Microsoft Excel

  9. Кликнете върху клетката, разположена вляво от горната лява клетка на таблицата за изчисления. Както и последния път, когато се обадим на функцията Wizard, ще отворим аргументите на функцията SUM в нея. Като кликнете върху полето на първия аргумент, изберете целия горния ред на табличните клетки за изчисления. След като техните координати бъдат въведени в съответното поле, кликнете върху бутона "OK" .
  10. Аргументи на функцията SUM в Microsoft Excel

  11. Ставаме долния десен ъгъл на клетката с функцията SUM . Появява се дръжката за пълнене. Кликнете върху левия бутон на мишката и плъзнете дръжката за пълнене надолу до края на таблицата, за да я изчислите. Така че ние копирахме формулата.
  12. Копиране на формулата с маркера за попълване в Microsoft Excel

  13. Кликваме върху клетката, поставена над горната лява клетка на таблицата за изчисления. Както и в предходното време, наричаме функцията SUM , но този път използваме първата колона на таблицата за изчисления като аргумент. Кликнете върху бутона "OK" .
  14. Аргументи на функцията SUM в Microsoft Excel

  15. Копирайте точката за попълване на формулата за целия ред.
  16. Копиране на формулата с маркери за попълване на низ в Microsoft Excel

  17. Отворете раздела "Данни" . В инструментариума "Анализ" кликнете върху бутона "Намиране на решение" .
  18. Отидете в решението за търсене в Microsoft Excel

  19. Извеждат се опциите за търсене на решението. В полето "Оптимизиране на целевата функция" посочете клетката, съдържаща функцията SUMPRODUCT . В блок "За" задайте стойността на "Минимална" . В полето "Промяна на клетките на променливите" определяме целия обхват на таблицата за изчисление. В блок с настройки "В съответствие с ограниченията" кликнете върху бутона "Добавяне", за да добавите няколко важни ограничения.
  20. Опции за търсене в Microsoft Excel

  21. Прозорецът за добавяне на ограничение започва. На първо място, трябва да добавим условието сумата от данните в редовете на таблицата за изчисления да е равна на сумата от данните в редовете на таблицата с условието. В полето "Клетъчна справка" посочете обхвата на сумата в редовете на изчислителната таблица. След това задайте знака за равенство (=). В полето "Лимит" задайте диапазона от суми в редовете на таблицата със съответното условие. След това кликнете върху бутона "OK" .
  22. Добавяне на ограничения към Microsoft Excel

  23. По подобен начин добавяме условието, че колоните от две таблици трябва да бъдат еднакви една с друга. Добавяме ограничението, че сумата от диапазона на всички клетки в таблицата за изчисление трябва да бъде по-голяма или равна на 0, както и условието, че трябва да е цяло число. Общият изглед на ограниченията трябва да бъде както е показано на изображението по-долу. Не забравяйте да проверите дали близо до елемента "Направете променливи без ограничения, които не са отрицателни", е имало отметка и методът на решението е "Търсене на решение на нелинейни проблеми чрез метода OPG" . След като зададете всички настройки, кликнете върху бутона "Намиране на решение" .
  24. Опции за търсене в Microsoft Excel

  25. След това се извършва изчислението. Данните се извеждат към клетките на таблицата за изчисление. Появява се прозорецът с резултатите от търсенето на решение. Ако резултатите са задоволителни, кликнете върху бутона "OK" .

Резултатите от търсенето на транспортни задачи в Microsoft Excel

Както можете да видите, решаването на задачата за транспортиране в Excel се свежда до правилното формиране на входни данни. Изчисленията се извършват от програмата вместо от потребителя.