Innehåll
Formulering av problemet
Anta att företaget där du arbetar har tre lager, varifrån varorna går till fem av dina butiker utspridda över hela Moskva.
Varje butik kan sälja en viss mängd varor som vi känner till. Vart och ett av lagren har en begränsad kapacitet. Uppgiften är att rationellt välja från vilket lager till vilka butiker varorna ska levereras för att minimera de totala transportkostnaderna.
Innan du startar optimeringen kommer det att vara nödvändigt att sammanställa en enkel tabell på ett Excel-ark – vår matematiska modell som beskriver situationen:
Det är underförstått att:
- Den ljusgula tabellen (C4:G6) beskriver kostnaden för att frakta en vara från varje lager till varje butik.
- Lila celler (C15:G14) beskriver mängden varor som krävs för att varje butik ska sälja.
- Röda blodkroppar (J10:J13) visar kapaciteten för varje lager – den maximala mängd varor som lagret kan hålla.
- Gula (C13:G13) och blå (H10:H13) celler är rad- och kolumnsummorna för gröna celler.
- Den totala fraktkostnaden (J18) beräknas som summan av produkterna av antalet varor och deras motsvarande fraktkostnader – för beräkning används funktionen här SUMPRODUCT (SUMMAPRODUKT).
Således reduceras vår uppgift till valet av optimala värden för gröna celler. Och så att det totala beloppet för raden (blå celler) inte överstiger lagrets kapacitet (röda celler), och samtidigt får varje butik den mängd varor den behöver sälja (mängden för varje butik i gula celler ska vara så nära kraven som möjligt – lila celler).
Lösning
Inom matematiken har sådana problem med att välja optimal resursfördelning formulerats och beskrivits under lång tid. Och naturligtvis har sätt att lösa dem länge utvecklats inte genom trubbig uppräkning (vilket är väldigt lång), utan i ett mycket litet antal iterationer. Excel ger användaren sådan funktionalitet med hjälp av ett tillägg. Sök lösningar (Lösare) från fliken Data (Datum):
Om på fliken Data din Excel har inte ett sådant kommando – det är okej – det betyder att tillägget helt enkelt inte är anslutet ännu. Öppna den för att aktivera den Fil, Välj sedan parametrar - Add-ons - Om oss (Alternativ — Tillägg — Gå till). I fönstret som öppnas, markera rutan bredvid raden vi behöver Sök lösningar (Lösare).
Låt oss köra tillägget:
I det här fönstret måste du ställa in följande parametrar:
- Optimera målfunktionen (Sätt tpengar cell) – här är det nödvändigt att ange det slutliga huvudmålet för vår optimering, dvs rosa låda med den totala fraktkostnaden (J18). Målcellen kan minimeras (om det är utgifter, som i vårt fall), maximeras (om det till exempel är vinst) eller försöka få den till ett givet värde (till exempel passa exakt in i den tilldelade budgeten).
- Ändra variabla celler (By byte celler) – här indikerar vi de gröna cellerna (C10: G12), genom att variera de värden som vi vill uppnå vårt resultat – minimikostnaden för leverans.
- Överensstämmer med restriktioner (Ämne till d Begränsningar) – en lista över restriktioner som måste beaktas vid optimering. För att lägga till begränsningar i listan, klicka på knappen Lägg till (Lägg till) och ange villkoret i fönstret som visas. I vårt fall kommer detta att vara efterfrågebegränsningen:
och gräns för den maximala volymen av lager:
Förutom de uppenbara begränsningarna förknippade med fysiska faktorer (kapacitet hos lager och transportmedel, budget- och tidsbegränsningar, etc.), är det ibland nödvändigt att lägga till begränsningar "speciella för Excel". Så till exempel kan Excel enkelt ordna så att du "optimerar" leveranskostnaden genom att erbjuda dig att transportera varor från butiker tillbaka till lagret – kostnaderna blir negativa, dvs vi går med vinst! 🙂
För att förhindra att detta händer är det bäst att låta kryssrutan vara aktiverad. Gör obegränsade variabler till icke-negativa eller till och med ibland uttryckligen registrera sådana ögonblick i listan över begränsningar.
Efter att ha ställt in alla nödvändiga parametrar bör fönstret se ut så här:
I rullgardinsmenyn Välj en lösningsmetod måste du dessutom välja lämplig matematisk metod för att lösa ett urval av tre alternativ:
- Enkel metod är en enkel och snabb metod för att lösa linjära problem, dvs problem där utsignalen är linjärt beroende av ingången.
- Allmän nedgraderad gradientmetod (OGG) – För icke-linjära problem, där det finns komplexa icke-linjära beroenden mellan in- och utdata (till exempel försäljningens beroende av reklamkostnader).
- Evolutionärt sökande efter en lösning – en relativt ny optimeringsmetod baserad på principerna för biologisk evolution (hej Darwin). Denna metod fungerar många gånger längre än de två första, men kan lösa nästan alla problem (ickelinjär, diskret).
Vår uppgift är tydligt linjär: levererade 1 stycke - spenderade 40 rubel, levererade 2 stycken - spenderade 80 rubel. etc., så simplexmetoden är det bästa valet.
Nu när data för beräkningen är inmatade, tryck på knappen Hitta en lösning (Lösa)för att starta optimering. I svåra fall med många föränderliga celler och begränsningar kan det ta lång tid att hitta en lösning (särskilt med den evolutionära metoden), men vår uppgift för Excel kommer inte att vara ett problem - om ett par ögonblick kommer vi att få följande resultat :
Var uppmärksam på hur intressant utbudsvolymerna fördelades mellan butikerna, samtidigt som de inte överskred kapaciteten på våra lager och tillfredsställde alla önskemål om erforderligt antal varor för varje butik.
Om den hittade lösningen passar oss kan vi spara den, eller gå tillbaka till de ursprungliga värdena och försöka igen med andra parametrar. Du kan också spara den valda kombinationen av parametrar som Scenario. På användarens begäran kan Excel bygga tre typer Rapport om problemet som löses på separata blad: en rapport om resultaten, en rapport om lösningens matematiska stabilitet och en rapport om lösningens gränser (restriktioner), men i de flesta fall är de endast av intresse för specialister .
Det finns dock situationer där Excel inte kan hitta en lämplig lösning. Det är möjligt att simulera ett sådant fall om vi i vårt exempel anger butikernas krav i en mängd större än lagrens totala kapacitet. Sedan, när du utför en optimering, kommer Excel att försöka komma så nära lösningen som möjligt och sedan visa ett meddelande om att lösningen inte kan hittas. Ändå, även i det här fallet, har vi mycket användbar information – i synnerhet kan vi se de "svaga länkarna" i våra affärsprocesser och förstå områdena för förbättringar.
Det övervägda exemplet är naturligtvis relativt enkelt, men kan lätt skalas för att lösa mycket mer komplexa problem. Till exempel:
- Optimering av fördelningen av ekonomiska resurser per utgiftspost i affärsplanen eller budgeten för projektet. Begränsningarna, i det här fallet, kommer att vara finansieringsbeloppet och tidpunkten för projektet, och målet med optimering är att maximera vinsten och minimera projektkostnaderna.
- Optimering av personalens schemaläggning för att minimera företagets lönefond. Begränsningar, i detta fall, kommer att vara varje anställds önskemål enligt anställningsschemat och kraven i bemanningstabellen.
- Optimering av investeringsinvesteringar – Behovet av att korrekt fördela medel mellan flera banker, värdepapper eller aktier i företag för att återigen maximera vinsten eller (om viktigare) minimera riskerna.
I alla fall tillägg Sök lösningar (lösare) är ett mycket kraftfullt och vackert Excel-verktyg och värt din uppmärksamhet, eftersom det kan hjälpa till i många svåra situationer som du måste möta i modern verksamhet.