vsebina
Formulacija problema
Recimo, da ima podjetje, v katerem delate, tri skladišča, od koder gre blago v pet vaših trgovin, razpršenih po Moskvi.
Vsaka trgovina lahko proda določeno količino blaga, ki ga poznamo. Vsako od skladišč ima omejeno kapaciteto. Naloga je racionalno izbrati, iz katerega skladišča v katere trgovine dostaviti blago, da bi zmanjšali skupne transportne stroške.
Preden se lotimo optimizacije, bo potrebno na Excelovem listu sestaviti preprosto tabelo – naš matematični model, ki opisuje situacijo:
Razume se, da:
- Svetlorumena tabela (C4:G6) opisuje stroške pošiljanja enega artikla iz vsakega skladišča v vsako trgovino.
- Vijolične celice (C15:G14) opisujejo količino blaga, ki ga mora posamezna trgovina prodati.
- Rdeče celice (J10:J13) prikazujejo kapaciteto posameznega skladišča – največjo količino blaga, ki jo skladišče lahko sprejme.
- Rumene (C13:G13) in modre (H10:H13) celice so vsote vrstic in stolpcev za zelene celice.
- Skupni stroški pošiljanja (J18) se izračunajo kot vsota zmnožkov števila blaga in pripadajočih stroškov pošiljanja – za izračun se tukaj uporablja funkcija SUMPRODUCT (SUM PRODUKT).
Tako je naša naloga zmanjšana na izbiro optimalnih vrednosti zelenih celic. In tako, da skupni znesek za linijo (modre celice) ne preseže zmogljivosti skladišča (rdeče celice), hkrati pa vsaka trgovina prejme količino blaga, ki jo mora prodati (znesek za vsako trgovino v rumene celice naj bodo čim bližje zahtevam – vijolične celice).
Rešitev
V matematiki so takšni problemi izbire optimalne porazdelitve virov oblikovani in opisani že dolgo časa. In seveda so bili načini za njihovo reševanje že dolgo razviti ne z odkritim naštevanjem (ki je zelo dolgo), ampak v zelo majhnem številu ponovitev. Excel omogoča uporabniku takšno funkcionalnost z uporabo dodatka. Iskanje rešitev (Reševalec) iz zavihka datum (Datum):
Če je na zavihku datum vaš Excel nima takega ukaza – nič hudega – to pomeni, da dodatek preprosto še ni povezan. Če ga želite aktivirati, odprite file, Nato izberite parametri - Dodatki - O meni (Možnosti — Dodatki — Pojdi na). V oknu, ki se odpre, potrdite polje poleg vrstice, ki jo potrebujemo Iskanje rešitev (Reševalec).
Zaženimo dodatek:
V tem oknu morate nastaviti naslednje parametre:
- Optimizirajte ciljno funkcijo (Nastavite tDenar celica) – tukaj je potrebno označiti končni glavni cilj naše optimizacije, to je roza škatla s skupnimi stroški pošiljanja (J18). Ciljno celico lahko minimiziramo (če gre za stroške, kot v našem primeru), maksimiziramo (če je na primer dobiček) ali jo poskušamo spraviti na dano vrednost (na primer natančno prilegati dodeljenemu proračunu).
- Spreminjanje spremenljivih celic (By spreminjanje celice) – tukaj označimo zelene celice (C10: G12), s spreminjanjem vrednosti katerih želimo doseči naš rezultat – minimalni strošek dostave.
- Skladno z omejitvami (Zadeva do o Omejitve) – seznam omejitev, ki jih moramo upoštevati pri optimizaciji. Za dodajanje omejitev na seznam kliknite gumb Dodaj (Dodaj) in vnesite pogoj v okno, ki se prikaže. V našem primeru bo to omejitev povpraševanja:
in omejitev največjega obsega skladišč:
Poleg očitnih omejitev, povezanih s fizičnimi dejavniki (kapaciteta skladišč in prevoznih sredstev, proračunske in časovne omejitve itd.), je včasih treba dodati omejitve, »posebne za Excel«. Tako vam lahko na primer Excel brez težav poskrbi za “optimizacijo” stroškov dostave s ponudbo prevoza blaga iz trgovin nazaj v skladišče – stroški bodo postali negativni, mi bomo imeli dobiček! 🙂
Da se to ne bi zgodilo, je najbolje, da potrditveno polje pustite omogočeno. Neomejeno število spremenljivk naj ne bo negativno ali celo včasih izrecno registrira takšne trenutke na seznamu omejitev.
Po nastavitvi vseh potrebnih parametrov bi moralo biti okno videti takole:
Na spustnem seznamu Izberi metodo reševanja morate dodatno izbrati ustrezno matematično metodo za rešitev izbire treh možnosti:
- Simpleksna metoda je preprosta in hitra metoda za reševanje linearnih problemov, torej problemov, kjer je izhod linearno odvisen od vhoda.
- Splošna znižana gradientna metoda (OGG) – za nelinearne probleme, kjer obstajajo kompleksne nelinearne odvisnosti med vhodnimi in izhodnimi podatki (na primer odvisnost prodaje od stroškov oglaševanja).
- Evolucijsko iskanje rešitve – relativno nova optimizacijska metoda, ki temelji na načelih biološke evolucije (zdravo Darwin). Ta metoda deluje večkrat dlje kot prvi dve, vendar lahko reši skoraj vsak problem (nelinearen, diskreten).
Naša naloga je jasno linearna: dostavljen 1 kos – porabljenih 40 rubljev, dostavljen 2 kos – porabljenih 80 rubljev. itd., zato je metoda simpleksa najboljša izbira.
Zdaj, ko so podatki za izračun vneseni, pritisnite tipko Poiščite rešitev (Rešiti)za začetek optimizacije. V hudih primerih z veliko spreminjajočimi se celicami in omejitvami lahko iskanje rešitve traja dolgo (zlasti pri evolucijski metodi), vendar naša naloga za Excel ne bo težava – čez nekaj trenutkov bomo dobili naslednje rezultate :
Bodite pozorni na to, kako zanimivo so bile količine dobave razporejene po trgovinah, pri tem pa ne presežemo zmogljivosti naših skladišč in zadovoljimo vse zahteve po zahtevanem številu blaga za vsako trgovino.
Če nam najdena rešitev ustreza, jo lahko shranimo ali pa se vrnemo na prvotne vrednosti in poskusimo znova z drugimi parametri. Izbrano kombinacijo parametrov lahko tudi shranite kot Scenarij. Na zahtevo uporabnika lahko Excel zgradi tri vrste Poročila o problemu, ki se rešuje na ločenih listih: poročilo o rezultatih, poročilo o matematični stabilnosti rešitve in poročilo o mejah (omejitev) rešitve, vendar so v večini primerov zanimivi le za strokovnjake. .
Obstajajo pa situacije, ko Excel ne najde ustrezne rešitve. Takšen primer je mogoče simulirati, če v našem primeru navedemo zahteve trgovin v višini, ki je večja od skupne kapacitete skladišč. Nato se bo Excel med izvajanjem optimizacije poskušal čim bolj približati rešitvi in nato prikazal sporočilo, da rešitve ni mogoče najti. Kljub temu imamo tudi v tem primeru veliko koristnih informacij – predvsem lahko vidimo »šibke člene« naših poslovnih procesov in razumemo področja za izboljšave.
Obravnavani primer je seveda razmeroma preprost, vendar ga je enostavno prilagoditi reševanju veliko bolj zapletenih problemov. Na primer:
- Optimizacija distribucije finančnih sredstev po odhodkovni postavki v poslovnem načrtu ali proračunu projekta. Omejitve bodo v tem primeru višina financiranja in časovni okvir projekta, cilj optimizacije pa maksimiranje dobička in minimiziranje stroškov projekta.
- Optimizacija razporejanja zaposlenih da bi minimizirali plačni sklad podjetja. Omejitve bodo v tem primeru želje vsakega zaposlenega glede na razpored zaposlitve in zahteve kadrovske tabele.
- Optimizacija investicijskih vlaganj – potreba po pravilni porazdelitvi sredstev med več bankami, vrednostnimi papirji ali delnicami podjetij, da bi ponovno povečali dobiček ali (če je pomembneje) zmanjšali tveganja.
V vsakem primeru dodatek Iskanje rešitev (Reševalec) je zelo zmogljivo in lepo orodje Excel in vredno vaše pozornosti, saj lahko pomaga v številnih težkih situacijah, s katerimi se morate soočiti v sodobnem poslovanju.