Definování a vyřešení problému pomocí Řešitele

V následujícím příkladu ovlivňují výdaje na reklamu v jednotlivých čtvrtletích počet prodaných výrobků a nepřímo tak určují výši příjmů z prodeje, související výdaje a zisk. Řešitel dokáže měnit výši čtvrtletních rozpočtů na reklamu (buňky rozhodovacích proměnných B5:C5) až do hodnoty celkového rozpočtu, který je omezen částkou 40 000 Kč (buňka F5), tak, aby celkový zisk (buňka cíle F7) dosáhl maximální hodnoty. Hodnoty proměnných buněk se používají při výpočtu zisku v jednotlivých čtvrtletích, takže souvisejí se vzorcem v buňce cíle F7, =SUMA (Zisk čtv1:Zisk čtv2).

1. Proměnné buňky

2. Buňka s omezující podmínkou

3. Buňka cíle

Po dokončení výpočtu získáte následující nové hodnoty.

1. Na kartě Data klikněte ve skupině Analýza na tlačítko Řešitel.
   

   Poznámka: Pokud není příkaz Řešitel nebo skupina Analýza k dispozici, je potřeba Doplněk aktivovat. Podívejte se, jak aktivovat doplněk Řešitel.

   

2. Do pole Nastavit cíl zadejte Odkaz na buňku nebo Název buňky cíle. Buňka cíle musí obsahovat vzorec.

3. Udělejte jednu z následujících věcí:

   • Chcete-li, aby buňka cíle obsahovala nejvyšší možnou hodnotu, klikněte na přepínač Maximum.

   • Chcete-li, aby buňka cíle obsahovala nejnižší možnou hodnotu, klikněte na přepínač Minimum.

   • Chcete-li, aby buňka cíle obsahovala určitou hodnotu, klikněte na přepínač Hodnota a zadejte hodnotu do příslušného pole.

   • Do pole Proměnné modelu zadejte název nebo odkaz na všechny oblasti buněk s rozhodovacími proměnnými. Nesousedící odkazy oddělte čárkami. Proměnné buňky musí mít přímý nebo nepřímý vztah k buňce cíle. Můžete zadat až 200 proměnných buněk.

4. Do pole Omezující podmínky zadejte omezení, která chcete uplatnit, následujícím způsobem:

   1. V dialogovém okně Parametry Řešitele klikněte na tlačítko Přidat.

   2. Do pole Odkaz na buňku zadejte odkaz na buňku nebo název oblasti buněk, ve které chcete omezit hodnotu.

   3. Klikněte na požadovanou relaci ( <=, =, >=, int, bin nebo dif ), kterou chcete použít mezi odkazovanou buňkou a omezením. Pokud kliknete na int, zobrazí se v poli Omezenícelé číslo. Pokud kliknete na přihrádku, zobrazí se v poli Omezeníbinární soubor. Pokud kliknete na dif, alldifferent se zobrazí v poli Omezení .

   4. Pokud v poli Omezující podmínka zvolíte položku <=, =, nebo >=, zadejte číslo, odkaz na buňku nebo název buňky, případně vzorec.

   5. Proveďte jeden z následujících kroků:

      • Chcete-li zadanou omezující podmínku potvrdit a přidat další, klikněte na tlačítko Přidat do seznamu.

      • Pokud chcete omezující podmínku potvrdit a vrátit se k dialogovému oknu Parametry Řešitele, klikněte na OK.
        Poznámka:    Vztahy celé, binární a odlišné můžete použít jenom u omezujících podmínek, které se vztahují k buňkám rozhodovacích proměnných.

        Existující omezení můžete změnit nebo odstranit následujícím způsobem:

   6. V dialogovém okně Parametry Řešitele klikněte na omezující podmínku, kterou chcete změnit nebo odstranit.

   7. Klikněte na tlačítko Změnit a proveďte požadované změny nebo klikněte na tlačítko Odstranit.

