Përmbajtje
Formulimi i problemit
Supozoni se kompania ku punoni ka tre depo, nga ku mallrat shkojnë në pesë nga dyqanet tuaja të shpërndara në të gjithë Moskën.
Çdo dyqan është në gjendje të shesë një sasi të caktuar mallrash të njohura për ne. Secila nga magazinat ka një kapacitet të kufizuar. Detyra është që në mënyrë racionale të zgjedhësh nga cila magazinë në cilat dyqane të dorëzohen mallrat në mënyrë që të minimizohen kostot totale të transportit.
Para fillimit të optimizimit, do të jetë e nevojshme të përpiloni një tabelë të thjeshtë në një fletë Excel - modeli ynë matematikor që përshkruan situatën:
Kuptohet se:
- Tabela e verdhë e lehtë (C4:G6) përshkruan koston e transportit të një artikulli nga çdo magazinë në çdo dyqan.
- Qelizat e purpurta (C15:G14) përshkruajnë sasinë e mallrave të kërkuara për çdo dyqan për të shitur.
- Qelizat e kuqe (J10:J13) shfaqin kapacitetin e secilës magazinë – sasinë maksimale të mallrave që mund të mbajë magazina.
- Qelizat e verdha (C13:G13) dhe blu (H10:H13) janë shumat e rreshtave dhe kolonave për qelizat jeshile, respektivisht.
- Kostoja totale e transportit (J18) llogaritet si shuma e produkteve të numrit të mallrave dhe kostove përkatëse të transportit të tyre - për llogaritjen, funksioni përdoret këtu SUMPRODUCT (SUMPRODUCT).
Kështu, detyra jonë reduktohet në zgjedhjen e vlerave optimale të qelizave të gjelbra. Dhe në mënyrë që shuma totale për linjën (qelizat blu) të mos kalojë kapacitetin e magazinës (qelizat e kuqe), dhe në të njëjtën kohë çdo dyqan të marrë sasinë e mallrave që duhet të shesë (shuma për çdo dyqan në qelizat e verdha duhet të jenë sa më afër kërkesave - qelizat e purpurta).
Zgjidhje
Në matematikë, probleme të tilla të zgjedhjes së shpërndarjes optimale të burimeve janë formuluar dhe përshkruar për një kohë të gjatë. Dhe, natyrisht, mënyrat për t'i zgjidhur ato janë zhvilluar prej kohësh jo me numërim të hapur (që është shumë i gjatë), por në një numër shumë të vogël përsëritjesh. Excel i siguron përdoruesit një funksionalitet të tillë duke përdorur një shtesë. Kërko Zgjidhje (Zgjitës) nga skeda Data (Data):
Nëse në skedën Data Excel-i juaj nuk ka një komandë të tillë - është në rregull - kjo do të thotë që shtesa thjesht nuk është lidhur ende. Për ta aktivizuar hapeni Skedar, Pastaj zgjidhni Parameters - Shtesa - Rreth Nesh (Opsione - Shtesa - Shko te). Në dritaren që hapet, kontrolloni kutinë pranë rreshtit që na nevojitet Kërko Zgjidhje (Zgjitës).
Le të ekzekutojmë shtesën:
Në këtë dritare, ju duhet të vendosni parametrat e mëposhtëm:
- Optimizo funksionin e synuar (Set tpara qelizë) – këtu është e nevojshme të tregohet qëllimi kryesor përfundimtar i optimizimit tonë, pra kutia rozë me koston totale të transportit (J18). Qeliza e synuar mund të minimizohet (nëse janë shpenzime, si në rastin tonë), të maksimizohet (nëse është, për shembull, fitim) ose të përpiqet ta sjellë atë në një vlerë të caktuar (për shembull, të përshtatet saktësisht në buxhetin e alokuar).
- Ndryshimi i qelizave të ndryshueshme (By ndryshuar qelizat) – këtu tregojmë qelizat e gjelbra (C10: G12), duke ndryshuar vlerat e të cilave duam të arrijmë rezultatin tonë – koston minimale të dorëzimit.
- Në përputhje me kufizimet (subjekt në la Kufizimet) – një listë kufizimesh që duhet të merren parasysh gjatë optimizimit. Për të shtuar kufizime në listë, klikoni butonin Shtoj (Shto) dhe shkruani kushtin në dritaren që shfaqet. Në rastin tonë, ky do të jetë kufizimi i kërkesës:
dhe kufiri në vëllimin maksimal të depove:
Përveç kufizimeve të dukshme që lidhen me faktorët fizikë (kapaciteti i depove dhe mjeteve të transportit, buxheti dhe kufizimet kohore, etj.), ndonjëherë është e nevojshme të shtohen kufizime "të veçanta për Excel". Kështu, për shembull, Excel mund të organizojë lehtësisht që ju të "optimizoni" koston e dorëzimit duke ofruar transportin e mallrave nga dyqanet në magazinë - kostot do të bëhen negative, dmth ne do të bëjmë një fitim! 🙂
Për të parandaluar që kjo të ndodhë, është më mirë të lini kutinë e zgjedhjes të aktivizuar. Bëni variablat e pakufizuara jo-negative apo edhe ndonjëherë të regjistrojnë në mënyrë eksplicite momente të tilla në listën e kufizimeve.
Pas vendosjes së të gjithë parametrave të nevojshëm, dritarja duhet të duket si kjo:
Në listën rënëse Zgjidhni një metodë zgjidhjeje, ju duhet gjithashtu të zgjidhni metodën e duhur matematikore për zgjidhjen e një zgjedhjeje prej tre opsioneve:
- Metoda e thjeshtë është një metodë e thjeshtë dhe e shpejtë për zgjidhjen e problemeve lineare, gjegjësisht problemeve ku dalja është lineare e varur nga hyrja.
- Metoda e përgjithshme e gradientit të reduktuar (OGG) – për problemet jolineare, ku ka varësi komplekse jolineare midis të dhënave hyrëse dhe dalëse (për shembull, varësia e shitjeve nga kostot e reklamimit).
- Kërkimi evolucionar për një zgjidhje – një metodë relativisht e re optimizimi e bazuar në parimet e evolucionit biologjik (përshëndetje Darvin). Kjo metodë funksionon shumë herë më gjatë se dy të parat, por mund të zgjidhë pothuajse çdo problem (jolinear, diskret).
Detyra jonë është qartësisht lineare: dorëzuar 1 copë - shpenzuar 40 rubla, dorëzuar 2 copë - shpenzuar 80 rubla. etj., kështu që metoda simplex është zgjidhja më e mirë.
Tani që të dhënat për llogaritjen janë futur, shtypni butonin Gjej nje zgjidhje (Zgjidh)për të filluar optimizimin. Në raste të rënda me shumë ndryshime në qeliza dhe kufizime, gjetja e një zgjidhjeje mund të marrë shumë kohë (veçanërisht me metodën evolucionare), por detyra jonë për Excel nuk do të jetë problem - në disa momente do të marrim rezultatet e mëposhtme :
Kushtojini vëmendje se sa interesante u shpërndanë vëllimet e furnizimit midis dyqaneve, duke mos tejkaluar kapacitetin e magazinës tona dhe duke përmbushur të gjitha kërkesat për numrin e kërkuar të mallrave për çdo dyqan.
Nëse zgjidhja e gjetur na përshtatet, atëherë mund ta ruajmë atë ose të kthehemi në vlerat origjinale dhe të provojmë përsëri me parametra të tjerë. Ju gjithashtu mund të ruani kombinimin e zgjedhur të parametrave si skenar. Me kërkesë të përdoruesit, Excel mund të ndërtojë tre lloje Raportet mbi problemin që zgjidhet në fletë të veçanta: një raport mbi rezultatet, një raport mbi qëndrueshmërinë matematikore të zgjidhjes dhe një raport mbi kufijtë (kufizimet) e zgjidhjes, megjithatë, në shumicën e rasteve, ato janë me interes vetëm për specialistët .
Megjithatë, ka situata ku Excel nuk mund të gjejë një zgjidhje të përshtatshme. Është e mundur të simulohet një rast i tillë nëse tregojmë në shembullin tonë kërkesat e dyqaneve në sasi më të madhe se kapaciteti total i magazinës. Më pas, kur kryen një optimizim, Excel do të përpiqet t'i afrohet zgjidhjes sa më shumë që të jetë e mundur dhe më pas do të shfaqë një mesazh se zgjidhja nuk mund të gjendet. Megjithatë, edhe në këtë rast, ne kemi shumë informacione të dobishme - në veçanti, ne mund të shohim "lidhjet e dobëta" të proceseve tona të biznesit dhe të kuptojmë fushat për përmirësim.
Shembulli i konsideruar, natyrisht, është relativisht i thjeshtë, por lehtësisht shkallëzohet për të zgjidhur probleme shumë më komplekse. Për shembull:
- Optimizimi i shpërndarjes së burimeve financiare sipas zërit të shpenzimit në planin e biznesit ose buxhetin e projektit. Kufizimet, në këtë rast, do të jenë sasia e financimit dhe koha e projektit, dhe qëllimi i optimizimit është maksimizimi i fitimeve dhe minimizimi i kostove të projektit.
- Optimizimi i planifikimit të punonjësve me qëllim minimizimin e fondit të pagave të ndërmarrjes. Kufizime, në këtë rast, do të jenë dëshirat e çdo punonjësi sipas orarit të punësimit dhe kërkesave të tabelës së personelit.
- Optimizimi i investimeve investive – nevoja për të shpërndarë në mënyrë korrekte fondet ndërmjet disa bankave, letrave me vlerë ose aksioneve të ndërmarrjeve me qëllim që, sërish, të maksimizohen fitimet ose (nëse është më e rëndësishme) të minimizohen rreziqet.
Në çdo rast, shtesë Kërko Zgjidhje (Zgjidhës) është një mjet Excel shumë i fuqishëm dhe i bukur dhe i denjë për vëmendjen tuaj, pasi mund të ndihmojë në shumë situata të vështira me të cilat duhet të përballeni në biznesin modern.