Транспортна задача во Excel. Наоѓање на најдобриот начин на транспорт од продавачот до купувачот

Excel е многу функционална програма. Може да се користи за решавање на огромен слој проблеми со кои треба да се соочи во бизнисот. Еден од најчестите е транспортот. Замислете дека треба да разбереме кој начин на транспорт од производителот до крајниот купувач е најоптимален во однос на време, пари и други ресурси. Овој проблем е доста популарен, без разлика во која индустрија е бизнисот. Затоа, ајде внимателно да погледнеме како да го имплементираме со помош на Excel.

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

Значи, имаме две договорни страни кои постојано комуницираат едни со други. Во нашиот случај, ова е купувач и продавач. Треба да сфатиме како да транспортираме стока на таков начин што трошоците ќе бидат минимални. За да го направите ова, треба да ги прикажете сите податоци во шематски или матричен облик. Во Excel, ја користиме последната опција. Општо земено, постојат два вида транспортни задачи:

1. Затворено. Во овој случај, понудата и побарувачката се во рамнотежа.
2. Отвори. Тука нема еднаквост меѓу понудата и побарувачката. За да добиете решение за овој проблем, прво мора да го доведете до првиот тип, изедначувајќи ја понудата и побарувачката. За да го направите ова, треба да воведете дополнителен индикатор - присуство на условен купувач или продавач. Покрај тоа, треба да направите одредени промени во табелата за трошоци.

Како да ја овозможите функцијата Најдете решение во Excel

За решавање на транспортните проблеми во Excel, постои специјална функција наречена „Барај решение“. Стандардно не е овозможено, па затоа треба да ги направите следните чекори:

1. Отворете го менито „Датотека“, кое се наоѓа во горниот лев агол на прозорецот на програмата.
2. После тоа, кликнете на копчето со параметрите.
3. Следно, ја наоѓаме подсекцијата „Поставки“ и одиме во менито за управување со додатоци. Ова се мали програми кои работат во околината на Microsoft Excel. Гледаме дека прво кликнавме на менито „Додатоци“, а потоа во долниот десен дел ја поставивме ставката „Додатоци на Excel“ и кликнавме на копчето „Оди“. Сите потребни дејства се означени со црвени правоаголници и стрелки.
4. Следно, вклучете го додатокот „Барај решение“, по што ги потврдуваме нашите постапки со притискање на копчето ОК. Врз основа на описот на поставката, можеме да видиме дека е дизајнирана да анализира сложени податоци, како што се научни и финансиски.
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. Имаме прозорец во кој треба да ја избереме категоријата „Математика“. За која функција сме заинтересирани? Она што е означено во оваа слика од екранот. Функција СУМПРОДУКТ множи опсези или низи меѓу себе и ги сумира. Само она што ни треба. После тоа, притиснете го копчето ОК.
4. Следно, на екранот ќе се појави прозорец во кој треба да ги наведете параметрите на функцијата. Тие се следните:
   1. Низа 1. Ова е првиот аргумент во кој го пишуваме опсегот што е означен со жолто. Можете да ги поставите параметрите на функцијата или со помош на тастатурата или со избирање на соодветната област со левото копче на глувчето.
   2. Низа 2. Ова е вториот аргумент, што е новосоздадената табела. Дејствата се вршат на ист начин.

Потврдете ја вашата акција со притискање на копчето ОК.

