Doplnok Excel Solver vykonáva matematickú optimalizáciu. Toto sa zvyčajne používa na prispôsobenie zložitých modelov údajom alebo na nájdenie iteračných riešení problémov. Môžete napríklad chcieť preložiť krivku cez niektoré údajové body pomocou rovnice. Riešiteľ dokáže v rovnici nájsť konštanty, ktoré najlepšie zodpovedajú údajom. Ďalšia aplikácia je tam, kde je ťažké zmeniť usporiadanie modelu tak, aby sa požadovaný výstup stal predmetom rovnice.
Kde je Solver v Exceli?
Doplnok Riešiteľ je súčasťou Excelu, ale nie vždy sa načítava ako súčasť predvolenej inštalácie. Ak chcete skontrolovať, či je načítaný, vyberte kartu DATA a vyhľadajte ikonu Solver v sekcii Analýza.
Ak nemôžete nájsť Riešiteľ na karte DATA, budete musieť načítať doplnok:
-
Vyberte kartu FILE a potom vyberte Options.
Image -
V dialógovom okne Options vyberte z kariet na ľavej strane Add-Ins.
Image -
V spodnej časti okna vyberte Excel Add-ins z rozbaľovacej ponuky Manage a vyberte Prejsť…
Image -
Začiarknite políčko vedľa položky Doplnok Solver a vyberte OK.
Image -
Príkaz Solver by sa teraz mal objaviť na karte DATA. Ste pripravení použiť Solver.
Image
Používanie Riešiteľa v Exceli
Začnime jednoduchým príkladom, aby sme pochopili, čo Riešiteľ robí. Predstavte si, že chceme vedieť, aký polomer dá kružnica s plochou 50 štvorcových jednotiek. Poznáme rovnicu pre obsah kruhu (A=pi r2). Túto rovnicu by sme, samozrejme, mohli preusporiadať, aby sme získali polomer požadovaný pre danú oblasť, ale pre príklad predstierajme, že nevieme, ako to urobiť.
Vytvorte tabuľku s polomerom v B1 a vypočítajte plochu v B2 pomocou rovnice =pi()B1^2.
Hodnotu v B1 by sme mohli upraviť ručne, kým B2 neukáže hodnotu, ktorá je dostatočne blízka 50. V závislosti od toho, ako presne je to potrebné, môže to byť praktický prístup. Ak však potrebujeme byť veľmi presní, vykonanie požadovaných úprav bude trvať dlho. V skutočnosti to je v podstate to, čo Riešiteľ robí. Vykonáva úpravy hodnôt v určitých bunkách a kontroluje hodnotu v cieľovej bunke:
- Vyberte kartu DATA a Solver na načítanie dialógového okna Parametre riešiteľa
-
Nastaviť cieľ bunku na oblasť, B2. Toto je hodnota, ktorá sa bude kontrolovať, pričom sa budú upravovať ďalšie bunky, kým táto nedosiahne správnu hodnotu.
Image -
Vyberte tlačidlo pre Hodnota: a nastavte hodnotu 50. Toto je hodnota, ktorú by mal dosiahnuť B2.
Image -
Do poľa s názvom Zmenou buniek premenných: zadajte bunku obsahujúcu polomer, B1.
Image -
Ponechajte ostatné možnosti tak, ako sú predvolene, a vyberte Solve. Optimalizácia sa vykoná, hodnota B1 sa upraví, kým B2 nebude 50 a zobrazí sa dialóg Výsledky riešiteľa.
Image -
Ak chcete zachovať riešenie, vyberte OK.
Image
Tento jednoduchý príklad ukázal, ako funguje riešiteľ. V tomto prípade by sme mohli ľahšie získať riešenie inými spôsobmi. Ďalej sa pozrieme na niekoľko príkladov, kde Solver poskytuje riešenia, ktoré by bolo ťažké nájsť iným spôsobom.
Nastavenie zložitého modelu pomocou doplnku Excel Solver
Excel má vstavanú funkciu na vykonanie lineárnej regresie, preloženie priamky cez súbor údajov. Mnohé bežné nelineárne funkcie možno linearizovať, čo znamená, že lineárnu regresiu možno použiť na prispôsobenie funkcií, ako sú exponenciály. Pre komplexnejšie funkcie možno Riešiteľ použiť na vykonanie „minimalizácie najmenších štvorcov“. V tomto príklade zvážime prispôsobenie rovnice v tvare ax^b+cx^d k údajom uvedeným nižšie.
To zahŕňa nasledujúce kroky:
- Usporiadajte množinu údajov s hodnotami x v stĺpci A a hodnotami y v stĺpci B.
- Vytvorte 4 hodnoty koeficientov (a, b, c a d) niekde v tabuľke, môžete im zadať ľubovoľné počiatočné hodnoty.
-
Vytvorte stĺpec prispôsobených hodnôt Y pomocou rovnice v tvare ax^b+cx^d, ktorá odkazuje na koeficienty vytvorené v kroku 2 a na hodnoty x v stĺpci A. Všimnite si, že ak chcete vzorec skopírovať v stĺpci, odkazy na koeficienty musia byť absolútne, zatiaľ čo odkazy na hodnoty x musia byť relatívne.
Image -
Hoci to nie je nevyhnutné, môžete získať vizuálnu indikáciu o tom, ako dobre zodpovedá rovnica, vynesením oboch stĺpcov y proti hodnotám x na jednom bodovom grafe XY. Je zmysluplné použiť značky pre pôvodné dátové body, pretože ide o diskrétne hodnoty so šumom, a použiť čiaru pre prispôsobenú rovnicu.
Image -
Ďalej potrebujeme spôsob, ako kvantifikovať rozdiel medzi údajmi a našou prispôsobenou rovnicou. Štandardným spôsobom, ako to urobiť, je vypočítať súčet druhých mocnín rozdielov. V treťom stĺpci sa pre každý riadok pôvodná hodnota údajov pre Y odpočíta od hodnoty preloženej rovnice a výsledok sa umocní na druhú. Takže v D2 je hodnota daná ako =(C2-B2)^2 Potom sa vypočíta súčet všetkých týchto kvadratických hodnôt. Keďže hodnoty sú umocnené na druhú, môžu byť iba kladné.
Image -
Teraz ste pripravení vykonať optimalizáciu pomocou Riešiteľa. Existujú štyri koeficienty, ktoré je potrebné upraviť (a, b, c a d). Máte tiež jedinú objektívnu hodnotu, ktorú treba minimalizovať, súčet druhých mocnín rozdielov. Spustite riešič, ako je uvedené vyššie, a nastavte parametre riešiča tak, aby odkazovali na tieto hodnoty, ako je uvedené nižšie.
Image -
Zrušte začiarknutie možnosti Nastavte neobmedzené premenné ako nezáporné, tým by všetky koeficienty nadobudli kladné hodnoty.
Image -
Vyberte Solve a skontrolujte výsledky. Tabuľka sa aktualizuje a poskytuje dobrý prehľad o vhodnosti. Ak riešiteľ na prvý pokus nepasuje dobre, môžete ho skúsiť spustiť znova. Ak sa prispôsobenie zlepšilo, skúste to vyriešiť z aktuálnych hodnôt. V opačnom prípade sa môžete pokúsiť manuálne vylepšiť prispôsobenie pred vyriešením problému.
Image - Po získaní dobrej zhody môžete ukončiť riešiteľ.
Riešenie modelu iteratívne
Niekedy existuje relatívne jednoduchá rovnica, ktorá dáva výstup z hľadiska nejakého vstupu. Keď sa však snažíme problém obrátiť, nie je možné nájsť jednoduché riešenie. Napríklad výkon spotrebovaný vozidlom je približne daný P=av + bv^3 kde v je rýchlosť, a je koeficient valivého odporu a b je koeficient pre aerodynamický odpor. Aj keď je to celkom jednoduchá rovnica, nie je ľahké ju prestaviť tak, aby sa dala rovnica rýchlosti, ktorú vozidlo dosiahne pri danom príkone. Môžeme však použiť Solver na iteratívne nájdenie tejto rýchlosti. Nájdite napríklad rýchlosť dosiahnutú s príkonom 740 W.
-
Vytvorte si jednoduchú tabuľku s rýchlosťou, koeficientmi aab a z nich vypočítaným výkonom.
Image -
Spustite Riešiteľa a zadajte silu B5 ako cieľ. Nastavte cieľovú hodnotu 740 a vyberte rýchlosť, B2, ako bunky premennej, ktoré chcete zmeniť. Ak chcete spustiť riešenie, vyberte solve.
Image -
Riešiteľ nastavuje hodnotu rýchlosti, kým sa výkon veľmi nepribližuje k 740, čím poskytuje rýchlosť, ktorú požadujeme.
Image - Riešenie modelov týmto spôsobom môže byť často rýchlejšie a menej náchylné na chyby ako invertovanie zložitých modelov.
Porozumieť rôznym možnostiam dostupným v riešiči môže byť dosť ťažké. Ak máte problémy so získaním rozumného riešenia, potom je často užitočné použiť na vymeniteľné bunky okrajové podmienky. Toto sú hraničné hodnoty, za ktoré by sa nemali upravovať. Napríklad v predchádzajúcom príklade by rýchlosť nemala byť menšia ako nula a tiež by bolo možné nastaviť hornú hranicu. Toto by bola rýchlosť, pri ktorej ste si istí, že vozidlo nemôže ísť rýchlejšie. Ak ste schopní nastaviť hranice pre vymeniteľné premenné bunky, potom budú lepšie fungovať aj ďalšie pokročilejšie možnosti, ako napríklad multistart. Tým sa spustí množstvo rôznych riešení, počnúc rôznymi počiatočnými hodnotami premenných.
Výber metódy riešenia môže byť tiež náročný. Simplex LP je vhodný len pre lineárne modely, ak problém nie je lineárny, zlyhá so správou, že táto podmienka nebola splnená. Ďalšie dve metódy sú vhodné pre nelineárne metódy. GRG Nonlinear je najrýchlejší, ale jeho riešenie môže byť veľmi závislé od počiatočných počiatočných podmienok. Má flexibilitu, že nevyžaduje nastavenie hraníc premenných. Evolučný riešiteľ je často najspoľahlivejší, ale vyžaduje, aby všetky premenné mali hornú aj dolnú hranicu, čo môže byť ťažké určiť vopred.
Doplnok Excel Solver je veľmi výkonný nástroj, ktorý možno použiť na mnohé praktické problémy. Ak chcete naplno využívať možnosti Excelu, skúste skombinovať Riešiteľ s makrami Excel.