5. Klikněte na tlačítko Řešit a proveďte jednu z následujících akcí:

   • Chcete-li, aby získané hodnoty zůstaly na listu zobrazeny, klikněte v dialogovém okně Výsledky řešení na přepínač Uchovat řešení Řešitele.

   • Chcete-li před kliknutím na tlačítko Řešit obnovit původní hodnoty, klikněte na tlačítko Obnovit původní hodnoty.

   • Proces řešení můžete přerušit stisknutím klávesy Esc. Aplikace Excel přepočítá list s posledními hodnotami nalezenými pro buňky rozhodovacích proměnných.

   • Chcete-li vytvořit sestavu založenou na výsledku získaném pomocí Řešitele, můžete kliknout na typ sestavy v poli Sestavy a poté na tlačítko OK. Sestava se vytvoří na novém listu sešitu. Jestliže Řešitel nenalezne řešení, budou dostupné pouze určité sestavy, případně nebudou dostupné žádné sestavy.

   • Chcete-li hodnoty buněk rozhodovacích proměnných uložit jako scénář a později je opět zobrazit, klikněte na možnost Uložit scénář v dialogovém okně Výsledky řešitele a v poli Název scénáře zadejte název pro ukládaný scénář.

1. Po definování problému klikněte na položku Možnosti v dialogovém okně Parametry Řešitele.

2. Pokud chcete zobrazit hodnoty všech pokusných řešení, zaškrtněte v dialogovém okně Možnosti políčko Zobrazovat výsledky iterací a potom klikněte na tlačítko OK.

3. V dialogovém okně Parametry Řešitele klikněte na tlačítko Řešit.

4. V dialogovém okně Zobrazit pokusné řešení proveďte jeden z následujících kroků:

   • Kliknete-li na tlačítko Zastavit, bude proces řešení přerušen a zobrazí se dialogové okno Výsledky řešení.

   • Chcete-li v procesu řešení pokračovat a zobrazit další pokusné řešení, klikněte na tlačítko Pokračovat.

1. V dialogovém okně Parametry Řešitele klikněte na tlačítko Možnosti.

2. Zvolte nebo zadejte hodnoty možností na kartách Všechny metody, GRG Nonlinear a Evolutionary v dialogovém okně.

1. V dialogovém okně Parametry Řešitele klikněte na Načíst nebo uložit.

2. Zadejte oblast buněk pro oblast modelu a klikněte na tlačítko Uložit nebo Načíst.

   Při ukládání modelu zadejte odkaz na první buňku svislé oblasti prázdných buněk, do které chcete model problému umístit. Při načítání modelu zadejte odkaz na celou oblast buněk obsahující model problému.

   Tip: Když sešit uložíte, uloží se kromě listu i poslední hodnoty vybrané v dialogovém okně Parametry Řešitele. Na každém listu v sešitu může mít Řešitel vlastní výběry a všechny se uloží. Na jednom listu také můžete definovat několik problémů, pokud je po jednom uložíte kliknutím na Načíst nebo uložit.

V dialogovém okně Parametry Řešitele můžete vybrat libovolný z těchto tří algoritmů nebo metod řešení:

• GRG Nonlinear (Generalized Reduced Gradient):    Tato metoda slouží k řešení hladce nelineárních problémů.

• LP Simplex:    Tato metoda slouží k řešení lineárních problémů.

• Evolutionary:    Tato metoda slouží k řešení jiných než hladkých problémů.

V následujícím příkladu výdaje na reklamu ovlivní počet prodaných výrobků a nepřímo ovlivní i výši tržeb, vynaložené náklady a zisk. Řešitel dokáže změnit čtvrtletní rozpočet na reklamu (buňky rozhodovacích proměnných B5:C5) až do výše celkového rozpočtu omezeného celkovou částkou 20 000 USD (buňka D5) tak, aby celkový zisk (buňka cíle D7) dosáhl nejvyšší možné hodnoty. Hodnoty proměnných buněk se používají k výpočtu zisku za každé čtvrtletí, a proto jsou propojené se vzorcem cíle v buňce D7, =SUMA(Zisk čtv1: Zisk čtv2).

proměnných buněk

omezená buňka

Účelová funkce

Po spuštění Řešitele získáte následující nové hodnoty.

1. Klikněte na Řešitel dat >.

   

2. Do pole Nastavit cíl zadejte Odkaz na buňku nebo název buňky cíle.

   Poznámka: Účelová funkce musí obsahovat vzorec.

3. Udělejte něco z tohoto:

   Akce	Akce
   Maximalizovat hodnotu účelové funkce	Klikněte na Max.
   Minimalizovat hodnotu účelové funkce	Klikněte na Min.
   Nastavit účelovou funkci na určitou hodnotu	Klikněte na Hodnota a zadejte do pole hodnotu.

4. Do pole Proměnné modelu zadejte název nebo odkaz na všechny oblasti buněk s rozhodovacími proměnnými. K oddělení nesousedících odkazů použijte čárky.

   Proměnné buňky musí přímo nebo nepřímo souviset s účelovou funkcí. Můžete zadat až 200 proměnných buněk.

