Hledání cílů, součást sady nástrojů aplikace Excel pro analýzu co-když, je výkonná funkce používaná pro zpětné výpočty. V zásadě platí, že pokud znáte požadovaný výsledek vzorce, ale nejste si jisti, jaká vstupní hodnota tento výsledek vytvoří, můžete jej najít pomocí funkce Hledání cíle. Ať už jste finanční analytik, student nebo majitel firmy, pochopení toho, jak používat Goal Seek, může výrazně zefektivnit váš proces řešení problémů. V této příručce prozkoumáme, co je Goal Seek, jak k němu přistupovat, a předvedeme jeho praktické aplikace na různých příkladech, od úpravy splátkových kalendářů až po výpočet minimálního skóre pro zkoušky a další.

• Příklad 1: Úprava plánu splátek úvěru
• Příklad 2: Stanovení minimálního skóre pro složení zkoušky
• Příklad 3: Výpočet potřebných hlasů pro volební vítězství
• Příklad 4: Dosažení cílového zisku v podnikání

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

Hledání cíle je nedílnou součástí aplikace Microsoft Excel, která je součástí sady nástrojů pro analýzu typu what-if. Umožňuje uživatelům provádět zpětné výpočty – namísto výpočtu výsledku daných vstupů určíte požadovaný výsledek a Excel upraví vstupní hodnotu tak, aby tohoto cíle dosáhla. Tato funkce je užitečná zejména ve scénářích, kdy potřebujete najít vstupní hodnotu, která vytvoří konkrétní výsledek.

• Nastavení jedné proměnné:
  Hledání cíle může změnit pouze jednu vstupní hodnotu buňky najednou.
• Vyžaduje vzorec:
  Při použití funkce Hledání cíle musí cílová buňka obsahovat vzorec.
• Udržujte integritu vzorce:
  Hledání cílů nemění vzorec v cílové buňce; změní hodnotu ve vstupní buňce, na které vzorec závisí.

Přístup k hledání cílů v Excelu

Chcete-li získat přístup k funkci Hledání cíle, přejděte na Data klepněte na kartu Analýza What-If > Hledání cíle. Viz snímek obrazovky:

potom se Hledání cíle objeví se dialogové okno. Zde je vysvětlení tří možností v dialogovém okně Hledání cíle aplikace Excel:

• Nastavit buňku: Cílová buňka obsahující vzorec, kterého chcete dosáhnout. Tato buňka musí obsahovat vzorec a funkce Hledání cíle upraví hodnoty v jiné související buňce, aby hodnota této buňky dosáhla vašeho nastaveného cíle.
• Hodnotit: Cílová hodnota, kterou chcete "Nastavit buňku" dosáhnout.
• Změnou buňky: Vstupní buňka, kterou má funkce Hledání cíle upravit. Hodnota v této buňce bude změněna, aby byla zajištěna hodnota v "Nastavit buňku"splňuje"Hodnotit" cílová.
  
  

V další části na podrobných příkladech prozkoumáme, jak využít funkci hledání cílů aplikace Excel.

Použití hledání cílů s příklady

V této části si projdeme čtyři běžné příklady, které demonstrují užitečnost hledání cílů. Tyto příklady budou sahat od úprav osobních financí až po strategická obchodní rozhodnutí, které nabízejí pohled na to, jak lze tento nástroj použít v různých scénářích reálného světa. Každý příklad je navržen tak, aby vám lépe porozuměl tomu, jak efektivně používat Hledání cílů k řešení různých typů problémů.

Příklad 1: Úprava plánu splátek úvěru

Scénář: Osoba si plánuje vzít půjčku ve výši 20,000 5 $ a chce ji splatit za 5 let s roční úrokovou sazbou 377.42 %. Požadovaná měsíční splátka je 500 $. Později si uvědomí, že může zvýšit svou měsíční splátku o XNUMX dolarů. Vzhledem k tomu, že celková výše úvěru a roční úroková sazba zůstávají stejné, kolik let bude nyní trvat splacení úvěru?

Jak ukazuje následující tabulka:

• B1 obsahuje částku půjčky 20000 XNUMX $.
• B2 obsahuje výpůjční lhůtu 5 (let).
• B3 obsahuje roční úrokovou sazbu 5 %.
• B5 obsahuje měsíční splátku, která se vypočítá podle vzorce =PMT(B3/12,B2*12,B1).
  
  

Zde vám ukážu, jak ke splnění tohoto úkolu použít funkci Hledání cíle.

Krok 1: Aktivujte funkci Hledání cíle

Přejít na Data klepněte na kartu Analýza What-If > Hledání cíle.

Krok 2: Nakonfigurujte nastavení hledání cíle

1. v Nastavit buňku vyberte vzorec B5.
2. v Vážit si zadejte měsíční plánovanou výši splátek -500 (záporné číslo představuje platbu).
3. v Změnou buňky vyberte buňku B2, kterou chcete upravit.
4. klikněte OK.
   
   

