Excel Solver: Optimalizace výsledků, přidávání omezení a ukládání řešení jako scénáře

Model Řešitele Excel nastavíte pomocí dialogového okna Parametry Řešitele. Pomocí pole Nastavit cíl určíte buňku cíle a pomocí skupiny Komu sdělíte Excel Solveru, co od buňky cíle požaduje: maximální možnou hodnotu; minimální možná hodnota; nebo konkrétní hodnotu. Nakonec použijete pole Změnou proměnných buněk k určení buněk, které může Řešitel použít k vložení hodnot za účelem optimalizace výsledku.

Optimalizace výsledků Excel Solver

Když Řešitel najde řešení, můžete si vybrat buď Ponechat řešení Řešitel nebo Obnovit původní hodnoty. Pokud zvolíte Keep Solver Solution, Excel trvale změní list. Změny nelze vrátit zpět.

Když máte model listu připraveného pro Řešitel připravený k použití, postupujte podle následujících kroků, abyste našli optimální výsledek pro svůj model pomocí Řešitel:

Vyberte Data → Řešitel.
Excel otevře dialogové okno Parametry řešitele.

Do pole Nastavit cíl zadejte adresu buňky cíle vašeho modelu.
Všimněte si, že pokud na buňku kliknete, abyste ji zadali, Řešitel automaticky zadá absolutní adresu buňky (například $ B $ 14 místo B14). Řešitel funguje v obou směrech dobře.

Ve skupině Komu vyberte možnost:

• Max: Vrací maximální možnou hodnotu.
• Min: Vrací minimální možnou hodnotu.
• Hodnota: Zadejte číslo pro nastavení cílové buňky na toto číslo.

  Pro vzorový model byla vybrána hodnota Hodnota a do textového pole byla zadána 0 .

Do pole Změnou proměnných buněk zadejte adresy buněk, které má Řešitel změnit, zatímco hledá řešení.
V tomto příkladu jsou měnící se buňky B4 a C4. Následující obrázek ukazuje dokončené dialogové okno Parametry řešitele.

Dokončené dialogové okno Parametry řešitele.

Klepněte na tlačítko Vyřešit.
Řešitel se pustí do práce. Jak Řešitel na problému pracuje, může se stát, že se jednou nebo vícekrát zobrazí dialogová okna Zobrazit zkušební řešení.

V libovolném dialogovém okně Zobrazit zkušební řešení, které se objeví, klikněte na Pokračovat, abyste mohli věci posouvat dál.
Po dokončení optimalizace Excel zobrazí dialogové okno Výsledky řešitele.

Dialogové okno Výsledky řešitele a řešení problému zlomu.

Vyberte možnost Keep Solver Solution.
Pokud výsledek nechcete přijmout, vyberte místo toho možnost Obnovit původní hodnoty.

Klepněte na tlačítko OK.

Můžete požádat Řešitele o zobrazení jedné nebo více sestav, které vám poskytnou další informace o výsledcích. V dialogovém okně Výsledky řešení vyberte pomocí seznamu Zprávy každou zprávu, kterou chcete zobrazit:

• Odpověď: Zobrazí informace o cílové buňce modelu, buňkách proměnných a omezeních. Pro cílovou buňku a proměnnou zobrazuje Řešitel původní a konečné hodnoty.
• Citlivost: Pokusí se ukázat, jak citlivé je řešení na změny ve vzorcích modelu. Rozvržení zprávy Citlivost závisí na typu modelu, který používáte.
• Limity: Zobrazuje cílovou buňku a její hodnotu, stejně jako buňky proměnných a jejich adresy, názvy a hodnoty.

Excel Solver může použít jednu z několika metod řešení. V dialogovém okně Parametry řešitele vyberte ze seznamu Vybrat metodu řešení jednu z následujících možností:

• Simplex LP: Použijte, pokud je váš model listu lineární. Zjednodušeně řečeno, lineární model je model, ve kterém proměnné nejsou povýšeny na žádnou mocninu a nepoužívá se žádná z takzvaných transcendentních funkcí — jako SIN a COS.
• GRG nelineární: Použijte, pokud je váš model listu nelineární a hladký. Obecně řečeno, hladký model je model, ve kterém graf použité rovnice nevykazuje ostré hrany nebo zlomy.
• Evoluční: Použijte, pokud je váš model listu nelineární a nehladký.

Musíte se něčeho z toho obávat? Téměř jistě ne. Excel Solver ve výchozím nastavení používá GRG nelineární, a to by mělo fungovat téměř pro cokoli, co děláte s Solverem.

Přidání omezení do Excel Solveru

Reálný svět klade na vzorce omezení a podmínky. Továrna může mít maximální kapacitu 10 000 jednotek denně, počet zaměstnanců ve společnosti nesmí být záporné číslo a vaše reklamní náklady mohou být omezeny na 10 procent celkových nákladů.

Podobně předpokládejme, že provádíte analýzu rovnováhy dvou produktů. Pokud spustíte optimalizaci bez omezení, Řešitel může dosáhnout celkového zisku 0 tím, že jeden produkt nastaví na mírnou ztrátu a druhý na mírný zisk, kde se ztráta a zisk vzájemně vyruší. Ve skutečnosti, když se blíže podíváte na předchozí obrázek, je to přesně to, co Solver udělal. Chcete-li získat skutečné řešení pro dosažení rovnováhy, možná budete chtít vidět obě hodnoty zisku produktu jako 0.

