Ako používať vyhľadávanie cieľov v Exceli

Goal Seek je jedným z nástrojov Excelovej analýzy What-if, ktorý vám pomôže nájsť správnu vstupnú hodnotu vzorca na získanie požadovaného výstupu. Ukazuje, ako jedna hodnota vo vzorci ovplyvňuje druhú. Inými slovami, ak máte cieľovú hodnotu, ktorú chcete dosiahnuť, môžete použiť vyhľadávanie cieľov na nájdenie správnej vstupnej hodnoty na jej dosiahnutie.

Povedzme napríklad, že ste v predmete dosiahli celkovo 75 bodov a na získanie známky S v danom predmete potrebujete aspoň 90. Našťastie máte posledný test, ktorý vám môže pomôcť zvýšiť vaše priemerné skóre. Pomocou funkcie Goal Seek môžete zistiť, koľko bodov potrebujete v tomto záverečnom teste, aby ste získali známku S.

Hľadanie cieľa používa metódu pokus-omyl na spätné sledovanie problému zadávaním odhadov, kým sa nedosiahne správny výsledok. Funkcia hľadania cieľa dokáže nájsť najlepšiu vstupnú hodnotu pre vzorec v priebehu niekoľkých sekúnd, ktorej manuálne zistenie by trvalo dlho. V tomto článku vám na niekoľkých príkladoch ukážeme, ako používať nástroj Hľadanie cieľa v Exceli.

Komponenty funkcie hľadania cieľa

Funkcia hľadania cieľa pozostáva z troch parametrov, a to:

  • Nastaviť bunku – Toto je bunka, do ktorej sa zadáva vzorec. Určuje bunku, v ktorej chcete požadovaný výstup.
  • Ohodnotiť – Toto je cieľová/požadovaná hodnota, ktorú chcete ako výsledok operácie hľadania cieľa.
  • Zmenou bunky – Toto určuje bunku, ktorej hodnotu je potrebné upraviť, aby sa dosiahol požadovaný výstup.

Ako používať vyhľadávanie cieľov v Exceli: Príklad 1

Aby ste na jednoduchom príklade demonštrovali, ako to funguje, predstavte si, že prevádzkujete stánok s ovocím. Na snímke obrazovky nižšie bunka B11 (nižšie) zobrazuje, koľko vás stál nákup ovocia pre váš stánok, a bunka B12 zobrazuje váš celkový príjem z predaja tohto ovocia. A bunka B13 zobrazuje percento vášho zisku (20 %).

Ak by ste chceli zvýšiť svoj zisk na „30%“, ale nemôžete zvýšiť svoju investíciu, musíte zvýšiť svoje príjmy, aby ste dosiahli zisk. Ale za koľko? Hľadanie cieľa vám ho pomôže nájsť.

Na výpočet percenta zisku použite nasledujúci vzorec v bunke B13:

= (B12-B11)/B12

Teraz chcete vypočítať ziskovú maržu tak, aby vaše percento zisku bolo 30 %. Môžete to urobiť pomocou funkcie Hľadanie cieľa v Exceli.

Prvá vec, ktorú musíte urobiť, je vybrať bunku, ktorej hodnotu chcete upraviť. Pri každom použití funkcie Hľadanie cieľa musíte vybrať bunku, ktorá obsahuje vzorec alebo funkciu. V našom prípade vyberieme bunku B13, pretože obsahuje vzorec na výpočet percenta.

Potom prejdite na kartu „Údaje“, kliknite na tlačidlo „Analýza Čo ak“ v skupine Prognóza a vyberte „Hľadanie cieľa“.

Zobrazí sa dialógové okno Hľadanie cieľa s 3 poľami:

  • Nastaviť bunku – Zadajte odkaz na bunku, ktorá obsahuje vzorec (B13). Toto je bunka, ktorá bude mať požadovaný výstup.
  • Ohodnotiť – Zadajte požadovaný výsledok, ktorý sa snažíte dosiahnuť (30 %).
  • Zmenou bunky – Vložte odkaz na bunku pre vstupnú hodnotu, ktorú by ste chceli zmeniť (B12), aby ste dosiahli požadovaný výsledok. Stačí kliknúť na bunku alebo manuálne zadať odkaz na bunku. Keď vyberiete bunku, Excel pridá znak „$“ pred písmeno stĺpca a číslo riadku, aby sa stala absolútnou bunkou.

Po dokončení kliknite na tlačidlo „OK“ a otestujte ho.

Potom sa zobrazí dialógové okno „Stav hľadania cieľa“ a bude vás informovať, či sa našlo nejaké riešenie, ako je uvedené nižšie. Ak bolo nájdené riešenie, vstupná hodnota v „zmenacej bunke (B12)“ sa upraví na novú hodnotu. Toto chceme. V tomto príklade teda analýza určila, že na to, aby ste dosiahli svoj cieľ 30 % zisku, musíte dosiahnuť príjem 1 635,5 USD (B12).

Kliknite na „OK“ a Excel zmení hodnoty buniek alebo kliknutím na „Zrušiť“ zahodíte riešenie a obnovíte pôvodnú hodnotu.