5. Do pole Omezující podmínky zadejte omezení, která chcete použít.

   Pokud chcete přidat omezení, postupujte takto:

   1. V dialogovém okně Parametry Řešitele klikněte na tlačítko Přidat.

   2. Do pole Odkaz na buňku zadejte odkaz na buňku nebo název oblasti buněk, ve které chcete omezit hodnotu.

   3. V místní nabídce <= vyberte vztah, který má být mezi odkazovanou buňkou a omezením. Pokud v poli Omezení vyberete <=, = nebo >=, zadejte číslo, odkaz na buňku nebo její název, případně vzorec.

      Poznámka: V omezeních buněk s rozhodovacími proměnnými můžete použít jenom tyto vztahy: celé, binární a různé.

   4. Udělejte něco z tohoto:

   Akce	Akce
   Potvrdit omezení a přidat další	Klikněte na Přidat.
   Potvrdit omezení a vrátit se do dialogového okna Parametry Řešitele	Klikněte na OK.

6. Klikněte na Řešit a proveďte jednu z následujících akcí:

   Co chcete udělat	Akce
   Nechat na listu vyřešené hodnoty	V dialogovém okně Výsledky Řešitele klikněte na Uchovat řešení Řešitele.
   Obnovit původní data	Klikněte na Obnovit původní hodnoty.

1. Klikněte na Řešitel dat >.

   

2. Jakmile v dialogovém okně Parametry Řešitele nadefinujete problém, klikněte na Možnosti.

3. Pokud chcete zobrazovat hodnoty všech zkušebních řešení, zaškrtněte políčko Zobrazovat výsledky iterací a pak klikněte na OK.

4. V dialogovém okně Parametry Řešitele klikněte na Řešit.

5. V dialogovém okně Zobrazit pokusné řešení proveďte jeden z následujících kroků:

   Co chcete udělat	Akce
   Zastavit proces řešení a zobrazit dialogové okno Výsledky Řešitele.	Klikněte na Zastavit.
   Pokračovat v procesu řešení a zobrazit další zkušební řešení	Klikněte na Pokračovat.

1. Klikněte na Řešitel dat >.

   

2. Klikněte na Možnosti a v dialogovém okně Možnosti nebo Možnosti Řešitele zvolte některé z těchto možností:

   Co chcete udělat	Akce
   Nastavit dobu řešení a iterace	Na kartě Všechny metody zadejte v části Omezení řešení do pole Maximální čas (sekundy) počet sekund povolených pro hledání řešení. Do pole Iterace zadejte maximální počet iterací, které chcete povolit. Poznámka: Pokud proces hledání řešení dosáhne maximálního času nebo počtu iterací ještě předtím, než Řešitel najde řešení, zobrazí se v Řešiteli dialogové okno Zobrazit pokusné řešení.
   Nastavit stupeň přesnosti	Na kartě Všechny metody zadejte do pole Přesnost omezující podmínky požadovaný stupeň přesnosti. Čím menší číslo, tím větší přesnost.
   Nastavit stupeň konvergence	Na kartě Gradientní metoda nebo Evoluční algoritmus zadejte do pole Konvergence hodnotu relativní změny, kterou chcete povolit v posledních pěti iteracích, než Řešitel ukončí řešení. Menší číslo odpovídá menší povolené relativní změně.

3. Klikněte na OK.

4. V dialogovém okně Parametry Řešitele klikněte na Řešit nebo Zavřít.

1. Klikněte na Řešitel dat >.

   

2. Klikněte na Načíst nebo uložit, zadejte oblast buněk, která odpovídá oblasti modelu, a klikněte na Uložit nebo Načíst.

   Při ukládání modelu zadejte odkaz na první buňku svislé oblasti prázdných buněk, do které chcete model problému umístit. Při načítání modelu zadejte odkaz na celou oblast buněk obsahující model problému.

   Tip: Když sešit uložíte, uloží se kromě listu i poslední hodnoty vybrané v dialogovém okně Parametry Řešitele. Na každém listu v sešitu může mít Řešitel vlastní výběry a všechny se uloží. Na jednom listu také můžete definovat několik problémů, pokud je po jednom uložíte kliknutím na Načíst nebo uložit.

1. Klikněte na Řešitel dat >.

   

2. V rozevírací nabídce Vyberte metodu řešení vyberte jednu z těchto možností: