A probléma megfogalmazása
Tegyük fel, hogy a cég, ahol dolgozik, három raktárral rendelkezik, ahonnan az áruk öt Moszkvában szétszórt üzletébe kerülnek.
Minden üzlet egy bizonyos, általunk ismert árumennyiséget képes eladni. A raktárak mindegyike korlátozott kapacitással rendelkezik. A feladat az, hogy a teljes szállítási költség minimalizálása érdekében racionálisan válasszuk ki, hogy melyik raktárból melyik üzletekbe szállítsuk az árut.
Az optimalizálás megkezdése előtt egy egyszerű táblázatot kell összeállítani egy Excel lapon – a helyzetet leíró matematikai modellünk:
Érthető, hogy:
- A világossárga táblázat (C4:G6) leírja, hogy mennyi költséget kell raktáronként minden üzletbe szállítani.
- A lila cellák (C15:G14) az egyes üzletekhez szükséges áruk mennyiségét írják le.
- A vörös cellák (J10:J13) az egyes raktárak kapacitását jelenítik meg – a raktárban elhelyezhető áruk maximális mennyiségét.
- A sárga (C13:G13) és kék (H10:H13) cellák a zöld cellák sor- és oszlopösszegei.
- A teljes szállítási költség (J18) az áruk darabszámának és a hozzájuk tartozó szállítási költségeknek az összegeként kerül kiszámításra – a számításhoz itt a függvényt használjuk SUMPRODUCT (ÖSSZES TERMÉK).
Így a feladatunk a zöld sejtek optimális értékeinek kiválasztására korlátozódik. És hogy a sor teljes összege (kék cellák) ne haladja meg a raktár kapacitását (vörös cellák), és ugyanakkor minden üzlet megkapja az eladni kívánt árumennyiséget (az egyes üzletek mennyiségét a a sárga celláknak a lehető legközelebb kell lenniük a követelményekhez – lila sejtek).
Megoldás
A matematikában az erőforrások optimális elosztásának megválasztásának ilyen jellegű problémáit már régóta megfogalmazták és leírták. És persze megoldásuk módjait régóta nem tompa felsorolással (ami nagyon hosszú), hanem nagyon kis számú iterációval dolgozták ki. Az Excel egy bővítmény segítségével biztosítja a felhasználó számára ezeket a funkciókat. Keresési megoldások (Megoldó) a lapról dátum (Dátum):
Ha a lapon dátum az Excelben nincs ilyen parancs – ez rendben van – ez azt jelenti, hogy a bővítmény egyszerűen még nincs csatlakoztatva. Az aktiváláshoz nyissa meg filé, Majd válassza ki paraméterek - Bővítmények - Rólunk
(Opciók – Bővítmények – Ugrás). A megnyíló ablakban jelölje be a szükséges sor melletti négyzetet Keresési megoldások (Megoldó).
Futtassuk a kiegészítőt:
Ebben az ablakban a következő paramétereket kell beállítani:
- Optimalizálja a célfunkciót (Állítsa be a tpénz sejt) – itt kell feltüntetni optimalizálásunk végső fő célját, azaz rózsaszín dobozt a teljes szállítási költséggel (J18). A célcellát lehet minimalizálni (ha kiadásról van szó, mint esetünkben), maximalizálni (ha pl. profitról van szó) vagy megpróbálni egy adott értékre hozni (például pontosan beleilleszteni a kiosztott költségvetésbe).
- Változó cellák megváltoztatása (By változó sejtek) – itt jelöljük azokat a zöld cellákat (C10: G12), amelyek értékének változtatásával szeretnénk elérni az eredményt – a minimális szállítási költséget.
- A korlátozásokkal összhangban (Tárgy nak nek a Korlátok) – az optimalizálás során figyelembe veendő korlátozások listája. Ha korlátozásokat szeretne hozzáadni a listához, kattintson a gombra hozzáad (Hozzáadás) és a megjelenő ablakban írja be a feltételt. Esetünkben ez lesz a keresleti korlát:
és a raktárak maximális térfogatának korlátozása:
A fizikai tényezőkkel kapcsolatos nyilvánvaló korlátokon (raktárak és szállítóeszközök kapacitása, költségvetési és időkorlátok stb.) túlmenően időnként szükség van „Excelre vonatkozó speciális” korlátozásokra. Így például az Excel könnyen elintézheti, hogy „optimalizálja” a szállítási költséget azzal, hogy felajánlja az áruk visszaszállítását az üzletekből a raktárba – a költségek negatívak lesznek, azaz profitot termelünk! 🙂
Ennek elkerülése érdekében a legjobb, ha engedélyezve hagyja a jelölőnégyzetet. Tegye a korlátlan számú változót nem negatívvá vagy akár néha kifejezetten rögzíti az ilyen pillanatokat a korlátozások listáján.
Az összes szükséges paraméter beállítása után az ablaknak így kell kinéznie:
A Megoldási módszer kiválasztása legördülő listában ezenkívül ki kell választania a megfelelő matematikai módszert három lehetőség közül a megoldáshoz:
- Simplex módszer egy egyszerű és gyors módszer lineáris problémák megoldására, azaz olyan problémák megoldására, ahol a kimenet lineárisan függ a bemenettől.
- Általános leminősített gradiens módszer (OGG) – nemlineáris problémák esetén, ahol a bemeneti és kimeneti adatok között összetett nemlineáris függőségek vannak (például az eladások hirdetési költségektől való függése).
- Evolúciós megoldáskeresés – egy viszonylag új optimalizálási módszer a biológiai evolúció elvein (hello Darwin). Ez a módszer többszörösen tovább működik, mint az első kettő, de szinte bármilyen problémát megoldhat (nemlineáris, diszkrét).
Feladatunk egyértelműen lineáris: 1 db leszállított – költött 40 rubelt, leszállított 2 db – elköltött 80 rubelt. stb., így a szimplex módszer a legjobb választás.
Miután megadta a számításhoz szükséges adatokat, nyomja meg a gombot Megoldást talál (megoldani)az optimalizálás megkezdéséhez. Súlyos esetekben, sok cellaváltással és megszorítással, a megoldás keresése sokáig tarthat (főleg az evolúciós módszerrel), de az Excel-es feladatunk nem lesz probléma – pár pillanat múlva a következő eredményeket kapjuk :
Ügyeljen arra, hogy a kínálati mennyiségek milyen érdekesen oszlanak meg az üzletek között, miközben nem haladták meg raktáraink kapacitását, és az egyes üzletek által igényelt áruk mennyiségét kielégítették.
Ha a talált megoldás megfelel nekünk, akkor elmenthetjük, vagy visszagörgethetjük az eredeti értékeket és próbálkozhatunk más paraméterekkel. A kiválasztott paraméterkombinációt más néven is elmentheti Forgatókönyv. A felhasználó kérésére az Excel három típust készíthet Jelentések a megoldandó feladatról külön lapokon: jelentés az eredményekről, jelentés a megoldás matematikai stabilitásáról és jelentés a megoldás korlátairól (korlátairól), azonban ezek a legtöbb esetben csak a szakemberek érdeklődésére tarthatnak számot. .
Vannak azonban olyan helyzetek, amikor az Excel nem talál megfelelő megoldást. Egy ilyen esetet szimulálhatunk, ha példánkban a raktárak összkapacitásánál nagyobb mennyiségben tüntetjük fel az üzletek igényeit. Ezután az optimalizálás végrehajtásakor az Excel megpróbál a lehető legközelebb kerülni a megoldáshoz, majd megjelenít egy üzenetet, hogy a megoldás nem található. Ennek ellenére még ebben az esetben is sok hasznos információval rendelkezünk – különösen láthatjuk üzleti folyamataink „gyenge láncszemeit”, és megértjük a fejlesztendő területeket.
A vizsgált példa természetesen viszonylag egyszerű, de könnyen skálázható sokkal összetettebb problémák megoldására. Például:
- A pénzügyi források elosztásának optimalizálása kiadási tételenként a projekt üzleti tervében vagy költségvetésében. A korlátozás ebben az esetben a finanszírozás összege és a projekt ütemezése, az optimalizálás célja pedig a profit maximalizálása és a projekt költségeinek minimalizálása.
- Munkavállalói ütemezés optimalizálása a vállalkozás béralapjának minimalizálása érdekében. A korlátozások ebben az esetben az egyes alkalmazottak kívánságai lesznek a foglalkoztatási ütemtervnek és a létszámtáblázat követelményeinek megfelelően.
- Befektetési befektetések optimalizálása – a pénzeszközök helyes felosztásának szükségessége több bank, értékpapír vagy vállalkozásrész között a profit maximalizálása vagy (ha ez még fontosabb) a kockázatok minimalizálása érdekében.
Mindenesetre kiegészítő Keresési megoldások (Megoldó) egy nagyon hatékony és gyönyörű Excel-eszköz, és figyelmet érdemel, mivel számos nehéz helyzetben segíthet, amelyekkel a modern üzleti életben szembe kell néznie.