Problemos formulavimas
Tarkime, kad įmonė, kurioje dirbate, turi tris sandėlius, iš kurių prekės keliauja į penkias jūsų parduotuves, išsibarsčiusias po Maskvą.
Kiekviena parduotuvė gali parduoti tam tikrą mums žinomų prekių kiekį. Kiekvienas iš sandėlių turi ribotą talpą. Užduotis – racionaliai pasirinkti, iš kurio sandėlio į kokias parduotuves pristatyti prekes, siekiant maksimaliai sumažinti bendrąsias transportavimo išlaidas.
Prieš pradedant optimizavimą, Excel lape reikės sudaryti paprastą lentelę – mūsų matematinį modelį, apibūdinantį situaciją:
Suprantama, kad:
- Šviesiai geltona lentelė (C4:G6) apibūdina vienos prekės siuntimo iš kiekvieno sandėlio į kiekvieną parduotuvę kainą.
- Purpuriniai langeliai (C15:G14) apibūdina prekių kiekį, reikalingą kiekvienai parduotuvei parduoti.
- Raudonieji langeliai (J10:J13) rodo kiekvieno sandėlio talpą – maksimalų sandėlyje galimą prekių kiekį.
- Geltonos (C13:G13) ir mėlynos (H10:H13) langeliai yra atitinkamai žaliųjų langelių eilučių ir stulpelių sumos.
- Bendra siuntimo kaina (J18) skaičiuojama kaip prekių skaičiaus produktų ir jas atitinkančių siuntimo išlaidų suma – skaičiavimui čia naudojama funkcija SUMPRODUCT (SUMPRODUCT).
Taigi mūsų užduotis yra sumažinta iki optimalių žaliųjų ląstelių verčių parinkimo. Ir kad bendra suma linijai (mėlyni langeliai) neviršytų sandėlio talpos (raudonieji langeliai) ir tuo pačiu kiekviena parduotuvė gautų jai reikalingą parduoti prekių kiekį (kiekvienai parduotuvei geltonos spalvos langeliai turi būti kuo artimesni reikalavimams – violetinės spalvos langeliai).
Sprendimas
Matematikoje tokios optimalaus išteklių paskirstymo pasirinkimo problemos formuluojamos ir aprašomos jau seniai. Ir, žinoma, jų sprendimo būdai jau seniai buvo kuriami ne buku išvardinimu (kuris yra labai ilgas), o labai mažu iteracijų skaičiumi. „Excel“ suteikia vartotojui tokią funkciją naudodama priedą. Paieškos sprendimai (Spręstojas) iš skirtuko Duomenys (Data):
Jei skirtuke Duomenys jūsų Excel tokios komandos nėra – viskas gerai – tai reiškia, kad priedas tiesiog dar neprijungtas. Norėdami jį suaktyvinti, atidarykite filė, Tada pasirinkite Parametrai - Pridėti priedus - Apie mus (Parinktys – Priedai – Eiti į). Atsidariusiame lange pažymėkite laukelį šalia mums reikalingos eilutės Paieškos sprendimai (Spręstojas).
Paleiskite priedą:
Šiame lange turite nustatyti šiuos parametrus:
- Optimizuokite tikslinę funkciją (Nustatykite tpinigai ląstelė) – čia būtina nurodyti galutinį pagrindinį mūsų optimizavimo tikslą, ty rožinę dėžutę su bendra siuntimo kaina (J18). Tikslinė ląstelė gali būti minimizuota (jei tai yra išlaidos, kaip mūsų atveju), maksimaliai padidinta (jei tai, pavyzdžiui, pelnas) arba bandoma padidinti ją iki nurodytos vertės (pavyzdžiui, tiksliai tilpti į skirtą biudžetą).
- Kintamųjų ląstelių keitimas (By keičiasi ląstelės) – čia nurodome žalius langelius (C10: G12), kurių dydžius keičiant norime pasiekti savo rezultatą – minimalias pristatymo išlaidas.
- Atitinka apribojimus (Tema į As Apribojimai) – apribojimų, į kuriuos reikia atsižvelgti optimizuojant, sąrašas. Norėdami įtraukti apribojimus į sąrašą, spustelėkite mygtuką papildyti (Papildyti) ir pasirodžiusiame lange įveskite sąlygą. Mūsų atveju tai bus paklausos apribojimas:
ir didžiausio sandėlių tūrio apribojimas:
Be akivaizdžių apribojimų, susijusių su fiziniais veiksniais (sandėlių ir transporto priemonių talpa, biudžeto ir laiko apribojimai ir kt.), kartais reikia pridėti apribojimus „specialiai Excel“. Taigi, pavyzdžiui, „Excel“ gali nesunkiai pasirūpinti, kad „optimizuotume“ pristatymo kaštus, siūlydama prekes iš parduotuvių gabenti atgal į sandėlį – išlaidos taps neigiamos, ty gausime pelną! 🙂
Kad taip nenutiktų, geriausia palikti žymimąjį laukelį įjungtą. Padarykite neribotus kintamuosius neneigiamus ar net kartais tokius momentus aiškiai registruoti apribojimų sąraše.
Nustačius visus reikiamus parametrus, langas turėtų atrodyti taip:
Išskleidžiamajame sąraše Pasirinkite sprendimo metodą papildomai turite pasirinkti tinkamą matematinį metodą, kad išspręstumėte vieną iš trijų variantų:
- Paprastas metodas yra paprastas ir greitas būdas spręsti tiesines problemas, ty problemas, kai išvestis tiesiškai priklauso nuo įvesties.
- Bendrasis žemesnio lygio gradiento metodas (OGG) – netiesinėms problemoms, kai yra sudėtingos nelinijinės įvesties ir išvesties duomenų priklausomybės (pavyzdžiui, pardavimų priklausomybė nuo reklamos išlaidų).
- Evoliucinė sprendimo paieška – palyginti naujas optimizavimo metodas, paremtas biologinės evoliucijos principais (labas, Darvinai). Šis metodas veikia daug kartų ilgiau nei pirmieji du, tačiau gali išspręsti beveik bet kokią problemą (netiesinę, diskrečiąją).
Mūsų užduotis aiškiai linijinė: atvežė 1 vnt. – išleido 40 rublių, atvežė 2 vnt. – išleido 80 rublių. ir tt, todėl geriausias pasirinkimas yra simpleksinis metodas.
Dabar, kai įvesti skaičiavimo duomenys, paspauskite mygtuką Rasti sprendimą (Išspręsti)pradėti optimizavimą. Sunkiais atvejais, kai keičiasi daug ląstelių ir suvaržymų, sprendimo paieška gali užtrukti ilgai (ypač taikant evoliucinį metodą), tačiau mūsų užduotis Excel nesukels problemų – po kelių akimirkų gausime tokius rezultatus :
Atkreipkite dėmesį į tai, kaip įdomiai pasiskirstė tiekimo apimtys tarp parduotuvių, tuo pačiu neviršijant mūsų sandėlių pajėgumų ir patenkinant visus prašymus dėl reikiamo prekių kiekio kiekvienai parduotuvei.
Jei rastas sprendimas mums tinka, galime jį išsaugoti arba grįžti prie pradinių reikšmių ir bandyti dar kartą su kitais parametrais. Taip pat galite išsaugoti pasirinktą parametrų derinį kaip Scenarijus. Vartotojo pageidavimu „Excel“ gali sukurti trijų tipų Ataskaitos apie sprendžiamą uždavinį atskiruose lapuose: rezultatų ataskaita, sprendimo matematinis stabilumas ir sprendimo ribų (apribojimų) ataskaita, tačiau dažniausiai jie domina tik specialistus. .
Tačiau yra situacijų, kai „Excel“ negali rasti tinkamo sprendimo. Galima imituoti tokį atvejį, jei savo pavyzdyje nurodysime parduotuvių poreikius didesne suma nei bendra sandėlių talpa. Tada, atlikdama optimizavimą, „Excel“ bandys priartėti prie sprendimo kuo arčiau, o tada parodys pranešimą, kad sprendimo nerasta. Nepaisant to, net ir šiuo atveju turime daug naudingos informacijos – ypač matome savo verslo procesų „silpnas grandis“ ir suprantame tobulintinas sritis.
Nagrinėjamas pavyzdys, žinoma, yra gana paprastas, tačiau lengvai pritaikomas sprendžiant daug sudėtingesnes problemas. Pavyzdžiui:
- Finansinių išteklių paskirstymo optimizavimas pagal išlaidų straipsnius verslo plane arba projekto biudžete. Apribojimai šiuo atveju bus finansavimo suma ir projekto laikas, o optimizavimo tikslas – maksimaliai padidinti pelną ir sumažinti projekto išlaidas.
- Darbuotojų darbo grafiko optimizavimas siekiant kuo labiau sumažinti įmonės darbo užmokesčio fondą. Apribojimai, šiuo atveju, bus kiekvieno darbuotojo pageidavimai pagal įdarbinimo grafiką ir etatų lentelės reikalavimus.
- Investicinių investicijų optimizavimas – poreikis teisingai paskirstyti lėšas tarp kelių bankų, vertybinius popierius ar įmonių akcijas, siekiant, vėlgi, maksimaliai padidinti pelną arba (jei svarbiau) sumažinti riziką.
Bet kokiu atveju, priedas Paieškos sprendimai (sprendėjas) yra labai galingas ir gražus „Excel“ įrankis, vertas jūsų dėmesio, nes gali padėti daugelyje sudėtingų situacijų, su kuriomis tenka susidurti šiuolaikiniame versle.