1. После тоа, правиме лев клик на глувчето на ќелијата што служи како горе лево во новосоздадената табела. Сега повторно кликнете на копчето за вметнување функција.
2. Ја избираме истата категорија како и во претходниот случај. Но, овој пат не интересира функцијата SUM.
3. Сега доаѓа фазата на пополнување на аргументите. Како прв аргумент, го пишуваме горниот ред од табелата што го создадовме на почетокот. На ист начин како и претходно, ова може да се направи со избирање на овие ќелии на листот или рачно. Ги потврдуваме нашите постапки со притискање на копчето ОК.
4. Резултатите ќе ги видиме во ќелијата со функцијата. Во овој случај, тоа е нула. Следно, поместете го курсорот во долниот десен агол, по што ќе се појави маркер за автоматско комплетирање. Изгледа како мал црн кадифен. Ако се појави, држете го левото копче на глувчето и поместете го курсорот до последната ќелија во нашата табела.
5. Ова ни дава можност да ја пренесеме формулата на сите други ќелии и да ги добиеме точните резултати без да мора да вршиме дополнителни пресметки.
6. Следниот чекор е да ја изберете горната лева ќелија и да ја залепите функцијата SUM во неа. После тоа, ги внесуваме аргументите и го користиме маркерот за автоматско комплетирање за да ги пополниме сите преостанати ќелии.
7. После тоа, продолжуваме директно на решавање на проблемот. За да го направите ова, ќе го користиме додатокот што го вклучивме претходно. Одете во табулаторот „Податоци“ и таму ја наоѓаме алатката „Барај решение“. Ние кликнуваме на ова копче.
8. Сега пред нашите очи се појави прозорец преку кој можете да ги конфигурирате параметрите на нашиот додаток. Ајде да погледнеме во секоја од овие опции:
   1. Оптимизирајте ја целната функција. Тука треба да ја избереме ќелијата што ја содржи функцијата СУМПРОДУКТ. Гледаме дека оваа опција овозможува да се избере функција за која ќе се бара решение.
   2. Пред. Овде ја поставивме опцијата „Минимум“.
   3. Со менување на ќелиите на променливите. Овде го означуваме опсегот што одговара на табелата што ја создадовме на самиот почеток (со исклучок на сумирачкиот ред и колона).
   4. Предмет на ограничувања. Тука треба да додадеме ограничувања со кликнување на копчето Додај.
   5. Се сеќаваме каков вид на ограничување треба да создадеме - збирот на вредностите на барањата на купувачите и понудите на продавачите мора да биде ист.
9. Задачата за ограничувања се врши на следниов начин:
   1. Врска до ќелии. Овде го внесуваме опсегот на табелата за пресметки.
   2. Услови. Ова е математичка операција со која се проверува опсегот наведен во првото поле за внесување.
   3. Вредноста на условот или ограничувањето. Овде ја внесуваме соодветната колона во изворната табела.
   4. Откако ќе се завршат сите чекори, кликнете на копчето ОК, со што ќе ги потврдите нашите постапки.

Ние ги извршуваме токму истите операции за горните редови, поставувајќи ја следнава состојба: тие мора да бидат еднакви.

Следниот чекор е поставување на условите. Треба да ги поставиме следните критериуми за збирот на ќелиите во табелата - поголем или еднаков на нула, цел број. Како резултат на тоа, имаме таков список на услови под кои се решава проблемот. Овде треба да бидете сигурни дека полето за избор до опцијата „Направи променливи без ограничувања не-негативни“ е означено. Исто така, во нашата ситуација се бара да се избере методот за решавање на проблемот – „Барање решение за нелинеарни проблеми на методите на ОПГ“. Сега можеме безбедно да кажеме дека поставувањето е завршено. Затоа, останува само да се извршат пресметките. За да го направите ова, кликнете на копчето „Најди решение“.

После тоа, сите податоци ќе се пресметуваат автоматски, а потоа Excel ќе прикаже прозорец со резултатите. Неопходно е за двојно да се провери работата на компјутерот, бидејќи можни се грешки ако условите претходно биле поставени погрешно. Ако сè е точно, кликнете на копчето „OK“ и видете ја завршената табела.

Ако се испостави дека нашата задача стана отворен тип, тогаш ова е лошо, затоа што треба да ја уредите изворната табела така што задачата да се претвори во затворена. Меѓутоа, кога ова ќе се направи, преостанатиот алгоритам ќе биде ист.

Заклучок

Како што можете да видите, Excel може да се користи и за многу сложени пресметки, кои на прв поглед не се достапни за едноставна компјутерска програма што е инсталирана скоро кај сите. Сепак, тоа е. Денес веќе го опфативме напредното ниво на употреба. Темава не е толку едноставна, но како што велат, патот ќе го совлада пешачкиот. Главната работа е да го следите акциониот план и точно да ги извршите сите дејства наведени погоре. Тогаш нема да има грешки, а програмата самостојно ќе ги изврши сите потребни пресметки. Нема да има потреба да се размислува за тоа која функција да се користи и така натаму.