Jak používat hledání cílů v Excelu

Goal Seek je jedním z Excelových nástrojů What-if Analysis, který vám pomůže najít správnou vstupní hodnotu vzorce, abyste získali požadovaný výstup. Ukazuje, jak jedna hodnota ve vzorci ovlivňuje druhou. Jinými slovy, pokud máte cílovou hodnotu, které chcete dosáhnout, můžete pomocí hledání cíle najít správnou vstupní hodnotu pro její dosažení.

Řekněme například, že jste v předmětu získali celkem 75 bodů a potřebujete alespoň 90, abyste v tomto předmětu získali známku S. Naštěstí máte poslední test, který vám může pomoci zvýšit vaše průměrné skóre. Pomocí Goal Seek můžete zjistit, kolik skóre potřebujete v tomto závěrečném testu, abyste získali známku S.

Hledání cílů používá metodu pokus-omyl ke zpětnému sledování problému zadáváním odhadů, dokud nedojde ke správnému výsledku. Funkce Goal Seek dokáže najít nejlepší vstupní hodnotu pro vzorec během několika sekund, jejíž ruční zjištění by trvalo dlouho. V tomto článku vám na několika příkladech ukážeme, jak používat nástroj Hledání cílů v Excelu.

Komponenty funkce hledání cíle

Funkce hledání cíle se skládá ze tří parametrů:

  • Nastavit buňku – Toto je buňka, do které se zadává vzorec. Určuje buňku, ve které chcete požadovaný výstup.
  • Hodnotit – Toto je cílová / požadovaná hodnota, kterou požadujete jako výsledek operace hledání cíle.
  • Změnou buňky – Toto určuje buňku, jejíž hodnotu je třeba upravit, abyste získali požadovaný výstup.

Jak používat hledání cílů v Excelu: Příklad 1

Chcete-li demonstrovat, jak to funguje na jednoduchém příkladu, představte si, že provozujete stánek s ovocem. Na níže uvedeném snímku obrazovky buňka B11 (níže) zobrazuje, kolik vás stál nákup ovoce pro váš stánek, a buňka B12 zobrazuje váš celkový příjem z prodeje tohoto ovoce. A buňka B13 ukazuje procento vašeho zisku (20 %).

Pokud byste chtěli zvýšit svůj zisk na „30%“, ale nemůžete zvýšit svou investici, musíte zvýšit své příjmy, abyste získali zisk. Ale do kolika? Hledání cílů vám ho pomůže najít.

K výpočtu procenta zisku použijte v buňce B13 následující vzorec:

=(B12-B11)/B12

Nyní chcete vypočítat ziskovou marži tak, aby vaše procento zisku bylo 30 %. Můžete to udělat pomocí funkce Hledání cílů v Excelu.

První věc, kterou musíte udělat, je vybrat buňku, jejíž hodnotu chcete upravit. Při každém použití funkce Hledání cílů musíte vybrat buňku, která obsahuje vzorec nebo funkci. V našem případě vybereme buňku B13, protože obsahuje vzorec pro výpočet procenta.

Poté přejděte na kartu „Data“, klikněte na tlačítko „Analýza Co když“ ve skupině Prognóza a vyberte „Hledání cíle“.

Zobrazí se dialogové okno Hledání cíle se 3 poli:

  • Nastavit buňku – Zadejte odkaz na buňku obsahující vzorec (B13). Toto je buňka, která bude mít požadovaný výstup.
  • Hodnotit – Zadejte požadovaný výsledek, kterého se snažíte dosáhnout (30 %).
  • Změnou buňky – Vložte odkaz na buňku pro vstupní hodnotu, kterou chcete změnit (B12), abyste dosáhli požadovaného výsledku. Jednoduše klikněte na buňku nebo ručně zadejte odkaz na buňku. Když vyberete buňku, aplikace Excel přidá znak „$“ před písmeno sloupce a číslo řádku, aby se stala absolutní buňkou.

Až budete hotovi, klikněte na „OK“ a otestujte to.

Poté se objeví dialogové okno ‚Stav hledání cíle‘ a bude vás informovat, zda našlo nějaké řešení, jak je uvedeno níže. Pokud bylo nalezeno řešení, vstupní hodnota v „měnící se buňce (B12)“ bude upravena na novou hodnotu. To je to, co chceme. V tomto příkladu tedy analýza určila, že abyste dosáhli svého cíle 30% zisku, musíte dosáhnout výnosu 1635,5 USD (B12).

Klikněte na „OK“ a Excel změní hodnoty buněk nebo klikněte na „Zrušit“ pro zrušení řešení a obnovení původní hodnoty.

