Excel Solver: Optimalizácia výsledkov, pridávanie obmedzení a ukladanie riešení ako scenárov

Model Excel Riešiteľ nastavíte pomocou dialógového okna Parametre riešiteľa. Pomocou poľa Nastaviť cieľ určíte bunku cieľa a pomocou skupiny Komu poviete Excel Solver, čo chcete od bunky cieľa: maximálnu možnú hodnotu; minimálna možná hodnota; alebo konkrétnu hodnotu. Nakoniec použijete pole Zmenou buniek premenných na určenie buniek, ktoré môže Riešiteľ použiť na vloženie hodnôt na optimalizáciu výsledku.

Optimalizácia výsledkov Excel Solver

Keď Riešiteľ nájde riešenie, môžete si vybrať buď Ponechať riešenie Riešiteľa alebo Obnoviť pôvodné hodnoty. Ak vyberiete možnosť Keep Solver Solution, Excel natrvalo zmení pracovný hárok. Zmeny nemôžete vrátiť späť.

Keď je váš model pracovného hárka pripravený na použitie pre Riešiteľa, tu sú kroky, ktoré je potrebné dodržať, aby ste našli optimálny výsledok pre váš model pomocou Riešiteľa:

Vyberte Údaje → Riešiteľ.
Excel otvorí dialógové okno Parametre riešiteľa.

Do poľa Nastaviť cieľ zadajte adresu bunky cieľa vášho modelu.
Upozorňujeme, že ak na bunku kliknete, aby ste ju zadali, Riešiteľ automaticky zadá absolútnu adresu bunky (napríklad $ B $ 14 namiesto B14). Riešiteľ funguje dobre v oboch smeroch.

V skupine Komu vyberte možnosť:

• Max: Vráti maximálnu možnú hodnotu.
• Min: Vráti minimálnu možnú hodnotu.
• Hodnota: Zadajte číslo na nastavenie cieľovej bunky na toto číslo.

  Pre vzorový model bola vybratá hodnota a do textového poľa zadaná 0 .

Do poľa Zmenou buniek premenných zadajte adresy buniek, ktoré má Riešiteľ zmeniť, kým bude hľadať riešenie.
V tomto príklade sú meniace sa bunky B4 a C4. Nasledujúci obrázok zobrazuje dokončené dialógové okno Parametre riešiteľa.

Dokončené dialógové okno Parametre riešiteľa.

Kliknite na tlačidlo Vyriešiť.
Riešiteľ sa pustí do práce. Keď Riešiteľ pracuje na probléme, možno sa vám raz alebo viackrát zobrazia dialógové okná Zobraziť skúšobné riešenie.

V akomkoľvek dialógovom okne Zobraziť skúšobné riešenie, ktoré sa zobrazí, kliknite na Pokračovať, aby ste veci posunuli ďalej.
Po dokončení optimalizácie Excel zobrazí dialógové okno Výsledky riešiteľa.

Dialógové okno Výsledky riešiteľa a riešenie problému rovnovážneho stavu.

Vyberte možnosť Keep Solver Solution.
Ak výsledok nechcete prijať, vyberte namiesto toho možnosť Obnoviť pôvodné hodnoty.

Kliknite na tlačidlo OK.

Môžete požiadať Riešiteľa o zobrazenie jednej alebo viacerých správ, ktoré vám poskytnú ďalšie informácie o výsledkoch. V dialógovom okne Výsledky riešenia použite zoznam Správy na výber každej správy, ktorú chcete zobraziť:

• Odpoveď: Zobrazuje informácie o cieľovej bunke modelu, premenných bunkách a obmedzeniach. Pre cieľovú bunku a premenné bunky zobrazuje Riešiteľ pôvodné a konečné hodnoty.
• Citlivosť: Pokusy ukázať, aké citlivé je riešenie na zmeny vo vzorcoch modelu. Rozloženie správy o citlivosti závisí od typu modelu, ktorý používate.
• Limity: Zobrazuje cieľovú bunku a jej hodnotu, ako aj bunky premenných a ich adresy, názvy a hodnoty.

