Mi az Excel Solver?

2024 Szerző: Abigail Brown | [email protected]. Utoljára módosítva: 2023-12-17 06:50

Az Excel Solver bővítmény matematikai optimalizálást végez. Ezt általában összetett modellek adatokhoz illesztésére vagy iteratív problémák megoldására használják. Előfordulhat például, hogy egy egyenlet segítségével egy görbét egyes adatpontokon keresztül kíván illeszteni. A Solver megtalálja az egyenletben azokat az állandókat, amelyek a legjobban illeszkednek az adatokhoz. Egy másik alkalmazás az, ahol nehéz a modellt úgy átrendezni, hogy a kívánt kimenet egy egyenlet tárgyává váljon.

Hol van a Solver az Excelben?

A Solver bővítmény benne van az Excelben, de nem mindig az alapértelmezett telepítés részeként töltődik be. Annak ellenőrzéséhez, hogy be van-e töltve, válassza a DATA lapot, és keresse meg a Solver ikont az Analysis részben..

Ha nem találja a Solvert az ADATOK lapon, akkor be kell töltenie a bővítményt:

1. Válassza ki a FILE lapot, majd válassza az Options lehetőséget.

   

2. A Options párbeszédpanelen válassza ki a Add-Ins lehetőséget a bal oldali fülek közül.

   

3. Az ablak alján válassza ki az Excel-bővítmények elemet a Manage legördülő menüből, és válassza a Ugrás…

   

4. Jelölje be a Solver Add-in melletti jelölőnégyzetet, és válassza az OK lehetőséget.

   

5. A Solver parancsnak meg kell jelennie a DATA lapon. Készen áll a Solver használatára.

   

A Solver használata az Excelben

Kezdjük egy egyszerű példával, hogy megértsük, mit csinál a Solver. Képzeljük el, hogy tudni akarjuk, milyen sugarú lesz egy 50 négyzetegység területű kör. Ismerjük a kör területének egyenletét (A=pi r²). Természetesen átrendezhetjük ezt az egyenletet, hogy megadjuk az adott területhez szükséges sugarat, de a példa kedvéért tegyünk úgy, mintha nem tudnánk, hogyan kell ezt megtenni.

Hozzon létre egy táblázatot a B1 sugárral, és számítsa ki a területet B2 a =pi()B1^2.

Manuálisan módosíthatjuk a B1 értéket, amíg a B2 olyan értéket nem mutat, amely elég közel van az 50-hez. Attól függően, hogy mennyire pontosak vagyunk ez egy gyakorlati megközelítés lehet. Ha azonban nagyon pontosnak kell lennünk, akkor a szükséges beállítások elvégzése hosszú ideig tart. Valójában ezt csinálja a Solver. Módosítja az értékeket bizonyos cellákban, és ellenőrzi az értéket a célcellában:

1. Válassza a DATA fület és a Solver lehetőséget a Solver Parameters párbeszédpanel betöltéséhez

2. Állítsa be az Objective cellát Területként, B2. Ez az az érték, amelyet a rendszer ellenőrizni fog, módosítva a többi cellát, amíg ez el nem éri a megfelelő értéket.

   

3. Válassza ki a Value of: gombot, és állítsa be az 50-es értéket. Ez az az érték, amelyet B2-nek el kell érnie.

   

4. A Változó cellák módosításával: írja be a sugarat tartalmazó cellát, B1.

   

5. Hagyja meg a többi beállítást alapértelmezés szerint, és válassza a Solve lehetőséget. Az optimalizálás megtörténik, a B1 értékét addig állítja, amíg B2 50 nem lesz, és megjelenik a Solver Results párbeszédablak.

   

6. A megoldás megtartásához válassza az OK lehetőséget.

   

Ez az egyszerű példa bemutatta a megoldó működését. Ebben az esetben más módokon is könnyebben megkaphattuk volna a megoldást. A következőkben néhány olyan példát tekintünk meg, ahol a Solver olyan megoldásokat ad, amelyeket más módon nehéz lenne megtalálni.

Összetett modell illesztése az Excel Solver bővítmény használatával

Az Excel beépített funkcióval rendelkezik a lineáris regresszió végrehajtására, amely egyenes vonalat illeszt egy adathalmazba. Sok gyakori nemlineáris függvény linearizálható, ami azt jelenti, hogy a lineáris regresszió használható függvények, például exponenciális illesztésére. Bonyolultabb funkciókhoz a Solver használható a „legkisebb négyzetek minimalizálására”. Ebben a példában megfontoljuk egy ax^b+cx^d formátumú egyenlet illesztését az alábbi adatokhoz.

Ez a következő lépésekből áll:

1. Rendezd el az adatkészletet az A oszlopban lévő x értékekkel és a B oszlopban az y-értékekkel.
2. Hozza létre a 4 együttható értéket (a, b, c és d) valahol a táblázatban, ezek tetszőleges kezdőértékek adhatók.

3. Hozzon létre egy oszlopot illesztett Y értékekből az ax^b+cx^d formátumú egyenlet segítségével, amely a 2. lépésben létrehozott együtthatókra és az A oszlopban lévő x értékekre hivatkozik. Vegye figyelembe, hogy a képlet lemásolásához az oszlopban az együtthatókra való hivatkozásoknak abszolútnak, míg az x értékekre való hivatkozásoknak relatívnak kell lenniük.

   

