Contents [show]
Formulering van die probleem
Veronderstel dat die maatskappy waar jy werk drie pakhuise het, vanwaar die goedere gaan na vyf van jou winkels wat oor Moskou versprei is.
Elke winkel kan 'n sekere hoeveelheid goedere wat aan ons bekend is, verkoop. Elkeen van die pakhuise het 'n beperkte kapasiteit. Die taak is om rasioneel te kies uit watter pakhuis na watter winkels om die goedere af te lewer om sodoende die totale vervoerkoste te minimaliseer.
Voordat u met die optimalisering begin, sal dit nodig wees om 'n eenvoudige tabel op 'n Excel-blad saam te stel - ons wiskundige model wat die situasie beskryf:
Dit word verstaan dat:
- Die liggeel tabel (C4:G6) beskryf die koste om een item van elke pakhuis na elke winkel te stuur.
- Pers selle (C15:G14) beskryf die hoeveelheid goedere wat nodig is vir elke winkel om te verkoop.
- Rooi selle (J10:J13) vertoon die kapasiteit van elke pakhuis – die maksimum hoeveelheid goedere wat die pakhuis kan hou.
- Geel (C13:G13) en blou (H10:H13) selle is onderskeidelik die ry- en kolomsomme vir groen selle.
- Die totale versendingskoste (J18) word bereken as die som van die produkte van die aantal goedere en hul ooreenstemmende verskepingskoste – vir berekening word die funksie hier gebruik SUMPRODUCT (SOMPRODUK).
Dus, ons taak is verminder tot die keuse van optimale waardes van groen selle. En sodat die totale bedrag vir die lyn (blou selle) nie die kapasiteit van die pakhuis (rooi selle) oorskry nie, en terselfdertyd ontvang elke winkel die hoeveelheid goedere wat dit moet verkoop (die bedrag vir elke winkel in die geel selle moet so na as moontlik aan die vereistes wees – pers selle).
Oplossing
In wiskunde is sulke probleme van die keuse van die optimale verspreiding van hulpbronne al lank geformuleer en beskryf. En, natuurlik, maniere om dit op te los is lankal ontwikkel nie deur stomp opsomming (wat baie lank is), maar in 'n baie klein aantal iterasies. Excel voorsien die gebruiker van sulke funksionaliteit deur 'n byvoeging te gebruik. Soek oplossings (Oplosmiddel) vanaf die blad data (Datum):
As op die blad data jou Excel het nie so 'n opdrag nie – dit is oukei – dit beteken dat die byvoeging eenvoudig nog nie gekoppel is nie. Maak oop om dit te aktiveer FileKies dan Grense - Byvoegings - Oor (Opsies - Byvoegings - Gaan na). Merk die blokkie langs die lyn wat ons benodig in die venster wat oopmaak Soek oplossings (Oplosmiddel).
Kom ons voer die byvoeging uit:
In hierdie venster moet u die volgende parameters instel:
- Optimaliseer teikenfunksie (Stel tgeld sel) – hier is dit nodig om die finale hoofdoel van ons optimalisering aan te dui, dit wil sê pienk boks met die totale verskepingskoste (J18). Die teikensel kan geminimaliseer word (as dit uitgawes is, soos in ons geval), gemaksimeer word (as dit byvoorbeeld wins is) of probeer om dit tot 'n gegewe waarde te bring (pas byvoorbeeld presies in die toegekende begroting).
- Verandering van veranderlike selle (By veranderende selle) - hier dui ons die groen selle aan (C10: G12), deur die waardes te verander waarvan ons ons resultaat wil bereik - die minimum afleweringskoste.
- In ooreenstemming met beperkings (Onderwerp om die Beperkings) – 'n lys van beperkings wat in ag geneem moet word wanneer geoptimaliseer word. Om beperkings by die lys te voeg, klik die knoppie Voeg (Voeg by) en voer die toestand in in die venster wat verskyn. In ons geval sal dit die vraagbeperking wees:
en beperking op die maksimum volume pakhuise:
Benewens die ooglopende beperkings wat verband hou met fisiese faktore (kapasiteit van pakhuise en vervoermiddels, begroting en tydsbeperkings, ens.), is dit soms nodig om beperkings "spesiaal vir Excel" by te voeg. So byvoorbeeld kan Excel maklik reël dat jy die afleweringskoste “optimaliseer” deur aan te bied om goedere van winkels terug na die pakhuis te vervoer – die koste sal negatief word, maw ons sal wins maak! 🙂
Om te voorkom dat dit gebeur, is dit die beste om die merkblokkie geaktiveer te laat. Maak onbeperkte veranderlikes nie-negatief of selfs soms uitdruklik sulke oomblikke in die lys van beperkings registreer.
Nadat al die nodige parameters gestel is, moet die venster soos volg lyk:
In die aftreklys Kies 'n oplossingsmetode moet u ook die toepaslike wiskundige metode kies om 'n keuse van drie opsies op te los:
- Eenvoudige metode is 'n eenvoudige en vinnige metode om lineêre probleme op te los, dit wil sê probleme waar die uitset lineêr afhanklik is van die inset.
- Algemene afgegradeerde gradiëntmetode (OGG) – vir nie-lineêre probleme, waar daar komplekse nie-lineêre afhanklikhede tussen inset- en uitsetdata is (byvoorbeeld die afhanklikheid van verkope van advertensiekoste).
- Evolusionêre soeke na 'n oplossing – 'n relatief nuwe optimeringsmetode gebaseer op die beginsels van biologiese evolusie (hallo Darwin). Hierdie metode werk baie keer langer as die eerste twee, maar kan byna enige probleem oplos (nie-lineêr, diskreet).
Ons taak is duidelik lineêr: gelewer 1 stuk - spandeer 40 roebels, afgelewer 2 stukke - spandeer 80 roebels. ens., so die simpleksmetode is die beste keuse.
Noudat die data vir die berekening ingevoer is, druk die knoppie Vind 'n oplossing (Los op)optimering te begin. In ernstige gevalle met baie veranderende selle en beperkings, kan dit lank neem om 'n oplossing te vind (veral met die evolusionêre metode), maar ons taak vir Excel sal nie 'n probleem wees nie - oor 'n paar oomblikke sal ons die volgende resultate kry :
Gee aandag aan hoe interessant die voorraadvolumes onder die winkels versprei is, terwyl dit nie die kapasiteit van ons pakhuise oorskry nie en aan alle versoeke vir die vereiste aantal goedere vir elke winkel voldoen word.
As die gevind oplossing ons pas, dan kan ons dit stoor, of terugrol na die oorspronklike waardes en probeer weer met ander parameters. Jy kan ook die geselekteerde kombinasie van parameters stoor as scenario. Op versoek van die gebruiker kan Excel drie tipes bou Berigte oor die probleem wat op aparte velle opgelos word: 'n verslag oor die resultate, 'n verslag oor die wiskundige stabiliteit van die oplossing en 'n verslag oor die limiete (beperkings) van die oplossing, maar in die meeste gevalle is dit slegs van belang vir spesialiste .
Daar is egter situasies waar Excel nie 'n geskikte oplossing kan vind nie. Dit is moontlik om so 'n geval te simuleer as ons in ons voorbeeld die vereistes van die winkels aandui in die hoeveelheid groter as die totale kapasiteit van die pakhuise. Dan, wanneer 'n optimering uitgevoer word, sal Excel probeer om so na as moontlik aan die oplossing te kom, en dan 'n boodskap vertoon dat die oplossing nie gevind kan word nie. Nietemin, selfs in hierdie geval, het ons baie nuttige inligting – ons kan veral die “swak skakels” van ons besigheidsprosesse sien en die areas vir verbetering verstaan.
Die oorwoë voorbeeld is natuurlik relatief eenvoudig, maar kan maklik afgeskaal word om baie meer komplekse probleme op te los. Byvoorbeeld:
- Optimalisering van die verspreiding van finansiële hulpbronne per uitgawe-item in die sakeplan of begroting van die projek. Die beperkings, in hierdie geval, sal die bedrag van finansiering en die tydsberekening van die projek wees, en die doel van optimalisering is om wins te maksimeer en projekkoste te minimaliseer.
- Werknemerskedulering optimalisering ten einde die loonfonds van die onderneming te minimaliseer. Beperkings, in hierdie geval, sal die wense van elke werknemer wees volgens die indiensnemingskedule en die vereistes van die personeeltabel.
- Optimalisering van beleggingsbeleggings – die behoefte om fondse korrek tussen verskeie banke, sekuriteite of aandele van ondernemings te verdeel om weereens winste te maksimeer of (indien belangriker) risiko's te minimaliseer.
In elk geval, byvoeging Soek oplossings (Oplosser) is 'n baie kragtige en pragtige Excel-instrument en verdien u aandag, aangesien dit kan help in baie moeilike situasies wat u in moderne besigheid moet trotseer.