Excel Solver: Mi ez és mikor érdemes használni?

Hasznosak az Excel táblázatkezelő eszközei, például a Goal Seek, amelyek egyetlen változót módosítanak, de sajnos az üzleti életben a legtöbb probléma nem ilyen egyszerű. Általában legalább két, néha több tucat változót tartalmazó képletekkel kell szembenéznie. Gyakran előfordul, hogy egy problémának több megoldása is van, és az Ön kihívása az, hogy az Excel segítségével megtalálja az optimális megoldást (vagyis azt, amely maximalizálja a profitot, minimalizálja a költségeket, vagy megfelel más kritériumoknak). Ezekhez a nagyobb kihívásokhoz izmosabb eszközre van szüksége. Az Excelnek csak a válasza van: Megoldó. Az Excel Solver egy kifinomult optimalizáló program, amely lehetővé teszi olyan összetett problémák megoldását, amelyek egyébként magas szintű matematikai elemzést igényelnének.

Az Excel Solver megértése

A Solver a Goal Seekhez hasonlóan iteratív módszert használ a számítások elvégzéséhez. Az iteráció használata azt jelenti, hogy az Excel Solver megpróbál egy megoldást, elemzi az eredményeket, megpróbál egy másik megoldást, és így tovább. Ez a ciklikus iteráció azonban nem csak találgatás a Solver részéről. Ez butaság lenne. Nem, az Excel Solver megvizsgálja, hogyan változnak az eredmények minden új iterációval, és néhány kifinomult matematikai folyamaton keresztül (amely szerencsére a háttérben történik, és figyelmen kívül hagyható) általában meg tudja mondani, milyen irányba kell a megoldás felé haladnia.

Az Excel Solver előnyei

Igen, a Goal Seek és a Solver is iteratív, de ettől még nem egyenlőek. Valójában az Excel Solver számos előnnyel jár a táblázatban:

• Az Excel Solver lehetővé teszi több állítható cella megadását. Összesen legfeljebb 200 állítható cellát használhat.
• Az Excel Solver lehetővé teszi, hogy megszorításokat állítson be az állítható cellákra. Például megmondhatja a Solvernek, hogy találjon olyan megoldást, amely nemcsak maximalizálja a profitot, hanem megfelel bizonyos feltételeknek is, például 20 és 30 százalék közötti bruttó árrés elérésére, vagy 100 000 dollár alatti kiadásokra. Ezek a feltételek a megoldás korlátainak mondják .
• Az Excel Solver nemcsak a kívánt eredményt keresi (a „célt” a Goal Seek-ben), hanem az optimálisat is. Például az optimális eredmény keresése azt jelentheti, hogy megtalálja a lehető legnagyobb vagy minimális megoldást.
• Összetett problémák esetén a Solver több megoldást is generálhat . Ezután elmentheti ezeket a különböző megoldásokat különböző forgatókönyvek szerint.

Mikor érdemes az Excel Solver-t használni?

Oké, lássuk rögtön: az Excel Solver egy olyan hatékony eszköz, amelyre a legtöbb Excel-felhasználónak nincs szüksége. Túlzás lenne például a Solver segítségével kiszámítani a nettó nyereséget rögzített bevételi és költségadatok mellett. Sok probléma azonban nem kíván kevesebbet, mint a Megoldó megközelítést. Ezek a problémák sok különböző területet és helyzetet fednek le, de mindegyikben a következő közös jellemzők vannak:

• Nekik van egy cél cella (más néven a megcélzott sejt ), amely egy képlet szeretné maximalizálni, minimalizálására, illetve beállítani egy bizonyos értéket. Ez a képlet lehet számítás, például teljes szállítási költség vagy nettó nyereség.
• Az objektív cellaképlet egy vagy több változó cellára (más néven ismeretlen vagy változó cellára ) hivatkozik. A Solver úgy állítja be ezeket a cellákat, hogy megtalálja az optimális megoldást az objektív cellaképlethez. Ezek a változó cellák olyan tételeket tartalmazhatnak, mint az eladott egységek, a szállítási költségek vagy a hirdetési költségek.
• Opcionálisan van egy vagy több kényszercella, amelynek meg kell felelnie bizonyos feltételeknek. Például megkövetelheti, hogy a hirdetés az összköltség 10 százalékánál kevesebb legyen, vagy az ügyfeleknek nyújtott kedvezmény 40 és 60 százalék közötti összeg legyen.