4. Bár nem feltétlenül szükséges, vizuális jelzést kaphat arról, hogy az egyenlet mennyire jól illeszkedik, ha mindkét y oszlopot ábrázolja az x értékek függvényében egyetlen XY szóródiagramon. Érdemes jelölőket használni az eredeti adatpontokhoz, mivel ezek diszkrét értékek zajjal, az illesztett egyenlethez pedig egy sort kell használni.

   

5. Következő módra van szükségünk az adatok és az illesztett egyenletünk közötti különbség számszerűsítésére. Ennek szokásos módja a különbségek négyzetes összegének kiszámítása. A harmadik oszlopban minden sorban az Y eredeti adatértékét kivonjuk az illesztett egyenlet értékéből, és az eredményt négyzetre emeljük. Tehát a D2-ban az értéket a következőképpen adja meg: =(C2-B2)^2 A rendszer ezután kiszámítja ezeknek a négyzetes értékeknek az összegét. Mivel az értékek négyzetesek, csak pozitívak lehetnek.

   

6. Most készen áll az optimalizálás elvégzésére a Solver segítségével. Négy együtthatót kell módosítani (a, b, c és d). Ezenkívül egyetlen célértéket kell minimalizálni, a különbségek négyzetének összegét. Indítsa el a megoldót a fentiek szerint, és állítsa be a megoldó paramétereit, hogy ezekre az értékekre hivatkozzanak, az alábbiak szerint.

   

7. Távolítsa el a jelölést a Korlátlan változók nem negatívvá tételeopcióból, ezzel az összes együttható pozitív értéket vesz fel.

   

8. Válassza ki a Solve lehetőséget, és tekintse át az eredményeket. A diagram frissül, jól jelzi az illeszkedés jóságát. Ha a megoldó az első próbálkozásra nem ad megfelelő illeszkedést, megpróbálhatja újra futtatni. Ha az illeszkedés javult, próbálja meg a jelenlegi értékek alapján feloldani. Ellenkező esetben megpróbálhatja manuálisan javítani az illeszkedést a megoldás előtt.

   

9. Ha a megfelelő illeszkedést elérte, kiléphet a megoldóból.

Modell iteratív megoldása

Néha van egy viszonylag egyszerű egyenlet, amely valamilyen bemeneten keresztül ad egy kimenetet. Amikor azonban megpróbáljuk megfordítani a problémát, nem találhatunk egyszerű megoldást. Például a jármű által fogyasztott teljesítmény hozzávetőlegesen a következőképpen adódik: P=av + bv^3, ahol v a sebesség, a a gördülési ellenállás együtthatója és b a gördülési ellenállás együtthatója aerodinamikai légellenállás. Bár ez meglehetősen egyszerű egyenlet, nem könnyű átrendezni, hogy egyenletet adjon a jármű által elért sebességről egy adott teljesítményfelvétel mellett. Használhatjuk azonban a Solvert, hogy iteratív módon meghatározzuk ezt a sebességet. Például keresse meg a 740 W bemeneti teljesítmény mellett elért sebességet.

1. Készítsen egy egyszerű táblázatot a sebességgel, az a és b együtthatókkal, valamint az ezekből számított teljesítménygel.

   

2. Indítsa el a Solver-t, és adja meg a B5 hatványt célként. Állítson be egy 740 célértéket, és válassza ki a sebességet, B2, mint a módosítandó változócellákat. A megoldás elindításához válassza a solve lehetőséget.

   

3. A megoldó addig állítja a sebesség értékét, amíg a hatvány nagyon közel nem ér 740-hez, biztosítva a szükséges sebességet.

   

4. A modellek ilyen módon történő megoldása gyakran gyorsabb és kevésbé hibás, mint az összetett modellek invertálása.

A megoldóban elérhető különféle opciók megértése meglehetősen nehéz lehet. Ha nehézségei vannak egy ésszerű megoldás megtalálásával, akkor gyakran hasznos peremfeltételeket alkalmazni a változtatható cellákra. Ezek határértékek, amelyeken túl nem szabad módosítani. Például az előző példában a sebesség nem lehet kisebb nullánál, és egy felső korlátot is be lehet állítani. Ez az a sebesség, aminél biztos lehetsz benne, hogy a jármű nem tud gyorsabban haladni. Ha be tudja állítani a korlátokat a változtatható változó cellák számára, akkor más fejlettebb opciók is jobban működnek, mint például a multistart. Ez számos különböző megoldást fog futtatni, kezdve a változók különböző kezdeti értékeivel.

A megoldási módszer kiválasztása is nehéz lehet. A Simplex LP csak lineáris modellekhez alkalmas, ha a probléma nem lineáris, akkor meghiúsul egy üzenet, hogy ez a feltétel nem teljesül. A másik két módszer egyaránt alkalmas nemlineáris módszerekre. A GRG Nonlinear a leggyorsabb, de megoldása nagymértékben függhet a kezdeti indulási feltételektől. Megvan az a rugalmassága, hogy nincs szükség változókra a határok beállításához. Az Evolúciós megoldó gyakran a legmegbízhatóbb, de megköveteli, hogy minden változónak legyen felső és alsó határa is, amit nehéz lehet előre kidolgozni.

Az Excel Solver bővítmény egy nagyon hatékony eszköz, amely számos gyakorlati problémára alkalmazható. Az Excel képességeinek teljes körű eléréséhez próbálja meg a Solver-t Excel-makróval kombinálni.