Výsledek

Projekt Stav hledání cíle Poté se objeví dialogové okno, které ukazuje, že bylo nalezeno řešení. Současně bude hodnota v buňce B2, kterou jste zadali v poli „Změnou buňky“, nahrazena novou hodnotou a buňka vzorce získá cílovou hodnotu na základě změny proměnné. Klikněte OK použít změny

Nyní mu bude splacení půjčky trvat asi 3.7 roku.

Příklad 2: Stanovení minimálního skóre pro složení zkoušky

Scénář: Student musí absolvovat 5 zkoušek, z nichž každá má stejnou váhu. Aby student uspěl, musí dosáhnout průměrného skóre 70 % ve všech zkouškách. Po dokončení prvních 4 zkoušek a znalosti jejich skóre nyní musí student vypočítat minimální skóre požadované u páté zkoušky, aby zajistil, že celkový průměr dosáhne nebo překročí 70 %.

Jak je znázorněno na snímku obrazovky níže:

• B1 obsahuje skóre z první zkoušky.
• B2 obsahuje skóre druhé zkoušky.
• B3 obsahuje skóre třetí zkoušky.
• B4 obsahuje skóre čtvrté zkoušky.
• B5 bude obsahovat skóre páté zkoušky.
• B7 je průměrné skóre pro úspěšné složení, které se vypočítá podle vzorce =(B1+B2+B3+B4+B5)/5.
  
  

K dosažení tohoto cíle můžete použít funkci Hledání cíle následovně:

Krok 1: Aktivujte funkci Hledání cíle

Přejít na Data klepněte na kartu Analýza What-If > Hledání cíle.

Krok 2: Nakonfigurujte nastavení hledání cíle

1. v Nastavit buňku vyberte buňku vzorce B7.
2. v Vážit si do pole zadejte průběžné průměrné skóre 70 %.
3. v Změnou buňky vyberte buňku (B5), kterou chcete upravit.
4. klikněte OK.
   
   

Výsledek

Projekt Stav hledání cíle Poté se objeví dialogové okno, které ukazuje, že bylo nalezeno řešení. Současně se do buňky B7, kterou jste zadali v poli „Změnou buňky“, automaticky zadá odpovídající hodnota a buňka vzorce získá cílovou hodnotu na základě změny proměnné. Klikněte OK přijmout řešení.

K dosažení požadovaného celkového průměru a složení všech zkoušek tedy student potřebuje u poslední zkoušky dosáhnout alespoň 71 %.

Příklad 3: Výpočet potřebných hlasů pro volební vítězství

Scénář: Při volbách musí kandidát vypočítat minimální počet hlasů potřebný k získání většiny. Když je znám celkový počet odevzdaných hlasů, jaký minimální počet hlasů musí kandidát získat, aby dosáhl více než 50 % a vyhrál?

Jak je znázorněno na snímku obrazovky níže:

• Buňka B2 obsahuje celkový počet odevzdaných hlasů ve volbách.
• V buňce B3 je uveden aktuální počet hlasů, které kandidát obdržel.
• Buňka B4 obsahuje požadovanou většinu (%) hlasů, kterých chce kandidát dosáhnout a která se vypočítá podle vzorce =B2/B1.
  
  

K dosažení tohoto cíle můžete použít funkci Hledání cíle následovně:

Krok 1: Aktivujte funkci Hledání cíle

Přejít na Data klepněte na kartu Analýza What-If > Hledání cíle.

Krok 2: Nakonfigurujte nastavení hledání cíle

1. v Nastavit buňku vyberte buňku vzorce B4.
2. v Vážit si zadejte požadované procento většiny hlasů. V tomto případě musí kandidát pro vítězství ve volbách získat více než polovinu (tedy přes 50 %) z celkového počtu platných hlasů, zadávám tedy 51 %.
3. v Změnou buňky vyberte buňku (B2), kterou chcete upravit.
4. klikněte OK.
   
   

Výsledek

Projekt Stav hledání cíle Poté se objeví dialogové okno, které ukazuje, že bylo nalezeno řešení. Současně bude buňka B2, kterou jste zadali v poli „Změnou buňky“, automaticky nahrazena novou hodnotou a buňka vzorce získá cílovou hodnotu na základě změny proměnné. Klikněte OK použít změny.

Kandidát tedy potřebuje k vítězství ve volbách alespoň 5100 hlasů.

Příklad 4: Dosažení cílového zisku v podnikání

Scénář: Společnost si klade za cíl dosáhnout konkrétního cíle zisku ve výši 150,000 XNUMX USD za běžný rok. S ohledem na fixní i variabilní náklady musí určit potřebné tržby z prodeje. Kolik jednotek prodeje je potřeba k dosažení tohoto cíle zisku?

Jak je znázorněno na snímku obrazovky níže:

