Řešitel – základní použití
S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.
V tomto příspěvku je popsáno použití Řešitele. Je vysvětlené na jednoduchém příkladě, nicméně tuto funkcionalitu je možné používat i pro velmi sofistikované úlohy.
Příklad
Potřebuji vypočítat tento příklad:
- Firma vyrábí hračky – autíčka a vláčky. K výrobě potřebuje pouze dřevo a plastová kolečka.
- Na jedno autíčko spotřebuje 4 kolečka a 0,8 metru dřeva.
- Na jeden vláček spotřebuje 6 koleček a 0,4 metru dřeva.
- Na jednom vláčku utrží 180 Kč a na jednom autíčku 120 Kč.
- Pro příští týden má k dispozici 1000 koleček a 200 metrů dřevěného materiálu.
Co má firma vyrábět, aby maximalizovala tržby?
Návod
Použiji funkcionalitu (nechci používat slovo funkce, protože z hlediska logiky Excelu to není funkce) Řešitel, v anglických verzích Solver.
Než s ním budu pracovat, potřebuji si jej zprovoznit (pokud jsem ho ještě nepoužíval) a připravit si tabulku, se kterou budu pracovat.
Zprovoznění Řešitele
Soubor / Možnosti / Doplňky / Doplňky aplikace Excel / Přejít.
V navazujícím dialogu vybereme Řešitel.
Že je vše OK poznám podle toho, že ve volbě Data přibude vpravo karta Řešitel.
Řešení úlohy
Připravím si takovouto tabulku:
- Ve sloupci A mam vypsané suroviny, jichž mám omezené množství. Jejich množství nakonec omezí počet výrobků, které mohu vyprodukovat.
- Ve sloupečcích B a C jsou pak jejich množství, které chci použít na jednotlivé výrobky.
- Do sloupce E napíšu, kolik maximálně mohu těchto surovin použít.
- Logicky bude například platit, že počet koleček použitých na vláčky krát počet vláčků plus počet koleček použitých na autíčka krát počet autíček musí být nakonec menší než počet koleček, které mám k dispozici. To samé s dřevem. Mohl bych po jednotlivých buňkách násobit, šikovnější je ale použit funkci SOUČIN.SKALÁRNÍ / SUMPRODUCT.
- V pátém řádku mám napsáno, kolik utržím za jednotlivé produkty, v D4 je pak součet – a právě tuto buňku, celkové tržby, chci maximalizovat.
- Hodnoty v řádku 5, stejně jako hodnoty ve sloupečku D, chci zjistit – dozvím se, kolik čeho mám vyrábět.
Použití Řešitele
Otevřu řešitele a takto ho nakonfiguruji:
- Nastavit buňku
Vyberu D4. V této buňce mám hodnotu, u které chci dosáhnout co největší hodnotu – jsou v ní celkové tržby.
- Rovno:
Vyberu maximalizovat – jde o tržby. Alternativně je možné minimalizovat i cílovat na určitou hodnotu.
- Měněné buňky:
Vyberu B5 a C5. Excel bude tyto buňky tak dlouho měnit, dokud nedosáhne nejvyšších možných tržeb.
Omezující podmínky
Pomocí “Přidat” nastavím omezení, která se při optimalizaci nesmí překročit.
B5 a C5 musí být celá čisla – protože chci vyrábět hračky celé
B5 a C5 musí být kladné – protože nemohu vyrábět záporná množství výrobků
Buńky ve sloupečku D musí být menší než odpovídající buňky ve sloupečku E – protože materiál spotřebovaný celkem musí být menší než ten, co mám k dispozici
- Kliknu na Řešit a Excel spočítá optimální kombinaci vyrobených hraček.
- V našem případě asi doplní do buněk B5 a C5 hodnoty 115 a 77 – největší tržby tedy budu mít při výrobě 115 vláčků a 77 autíček.
- Do sloupečku D dostanu počty spotřebovaného materiálu a skutečné tržby, kterých dosáhnu.
A to je všechno. Hotový příklad je tady:
StáhnoutS tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.
Ten příklad není domyšlený. Tržba na jedno kolečko je shodná pro vláčky i autíčka, takže za každé dva vláčky nebo za tři autíčka budu tržit stejně. Mohu začít u 250 autíček a žádného vláčku a skončit u 1 autíčka a 166 vláčků – pořád budu tržit 30000. Kolečka v těch případech spotřebuji vždy všechna a dřevo vždy postačí. Proč si Řešitel vybere zrovna (115;77) s tržbou 29940 (tedy horší řešení), je varující. Když nastavím 0% optimality, spočítá aspoň (114;79) s tržbou 30000. Jednotlivá řešení pro 30000 se liší spotřebou dřeva, ale to není kriteriální podmínka. Prostě by to chtělo lepší příklad.
OK, díky za komentář. Je fakt, že jsem si nevšiml toho, že se nechá logickou úvahou odvodit lepší výsledek než poskytne řešitel, zkusím zadání změnit. Na druhou stranu ale – řešitel občas najde místo globálního minima / maxima optimalizační funkce minimum / maximum jen lokální a pokud si chceme být výsledkem přiměřeně jisti, měli bychom vyzkoušet řešení z různých výchozích hodnot v měněných buňkách.
I když řešitel dodá v tomto případě výsledek v absolutních číslech, řešením (po přidání, třeba jen pomyslném, dalších omezujích podmínek) bývá pak vždy množina řešení. Nejlépe byste to viděli při grafickém znázornění.
Počítače takové úlohy zpracovávaly většinou Simplexovou metodou, ale přestože na to stačili i 16 bitové aplikace (většinou placené a dělané na zakázku), pochybuji že Excel by takový algoritmus obsahoval. Zájem o podobné analýzy a optimalizace v posledních 20-ti letech velmi upadl a dále upadá, lidé upřednostnili divoké a chybné odhady (prakticky ve všech oblastech lidského života).
Dobrý den, nechci Vám brát Váš skepticismus a nejsem v této oblasti odborník, ale simplexová metoda v Excelu samozřejmě je – stačí si ji vybrat v možnostech. Simplexově se nechá počítat i ručně úpravou matice, potřeba počítačového výkonu je proto téměř nulová a výsledky jednoznačné.
Má ale jeden velký háček, a sice že předpokládá lineární vztahy mezi proměnnými, což sice platí v tomto případu, ale v praxi často ne. Proto si v Excelu můžete vybrat také gradientní metodu (funkce nemusí být lineární, ale musí být hladká), a evoluční algoritmus (jakákoliv funkce)…
Více informací tady:
http://www.solver.com/press/backgrounder-genetic-and-evolutionary-algorithms-versus-classical-optimization
Tak to si sypu popel na hlavu a hned se jdu podívat do 2007. Doufám, že uvedená metoda je v Excelu teprve “chvíli” (nemuseli jsme “krást” ty 16-ti bitové aplikace). Určitě si nepamatuji že bysme někdy zkoušeli na výpočet použít Excel 97. No a musím se přiznat, že od doby post Office 2003 (v mezidobí jsem měl jen verzi 2000) mne při pohledu na pás karet opouští chuť vůbec s tou věcí pracovat, natož se zabývat něčím netriviálním či neznámým.
Vám pane děkuji za upozornění.