Vstupní hodnota (1635,5) v buňce B12 je to, co jsme zjistili pomocí hledání cíle, abychom dosáhli našeho cíle (30 %).

Na co si pamatovat při používání funkce Hledání cílů

  • Nastavit buňku musí vždy obsahovat vzorec, který je závislý na buňce ‚Změnou buňky‘.
  • Hledání cíle můžete použít pouze pro vstupní hodnotu jedné buňky najednou
  • Položka „Změnou buňky“ musí obsahovat hodnotu, nikoli vzorec.
  • Pokud funkce Hledání cílů nemůže najít správné řešení, zobrazí nejbližší hodnotu, kterou může vytvořit, a sdělí vám, že zpráva „Hledání cílů možná nenalezla řešení“.

Co dělat, když aplikace Excel Goal Search nefunguje

Pokud jste si však jisti, že existuje řešení, existuje několik věcí, které můžete zkusit tento problém vyřešit.

Zkontrolujte parametry a hodnoty hledání cíle

Měli byste zkontrolovat dvě věci: nejprve zkontrolujte, zda parametr ‚Nastavit buňku‘ odkazuje na buňku vzorce. Za druhé se ujistěte, že buňka vzorce (Nastavit buňku) přímo nebo nepřímo závisí na měnící se buňce.

Úprava nastavení iterace

V nastavení Excelu můžete změnit počet možných pokusů, které může Excel provést, aby našel správné řešení, a také jeho přesnost.

Chcete-li změnit nastavení výpočtu iterace, klikněte na „Soubor“ v seznamu karet. Poté klikněte na „Možnosti“ v dolní části.

V okně Možnosti aplikace Excel klikněte v levém podokně na „Vzorce“.

V části Možnosti výpočtu změňte tato nastavení:

  • Maximální iterace – Označuje počet možných řešení, která Excel vypočítá; čím vyšší číslo, tím více iterací může provést. Pokud například nastavíte „Maximální počet iterací“ na 150, Excel otestuje 150 možných řešení.
  • Maximální změna – Označuje přesnost výsledků; menší číslo poskytuje vyšší přesnost. Pokud se například hodnota vaší vstupní buňky rovná „0“, ale funkce Hledání cíle se přestane počítat na hodnotě „0,001“, změna této hodnoty na „0,0001“ by měla problém vyřešit.

Zvyšte hodnotu „Maximální počet iterací“, pokud chcete, aby Excel testoval více možných řešení, a snižte hodnotu „Maximální změna“, pokud chcete přesnější výsledek.

Snímek obrazovky níže ukazuje výchozí hodnotu:

Žádné kruhové odkazy

Když vzorec odkazuje zpět na svou vlastní buňku, nazývá se to kruhový odkaz. Pokud chcete, aby aplikace Excel Goal Seek fungovala správně, vzorce by neměly používat kruhový odkaz.

Příklad hledání cílů v Excelu 2

Řekněme, že si od někoho půjčujete peníze „25 000 $“. Půjčí vám peníze s úrokovou sazbou 7 % měsíčně po dobu 20 měsíců, což znamená splácení „1327 $“ měsíčně. Ale můžete si dovolit platit „1 000 $“ měsíčně po dobu 20 měsíců se 7% úrokem. Goal Seek vám může pomoci najít částku půjčky, která produkuje měsíční splátku (EMI) „1000 $“.

Zadejte tři vstupní proměnné, které budete potřebovat k výpočtu výpočtu PMT, tj. úrokovou sazbu 7 %, dobu trvání 20 měsíců a částku jistiny 25 000 USD.

Zadejte následující vzorec PMT do buňky B5, což vám dá částku EMI ve výši 1 327,97 $.

Syntaxe funkce PMT:

=PMT(úroková sazba/12, období, jistina)

vzorec:

=PMT(B3/12;B4;-B2)

Vyberte buňku B5 (buňku vzorce) a přejděte na Data –> Analýza Co když –> Hledání cíle.

V okně „Hledání cíle“ použijte tyto parametry:

  • Nastavit buňku – B5 (buňka, která obsahuje vzorec pro výpočet EMI)
  • Hodnotit – 1000 (výsledek vzorce/cíl, který hledáte)
  • Změnou buňky – B2 (toto je částka půjčky, kterou chcete změnit, abyste dosáhli cílové hodnoty)

Poté klikněte na ‚OK‘, chcete-li nalezené řešení zachovat, nebo ‚Zrušit‘, chcete-li jej zahodit.

Analýza vám říká, že maximální částka peněz, kterou si můžete půjčit, je 18 825 USD, pokud chcete překročit svůj rozpočet.

Takto používáte Hledání cílů v Excelu.