Транспортна задача в Excel. Намиране на най-добрия метод за транспортиране от продавача до купувача

Excel е много функционална програма. Може да се използва за решаване на огромен слой от проблеми, с които човек трябва да се сблъска в бизнеса. Един от най-разпространените е транспортът. Представете си, че трябва да разберем кой метод на транспортиране от производителя до крайния купувач е най-оптималният от гледна точка на време, пари и други ресурси. Този проблем е доста популярен, независимо в коя индустрия е бизнесът. Затова нека разгледаме по-отблизо как да го приложим с помощта на Excel.

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

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

1. Затворено. В този случай търсенето и предлагането са в баланс.
2. Отворете. Тук няма равенство между търсене и предлагане. За да получите решение на този проблем, първо трябва да го приведете към първия тип, изравнявайки търсенето и предлагането. За да направите това, трябва да въведете допълнителен индикатор - наличието на условен купувач или продавач. Освен това трябва да направите някои промени в таблицата с разходите.

Как да активирате функцията за намиране на решение в Excel

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

1. Отворете менюто "Файл", което се намира в горния ляв ъгъл на прозореца на програмата.
2. След това кликнете върху бутона с параметрите.
3. След това намираме подраздела „Настройки“ и отиваме в менюто за управление на добавките. Това са малки програми, които работят в средата на Microsoft Excel. Виждаме, че първо щракнахме върху менюто „Добавки“, а след това в долната дясна част зададохме елемента „Добавки на Excel“ и щракнахме върху бутона „Отиди“. Всички необходими действия са подчертани с червени правоъгълници и стрелки.
4. След това включете добавката „Търсене на решение“, след което потвърждаваме действията си, като натискаме бутона OK. Въз основа на описанието на настройката можем да видим, че тя е проектирана да анализира сложни данни, като научни и финансови.
5. След това отидете в раздела „Данни“, където виждаме нов бутон, който се нарича по същия начин като добавката. Може да се намери в групата инструменти за анализ.

Остава само да кликнете върху този бутон и пристъпваме към решаването на транспортния проблем. Но преди това трябва да поговорим малко повече за инструмента Solver в Excel. Това е специална добавка за Excel, която дава възможност да се намери най-бързото решение на проблем. Характерна особеност е отчитането на ограниченията, които потребителят задава на етапа на подготовка. С прости думи, това е подпрограма, която дава възможност да се определи най-добрият начин за постигане на определена задача. Такива задачи могат да включват следното:

1. Инвестиране, зареждане на склад или друга подобна дейност. Включително доставка на стоки.
2. По най-добрия начин. Това включва цели като постигане на максимална печалба при минимални разходи, как да се постигне най-доброто качество с наличните ресурси и т.н.

В допълнение към транспортните задачи, тази добавка се използва и за следните цели:

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

Както можете да видите, задачите са много различни. Универсалното правило за прилагане на тази добавка е, че преди решаването на проблема е необходимо да се създаде модел, който да съответства на ключовите характеристики на поставения проблем. Моделът е колекция от функции, които използват променливи като свои аргументи. Тоест стойности, които могат да се променят.

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

Добавката Solver изброява различните стойности на променливите, които се предават на целевата функция по такъв начин, че тя да е максималната, минималната или равна на определена стойност (това е точно ограничението). Има друга функция, която е донякъде подобна на принципа на работа и която често се бърка с „Търсене на решение“. Нарича се „Избор на опция“. Но ако копаете по-дълбоко, разликата между тях е огромна:

1. Функцията Goal Seek не работи с повече от една променлива.
2. Той не предоставя възможност за задаване на ограничения на променливите.
3. Той е в състояние да определи само равенството на целевата функция на определена стойност, но не дава възможност да се намери максимумът и минимумът. Следователно не е подходящ за нашата задача.
4. Може да изчислява ефективно само ако моделът е линеен тип. Ако моделът е нелинеен, тогава той намира стойността, която е най-близка до първоначалната стойност.

Транспортната задача е много по-сложна по своята структура, така че добавката „Избор на параметри“ не е достатъчна за това. Нека разгледаме по-подробно как да приложим на практика функцията „Търсене на решение“, използвайки примера на транспортен проблем.

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

За да покажем ясно как да решаваме транспортни проблеми на практика в Excel, нека дадем пример.

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

Да предположим, че имаме 6 продавачи и 7 купувачи. Търсенето и предлагането между тях се разпределят съответно по следния начин: 36, 51, 32, 44, 35 и 38 единици са продавачи, а 33, 48, 30, 36, 33, 24 и 32 единици са купувачи. Ако сумирате всички тези стойности, ще откриете, че търсенето и предлагането са в баланс. Следователно този проблем е от затворен тип, който се решава много просто.

Освен това имаме информация колко трябва да похарчите за транспорт от точка А до точка Б (в примера те са подчертани в жълти клетки).

Решение – алгоритъм стъпка по стъпка

Сега, след като се запознахме с таблиците с изходните данни, можем да използваме следния алгоритъм за решаване на този проблем:

