содржина
Формулирање на проблемот
Да претпоставиме дека компанијата во која работите има три магацини, од каде што стоката оди во пет ваши продавници расфрлани низ Москва.
Секоја продавница е во состојба да продаде одредена количина на стоки кои ни се познати. Секој од магацините има ограничен капацитет. Задачата е рационално да се избере од кој магацин до кои продавници ќе се достави стоката за да се минимизираат вкупните транспортни трошоци.
Пред да започнете со оптимизација, ќе биде неопходно да се состави едноставна табела на лист Excel - нашиот математички модел кој ја опишува ситуацијата:
Се подразбира дека:
- Светло жолтата табела (C4:G6) ги опишува трошоците за испорака на еден предмет од секој магацин до секоја продавница.
- Виолетовите ќелии (C15:G14) ја опишуваат количината на стоки што се потребни за секоја продавница да се продаде.
- Црвените клетки (J10:J13) го прикажуваат капацитетот на секој магацин - максималната количина на стоки што може да ги собере складиштето.
- Жолтите (C13:G13) и сините (H10:H13) ќелии се збир на редови и колони за зелените ќелии, соодветно.
- Вкупниот трошок за испорака (J18) се пресметува како збир на производите од бројот на стоки и нивните соодветни трошоци за испорака - за пресметка, функцијата се користи овде СУМПРОДУКТ (SumProduct).
Така, нашата задача е сведена на избор на оптимални вредности на зелените ќелии. И така што вкупниот износ за линијата (сини ќелии) не го надминува капацитетот на магацинот (црвените ќелии), а во исто време секоја продавница ја добива количината на стока што треба да ја продаде (износот за секоја продавница во жолтите ќелии треба да бидат што поблиску до барањата – виолетови ќелии).
Решение
Во математиката, ваквите проблеми за избор на оптимална распределба на ресурсите се формулирани и опишани долго време. И, се разбира, начини за нивно решавање одамна се развиени не со тапа набројување (што е многу долго), туку во многу мал број повторувања. Excel му овозможува на корисникот таква функционалност користејќи додаток. Решенија за пребарување (Решавач) од јазичето податоци (Датум):
Ако на јазичето податоци вашиот Excel нема таква команда - во ред е - тоа значи дека додатокот едноставно сè уште не е поврзан. За да го активирате, отворете датотека, Потоа одберете параметри - Додатоци - За нас (Опции - Додатоци - Оди на). Во прозорецот што се отвора, проверете го полето до линијата што ни треба Решенија за пребарување (Решавач).
Ајде да го извршиме додатокот:
Во овој прозорец, треба да ги поставите следниве параметри:
- Оптимизирајте ја целната функција (Поставете тпари ќелија) – тука е неопходно да се наведе конечната главна цел на нашата оптимизација, односно розовата кутија со вкупните трошоци за испорака (J18). Целната ќелија може да се минимизира (ако се работи за трошоци, како во нашиот случај), да се максимизира (ако е, на пример, профит) или да се обиде да ја доведе до дадена вредност (на пример, точно да се вклопи во доделениот буџет).
- Менување на променливи ќелии (By менување клетки) - тука ги означуваме зелените ќелии (C10: G12), со менување на вредностите на кои сакаме да го постигнеме нашиот резултат - минималната цена за испорака.
- Во согласност со ограничувањата (предмет до на Ограничувања) – список на ограничувања што мора да се земат предвид при оптимизирање. За да додадете ограничувања на списокот, кликнете на копчето Додај (Додај) и внесете ја состојбата во прозорецот што се појавува. Во нашиот случај, ова ќе биде ограничувањето на побарувачката:
и ограничување на максималниот обем на магацини:
Покрај очигледните ограничувања поврзани со физичките фактори (капацитет на магацини и транспортни средства, буџетски и временски ограничувања итн.), понекогаш е неопходно да се додадат ограничувања „специјални за Excel“. Така, на пример, Excel може лесно да организира да ги „оптимизирате“ трошоците за испорака со понуда за транспорт на стоки од продавниците назад до магацинот - трошоците ќе станат негативни, односно ќе оствариме профит! 🙂
За да спречите тоа да се случи, најдобро е да го оставите овозможено полето за избор. Направете неограничени променливи не-негативни или дури понекогаш експлицитно да ги регистрирате таквите моменти во листата на ограничувања.
По поставувањето на сите потребни параметри, прозорецот треба да изгледа вака:
Во паѓачката листа Изберете метод за решавање, дополнително треба да го изберете соодветниот математички метод за решавање на избор од три опции:
- Симплекс метод е едноставен и брз метод за решавање на линеарни проблеми, односно проблеми каде што излезот е линеарно зависен од влезот.
- Општ метод на намален градиент (OGG) – за нелинеарни проблеми, каде што има сложени нелинеарни зависности помеѓу влезните и излезните податоци (на пример, зависноста на продажбата од трошоците за рекламирање).
- Еволутивна потрага по решение – релативно нов метод за оптимизација заснован на принципите на биолошката еволуција (здраво Дарвин). Овој метод работи многу пати подолго од првите два, но може да го реши речиси секој проблем (нелинеарен, дискретен).
Нашата задача е јасно линеарна: испорачано 1 парче - потрошени 40 рубли, испорачани 2 парчиња - потрошени 80 рубли. итн., така што методот симплекс е најдобриот избор.
Сега кога се внесени податоците за пресметката, притиснете го копчето Пронајдете решение (Реши)за да започнете со оптимизација. Во тешки случаи со многу променливи ќелии и ограничувања, наоѓањето решение може да потрае долго време (особено со еволутивниот метод), но нашата задача за Excel нема да биде проблем - за неколку моменти ќе ги добиеме следните резултати :
Обрнете внимание на тоа колку интересно беа распределени количините на набавки меѓу продавниците, притоа не надминувајќи го капацитетот на нашите магацини и задоволувајќи ги сите барања за потребниот број на стоки за секоја продавница.
Ако најденото решение ни одговара, тогаш можеме да го зачуваме или да се вратиме на оригиналните вредности и да се обидеме повторно со други параметри. Можете исто така да ја зачувате избраната комбинација на параметри како Сценарио. На барање на корисникот, Excel може да изгради три типа Извештаи за проблемот што се решава на посебни листови: извештај за резултатите, извештај за математичката стабилност на решението и извештај за границите (ограничувањата) на решението, меѓутоа, во повеќето случаи, тие се од интерес само за специјалисти .
Меѓутоа, постојат ситуации кога Excel не може да најде соодветно решение. Можно е да се симулира таков случај ако во нашиот пример ги посочиме барањата на продавниците во износ поголем од вкупниот капацитет на магацините. Потоа, при извршување на оптимизација, Excel ќе се обиде да се доближи што е можно поблиску до решението, а потоа ќе прикаже порака дека решението не може да се најде. Сепак, дури и во овој случај, имаме многу корисни информации - особено, можеме да ги видиме „слабите алки“ на нашите деловни процеси и да ги разбереме областите за подобрување.
Разгледаниот пример, се разбира, е релативно едноставен, но лесно се размерува за решавање на многу посложени проблеми. На пример:
- Оптимизација на распределбата на финансиските ресурси по ставка на расходи во деловниот план или буџет на проектот. Ограничувањата, во овој случај, ќе бидат износот на финансирање и времето на проектот, а целта на оптимизацијата е да се максимизира профитот и да се минимизираат трошоците на проектот.
- Оптимизација на распоредот на вработените со цел да се минимизира фондот за плати на претпријатието. Ограничувања, во овој случај, ќе бидат желбите на секој вработен според распоредот за вработување и барањата на табелата за персонал.
- Оптимизација на инвестициските инвестиции – потребата за правилна распределба на средствата помеѓу неколку банки, хартии од вредност или акции на претпријатија со цел, повторно, да се максимизира профитот или (ако е поважно) да се минимизираат ризиците.
Во секој случај, додаток Решенија за пребарување (Решавач) е многу моќна и убава алатка на Excel и достојна за вашето внимание, бидејќи може да помогне во многу тешки ситуации со кои треба да се соочите во современиот бизнис.