Jak používat Řešitel Excel 2019

Přestože příkazy Excelu Data Table a Goal Seek fungují dobře pro jednoduché problémy, které vyžadují určení přímého vztahu mezi vstupy a výsledky ve vzorci, musíte při řešení složitějších problémů použít doplněk Řešitel. Například použijte Řešitel k nalezení nejlepšího řešení, když potřebujete změnit více vstupních hodnot v modelu aplikace Excel a potřebujete uvalit omezení na tyto hodnoty a/nebo výstupní hodnotu.

Doplněk Řešitel funguje tak, že používá iterační metody k nalezení „nejlepšího“ řešení s ohledem na vstupy, požadované řešení a omezení, která zavedete. Při každé iteraci program aplikuje metodu pokus-omyl (založenou na použití lineárních nebo nelineárních rovnic a nerovnic), která se snaží přiblížit optimálnímu řešení.

Při používání doplňku Řešitel mějte na paměti, že mnoho problémů, zejména těch složitějších, má mnoho řešení. Ačkoli Řešitel vrací optimální řešení, vzhledem k výchozím hodnotám, proměnným, které se mohou měnit, a omezením, která definujete, toto řešení často není jediné možné a ve skutečnosti pro vás nemusí být tím nejlepším řešením. Abyste si byli jisti, že nacházíte nejlepší řešení, možná budete chtít spustit Řešitel více než jednou a upravit počáteční hodnoty pokaždé, když problém vyřešíte.

Při nastavování problému pro doplněk Řešitel v listu aplikace Excel definujte následující položky:

• Cílová buňka: Cílová buňka v listu, jejíž hodnota má být maximalizována, minimalizována nebo upravena tak, aby dosáhla konkrétní hodnoty. Všimněte si, že tato buňka musí obsahovat vzorec.
• Proměnné buňky: Měnící se buňky ve vašem listu, jejichž hodnoty se mají upravovat, dokud nebude nalezena odpověď.
• Buňky omezení: Buňky, které obsahují omezení, která uvalíte na měnící se hodnoty v buňkách proměnné a/nebo cílové buňce v buňce cíle.

Poté, co dokončíte definování problému s těmito parametry a necháte problém vyřešit pomocí doplňku Řešitel, program vrátí optimální řešení úpravou hodnot ve vašem listu. V tomto okamžiku se můžete rozhodnout zachovat změny v listu nebo obnovit původní hodnoty na listu. Řešení můžete také uložit jako scénář, abyste si jej mohli prohlédnout později, než obnovíte původní hodnoty.

Můžete použít doplněk Řešitel se Scenario Managerem, který vám pomůže nastavit problém, který chcete vyřešit, nebo uložit řešení, abyste si ho mohli prohlédnout později. Měnící se buňky, které definujete pro Správce scénářů, jsou automaticky vybrány a použity Řešitelem, když vyberete tento příkaz, a naopak. Také můžete uložit řešení problému řešitele jako scénář (kliknutím na tlačítko Uložit scénář v dialogovém okně Řešitel), který si pak můžete prohlédnout pomocí Správce scénářů.

Nastavení a definování problému v Excelu 2019

Prvním krokem při nastavování problému, na kterém bude Řešitel pracovat, je vytvoření modelu listu, pro který definujete buňku cíle, buňky proměnných a buňky omezení.

Mějte na paměti, že Řešitel je doplňkový nástroj. To znamená, že než jej budete moci použít, musíte se ujistit, že je stále načten doplňkový program Řešitel, jak naznačuje vzhled tlačítka Řešitel ve skupině Analýza na konci karty Data na pásu karet. Pokud toto tlačítko chybí, můžete načíst Řešitel otevřením karty Doplňky v dialogovém okně Možnosti aplikace Excel (Alt+FTAA) a kliknutím na tlačítko Přejít poté, co se ujistíte, že jsou v rozevíracím seznamu Spravovat zobrazeny Doplňky aplikace Excel. seznam na levé straně. Poté zaškrtněte políčko Doplněk Řešitel v dialogovém okně Doplňky a zaškrtněte jej před klepnutím na tlačítko OK, čímž se dialogové okno zavře a doplněk znovu načte.