Excel Solver môže použiť jednu z niekoľkých metód riešenia. V dialógovom okne Parametre riešiteľa použite zoznam Vybrať metódu riešenia na výber jednej z nasledujúcich možností:

• Simplex LP: Použite, ak je váš model pracovného hárka lineárny. Zjednodušene povedané, lineárny model je model, v ktorom premenné nie sú povýšené na žiadnu mocnosť a nepoužíva sa žiadna z takzvaných transcendentných funkcií – ako SIN a COS.
• GRG nelineárne: Použite, ak je váš model pracovného hárka nelineárny a hladký. Vo všeobecnosti je hladký model taký, v ktorom graf použitej rovnice nevykazuje ostré hrany ani zlomy.
• Evolučný: Použite, ak je váš model pracovného hárka nelineárny a nehladký.

Musíte sa niečoho z toho obávať? Takmer určite nie. Excel Solver predvolene používa GRG Nonlinear, a to by malo fungovať takmer pri všetkom, čo robíte s Solverom.

Pridanie obmedzení do Excel Solver

Reálny svet kladie na vzorce obmedzenia a podmienky. Továreň môže mať maximálnu kapacitu 10 000 jednotiek denne, počet zamestnancov v spoločnosti nemôže byť záporné číslo a vaše náklady na reklamu môžu byť obmedzené na 10 percent celkových nákladov.

Podobne predpokladajme, že vykonávate analýzu rovnováhy dvoch produktov. Ak spustíte optimalizáciu bez akýchkoľvek obmedzení, Riešiteľ môže dosiahnuť celkový zisk 0 tým, že jeden produkt nastaví na miernu stratu a druhý na mierny zisk, pričom strata a zisk sa navzájom vyrušia. V skutočnosti, ak sa bližšie pozriete na predchádzajúci obrázok, je to presne to, čo Solver urobil. Ak chcete získať skutočné rovnovážne riešenie, možno budete chcieť vidieť obe hodnoty zisku produktu ako 0.

Takéto obmedzenia a podmienky sú príkladmi toho, čo Riešiteľ nazýva obmedzeniami. Pridanie obmedzení povie Riešiteľovi, aby našiel riešenie, aby tieto podmienky neboli porušené.

Tu je návod, ako spustiť Solver s obmedzeniami pridanými k optimalizácii:

Vyberte Údaje → Riešiteľ.
Excel otvorí dialógové okno Parametre riešiteľa.

Použite pole Nastaviť cieľ, skupinu Komu a pole Zmenou buniek premenných na nastavenie Riešiteľa, ako je popísané vyššie.

Kliknite na Pridať.
Excel zobrazí dialógové okno Pridať obmedzenie.

Do poľa Odkaz na bunku zadajte adresu bunky, ktorú chcete obmedziť.
Môžete zadať adresu alebo vybrať bunku v hárku.

V rozbaľovacom zozname vyberte operátora, ktorého chcete použiť.
Väčšinou používate operátor porovnávania, napríklad rovný (=) alebo väčší ako (>). Operátor int (celé číslo) použite, keď potrebujete, aby obmedzenie, napríklad celkový počet zamestnancov, bolo celočíselnou hodnotou namiesto skutočného čísla (to znamená číslo s desatinnou zložkou; nemôžete mať 10,5 zamestnanca!). Ak máte obmedzenie, ktoré musí byť TRUE alebo FALSE (alebo 1 alebo 0), použite operátor bin (binárny).

Ak ste v kroku 5 vybrali operátor porovnávania, do poľa Obmedzenie zadajte hodnotu, ktorou chcete bunku obmedziť.
Tento obrázok zobrazuje príklad dokončeného dialógového okna Pridať obmedzenie. Vo vzorovom modeli toto obmedzenie prikazuje Riešiteľovi nájsť také riešenie, že zisk z produktu nafukovacieho terča na šípky (bunka B12) sa rovná 0.