Például az alábbi képen egy munkalap adatmodell látható, amely az Excel Solverhez be van állítva. A modell két termék bevételét (az eladott egységek árát) és költségeit, az egyes termékek által termelt nyereséget és a teljes nyereséget mutatja. Az itt megválaszolandó kérdés a következő: Hány egységet kell eladni az egyes termékekből, hogy 0 dollár össznyereséget kapjunk? Ezt az üzleti életben fedezeti elemzésnek nevezik .

Ennek az adatmodellnek a célja a fedezeti pont megtalálása (ahol a teljes nyereség 0 USD).

Ez úgy hangzik, mint egy egyszerű célkeresési feladat, de ennek a modellnek van egy trükkös aspektusa: a változó költségek. Általában egy termék változó költsége az egységköltség szorzata az eladott egységek számával. Ha az A termék előállítása 10 dollárba kerül, és Ön 10 000 egységet ad el, akkor a termék változó költségei 100 000 dollárba kerülnek. A való világban azonban az ilyen költségek gyakran több termék között keverednek. Ha például közös reklámkampányt futtat két termékre, akkor ezeket a költségeket mindkét termék viseli. Ezért ez a modell azt feltételezi, hogy az egyik termék költségei a másik termék eladott egységeihez kapcsolódnak. Itt van például a felfújható darttábla (B8 cella) költségeinek kiszámításához használt képlet:

=B7 * B4 – C4

Más szóval, a felfújható dartstábla változó költségei egy dollárral csökkennek minden eladott Dog Polisher egység után. Ez utóbbi változó költségei hasonló képletet használnak (a C8 cellában):

=C7 * C4 – B4

A több termékhez kapcsolódó változó költségek miatt ez az adatmodell kívül esik azon, amit a Goal Seek tud, de a Solver megfelel a kihívásnak. Itt vannak a Solver által használt speciális cellák a modellben:

• Az objektív cella C14; ennek a képletnek a teljes nyeresége és a célmegoldása 0 (azaz a fedezeti pont).
• A változó cellák a B4 és C4, amelyek az egyes termékekhez eladott darabszámot tartalmazzák.
• A megszorításokhoz érdemes hozzátenni, hogy mindkét termék profitcellája (B12 és C12) is 0 legyen.

Az Excel Solver bővítmény betöltése

A bővítmény olyan szoftver, amely egy vagy több funkciót ad hozzá az Excelhez. A bővítmények telepítése olyan további Excel-szolgáltatásokat biztosít, amelyek alapértelmezés szerint nem érhetők el a menüszalagon. A mellékelt bővítményszoftver az Excel tartalmazza, de nem települ automatikusan az Excel telepítésekor . Számos bővítmény az Excel alapfelszereltsége, köztük a Solver, amely lehetővé teszi az optimalizálási problémák megoldását.

A mellékelt bővítményeket az Excel beállításai párbeszédpanel segítségével telepítheti; a Kiegészítők részben találja őket. Telepítésük után a bővítmények azonnal elérhetők. Általában a funkciójukhoz kapcsolódó lapon jelennek meg. Például a Solver megjelenik az Adatok lapon.

Az Excel Solver bővítmény betöltéséhez kövesse az alábbi lépéseket:

Válassza a Fájl → Beállítások menüpontot.
Megjelenik az Excel beállításai párbeszédpanel.

Válassza a Bővítmények lehetőséget.

A Kezelés listában válassza az Excel-bővítmények, majd az Ugrás lehetőséget.
Az Excel megjeleníti a Bővítmények párbeszédpanelt.

Jelölje be a Solver bővítmény jelölőnégyzetet

Kattintson az OK gombra.
Az Excel hozzáad egy Megoldó gombot az Adatok lap Elemzés csoportjához.

Vessen egy pillantást az Excel Solver használatára .