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