Dokončené dialógové okno Pridať obmedzenie.

Ak chcete zadať ďalšie obmedzenia, kliknite na Pridať a podľa potreby zopakujte kroky 4 až 6.

Napríklad pridáte obmedzenie, ktoré požaduje, aby zisk produktu Dog Polisher (bunka C12) bol 0.

Kliknite na tlačidlo OK.

Excel sa vráti do dialógového okna Parametre riešiteľa a zobrazí vaše obmedzenia v zozname Predmet obmedzenia.

Kliknite na tlačidlo Vyriešiť.

V akomkoľvek dialógovom okne Zobraziť skúšobné riešenie, ktoré sa zobrazí, kliknite na Pokračovať, aby ste veci posunuli ďalej.
Obrázok nižšie zobrazuje príklad riešenia rovnovážneho stavu s pridanými obmedzeniami. Všimnite si, že nielen bunka Celkový zisk (B14) je nastavená na 0, ale aj dve bunky Zisk produktu (B12 a C12).

Dialógové okno Výsledky riešiteľa a konečné riešenie problému rovnovážneho stavu.

Vyberte možnosť Keep Solver Solution.
Ak výsledok nechcete prijať, vyberte namiesto toho možnosť Obnoviť pôvodné hodnoty.

Kliknite na tlačidlo OK.

Môžete pridať maximálne 100 obmedzení. Ak tiež potrebujete zmeniť obmedzenie predtým, ako začnete riešiť, vyberte obmedzenie v zozname Predmet obmedzenia, kliknite na položku Zmeniť a potom vykonajte úpravy v dialógovom okne Zmeniť obmedzenie, ktoré sa zobrazí. Ak chcete odstrániť obmedzenie, ktoré už nepotrebujete, vyberte obmedzenie a potom kliknite na Odstrániť.

Uložte riešenie Excel Solver ako scenár

Kedykoľvek máte tabuľkový model, ktorý používa koherentný súbor vstupných hodnôt – známy ako zmena buniek – máte to, čo Excel nazýva scenár. V prípade Riešiteľa sú tieto meniace sa bunky jeho variabilnými bunkami, takže riešenie Riešiteľa predstavuje určitý druh scenára v Exceli . Riešiteľ vám však neposkytuje jednoduchý spôsob uloženia a opätovného spustenia konkrétneho riešenia. Ak chcete tento problém obísť, môžete si uložiť riešenie ako scenár, ktorý si neskôr môžete vyvolať pomocou funkcie Správca scenárov v Exceli.

Ak chcete uložiť riešenie Riešiteľ ako scenár, postupujte podľa týchto krokov:

Vyberte Údaje → Riešiteľ.
Excel otvorí dialógové okno Parametre riešiteľa.

Na nastavenie Riešiteľa, ako je popísané vyššie, použite pole Nastaviť cieľ, skupinu Komu, Zmenou buniek premenných a Zoznam Obmedzenia.

Kliknite na tlačidlo Vyriešiť.

Vždy, keď sa zobrazí dialógové okno Show Trial Solution, zvoľte Pokračovať.
Po dokončení optimalizácie Excel zobrazí dialógové okno Výsledky riešiteľa.

Kliknite na položku Uložiť scenár.
Excel zobrazí dialógové okno Uložiť scenár.

V dialógovom okne Názov scenára zadajte názov scenára a potom kliknite na tlačidlo OK.
Excel vás vráti do dialógového okna Výsledky riešiteľa.

Vyberte možnosť Keep Solver Solution.
Ak výsledok nechcete prijať, vyberte namiesto toho možnosť Obnoviť pôvodné hodnoty.

Kliknite na tlačidlo OK.