Jak používat hledání cílů v aplikaci Microsoft Excel pro scénáře What-If

Jak používat hledání cílů v aplikaci Microsoft Excel pro scénáře What-If

Excel's What-If Analysis vám umožňuje zjistit, jak změna buňky může ovlivnit výstup vzorce. K výpočtu účinku změny hodnoty buňky ve vzorci můžete k tomuto účelu použít nástroje Excelu.





Excel má tři typy nástrojů What-If Analysis: Scenario Manager, Goal Seek a Data Table. Pomocí funkce Hledání cíle můžete určit, jaký vstup potřebujete k převodu vzorce zpět na určitý výstup.



Funkce Hledání cíle v aplikaci Excel je pokus -omyl, takže pokud neprodukuje to, co chcete, pak Excel pracuje na zlepšování této hodnoty, dokud to neudělá.





Jaké jsou vzorce pro hledání cílů aplikace Excel?

Hledání cílů v aplikaci Excel se v zásadě dělí na tři hlavní součásti:

  1. Nastavit buňku : Buňka, kterou chcete použít jako cíl.
  2. Vážit si : Hodnota, kterou chcete jako cíl.
  3. Změnou buňky : Buňka, kterou chcete změnit, abyste dosáhli hodnoty cíle.

S těmito třemi nastavenými nastaveními se Excel pokusí zlepšit hodnotu v buňce, kterou jste nastavili Změnou buňky do buňky, kterou jste nastavili Nastavit buňku dosáhne hodnoty, kterou jste určili Vážit si .



Pokud to všechno zní matoucí, níže uvedené příklady vám poskytnou dobrou představu o tom, jak funguje hledání cílů.

Příklad hledání cíle 1

Řekněme například, že máte dvě buňky (A & B) a třetí buňku, která vypočítá průměr těchto dvou.



Předpokládejme nyní, že máte statickou hodnotu pro A a chcete zvýšit průměrnou úroveň změnou hodnoty pro B. Pomocí funkce Hledání cíle můžete vypočítat, jaká hodnota B vytvoří průměr, který chcete.

Související: Tipy, jak ušetřit čas v aplikaci Microsoft Excel



chyba systémové výjimky Windows 10

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

  1. V Excelu klikněte na buňku C1 .
  2. Na řádku vzorců zadejte následující kód: | _+_ | Tento vzorec nechá Excel vypočítat průměr hodnot v buňkách A1 a B1 a výstup, který v buňce C1 . Můžete zajistit, aby váš vzorec fungoval, zadáním čísel do A1 a B1. C1 by měl ukázat jejich průměr.
  3. V tomto případě změňte hodnotu A1 buňka do 16 .
  4. Vyberte buňku C1 , a poté z pásu karet přejděte na Data tab.
  5. Pod Karta Data , vyberte Analýza What-If a pak Hledání cíle . Tím se otevře okno Hledání cíle.
  6. V okně Hledání cíle nastavte Nastavit buňku na C1 . Toto bude vaše cílová buňka. Po zvýraznění C1 před otevřením okna Hledání cíle se automaticky nastaví Nastavit buňku .
  7. v Vážit si , vložte požadovanou hodnotu cíle. V tomto případě chceme, aby náš průměr byl 26, takže cílová hodnota bude také 26.
  8. Nakonec v Změnou buňky , vyberte buňku, kterou chcete změnit, abyste dosáhli cíle. V tomto případě to bude buňka B2 .
  9. Klikněte OK aby cíl hledání fungoval jako kouzlo.

Jakmile kliknete na OK, zobrazí se dialog s informací, že Hledání cílů našlo řešení.

Hodnota buňky A1 se změní na řešení a tím se přepíší předchozí data. Je dobré spustit Goal Seek na kopii datového listu, aby nedošlo ke ztrátě původních dat.

Příbuzný: Crazy Excel vzorce, které dělají úžasné věci

Příklad hledání cíle 2

Hledání cílů může být užitečné, když ho použijete v reálných scénářích. Abyste však mohli plně využít potenciál hledání cílů, musíte mít na začátku správný vzorec.

V tomto případě si vezměme malý bankovní případ. Předpokládejme, že máte bankovní účet, který vám dává 4% roční úrok z peněz, které máte na účtu.

Pomocí analýzy What-If a Hledání cílů můžete vypočítat, kolik peněz musíte mít na účtu, abyste získali určitou částku měsíčních úrokových plateb.

V tomto případě řekněme, že chceme každý měsíc získat 350 $ z úrokových plateb. Můžete to vypočítat takto:

jak vytvořit rámeček profilu na facebooku
  1. V cele A1 typ Zůstatek .
  2. V cele A2 typ Roční sazba .
  3. V cele A4 typ Měsíční zisk .
  4. V cele B2 typ 4% .
  5. Vyberte buňku B4 a do řádku vzorců zadejte vzorec níže: | _+_ | Měsíční zisk se bude rovnat zůstatku účtu vynásobenému roční sazbou a poté děleno počtem měsíců v roce, 12 měsíců.
  6. Jděte na Data kartu, klikněte na Analýza What-If, a poté vyberte Hledání cíle .
  7. V Hledání cíle okno, napište B4 v Nastavit buňku .
  8. Do pole zadejte 350 Vážit si buňka. (Toto je měsíční zisk, který chcete získat)
  9. Typ B1 v Změnou buňky . (Tím se změní zůstatek na hodnotu, která přinese 350 $ měsíčně)
  10. Klikněte OK . The Stav hledání cíle vyskočí dialog.

V dialogu Stav hledání cíle uvidíte, že Hledání cílů našlo řešení v buňce B4. V buňce B1 můžete vidět řešení, které by mělo být 105 000.

Požadavky na hledání cílů

Při hledání cíle můžete vidět, že může změnit pouze jednu buňku, aby dosáhla hodnoty cíle. To znamená, že hledání cílů nemůže vyřešit a dosáhnout řešení, pokud má více než jednu proměnnou.

V aplikaci Excel můžete stále řešit více než jednu proměnnou, ale budete muset použít jiný nástroj s názvem Řešitel. Další informace o řešení Excel's Solver si můžete přečíst v našem článku na Jak používat Řešitel aplikace Excel .

Omezení hledání cíle

Vzorec pro hledání cílů používá k dosažení optimální hodnoty proces pokusů a vylepšení, což může představovat určité problémy, když vzorec vytvoří nedefinovanou hodnotu. Zde je návod, jak si to můžete sami vyzkoušet:

  1. V cele A1 , typ 3 .
  2. Vyberte buňku C1 , pak do řádku vzorců zadejte vzorec níže: | _+_ | Tento vzorec rozdělí jeden na A1-5, a pokud je A1-5 náhodou 0, výsledkem bude nedefinovaná hodnota.
  3. Jděte na Data klepněte na kartu Analýza What-If a poté vyberte Hledání cíle .
  4. v Nastavit buňku , typ C1 .
  5. v Vážit si , typ 1 .
  6. Nakonec v Změnou buňky typ A1 . (Tím se změní hodnota A1 na 1 v C1)
  7. Klikněte OK .

Dialogové okno Stav hledání cíle vám řekne, že možná nenašlo řešení, a můžete znovu zkontrolovat, zda je hodnota A1 daleko.

Nyní by řešením tohoto Hledání cílů bylo jednoduše mít 6 v A1. To dá 1/1, což se rovná 1. Ale v jednom bodě během procesu zkoušek a vylepšení se Excel pokusí 5 v A1, což dává 1/0, což je nedefinováno, čímž se proces zabije.

Cestou kolem tohoto problému Hledání cíle je mít jinou počáteční hodnotu, která se může vyhnout nedefinovanému procesu zkoušení a zlepšování.

Pokud například změníte A1 na libovolné číslo větší než 5 a poté zopakujete stejné kroky pro hledání cíle pro 1, měli byste získat správný výsledek.

přenos písně z iPodu na iTunes

Zefektivněte výpočty hledáním cílů

Analýza What-If a Hledání cílů může pomocí vašich vzorců zrychlit vaše výpočty na výbornou. Naučit se správné vzorce je dalším důležitým krokem k tomu, abyste se stali vynikajícím mistrem.

Podíl Podíl tweet E-mailem 15 vzorců aplikace Excel, které vám pomohou vyřešit skutečné problémy

Excel není jen pro podnikání. Zde je několik vzorců aplikace Microsoft Excel, které vám pomohou vyřešit složité každodenní problémy.

Číst dále Související témata
  • Produktivita
  • Tabulkový procesor
  • Microsoft Excel
  • Tipy pro Microsoft Office
  • Analýza dat
O autorovi Amir M. Intelligence(39 článků zveřejněno)

Amir je student farmacie s vášní pro technologie a hry. Rád hraje hudbu, řídí auta a píše slova.

Více od Amira M. Bohlooliho

Přihlaste se k odběru našeho zpravodaje

Připojte se k našemu zpravodaji a získejte technické tipy, recenze, bezplatné elektronické knihy a exkluzivní nabídky!

Kliknutím sem se přihlásíte k odběru