Formulering af problemet
Antag, at virksomheden, hvor du arbejder, har tre lagre, hvorfra varerne går til fem af dine butikker spredt over hele Moskva.
Hver butik er i stand til at sælge en vis mængde varer, vi kender. Hvert af lagrene har en begrænset kapacitet. Opgaven er rationelt at vælge fra hvilket lager til hvilke butikker varerne skal leveres for at minimere de samlede transportomkostninger.
Inden optimeringen påbegyndes, vil det være nødvendigt at kompilere en simpel tabel på et Excel-ark – vores matematiske model, der beskriver situationen:
Det er underforstået, at:
- Den lysegule tabel (C4:G6) beskriver omkostningerne ved at sende en vare fra hvert lager til hver butik.
- Lilla celler (C15:G14) beskriver den mængde varer, der kræves for hver butik at sælge.
- Røde celler (J10:J13) viser kapaciteten af hvert lager – den maksimale mængde varer, som lageret kan rumme.
- Gule (C13:G13) og blå (H10:H13) celler er række- og kolonnesummerne for henholdsvis grønne celler.
- Den samlede forsendelsesomkostning (J18) er beregnet som summen af produkterne af antallet af varer og deres tilsvarende forsendelsesomkostninger – til beregning anvendes funktionen her SUMPRODUCT (SUMPRODUKT).
Således er vores opgave reduceret til udvælgelsen af optimale værdier af grønne celler. Og så det samlede beløb for linjen (blå celler) ikke overstiger lagerets kapacitet (røde celler), og samtidig modtager hver butik den mængde varer, den skal sælge (beløbet for hver butik i gule celler skal være så tæt som muligt på kravene – lilla celler).
Løsning
I matematik har sådanne problemer med at vælge den optimale fordeling af ressourcer været formuleret og beskrevet i lang tid. Og selvfølgelig er måder at løse dem på længe blevet udviklet ikke ved stump opregning (som er meget lang), men i et meget lille antal iterationer. Excel giver brugeren en sådan funktionalitet ved hjælp af et tilføjelsesprogram. Søgeløsninger (Løser) fra fanen data (Dato):
Hvis på fanen data din Excel har ikke sådan en kommando – det er okay – det betyder, at tilføjelsesprogrammet simpelthen ikke er tilsluttet endnu. Åbn den for at aktivere den File (Felt), Vælg derefter parametre - Tilføjelser - Om (Valgmuligheder — Tilføjelser — Gå til). I vinduet, der åbnes, skal du markere feltet ud for den linje, vi skal bruge Søgeløsninger (Løser).
Lad os køre tilføjelsen:
I dette vindue skal du indstille følgende parametre:
- Optimer målfunktionen (Sæt tpenge celle) – her er det nødvendigt at angive det endelige hovedmål for vores optimering, dvs. pink boks med den samlede forsendelsesomkostning (J18). Målcellen kan minimeres (hvis det er udgifter, som i vores tilfælde), maksimeres (hvis det for eksempel er profit) eller forsøge at bringe det til en given værdi (for eksempel passe præcist ind i det tildelte budget).
- Ændring af variable celler (By skiftende celler) – her angiver vi de grønne celler (C10: G12), ved at variere de værdier, som vi ønsker at opnå vores resultat – minimumsomkostningerne ved levering.
- I overensstemmelse med restriktioner (Emne til og Begrænsninger) – en liste over restriktioner, der skal tages i betragtning ved optimering. Klik på knappen for at tilføje begrænsninger til listen Tilføj (Tilføje) og indtast betingelsen i det vindue, der vises. I vores tilfælde vil dette være efterspørgselsbegrænsningen:
og begrænsning på det maksimale lagervolumen:
Ud over de åbenlyse begrænsninger forbundet med fysiske faktorer (kapacitet af varehuse og transportmidler, budget- og tidsbegrænsninger osv.), er det nogle gange nødvendigt at tilføje begrænsninger "særlige for Excel". Så fx kan Excel nemt sørge for, at du "optimerer" leveringsomkostningerne ved at tilbyde at transportere varer fra butikker tilbage til lageret – omkostningerne bliver negative, dvs. vi får overskud! 🙂
For at forhindre dette i at ske, er det bedst at lade afkrydsningsfeltet være aktiveret. Gør ubegrænsede variabler til ikke-negative eller endda nogle gange eksplicit registrere sådanne øjeblikke i listen over restriktioner.
Efter at have indstillet alle de nødvendige parametre, skal vinduet se sådan ud:
I rullelisten Vælg en løsningsmetode skal du desuden vælge den relevante matematiske metode til at løse et valg af tre muligheder:
- Enkel metode er en enkel og hurtig metode til løsning af lineære problemer, altså problemer hvor outputtet er lineært afhængigt af input.
- General Downgraded Gradient Method (OGG) – for ikke-lineære problemer, hvor der er komplekse ikke-lineære afhængigheder mellem input- og outputdata (f.eks. salgets afhængighed af reklameomkostninger).
- Evolutionær søgen efter en løsning – en relativt ny optimeringsmetode baseret på principperne for biologisk evolution (hej Darwin). Denne metode virker mange gange længere end de to første, men kan løse næsten ethvert problem (ikke-lineær, diskret).
Vores opgave er klart lineær: leveret 1 stykke - brugt 40 rubler, leveret 2 stykker - brugt 80 rubler. osv., så simplex-metoden er det bedste valg.
Nu hvor dataene til beregningen er indtastet, tryk på knappen Find en løsning (Løse)for at starte optimering. I alvorlige tilfælde med mange skiftende celler og begrænsninger kan det tage lang tid at finde en løsning (især med den evolutionære metode), men vores opgave for Excel vil ikke være et problem - om et par øjeblikke vil vi få følgende resultater :
Vær opmærksom på, hvor interessant forsyningsmængderne var fordelt mellem butikkerne, mens de ikke overskred kapaciteten på vores varehuse og imødekom alle anmodninger om det nødvendige antal varer til hver butik.
Hvis den fundne løsning passer os, så kan vi gemme den eller rulle tilbage til de oprindelige værdier og prøve igen med andre parametre. Du kan også gemme den valgte kombination af parametre som Scenario. På opfordring fra brugeren kan Excel bygge tre typer Rapporter om problemet, der løses på separate ark: en rapport om resultaterne, en rapport om den matematiske stabilitet af løsningen og en rapport om grænserne (begrænsningerne) af løsningen, men i de fleste tilfælde er de kun af interesse for specialister .
Der er dog situationer, hvor Excel ikke kan finde en passende løsning. Det er muligt at simulere et sådant tilfælde, hvis vi i vores eksempel angiver butikkernes behov i mængder, der er større end lagrenes samlede kapacitet. Derefter vil Excel, når du udfører en optimering, forsøge at komme så tæt på løsningen som muligt, og derefter vise en besked om, at løsningen ikke kan findes. Ikke desto mindre, selv i dette tilfælde, har vi en masse nyttig information - især kan vi se de "svage led" i vores forretningsprocesser og forstå områderne for forbedring.
Det betragtede eksempel er selvfølgelig relativt simpelt, men kan nemt skaleres til at løse meget mere komplekse problemer. For eksempel:
- Optimering af fordelingen af økonomiske ressourcer efter udgiftspost i projektets forretningsplan eller budget. Begrænsningerne vil i dette tilfælde være mængden af finansiering og tidspunktet for projektet, og målet med optimering er at maksimere fortjenesten og minimere projektomkostningerne.
- Optimering af medarbejderplanlægning for at minimere virksomhedens lønfond. Begrænsninger vil i dette tilfælde være hver enkelt medarbejders ønsker i henhold til ansættelsesskemaet og kravene i personaletabellen.
- Optimering af investeringsinvesteringer – behovet for korrekt fordeling af midler mellem flere banker, værdipapirer eller aktier i virksomheder for igen at maksimere profitten eller (hvis vigtigere) minimere risici.
Under alle omstændigheder add-on Søgeløsninger (Løser) er et meget kraftfuldt og smukt Excel-værktøj og værd at være opmærksom på, da det kan hjælpe i mange vanskelige situationer, som du står over for i moderne forretning.