Chcete-li definovat a vyřešit problém s doplňkem Řešitel po načtení doplňku a vytvoření modelu listu, postupujte takto:

Klikněte na příkazové tlačítko Řešitel ve skupině Analyzovat na konci karty Data na pásu karet.

Excel otevře dialogové okno Parametry řešitele.

Zadání parametrů, které se mají použít na model, v dialogovém okně Parametry řešitele.

Klepněte na cílovou buňku v listu nebo zadejte její odkaz na buňku nebo název rozsahu do textového pole Nastavit cíl.

Dále musíte vybrat nastavení Komu. Pokud chcete, aby byla hodnota cílové buňky co největší, klepněte na tlačítko volby Max. Pokud chcete, aby byla hodnota cílové buňky co nejmenší, klepněte na tlačítko volby Min. Chcete-li, aby hodnota cílové buňky dosáhla konkrétní hodnoty, klikněte na tlačítko volby Hodnota a potom zadejte hodnotu do přidruženého textového pole.

Klepněte na příslušnou možnost tlačítka v části Komu dialogového okna. Pokud vyberete tlačítko volby Hodnota, zadejte odpovídající hodnotu do přidruženého textového pole.

Dále určete proměnné buňky – tedy ty, které Řešitel může změnit, aby dosáhl vašeho cíle Rovná se.

Klepněte na textové pole Změnou buněk s proměnnými a potom v listu vyberte buňky, které chcete změnit, nebo do textového pole zadejte jejich odkazy na buňky nebo název rozsahu.

Pamatujte, že chcete-li vybrat nesousedící buňky v listu, musíte při klepání na každou buňku ve výběru podržet klávesu Ctrl. Chcete-li, aby Excel vybral měnící se buňky za vás na základě cílové buňky, kterou jste vybrali, klikněte na tlačítko Hádat napravo od tohoto textového pole.

Než necháte Řešitele upravit váš model, můžete pro cílovou buňku nebo kteroukoli z měnících se buněk přidat omezení, která určují její limity při úpravě hodnot.

(Volitelné) Klepněte na tlačítko Přidat napravo od seznamu Předmět omezení v dialogovém okně Parametry řešitele.

Tato akce otevře dialogové okno Přidat omezení. Při definování omezení vyberte buňku, jejíž hodnotu chcete omezit, nebo vyberte buňku v listu nebo zadejte její odkaz na buňku do textového pole Odkaz na buňku. Potom vyberte vztah (=, <=,>= nebo int pro celé číslo nebo bin pro binární) z rozevíracího seznamu napravo a (pokud jste nezvolili int nebo bin ) zadejte příslušnou hodnotu nebo odkaz na buňku do pole Textové pole omezení.

Chcete-li pokračovat v přidávání omezení pro další buňky používané Řešitelem, kliknutím na tlačítko Přidat přidejte omezení a zrušte zaškrtnutí textových polí v dialogovém okně Přidat omezení. Poté opakujte krok 5 a přidejte nové omezení. Poté, co dokončíte definování omezení pro cílovou buňku a změnu hodnot v modelu, klepnutím na OK zavřete dialogové okno Přidat omezení a vraťte se do dialogového okna Parametry řešitele (které nyní uvádí vaše omezení v seznamu Předmět omezení).

(Volitelné) Pokud chcete povolit záporné hodnoty, když se na buňky proměnných nevztahují žádná omezení, zrušte zaškrtnutí políčka Udělat z neomezených proměnných nezáporné.
Ve výchozím nastavení doplněk Řešitel využívá nelineární metodu GRG (Generalized Reduced Gradient) při řešení modelu, jehož parametry nastavujete, což je velmi efektivní způsob řešení hladkých nelineárních problémů. Chcete-li použít metodu LP Simplex (pro lineární programování podle Simplexového algoritmu) nebo Evolutionary engine pro řešení nehladkých problémů, musíte postupovat podle kroku 7.

(Volitelně) Vyberte LP Simplex nebo Evolutionary z rozevíracího seznamu Select a Solving Method, chcete-li použít jednu z těchto metod k řešení nehladkých problémů.