Taková omezení a podmínky jsou příklady toho, co Řešitel nazývá omezeními. Přidání omezení říká Řešiteli, aby našel řešení, aby tyto podmínky nebyly porušeny.

Zde je návod, jak spustit Solver s omezeními přidanými k optimalizaci:

Vyberte Data → Řešitel.
Excel otevře dialogové okno Parametry řešitele.

Použijte pole Nastavit cíl, skupinu Komu a pole Změnou buněk proměnných k nastavení Řešiče, jak je popsáno výše.

Klikněte na Přidat.
Excel zobrazí dialogové okno Přidat omezení.

Do pole Odkaz na buňku zadejte adresu buňky, kterou chcete omezit.
Můžete zadat adresu nebo vybrat buňku na listu.

V rozevíracím seznamu vyberte operátora, kterého chcete použít.
Ve většině případů používáte operátor porovnání, například rovno (=) nebo větší než (>). Operátor int (celé číslo) použijte, když potřebujete, aby omezení, například celkový počet zaměstnanců, bylo celočíselnou hodnotou namísto reálného čísla (to je číslo s desetinnou složkou; nemůžete mít 10,5 zaměstnance!). Operátor bin (binární) použijte, pokud máte omezení, které musí být buď PRAVDA, nebo NEPRAVDA (nebo 1 nebo 0).

Pokud jste v kroku 5 zvolili operátor porovnání, do pole Omezení zadejte hodnotu, kterou chcete buňku omezit.
Tento obrázek ukazuje příklad dokončeného dialogového okna Přidat vazbu. V modelovém příkladu toto omezení říká Solverovi, aby našel takové řešení, že zisk produktu nafukovacího terče na šipky (buňka B12) je roven 0.

Dokončené dialogové okno Přidat omezení.

Chcete-li zadat více omezení, klikněte na Přidat a podle potřeby opakujte kroky 4 až 6.

Například přidáte omezení, které požaduje, aby zisk produktu Dog Polisher (buňka C12) byl 0.

Klepněte na tlačítko OK.

Excel se vrátí do dialogového okna Parametry řešitele a zobrazí vaše omezení v seznamu Předmět omezení.

Klepněte na tlačítko Vyřešit.

V libovolném dialogovém okně Zobrazit zkušební řešení, které se objeví, klikněte na Pokračovat, abyste mohli věci posouvat dál.
Obrázek níže ukazuje příklad řešení pro dosažení rovnováhy s přidanými omezeními. Všimněte si, že nejen buňka Celkový zisk (B14) je nastavena na 0, ale také dvě buňky Product Profit (B12 a C12).

Dialogové okno Výsledky řešitele a konečné řešení problému zlomu.

Vyberte možnost Keep Solver Solution.
Pokud výsledek nechcete přijmout, vyberte místo toho možnost Obnovit původní hodnoty.

Klepněte na tlačítko OK.

Můžete přidat maximálně 100 omezení. Pokud také potřebujete provést změnu omezení, než začnete řešit, vyberte omezení v seznamu Předmět omezení, klepněte na Změnit a poté proveďte úpravy v dialogovém okně Změnit omezení, které se zobrazí. Pokud chcete odstranit omezení, které již nepotřebujete, vyberte omezení a klikněte na Odstranit.

Uložte řešení Excel Solver jako scénář

Kdykoli máte tabulkový model, který používá koherentní sadu vstupních hodnot – známou jako změna buněk – máte to, co Excel nazývá scénář. S Řešitelem jsou tyto měnící se buňky jeho proměnnými buňkami, takže řešení Řešitel představuje jakýsi scénář v Excelu . Řešitel vám však neposkytuje snadný způsob, jak uložit a znovu spustit konkrétní řešení. Chcete-li tento problém vyřešit, můžete uložit řešení jako scénář, který si můžete později vyvolat pomocí funkce Správce scénářů aplikace Excel.

Chcete-li uložit řešení Řešitel jako scénář, postupujte takto:

Vyberte Data → Řešitel.
Excel otevře dialogové okno Parametry řešitele.

Použijte pole Nastavit cíl, skupinu Komu, pole Změnou buněk proměnných a seznam Podléhající omezením k nastavení Řešiče, jak je popsáno výše.

Klepněte na tlačítko Vyřešit.

Kdykoli se zobrazí dialogové okno Zobrazit zkušební řešení, zvolte Pokračovat.
Po dokončení optimalizace Excel zobrazí dialogové okno Výsledky řešitele.

Klikněte na Uložit scénář.
Excel zobrazí dialogové okno Uložit scénář.

V dialogovém okně Název scénáře zadejte název scénáře a klepněte na tlačítko OK.
Excel vás vrátí do dialogového okna Výsledky řešitele.

Vyberte možnost Keep Solver Solution.
Pokud výsledek nechcete přijmout, vyberte místo toho možnost Obnovit původní hodnoty.

Klepněte na tlačítko OK.