Probleemi sõnastamine
Oletame, et ettevõttel, kus te töötate, on kolm ladu, kust kaup liigub viide teie kauplusesse, mis on hajutatud üle Moskva.
Iga kauplus suudab müüa teatud koguse meile teadaolevat kaupa. Kõik laod on piiratud mahutavusega. Ülesandeks on ratsionaalselt valida, millisest laost millistesse kauplustesse kaup toimetada, et minimeerida transpordi kogukulusid.
Enne optimeerimise alustamist on vaja koostada Exceli lehel lihtne tabel – meie olukorda kirjeldav matemaatiline mudel:
On arusaadav, et:
- Helekollane tabel (C4:G6) kirjeldab igast laost igasse poodi ühe kauba saatmise kulu.
- Lillad lahtrid (C15:G14) kirjeldavad kaupade kogust, mis on vajalik igas kaupluses müümiseks.
- Punased rakud (J10:J13) näitavad iga lao mahutavust – maksimaalset kaubakogust, mida lattu mahub.
- Kollased (C13:G13) ja sinised (H10:H13) lahtrid on vastavalt roheliste lahtrite ridade ja veergude summad.
- Kogu saatekulu (J18) arvutatakse kaupade arvu toodete ja neile vastavate saatekulude summana – arvutamiseks kasutatakse siin funktsiooni SUMPRODUCT (TOODE SUMMA).
Seega on meie ülesanne taandatud roheliste rakkude optimaalsete väärtuste valikule. Ja nii, et rea kogusumma (sinised lahtrid) ei ületaks lao mahtu (punased rakud) ja samal ajal saab iga pood müüdava kaubakoguse (iga kaupluse summa). kollased lahtrid peaksid olema võimalikult lähedased nõuetele – lillad lahtrid).
Lahendus
Matemaatikas on selliseid ressursside optimaalse jaotuse valimise probleeme sõnastatud ja kirjeldatud pikka aega. Ja loomulikult on nende lahendamise viise juba pikka aega välja töötatud mitte nüri loendamisega (mis on väga pikk), vaid väga väikese arvu iteratsioonidega. Excel pakub kasutajale selliseid funktsioone lisandmooduli abil. Otsingulahendused (Lahendaja) vahekaardilt kuupäev (Kuupäev):
Kui vahekaardil kuupäev sinu Excelis sellist käsku pole – pole hullu – see tähendab, et lisandmoodul pole lihtsalt veel ühendatud. Selle aktiveerimiseks avage fail, Seejärel valige parameetrid - Lisandmoodulite - MEIST (Valikud - Lisandmoodulid - Mine). Avanevas aknas märkige ruut selle rea kõrval, mida vajame Otsingulahendused (Lahendaja).
Käivitame lisandmooduli:
Selles aknas peate määrama järgmised parameetrid:
- Sihtfunktsiooni optimeerimine (Seadke traha kamber) – siin on vaja ära märkida meie optimeerimise lõppeesmärk ehk roosa kast kogu saatekuluga (J18). Sihtlahtrit saab minimeerida (kui see on kulud, nagu meie puhul), maksimeerida (kui see on näiteks kasum) või proovida viia see etteantud väärtuseni (näiteks mahutada täpselt eraldatud eelarvesse).
- Muutuvate rakkude muutmine (By muutuv rakud) – siin märgime rohelised lahtrid (C10: G12), mille väärtusi muutes soovime saavutada oma tulemuse – minimaalse tarnekulu.
- Kooskõlas piirangutega (Teema et the,en Piirangud) – loetelu piirangutest, mida tuleb optimeerimisel arvestada. Loendile piirangute lisamiseks klõpsake nuppu lisama (Lisama) ja sisestage kuvatavas aknas tingimus. Meie puhul on see nõudluse piirang:
ja ladude maksimaalse mahu piirang:
Lisaks ilmsetele füüsiliste teguritega seotud piirangutele (ladude ja transpordivahendite mahutavus, eelarve- ja ajapiirangud jne) on mõnikord vaja lisada piiranguid “Exceli jaoks spetsiaalselt”. Nii saab näiteks Excel hõlpsasti korraldada tarnekulu “optimeerimise”, pakkudes kaupade transporti kauplustest tagasi lattu – kulud lähevad negatiivseks ehk saame kasumit! 🙂
Selle vältimiseks on parem jätta märkeruut sisse lülitatuks. Muutke piiramatud muutujad mittenegatiivseks või isegi mõnikord selgesõnaliselt registreerida sellised hetked piirangute loendis.
Pärast kõigi vajalike parameetrite seadistamist peaks aken välja nägema järgmine:
Rippmenüüs Lahendusmeetodi valimine peate lisaks valima sobiva matemaatilise meetodi kolme valiku hulgast.
- Lihtne meetod on lihtne ja kiire meetod lineaarsete ülesannete lahendamiseks, st probleemide lahendamiseks, kus väljund on lineaarselt sõltuv sisendist.
- Üldine alandatud gradiendi meetod (OGG) – mittelineaarsete probleemide puhul, kus sisend- ja väljundandmete vahel on keerulised mittelineaarsed sõltuvused (näiteks müügimahu sõltuvus reklaamikuludest).
- Evolutsiooniline lahenduse otsimine – suhteliselt uus optimeerimismeetod, mis põhineb bioloogilise evolutsiooni põhimõtetel (tere Darwin). See meetod töötab mitu korda kauem kui kaks esimest, kuid suudab lahendada peaaegu kõik probleemid (mittelineaarne, diskreetne).
Meie ülesanne on selgelt lineaarne: tarnitud 1 tükk – kulus 40 rubla, tarnitud 2 tükki – kulus 80 rubla. jne, seega on simpleksmeetod parim valik.
Nüüd, kui arvutusandmed on sisestatud, vajutage nuppu Leida lahendus (Lahenda)optimeerimise alustamiseks. Rasketel juhtudel, kus on palju muutuvaid rakke ja piiranguid, võib lahenduse leidmine võtta kaua aega (eriti evolutsioonilise meetodi puhul), kuid meie ülesanne Excelis ei valmista probleeme – paari hetkega saame järgmised tulemused :
Pöörake tähelepanu sellele, kui huvitavalt jaotusid tarnemahud kaupluste vahel, samas mitte ületades meie ladude võimsust ja rahuldades iga kaupluse kõiki soove vajaliku kauba arvu osas.
Kui leitud lahendus meile sobib, siis saame selle salvestada või naasta algväärtustele ja proovida uuesti teiste parameetritega. Valitud parameetrite kombinatsiooni saate salvestada ka nimega Stsenaarium. Kasutaja soovil saab Excel ehitada kolme tüüpi Aruanded lahendatava ülesande kohta eraldi lehtedel: aruanne tulemuste kohta, aruanne lahenduse matemaatilise stabiilsuse kohta ja aruanne lahenduse piiride (piirangute) kohta, kuid enamasti pakuvad need huvi ainult spetsialistidele .
Siiski on olukordi, kus Excel ei leia sobivat lahendust. Sellist juhtumit on võimalik simuleerida, kui näitame oma näites kaupluste nõudeid summas, mis on suurem kui ladude kogumaht. Seejärel proovib Excel optimeerimise ajal jõuda lahendusele võimalikult lähedale ja kuvab seejärel teate, et lahendust ei leita. Sellegipoolest on meil ka sel juhul palju kasulikku infot – eelkõige näeme oma äriprotsesside “nõrkasid lülisid” ja mõistame parenduskohti.
Vaadeldav näide on muidugi suhteliselt lihtne, kuid kergesti mastaapne palju keerulisemate probleemide lahendamiseks. Näiteks:
- Rahaliste vahendite jaotamise optimeerimine kuluartiklite kaupa projekti äriplaanis või eelarves. Piirangud on sel juhul rahastamise summa ja projekti ajastus ning optimeerimise eesmärk on maksimeerida kasumit ja minimeerida projekti kulusid.
- Töötajate ajakava optimeerimine ettevõtte palgafondi minimeerimiseks. Piirangud on sel juhul iga töötaja soovid vastavalt töögraafikule ja personalitabeli nõuetele.
- Investeerimisinvesteeringute optimeerimine – vajadus jaotada korrektselt raha mitme panga vahel, väärtpabereid või ettevõtete aktsiaid, et jällegi maksimeerida kasumit või (kui see on olulisem) minimeerida riske.
Igal juhul lisandmoodul Otsingulahendused (lahendaja) on väga võimas ja ilus Exceli tööriist ning väärib teie tähelepanu, kuna see võib aidata paljudes keerulistes olukordades, millega tänapäeva äris silmitsi seisate.