Klepněte na tlačítko Řešit, aby Řešitel vyřešil problém tak, jak jste jej definovali v dialogovém okně Parametry Řešitele.

Řešení problému s Řešitelem Excelu

Když klepnete na tlačítko Řešit, dialogové okno Parametry řešitele zmizí a stavový řádek indikuje, že Řešitel nastavuje problém, a poté vás informuje o postupu při řešení problému zobrazením čísla středního (nebo zkušebního). řešení, jak jsou vyzkoušena. Chcete-li přerušit proces řešení kdykoli předtím, než Excel vypočítá poslední iteraci, stiskněte klávesu Esc. Excel poté zobrazí dialogové okno Zobrazit zkušební řešení, které vás informuje, že proces řešení byl pozastaven. Chcete-li pokračovat v procesu řešení, klikněte na tlačítko Pokračovat. Chcete-li proces řešení přerušit, klikněte na tlačítko Zastavit.

Když Excel dokončí proces řešení, zobrazí se dialogové okno Výsledky řešitele. Toto dialogové okno vás informuje, zda Řešitel byl schopen najít řešení vzhledem k cílové buňce, změněným buňkám a omezením definovaným pro problém. Chcete-li zachovat změny, které Řešitel provede v modelu listu, ponechte vybrané tlačítko Zachovat řešení Řešitel a kliknutím na OK zavřete dialogové okno Výsledky Řešitele. Chcete-li vrátit původní hodnoty do listu, klikněte místo toho na tlačítko volby Obnovit původní hodnoty. Chcete-li uložit změny jako scénář před obnovením původních hodnot, klepněte na tlačítko Uložit scénář a přiřaďte název aktuálnímu scénáři, než klepnete na možnost Obnovit původní hodnoty a na tlačítko OK.

Dialogové okno Výsledky Řešitel ukazuje, že Řešitel našel řešení problému.

Na rozdíl od použití příkazu Hledání cíle nelze po klepnutí na tlačítko volby Zachovat řešení řešitele v dialogovém okně Výsledky řešitele použít příkazové tlačítko Zpět na panelu nástrojů Rychlý přístup k obnovení původních hodnot na listu. Pokud chcete mít možnost přepínat mezi zobrazením listu „před“ a „po“, musíte změny uložit pomocí tlačítka Uložit scénář a poté vybrat tlačítko volby Obnovit původní hodnoty. Tímto způsobem můžete zachovat pohled „před“ v původním listu a pomocí Správce scénářů zobrazit pohled „po“ vytvořený Řešitelem.

Změna možností Řešitel v Excelu

Pro většinu problémů jsou výchozí možnosti používané Řešitelem adekvátní. V některých situacích však možná budete chtít změnit některé možnosti Řešitele před zahájením procesu řešení. Chcete-li změnit možnosti řešení, klepněte na tlačítko Možnosti v dialogovém okně Parametry řešitele. Excel poté otevře dialogové okno Možnosti s vybranou kartou Všechny metody, kde můžete provést všechny potřebné změny.

Úprava možností řešení v dialogovém okně Možnosti.

Nastavení možností řešení Excel 2019

Po změně voleb klepněte na OK pro návrat do dialogového okna Parametry řešitele; odtud můžete kliknout na tlačítko Vyřešit a zahájit proces řešení s novým nastavením řešení, které jste právě změnili.

Když použijete výchozí nelineární nebo evoluční metodu GRG (Generalized Reduced Gradient), můžete nastavit další nastavení Řešiče pomocí voleb na kartách GRG Nelineární a Evoluční v dialogovém okně Možnosti. Tyto možnosti zahrnují změnu nastavení Converge, Population Size a Random Seed pro jednu z těchto konkrétních metod.

Ukládání a načítání problému s modelem v Excelu 2019

Po klepnutí na tlačítko Uložit na panelu nástrojů Rychlý přístup (Ctrl+S) se naposledy použitá buňka cíle, buňky proměnných, buňky omezení a možnosti Řešitel uloží jako součást listu aplikace Excel. Když definujete další problémy pro stejný list, který chcete uložit, musíte kliknout na tlačítko Uložit model v dialogovém okně Možnosti řešitele a uvést odkaz na buňku nebo název rozsahu v aktivním listu, kde chcete mít parametry problému. vložena.