• B1 obsahuje fixní náklady.
• B2 obsahuje variabilní náklady na jednotku.
• B3 obsahuje cenu za jednotku.
• B4 obsahuje prodané jednotky.
• B6 je celkový příjem, který se vypočítá podle vzorce =B3*B4.
• B7 jsou celkové náklady, které se vypočítají podle vzorce =B1+(B2*B4).
• B9 je aktuální zisk z prodeje, který se vypočítá podle vzorce =B6-B7.
  
  

Chcete-li získat celkové jednotky prodeje na základě cílového zisku, můžete použít funkci Hledání cíle následovně.

Krok 1: Aktivujte funkci Hledání cíle

Přejít na Data klepněte na kartu Analýza What-If > Hledání cíle.

Krok 2: Nakonfigurujte nastavení hledání cíle

1. v Nastavit buňku vyberte buňku vzorce, která vrací zisk. Zde je buňka B9.
2. v Vážit si zadejte cílový zisk 150000 XNUMX.
3. v Změnou buňky vyberte buňku (B4), kterou chcete upravit.
4. klikněte OK.
   
   

Výsledek

Poté se zobrazí dialogové okno Stav hledání cíle, které ukazuje, že bylo nalezeno řešení. Současně bude buňka B4, kterou jste zadali v poli "Změnou buňky", automaticky nahrazena novou hodnotou a buňka vzorce získá cílovou hodnotu na základě změny proměnné. Klikněte OK přijmout změny.

V důsledku toho společnost potřebuje prodat alespoň 6666 jednotek, aby dosáhla cílového zisku 150,000 XNUMX USD.

Běžné problémy a řešení pro hledání cílů

Tato část uvádí některé běžné problémy a odpovídající řešení pro hledání cílů.

1. Hledání cílů nemůže najít řešení

• Důvod: K tomu obvykle dochází, když cílová hodnota není dosažitelná s žádnou možnou vstupní hodnotou nebo pokud je počáteční odhad příliš vzdálen od jakéhokoli potenciálního řešení.
• Řešení: Upravte cílovou hodnotu tak, aby byla v přijatelném rozsahu. Vyzkoušejte také různé počáteční odhady blíže k očekávanému řešení. Ujistěte se, že vzorec v "Nastavit buňku" je správný a může logicky vytvořit požadovanou hodnotu "To Value".

2. Pomalá rychlost výpočtu

• Důvod: Hledání cíle může být pomalé u velkých datových sad, složitých vzorců, nebo když je počáteční odhad daleko od řešení, což vyžaduje více iterací.
• Řešení: Kde je to možné, zjednodušte vzorce a zmenšete velikost dat. Ujistěte se, že počáteční odhad je co nejblíže očekávanému řešení, abyste snížili počet potřebných iterací.

3. Problémy s přesností

• Důvod: Při používání hledání cílů si můžete všimnout, že někdy Excel řekne, že našel řešení, ale není to přesně to, co chcete – blízko, ale ne dostatečně blízko. Hledání cílů nemusí vždy poskytovat vysoce přesný výsledek kvůli přesnosti výpočtu a zaokrouhlování aplikace Excel.
  
  Chcete-li například vypočítat konkrétní umocnění čísla a pomocí funkce Hledání cílů najít základnu, která dosáhne požadovaného výsledku. Zde změním vstupní hodnotu v buňce A1 (Základ) tak, aby buňka A3 (výsledek umocnění) odpovídala cílovému výsledku 25.
  
  Jak můžete vidět na níže uvedeném snímku obrazovky, hledání cíle poskytuje spíše přibližnou hodnotu než přesný kořen.
  
• Řešení: Zvyšte počet desetinných míst zobrazených v možnostech výpočtu aplikace Excel pro větší přesnost a poté znovu spusťte funkci Hledání cíle. Postupujte prosím následovně.
  
  1. klikněte Soubor > možnosti k otevření Možnosti aplikace Excel okno.
  2. vybrat Vzorce v levém podokně a v Možnosti výpočtu zvyšte počet desetinných míst v Maximální změna box. Tady se měním 0.001 na 0.000000001 a klepněte na tlačítko OK.
     
  3. Znovu spusťte hledání cíle a získáte přesný kořen, jak je znázorněno na obrázku níže.
     

4. Omezení a alternativní metody

• Důvod: Hledání cíle může upravit pouze jednu vstupní hodnotu a nemusí být vhodné pro rovnice vyžadující současné úpravy více proměnných.
• Řešení: Pro scénáře vyžadující úpravy více proměnných použijte Řešitel Excelu, který je výkonnější a umožňuje nastavení omezení.

Hledání cíle je výkonný nástroj pro provádění zpětných výpočtů, který vás ušetří od zdlouhavého ručního testování různých hodnot, abyste se přiblížili k cíli. Vybaveni statistikami z tohoto článku můžete nyní s důvěrou používat hledání cílů, čímž posouváte analýzu dat a efektivitu do nových výšin. Pro ty, kteří se chtějí hlouběji ponořit do možností Excelu, se náš web může pochlubit velkým množstvím výukových programů. Zde najdete další tipy a triky pro Excel.