Jak používat hledání cílů v Excelu (s příklady ze skutečného života)

Pokud znáte výsledek vzorce, ale neznáte vstupní hodnotu, která výsledek vygeneruje, Microsoft Excel vám pomůže. Jednoduše se naučte používat Goal Seek v Excelu a provádějte simulace vzorců a velkých číselných dat beze změny skutečných dat.

Ať už se jedná o vaše osobní finance, spořící investice nebo obchodní projekt, informované rozhodování je klíčem k úspěchu. Pro informované rozhodování potřebujete simulovaná data ze simulace databází na základě vzorce a vašeho cíle. Excel Goal Seek je nástroj pro takovou simulaci založenou na datech.

Pokud s Excelem začínáte nebo máte nějaké zkušenosti s jeho používáním, ale nejste odborník, čtěte dále, abyste se naučili základy hledání cílů v Excelu a jak používat funkci hledání cílů v aplikaci Excel.

Co je hledání cílů v Excelu?

Goal Seek je vestavěný nástroj What-If Analysis v Excelu. Ukazuje, jak konečná hodnota ve vzorci ovlivňuje vstupní hodnotu. Jednoduše řečeno, řekne vám vstupní hodnotu pro vzorec, abyste získali požadovaný výsledek ze stejného vzorce.

Čtěte také:  Povolení/zakázání zobrazení vzorců v buňkách v Excelu

Finanční investoři, finanční manažeři, majitelé firem a vedoucí pracovníci hojně využívají tento nástroj Excel What-If Analysis k předpovídání vstupu pro požadovaný výsledek z jakéhokoli matematického modelu.

Hledání cílů v Excelu můžete také nazvat automatizovaným simulačním systémem, který analyzuje váš vzorec spojený s libovolnou velikostí datových sad a sdělí vám vstupní hodnotu. Pokud zadáte vstupní hodnotu do svého matematického modelu, dostanete výsledek, který hledáte.

Tento nástroj můžete použít téměř v jakémkoli scénáři, ale hlavní jsou následující:

• Kolik jednotek produktů musíte prodat, abyste dosáhli konkrétního příjmu s přidanými slevami a provizemi agentů
• Za jakou úrokovou sazbu si musíte půjčit, abyste mohli půjčku splatit v určitém období v konkrétních měsíčních splátkách
• Kolik pracovních hodin je potřeba k dokončení projektu v předem stanoveném termínu

Kde je hledání cílů v Excelu?

Excel Goal Seek je jedním ze tří nástrojů analýzy What-If aplikace Microsoft Excel. Nástroj tedy najdete v nabídce příkazů What-If Analysis. V nabídce pásu karet Excel klikněte na kartu Data a poté přejděte zcela vpravo na pás karet. Zde byste měli vidět sekci Předpověď. Vyberte tlačítko What-If Analysis a hledání cíle je číslo dvě v  rozevíracím seznamu .

Kde je hledání cílů v Excelu

Umístění nástroje je stejné v Excelu pro aplikaci Mac. V době psaní tohoto článku je tato funkce dostupná v následujících edicích aplikace Excel:

• Excel pro Microsoft 365 (Windows a Mac)
• Excel 2021 (Windows a Mac)
• K dispozici také pro Windows a Mac od Excelu 2010 a novějších

Požadavky na použití hledání cílů v Excelu

Excel Goal Seek provede všechny výpočty v backendu. Vše, co musíte udělat, je definovat následující tři parametry z vašeho matematického modelu nebo tabulky dat:

Komponenty a parametry pro Excel Goal Seek

• Do pole Nastavit buňku zadejte odkaz na buňku . Tato referenční buňka musí obsahovat vzorec. Excel Goal Seek používá tento vzorec k odvození vstupní hodnoty.
• Do pole Do hodnoty musíte zadat výsledek nebo cíl, kterého chcete dosáhnout .
• Buňka Změnou je odkaz na buňku, ve které potřebujete vstup pro matematický model, který simulujete.

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