Když kliknete na tlačítko Načíst/Uložit, Excel otevře dialogové okno Načíst/Uložit model obsahující textové pole Vybrat oblast modelu. Toto textové pole obsahuje odkazy na buňky pro rozsah dostatečně velký, aby obsahoval všechny parametry problému, počínaje aktivní buňkou. Chcete-li uložit parametry problému v tomto rozsahu, klepněte na tlačítko OK. Pokud tento rozsah zahrnuje buňky s existujícími daty, musíte před kliknutím na OK upravit odkaz na buňku v tomto textovém poli, abyste zabránili Excelu nahradit existující data.

Po klepnutí na tlačítko OK Excel zkopíruje parametry problému v zadaném rozsahu. Tyto hodnoty se pak uloží jako součást listu při příštím uložení sešitu. Chcete-li znovu použít tyto parametry problému při řešení problému, stačí otevřít dialogové okno Možnosti řešitele, kliknutím na tlačítko Načíst/Uložit otevřete dialogové okno Načíst/Uložit model, klepněte na tlačítko Načíst a poté vyberte rozsah obsahující uložené parametry problému. Když klepnete na OK v dialogovém okně Načíst model, Excel načte parametry z této oblasti buněk do příslušných textových polí v dialogovém okně Parametry řešitele. Poté můžete zavřít dialogové okno Možnosti řešitele kliknutím na OK a problém můžete vyřešit pomocí těchto parametrů kliknutím na příkazové tlačítko Vyřešit.

Nezapomeňte, že tlačítko Reset All můžete použít, kdykoli budete chtít vymazat všechny parametry definované pro předchozí problém a vrátit možnosti Řešitel na výchozí hodnoty.

Vytváření sestav Řešitel v Excelu 2019

Pomocí Řešitele můžete vytvořit tři různé typy sestav:

• Sestava odpovědí: Uvádí cílovou buňku a měnící se buňky s jejich původními a konečnými hodnotami spolu s omezeními použitými při řešení problému.
• Zpráva o citlivosti: Označuje, jak citlivé je optimální řešení na změny ve vzorcích, které počítají cílovou buňku a omezení. Zpráva zobrazuje měnící se buňky s jejich konečnými hodnotami a sníženým gradientem pro každou buňku. (Snížený gradient měří cíl na jednotku zvýšení v měnící se buňce.) Pokud jste definovali omezení, zpráva Citlivost je uvede s jejich konečnými hodnotami a Lagrangeovým multiplikátorem pro každé omezení. (Lagrangeův multiplikátor měří cíl na jednotku zvýšení, který se objeví na pravé straně omezující rovnice.)
• Zpráva Limity: Zobrazuje cílovou buňku a měnící se buňky s jejich hodnotami, dolní a horní mezí a cílovými výsledky. Spodní mez představuje nejnižší hodnotu, kterou může mít měnící se buňka, zatímco fixuje hodnoty všech ostatních buněk a stále splňuje omezení. Horní limit představuje nejvyšší hodnotu, která to udělá.

Excel umístí každou sestavu, kterou vygenerujete pro problém Řešitel, do samostatného listu v sešitu. Chcete-li vygenerovat jednu (nebo všechny) z těchto zpráv, vyberte typ zprávy (Odpověď, Citlivost nebo Limity) ze seznamu Zprávy v dialogovém okně Výsledky řešení. Chcete-li vybrat více než jeden přehled, stačí kliknout na název přehledu.

Když klepnutím na OK zavřete dialogové okno Výsledky řešitele (po výběru mezi možnostmi Zachovat řešení řešitele a Obnovit původní hodnoty), Excel vygeneruje sestavu (nebo sestavy), které jste vybrali v novém listu, který přidá na začátek sešitu. . (Karty listu sestavy jsou pojmenovány podle typu sestavy, jako ve zprávě o odpovědi 1, zprávě o citlivosti 1 a zprávě o limitech 1. )