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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. За да решим проблема, изграждаме таблица с точно същия брой клетки, както описаната по-горе матрица на разходите.
2. Изберете всяка празна клетка в листа. Кликнете върху иконата "Вмъкване на функция" , разположена отляво на формулата.
3. Ще се отвори "Помощ за функциите". В списъка, който той предлага, трябва да намерим функцията SUMPRODUCT . Изберете го и кликнете върху бутона "OK" .
4. Отваря входния прозорец за функцията SUMPROSE . Като първи аргумент въвеждаме диапазона от клетки в матрицата на разходите. За да направите това, достатъчно е да маркирате данните за клетката с курсора. Вторият аргумент е диапазона от клетки в таблицата, подготвена за изчисления. След това кликнете върху бутона "OK" .
5. Кликнете върху клетката, разположена вляво от горната лява клетка на таблицата за изчисления. Както и последния път, когато се обадим на функцията Wizard, ще отворим аргументите на функцията SUM в нея. Като кликнете върху полето на първия аргумент, изберете целия горния ред на табличните клетки за изчисления. След като техните координати бъдат въведени в съответното поле, кликнете върху бутона "OK" .
6. Ставаме долния десен ъгъл на клетката с функцията SUM . Появява се дръжката за пълнене. Кликнете върху левия бутон на мишката и плъзнете дръжката за пълнене надолу до края на таблицата, за да я изчислите. Така че ние копирахме формулата.
7. Кликваме върху клетката, поставена над горната лява клетка на таблицата за изчисления. Както и в предходното време, наричаме функцията SUM , но този път използваме първата колона на таблицата за изчисления като аргумент. Кликнете върху бутона "OK" .
8. Копирайте точката за попълване на формулата за целия ред.
9. Отворете раздела "Данни" . В инструментариума "Анализ" кликнете върху бутона "Намиране на решение" .
10. Извеждат се опциите за търсене на решението. В полето "Оптимизиране на целевата функция" посочете клетката, съдържаща функцията SUMPRODUCT . В блок "За" задайте стойността на "Минимална" . В полето "Промяна на клетките на променливите" определяме целия обхват на таблицата за изчисление. В блок с настройки "В съответствие с ограниченията" кликнете върху бутона "Добавяне", за да добавите няколко важни ограничения.
11. Прозорецът за добавяне на ограничение започва. На първо място, трябва да добавим условието сумата от данните в редовете на таблицата за изчисления да е равна на сумата от данните в редовете на таблицата с условието. В полето "Клетъчна справка" посочете обхвата на сумата в редовете на изчислителната таблица. След това задайте знака за равенство (=). В полето "Лимит" задайте диапазона от суми в редовете на таблицата със съответното условие. След това кликнете върху бутона "OK" .
12. По подобен начин добавяме условието, че колоните от две таблици трябва да бъдат еднакви една с друга. Добавяме ограничението, че сумата от диапазона на всички клетки в таблицата за изчисление трябва да бъде по-голяма или равна на 0, както и условието, че трябва да е цяло число. Общият изглед на ограниченията трябва да бъде както е показано на изображението по-долу. Не забравяйте да проверите дали близо до елемента "Направете променливи без ограничения, които не са отрицателни", е имало отметка и методът на решението е "Търсене на решение на нелинейни проблеми чрез метода OPG" . След като зададете всички настройки, кликнете върху бутона "Намиране на решение" .
13. След това се извършва изчислението. Данните се извеждат към клетките на таблицата за изчисление. Появява се прозорецът с резултатите от търсенето на решение. Ако резултатите са задоволителни, кликнете върху бутона "OK" .

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

• ← Сума в думи в Microsoft Excel
• Логически функции в Microsoft Excel →