Pro informace o výuce Excelu klikněte zde

Řešitel je obecně použitelný optimalizační nástroj. Může sloužit pro řešení lineárních, nelineárních a celočíselných úloh. Pomocí Řešitele můžeme najít optimální  (maximální, minimální či přesnou) hodnotu jedné buňky změnou jiných buněk, které musí být propojeny pomocí vzorců. Upraví hodnoty v měněných buňkách tak, aby byl dosažen určený výsledek.
Jinými slovy, řešitele můžeme použít tehdy, pokud chceme současně měnit více parametrů, tj. hledáme řešení pro více proměnných.

Řešitel je dodáván s Excelem jako doplněk a před prvním použitím je třeba ho aktivovat pomocí příkazu Nástroje – Doplňky, kde v dialogovém okně zatrhneme položku Řešitel (Pro Excelu 2007 platí příkaz: Tlačítko Office – Možnosti aplikace Excel – Doplňky – Přejít). Pokud tato položka chybí, musíme doplněk doinstalovat.

Máte následující tabulku, kde jsou uvažované díly pro výrobu. Pro každý díl je zadána pracnost pro skupiny pracovníků.

Pod tabulkou máme předpokládaný zisk na jednotlivých dílech.

V buňkách G2:G4 máme počet dostupných kapacit. V tomto případě se jedná o kapacity lidské tj. počet pracovníků x fond pracovní doby x počet dnů určitého období. (Jsou i strojní kapacity, ale s těmi se v tomto příkladu nebudeme zabývat)

Použité vzorce:

Do buňky H2 zadáme tento maticový vzorec {=SUMA(B2:F2*$B$7:$F$7)}{=SUMA(B2:F2*$B$9:$F$9)}, místo maticového vzorce můžeme použít vzorec ve tvaru =B2*$B$9+C2*$C$9+D2*$D$9+E2*$E$9+F2*$F$9 a zkopírujeme směrem dolů do buněk H3:H4. Tyto vzorce nám budou počítat spotřebu kapacit podle počtu kusů.

Do buňky zadáme vzorec  I2 =G2-H2 a zkopírujeme směrem dolů do buněk I3:I4. Tyto vzorce nám budou počítat zbývající kapacity.

Do buňky G5 zadáme vzorec =SUMA(G2:G4) a zkopírujeme do buněk H5:I5. Tyto vzorce nám budou sčítat součet, spotřebu a zbývající kapacity.

Do buňky H7 zadáme maticový vzorec {=SUMA(B7:F7*B9:F9)}, místo maticového vzorce můžeme použít vzorec ve tvaru =B7*B9+C7*C9+D7*D9+E7*E9+F7*F9

Maximální zisk

Potřebujeme zjistit, kolik kusů kterého dílu musíme vyrobit, abychom měli co největší zisk. Přes panel nabídek Nástroje – Řešitel vyvoláme dialogové okno Parametry Řešitele

Nastavení Řešitele:

Nastavit buňku: Vybereme buňku H7, ve které je vzorec na součet zisku v závislosti na počtu vyrobených kusů.

Rovno: Vybereme Max, protože chceme maximální zisk

Měněné buňky: Vybereme oblast buněk B9:F9, ve které jsou počty kusů jednotlivých dílů.

Omezující podmínky:

B9:F9<=B11:F11 – tato podmínka nám zajistí výrobu max. počtu kusů dle zadaných hodnot v oblasti B11:F11

B9:F9 = celé_číslo – tato podmínka nám zajistí výrobu pouze celých kusů. (určitě nechceme vyrobit 5,26 kusů)

B9:F9>=B10:F10 – tato podmínka nám zajistí výrobu minimálního počtu kusů dle zadaných hodnot v oblasti B10:F10. Pokud máme v oblasti nastaveny samé nuly, můžeme místo této podmínky zadat podmínku B9:F9>= 0, nebo lépe v nastavení Možnosti Řešitele zatrhnout Nezáporná čísla. (nechceme vyrábět záporné množství kusů)

H2:H4<=G2:G4 – tato podmínka nám zajistí výrobu takového množství kusů, aby množství potřebných kapacit nepřesáhlo dostupné kapacity.

Popis Možnosti Řešitele:

• Maximální čas – čas, po jehož uplynutí je výpočet přerušen (výchozí čas je 100 sekund)
• Iterace – maximální počet iterací, po dosažení limitu se výpočet zastaví (výchozí počet iterací je 100)
• Přesnost – Přesnost výpočtu cílové buňky. Čím vyšší je přesnost, tím déle trvá řešení (výchozí přesnost je 0.000001)
• Tolerance – představuje v procentech odchylku vypočítané hodnoty cílové buňky od požadované. Používá se u problémů s celočíselnými omezujícími podmínkami (výchozí tolerance je 5%)
• Lineární model – zatrhneme při řešení úloh lineárního programování (ve výchozím stavu není tento přepínač zatrhnutý)
• Nezáporná čísla – zatrhnutím získáme řešení všech měnitelných buněk s nezápornými čísly (ve výchozím stavu není tento přepínač zatrhnutý)

Po nastavení parametrů a možností Řešitele klikneme na tlačítko Řešit a počkáme na okno Výsledky řešení, kde vidíme zprávu, zda bylo nalezeno optimální řešení, či zde je úloha neřešitelná. Pokud bylo nalezeno řešení, můžeme se rozhodnout, jestli chceme navrhované řešení uchovat, nebo obnovit původní hodnoty. Dále můžeme získat podrobnější informace o řešení úlohy.

Pokud máme celočíselné omezující podmínky, je vhodné snížit hodnotu pole Tolerance v dialogovém okně Možnosti Řešitele a umožnit tak Řešiteli nalézt vhodnější celočíselné řešení.

Výsledek při toleranci 5%:

Počet vyrobených kusů pro jednotlivé díly je 25,10,12,20,5. Zisk je ve výši 7430, spotřeba kapacit je 1673 a kapacit nám zbývá 27.

Výsledek při toleranci 1%:

Počet vyrobených kusů pro jednotlivé díly je 23,11,12,20,6. Zisk je ve výši 7480, spotřeba kapacit je 1692 a kapacit nám zbývá 8.

Minimální kapacity

Pokud potřebujeme zjistit, kolik kusů kterého dílů musíme vyrobit, abychom měli co nejmenší zbytek kapacit, tak v parametrech řešitele změníme nastavení na:

Nastavit buňku: Vyberme buňku I5, ve které je vzorec na součet zbývajících kapacit

Rovno: Vybereme Min, protože zbývající kapacity chceme co nejnižší.

Ostatní nastavení necháme stejné. Klikneme na Řešit.

Výsledek:

Počet vyrobených kusů pro jednotlivé díly je 16,16,10,18,10. Zisk je ve výši 7360, spotřeba kapacit je 1700 a kapacit nám zbývá 0.

Kombinace obou způsobů

Oba dva způsoby můžeme zkombinovat. U nastavení parametrů Řešitele pro maximální zisk přidáme podmínku I5<5 tj. zbývající kapacity musí být menší nebo rovno 5.

Počet vyrobených kusů pro jednotlivé díly je 25,16,10,18,4. Zisk je ve výši 7420, spotřeba kapacit je 1697 a kapacit nám zbývá 3.

Poznámky:

Několik příkladů a stručný přehled Řešitele je ve složce X:\Program Files\Microsoft Office\OfficeXX\Samples\SOLVSAMP.XLS

Řešení se ukládá se sešitem.

Po použití řešitele ztratíme možnost návratu zpět. Je proto vhodné pracovat na kopii sešitu.