Níže naleznete několik příkladů scénářů, kde můžete pomocí funkce Hledání cílů v Excelu předpovědět vstup pro požadovaný výstup z matematického vzorce. Postup je podobný pro zařízení se systémem Windows 11 i macOS.

1. Cílem hledání předpovědět skóre zkoušky

Řekněme, že potřebujete zrychlit své studijní plány v posledním semestru vysoké školy, abyste po absolvování dosáhli průměrného skóre 85 %. Zde je návod, jak můžete použít vzorec Hledání cílů k simulaci skóre potřebného v posledním semestru, pokud jste získali skóre z ostatních sedmi semestrů:

• Vytvořte záhlaví sloupců s názvem Semestr a Skóre na listu aplikace Excel.
• Ve sloupci Semestr zadejte Semestr I až Semestr VIII .
• Zadejte skóre všech sedmisemestrálních zkoušek kromě semestru VIII .
• Vytvořte průměrné skóre za semestr pomocí následujícího vzorce:

=PRŮMĚR (B2:B9)

• Upravte odkazy na buňky podle vlastního listu aplikace Excel.
• Nyní zavolejte funkci Hledání cíle .
• Buňka Set by měla být buňkou vzorce, kde jste vypočítali průměr.
• Buňka To value by měla být vaše požadované průměrné skóre za semestr, které je 85 %.
• Do pole Změnou buňky zadejte odkaz na prázdnou buňku pro skóre zkoušky Semestr VIII .

Zjistěte, jak používat hledání cíle k předpovídání skóre zkoušky

• Kliknutím na OK získáte skóre v semestru VIII, které si musíte zajistit pro průměrné skóre za absolvování 85 %.

Abyste dosáhli cílového skóre za promoce, musíte v posledním semestru dosáhnout 98 %.

Přečtěte si také:  Microsoft Excel: Jak snadno spravovat tabulky

2. Předvídejte výši úvěru pomocí hledání cíle

Předpokládejme, že si potřebujete půjčit kapitál na 40 let s pevným úrokem (8 %) s pevnou měsíční výplatou pro vaše podnikání. Nyní víte, že můžete platit 1 800 $ měsíčně z výnosů podniku. Nevíte však, kolik si musíte na výše uvedené období půjčit za výše uvedený úrok. Částku, kterou si chcete půjčit, můžete snadno vypočítat podle následujících kroků:

• Vytvořte řádky pro následující komponenty: Roční úroková sazba , Roky do splacení , Částka půjčky a Měsíční splátky .
• Zadejte příslušné hodnoty pro výše uvedené složky do příslušných buněk kromě Částky půjčky , kterou musíte vypočítat pomocí Hledání cíle.
• Nyní nahraďte hodnotu Měsíční splátky následujícím vzorcem PMT v Excelu:

=PMT(B1/12;B2*12;B3;0)

• Vyvolejte funkci Hledání cíle z Data > What-If Analysis > Goal Search .
• Nastavit buňku by měla odkazovat na buňku vzorce PMT.
• Hodnota by měla být 1 500 $.
• Změnou buňky by měla být hodnota Výše ​​půjčky .

Naučte se předvídat výši půjčky pomocí funkce Hledání cíle

• Kliknutím na OK simulujte výši půjčky pomocí iterativních výpočtů.

Podle funkce Goal Seek musí být výše půjčky 215 731 $, pokud si ji chcete půjčit na 40 let s 8% ročním úrokem a platit měsíční splátku 1 500 $.

3. Najděte cíl prodeje pomocí funkce Hledání cílů

Pokud jste vedoucí prodeje a marketingu nebo pracujete jako vedoucí prodeje a potřebujete vytvořit prodejní cíl, abyste dosáhli konkrétního výnosového cíle, je hledání cíle ideálním nástrojem. Vaše firma například aktuálně prodává 1 000 jednotek produktů.

Doporučená cena produktu je 50 USD a velkoobchodníkům také nabízíte slevu 10 %. Rádi byste však zvýšili cíl příjmů vaší společnosti z prodeje produktů na 75 000 USD, aniž byste změnili MSRP a diskontní sazbu. Kolik produktů pak musíte prodat? Pojďme to zjistit pomocí funkce Hledání cílů níže:

