Formulering av problemet
Anta at selskapet der du jobber har tre varehus, hvorfra varene går til fem av butikkene dine spredt over hele Moskva.
Hver butikk er i stand til å selge en viss mengde varer kjent for oss. Hvert av lagrene har begrenset kapasitet. Oppgaven er å rasjonelt velge fra hvilket lager til hvilke butikker som skal levere varene for å minimere de totale transportkostnadene.
Før du starter optimaliseringen, vil det være nødvendig å kompilere en enkel tabell på et Excel-ark – vår matematiske modell som beskriver situasjonen:
Det er forstått at:
- Den lysegule tabellen (C4:G6) beskriver kostnaden for å frakte én vare fra hvert lager til hver butikk.
- Lilla celler (C15:G14) beskriver mengden varer som kreves for at hver butikk skal selge.
- Røde celler (J10:J13) viser kapasiteten til hvert lager – den maksimale mengden varer som lageret kan inneholde.
- Gule (C13:G13) og blå (H10:H13) celler er henholdsvis rad- og kolonnesummene for grønne celler.
- Den totale fraktkostnaden (J18) beregnes som summen av produktene av antall varer og deres tilsvarende fraktkostnader – for beregning brukes funksjonen her SUMPRODUCT (SUMPRODUKT).
Dermed er vår oppgave redusert til valg av optimale verdier av grønne celler. Og slik at totalbeløpet for linjen (blå celler) ikke overstiger kapasiteten til lageret (røde celler), og samtidig mottar hver butikk mengden varer den trenger å selge (beløpet for hver butikk i gule celler skal være så nær kravene som mulig – lilla celler).
Oppløsning
I matematikk har slike problemer med å velge optimal fordeling av ressurser vært formulert og beskrevet i lang tid. Og selvfølgelig har måter å løse dem lenge blitt utviklet ikke ved stump oppregning (som er veldig lang), men i et veldig lite antall iterasjoner. Excel gir brukeren slik funksjonalitet ved hjelp av et tillegg. Søkeløsninger (løser) fra fanen Data (Dato):
Hvis på fanen Data Excel har ikke en slik kommando – det er greit – det betyr at tillegget rett og slett ikke er koblet til ennå. Åpne for å aktivere den filet, velg deretter parametere - Add-ons - Om oss (Alternativer — Tillegg — Gå til). I vinduet som åpnes, merk av i boksen ved siden av linjen vi trenger Søkeløsninger (løser).
La oss kjøre tillegget:
I dette vinduet må du angi følgende parametere:
- Optimaliser målfunksjonen (Sett tpenger celle) – her er det nødvendig å angi det endelige hovedmålet for optimaliseringen vår, dvs. rosa boks med total fraktkostnad (J18). Målcellen kan minimeres (hvis det er utgifter, som i vårt tilfelle), maksimeres (hvis det for eksempel er profitt) eller prøve å bringe det til en gitt verdi (for eksempel passe nøyaktig inn i det tildelte budsjettet).
- Endre variable celler (By endring celler) – her indikerer vi de grønne cellene (C10: G12), ved å variere verdiene som vi ønsker å oppnå vårt resultat – minimumskostnaden for levering.
- I samsvar med restriksjoner (Emne til de Begrensninger) – en liste over restriksjoner som må tas i betraktning ved optimalisering. Klikk på knappen for å legge til begrensninger i listen Legg til (Legge til) og skriv inn betingelsen i vinduet som vises. I vårt tilfelle vil dette være etterspørselsbegrensningen:
og grense for maksimalt volum av varehus:
I tillegg til de åpenbare begrensningene knyttet til fysiske faktorer (kapasiteten til varehus og transportmidler, budsjett- og tidsbegrensninger, etc.), er det noen ganger nødvendig å legge til restriksjoner "spesielle for Excel". Så for eksempel kan Excel enkelt tilrettelegge for at du «optimaliserer» leveringskostnadene ved å tilby å frakte varer fra butikker tilbake til lageret – kostnadene blir negative, det vil si at vi går med overskudd! 🙂
For å forhindre at dette skjer, er det best å la avmerkingsboksen være aktivert. Gjør ubegrensede variabler til ikke-negative eller til og med noen ganger eksplisitt registrere slike øyeblikk i listen over restriksjoner.
Etter å ha angitt alle nødvendige parametere, skal vinduet se slik ut:
I rullegardinlisten Velg en løsningsmetode må du i tillegg velge riktig matematisk metode for å løse et utvalg av tre alternativer:
- Enkel metode er en enkel og rask metode for å løse lineære problemer, dvs. problemer der utgangen er lineært avhengig av input.
- Generell nedgradert gradientmetode (OGG) – for ikke-lineære problemer, der det er komplekse ikke-lineære avhengigheter mellom input- og outputdata (for eksempel salgets avhengighet av annonseringskostnader).
- Evolusjonær søken etter en løsning – en relativt ny optimaliseringsmetode basert på prinsippene for biologisk evolusjon (hei Darwin). Denne metoden fungerer mange ganger lenger enn de to første, men kan løse nesten alle problemer (ikke-lineær, diskret).
Vår oppgave er tydelig lineær: leverte 1 stykke - brukte 40 rubler, leverte 2 stykker - brukte 80 rubler. etc., så simplex-metoden er det beste valget.
Nå som dataene for beregningen er lagt inn, trykk på knappen Finn en løsning (Løse)for å starte optimalisering. I alvorlige tilfeller med mange skiftende celler og begrensninger, kan det ta lang tid å finne en løsning (spesielt med den evolusjonære metoden), men oppgaven vår for Excel vil ikke være et problem - om et par øyeblikk vil vi få følgende resultater :
Vær oppmerksom på hvor interessant forsyningsvolumene ble fordelt mellom butikkene, samtidig som de ikke overskred kapasiteten til våre varehus og tilfredsstilte alle forespørsler om nødvendig antall varer for hver butikk.
Hvis løsningen som ble funnet passer oss, kan vi lagre den, eller rulle tilbake til de opprinnelige verdiene og prøve igjen med andre parametere. Du kan også lagre den valgte kombinasjonen av parametere som Scenario. På forespørsel fra brukeren kan Excel bygge tre typer Rapporter om problemet som løses på separate ark: en rapport om resultatene, en rapport om den matematiske stabiliteten til løsningen og en rapport om grensene (restriksjonene) for løsningen, men i de fleste tilfeller er de bare av interesse for spesialister .
Det er imidlertid situasjoner der Excel ikke kan finne en passende løsning. Det er mulig å simulere et slikt tilfelle hvis vi i vårt eksempel angir kravene til butikkene i mengden større enn den totale kapasiteten til varehusene. Deretter, når du utfører en optimalisering, vil Excel prøve å komme så nærme løsningen som mulig, og deretter vise en melding om at løsningen ikke kan finnes. Ikke desto mindre, selv i dette tilfellet, har vi mye nyttig informasjon – spesielt kan vi se de "svake leddene" i forretningsprosessene våre og forstå områder for forbedring.
Det betraktede eksemplet er selvfølgelig relativt enkelt, men kan enkelt skaleres for å løse mye mer komplekse problemer. For eksempel:
- Optimalisering av fordelingen av økonomiske ressurser etter utgiftspost i forretningsplanen eller budsjettet til prosjektet. Restriksjonene, i dette tilfellet, vil være mengden finansiering og tidspunktet for prosjektet, og målet med optimalisering er å maksimere fortjenesten og minimere prosjektkostnadene.
- Optimalisering av ansattplanlegging for å minimere bedriftens lønnsfond. Begrensninger, i dette tilfellet, vil være ønskene til hver ansatt i henhold til ansettelsesplanen og kravene i bemanningstabellen.
- Optimalisering av investeringsinvesteringer – behovet for å fordele midler riktig mellom flere banker, verdipapirer eller aksjer i foretak for igjen å maksimere fortjenesten eller (hvis viktigere) minimere risiko.
I alle fall tillegg Søkeløsninger (Løser) er et veldig kraftig og vakkert Excel-verktøy og verdt din oppmerksomhet, da det kan hjelpe i mange vanskelige situasjoner som du må møte i moderne virksomhet.