1. Първо правим таблица, състояща се от 6 реда и 7 колони.
2. След това отиваме до всяка клетка, която не съдържа никакви стойности и в същото време се намира извън новосъздадената таблица и вмъкваме функцията. За да направите това, щракнете върху бутона fx, който се намира вляво от реда за въвеждане на функция.
3. Имаме прозорец, в който трябва да изберем категорията „Математика“. Каква функция ни интересува? Този, подчертан на тази екранна снимка. функция SUMPRODUCT умножава диапазони или масиви помежду си и ги сумира. Точно това, от което се нуждаем. След това натиснете клавиша OK.
4. След това на екрана ще се появи прозорец, в който трябва да посочите параметрите на функцията. Те са следните:
   1. Масив 1. Това е първият аргумент, в който записваме диапазона, който е маркиран в жълто. Можете да зададете параметрите на функцията или с помощта на клавиатурата, или като изберете подходящата област с левия бутон на мишката.
   2. Масив 2. Това е вторият аргумент, който е новосъздадената таблица. Действията се извършват по същия начин.

Потвърдете действието си, като натиснете бутона OK.

1. След това щракваме с левия бутон на мишката върху клетката, която служи като горна лява в новосъздадената таблица. Сега щракнете отново върху бутона за вмъкване на функция.
2. Избираме същата категория като в предишния случай. Но този път ни интересува функцията SUM.
3. Сега идва етапът на попълване на аргументите. Като първи аргумент записваме горния ред на таблицата, която създадохме в началото. По същия начин, както преди, това може да стане чрез избиране на тези клетки на листа или ръчно. Потвърждаваме действията си с натискане на бутона OK.
4. Ще видим резултатите в клетката с функцията. В този случай е нула. След това преместете курсора в долния десен ъгъл, след което ще се появи маркер за автоматично попълване. Прилича на малък черен плюш. Ако се появи, задръжте левия бутон на мишката и преместете курсора до последната клетка в нашата таблица.
5. Това ни дава възможност да прехвърлим формулата във всички останали клетки и да получим правилните резултати, без да се налага да извършваме допълнителни изчисления.
6. Следващата стъпка е да изберете горната лява клетка и да поставите функцията SUM в нея. След това въвеждаме аргументите и използваме маркера за автоматично попълване, за да попълним всички останали клетки.
7. След това пристъпваме директно към решаването на проблема. За да направим това, ще използваме добавката, която включихме по-рано. Отидете в раздела „Данни“ и там намираме инструмента „Търсене на решение“. Щракваме върху този бутон.
8. Сега пред очите ни се появи прозорец, чрез който можете да конфигурирате параметрите на нашата добавка. Нека да разгледаме всяка от тези опции:
   1. Оптимизирайте целевата функция. Тук трябва да изберем клетката, съдържаща функцията SUMPRODUCT. Виждаме, че тази опция дава възможност да се избере функция, за която ще се търси решение.
   2. Преди. Тук задаваме опцията „Минимум“.
   3. Чрез промяна на клетките на променливите. Тук посочваме диапазона, съответстващ на таблицата, която създадохме в самото начало (с изключение на обобщаващия ред и колона).
   4. Предмет на ограничения. Тук трябва да добавим ограничения, като щракнете върху бутона Добавяне.
   5. Спомняме си какъв вид ограничение трябва да създадем - сборът от стойностите на исканията на купувачите и офертите на продавачите трябва да е еднакъв.
9. Задачата на ограниченията се изпълнява, както следва:
   1. Връзка към клетки. Тук въвеждаме диапазона на таблицата за изчисления.
   2. Условия. Това е математическа операция, спрямо която се проверява диапазонът, посочен в първото поле за въвеждане.
   3. Стойността на условието или ограничението. Тук въвеждаме съответната колона в изходната таблица.
   4. След като всички стъпки са изпълнени, щракнете върху бутона OK, като по този начин потвърдите нашите действия.

Извършваме абсолютно същите операции за горните редове, като задаваме следното условие: те трябва да са равни.

Следващата стъпка е определяне на условията. Трябва да зададем следните критерии за сумата на клетките в таблицата – по-голямо или равно на нула, цяло число. В резултат на това имаме такъв списък от условия, при които проблемът се решава. Тук трябва да се уверите, че квадратчето до опцията „Направете променливи без ограничения неотрицателни“ е отметнато. Също така в нашата ситуация е необходимо да се избере методът за решаване на проблема – „Търсене на решение на нелинейни проблеми на OPG методи“. Сега можем спокойно да кажем, че настройката е готова. Следователно остава само да се извършат изчисленията. За да направите това, щракнете върху бутона „Намерете решение“.

След това всички данни ще бъдат изчислени автоматично и тогава Excel ще покаже прозорец с резултатите. Необходимо е, за да проверите отново работата на компютъра, тъй като са възможни грешки, ако условията са били предварително зададени неправилно. Ако всичко е правилно, щракнете върху бутона „OK“ и вижте готовата таблица.

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

Заключение

Както можете да видите, Excel може да се използва и за много сложни изчисления, които на пръв поглед не са достъпни за проста компютърна програма, която е инсталирана в почти всички. Въпреки това е така. Днес вече разгледахме напредналото ниво на използване. Тази тема не е толкова проста, но както се казва, пътят ще бъде овладян от ходещия. Основното нещо е да следвате плана за действие и да изпълнявате точно всички действия, посочени по-горе. Тогава няма да има грешки и програмата самостоятелно ще извърши всички необходими изчисления. Няма да има нужда да мислите коя функция да използвате и т.н.