• Vytvořte řádky pro známé komponenty, jako jsou prodané jednotky , MSRP a Discount .
• Vypočítejte příjem pomocí následujícího vzorce:

=B2*(B3*(1-B4))

Naučte se najít prodejní cíl pomocí funkce Hledání cílů

• Nyní otevřete Hledání cílů a nastavte následující parametry:
  • Nastavit buňku : $ B $ 5
  • Hodnota : 75 000 USD
  • Změnou buňky : $B$2

Ujistěte se, že upravujete odkazy na buňky podle vlastních dat a listu aplikace Excel. Stiskněte OK a získáte prodejní cíl produktů, který je 1 667 jednotek se stejnou MSRP a diskontní sazbou.

Hledání cílů v Excelu: Nejčastější dotazy

Jak mohu použít hledání cílů v Excelu pro více buněk?

Hledání cílů v aplikaci Excel vám umožňuje simulovat vstup pro matematický vzorec, abyste získali předem určený výsledek. Bohužel nepodporuje žádné proměnné ani více buněk, které by se měly zohlednit. Pro více buněk a analýzu What-If založenou na variantách můžete použít doplněk Řešitel aplikace Excel.

Jaká je zkratka pro hledání cílů?

V současné době neexistuje žádná vyhrazená zkratka pro funkci Hledání cílů v Excelu. Můžete použít klávesu Alt v aplikaci Excel a poté stisknutím některých kláves na klávesnici vyvolat nástroj Hledání cílů bez použití myši. Kroky jsou uvedeny zde:

Press Alt on Excel > without clicking anywhere on the app press A > press W > press G to open Goal Seek

Jak dosáhnu toho, že hledání cíle bude přesnější?

Někdy může funkce Hledání cíle odvodit vstupní hodnotu v desetinných místech. Pokud potřebujete zaokrouhlenou postavu automaticky, můžete zvýšit její přesnost. Můžete to udělat takto:

• Klikněte na kartu Soubor a na bočním panelu vyberte Možnosti .
• V okně Možnosti aplikace Excel klikněte na možnost Vzorce .
• Zaškrtněte možnost Povolit iterativní výpočet .

Jak optimalizovat Excel Goal Seek

• Zvětšete pole Maximální počet iterací na 150 nebo více, aby Excel mohl testovat schůdnější řešení.
• Zvyšte také hodnotu Maximum Change z výchozí 0,001 na 0,00001.

Mohu hledat cíl na více buňkách?

V aplikaci Excel nemůžete provést hledání cíle pro více buněk.

Jaké jsou výhody hledání cíle?

Zde jsou výhody Goal Seek v Excelu:

• Hledání cílů je nejjednodušší dostupná simulace pro analýzu What-If v Excelu
• Dodává se s desktopovou aplikací Excel
• Goal Seek funguje v backendu a nevyžaduje úpravu celé databáze
• Užitečné pro finanční modelování

Jaká jsou omezení hledání cílů v Excelu?

Hledání cílů v Excelu má následující nevýhody:

• Hledání cíle nebude fungovat, pokud existují  kruhové odkazy
• Při provádění simulací hledání cíle nemůžete zadávat žádné proměnné
• Nefunguje na více buňkách
• Hledání cílů aplikace Excel není k dispozici v aplikaci Excel Web App nebo Excel pro webovou aplikaci

Excel Hledání cílů: Závěrečná slova

Přečtením výše uvedeného článku o Excel Goal Seek byste měli mít základní až střední znalosti o tom, jak provádět analýzu What-If v Excelu. Hledání cílů v Excelu je užitečné zejména v matematických úlohách, kde je k dispozici jasný vzorec, vstupní hodnota, několik proměnných spojených se vzorcem a jeden cíl, kterého má být dosaženo.

Níže zanechte svůj komentář ke své zkušenosti s výše uvedeným podrobným průvodcem Excel Goal Seek. Pokud znáte nějaké další techniky analýzy Excel What-If, dejte mi vědět.

Dále,  jak porovnat text v Excelu .