Vstupná hodnota (1635,5) v bunke B12 je to, čo sme zistili pomocou hľadania cieľa, aby sme dosiahli náš cieľ (30 %).

Na čo treba pamätať pri používaní hľadania cieľa

  • Bunka Nastaviť musí vždy obsahovať vzorec, ktorý závisí od bunky „Zmenou bunky“.
  • Hľadanie cieľa môžete naraz použiť iba na vstupnú hodnotu jednej bunky
  • Pole „Zmenou bunky“ musí obsahovať hodnotu, nie vzorec.
  • Ak Hľadanie cieľa nedokáže nájsť správne riešenie, zobrazí najbližšiu hodnotu, akú dokáže vytvoriť, a oznámi vám, že správa „Hľadanie cieľa možno nenašlo riešenie“.

Čo robiť, keď hľadanie cieľa v Exceli nefunguje

Ak ste si však istí, že existuje riešenie, existuje niekoľko vecí, ktoré môžete skúsiť vyriešiť.

Skontrolujte parametre a hodnoty hľadania cieľa

Mali by ste skontrolovať dve veci: najprv skontrolujte, či parameter „Nastaviť bunku“ odkazuje na bunku vzorca. Po druhé, uistite sa, že bunka vzorca (Nastaviť bunku) priamo alebo nepriamo závisí od meniacej sa bunky.

Úprava nastavení iterácie

V nastaveniach Excelu môžete zmeniť počet možných pokusov, ktoré môže Excel vykonať, aby našiel správne riešenie, ako aj jeho presnosť.

Ak chcete zmeniť nastavenia výpočtu iterácie, kliknite na „Súbor“ v zozname kariet. Potom kliknite na položku „Možnosti“ v dolnej časti.

V okne Možnosti programu Excel kliknite na položku „Vzorce“ na ľavej table.

V časti „Možnosti výpočtu“ zmeňte tieto nastavenia:

  • Maximálne iterácie – Označuje počet možných riešení, ktoré Excel vypočíta; čím vyššie číslo, tým viac iterácií môže vykonať. Ak napríklad nastavíte „Maximálny počet iterácií“ na 150, Excel otestuje 150 možných riešení.
  • Maximálna zmena – označuje presnosť výsledkov; menšie číslo dáva vyššiu presnosť. Ak sa napríklad vaša vstupná hodnota bunky rovná „0“, ale funkcia Hľadanie cieľa sa prestane počítať na hodnote „0,001“, problém by sa mal vyriešiť zmenou tejto hodnoty na „0,0001“.

Zvýšte hodnotu „Maximálny počet iterácií“, ak chcete, aby Excel otestoval viac možných riešení, a znížte hodnotu „Maximálna zmena“, ak chcete presnejší výsledok.

Snímka obrazovky nižšie zobrazuje predvolenú hodnotu:

Žiadne kruhové odkazy

Keď vzorec odkazuje späť na svoju vlastnú bunku, nazýva sa to kruhový odkaz. Ak chcete, aby funkcia Excel Goal Seek fungovala správne, vzorce by nemali používať kruhový odkaz.

Príklad hľadania cieľa v Exceli 2

Povedzme, že si od niekoho požičiavate peniaze „25 000 $“. Požičia vám peniaze s úrokovou sadzbou 7 % mesačne na obdobie 20 mesiacov, čo znamená splácanie „1327 $“ mesačne. Môžete si však dovoliť platiť „1 000 $“ mesačne počas 20 mesiacov so 7% úrokom. Goal Seek vám môže pomôcť nájsť sumu pôžičky, ktorá produkuje mesačnú platbu (EMI) „1 000 USD“.

Zadajte tri vstupné premenné, ktoré budete potrebovať na výpočet výpočtu PMT, t. j. úrokovú sadzbu 7 %, dobu trvania 20 mesiacov a sumu istiny 25 000 USD.

Do bunky B5 zadajte nasledujúci vzorec PMT, ktorý vám poskytne sumu EMI 1 327,97 USD.

Syntax funkcie PMT:

=PMT(úroková sadzba/12, obdobie, istina)

Vzorec:

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

Vyberte bunku B5 (bunka so vzorcom) a prejdite na Údaje –> Analýza Čo ak –> Hľadanie cieľa.

V okne „Hľadanie cieľa“ použite tieto parametre:

  • Nastaviť bunku – B5 (bunka, ktorá obsahuje vzorec na výpočet EMI)
  • Ohodnotiť – 1000 (výsledok vzorca/cieľ, ktorý hľadáte)
  • Zmenou bunky – B2 (toto je suma úveru, ktorú chcete zmeniť, aby ste dosiahli cieľovú hodnotu)

Potom kliknite na „OK“, ak chcete nájdené riešenie ponechať, alebo na tlačidlo „Zrušiť“, ak ho chcete zahodiť.

Analýza vám povie, že maximálna suma peňazí, ktorú si môžete požičať, je 18 825 USD, ak chcete prekročiť svoj rozpočet.

Takto používate Hľadanie cieľa v Exceli.