Ako používať hľadanie cieľov v programe Microsoft Excel na scenáre typu What-If

Ako používať hľadanie cieľov v programe Microsoft Excel na scenáre typu What-If

Excel's What-If Analysis vám umožňuje vidieť, ako zmena bunky môže ovplyvniť výstup vzorca. Na tento účel môžete použiť nástroje Excelu na výpočet účinku zmeny hodnoty bunky vo vzorci.





Program Excel má tri typy nástrojov analýzy Čo-ak: Správca scenárov, Hľadanie cieľa a Tabuľka údajov. Pomocou funkcie Hľadanie cieľa môžete určiť, aký vstup potrebujete na prevod vzorca vzad na určitý výstup.



Funkcia Hľadanie cieľa v Exceli je pokus - omyl, takže ak nevytvára to, čo chcete, Excel pracuje na zlepšovaní tejto hodnoty, kým sa tak nestane.





Aké sú vzorce na hľadanie cieľov v Exceli?

Hľadanie cieľa v programe Excel sa v zásade delí na tri hlavné zložky:

  1. Nastaviť bunku : Bunka, ktorú chcete použiť ako cieľ.
  2. Ohodnotiť : Hodnota, ktorú chcete dosiahnuť.
  3. Zmenou bunky : Bunka, ktorú chcete zmeniť, aby ste dosiahli hodnotu cieľa.

Keď sú tieto tri nastavenia nastavené, Excel sa pokúsi zlepšiť hodnotu v bunke, ktorú ste nastavili Zmenou bunky do bunky, do ktorej ste vložili Nastaviť bunku dosiahne hodnotu, v ktorej ste ju určili Ohodnotiť .



Ak to všetko znie mätúco, nižšie uvedené príklady vám poskytnú dobrú predstavu o tom, ako funguje hľadanie cieľov.

Príklad hľadania cieľa 1

Povedzme napríklad, že máte dve bunky (A a B) a tretiu bunku, ktorá vypočíta priemer týchto dvoch.



Teraz predpokladajme, že máte statickú hodnotu pre A a chcete zvýšiť priemernú úroveň zmenou hodnoty pre B. Pomocou funkcie Hľadanie cieľa môžete vypočítať, aká hodnota B vytvorí požadovaný priemer.

Súvisiace: Tipy na úsporu času v programe Microsoft Excel



ako zavolať späť na súkromné ​​číslo

Ako používať hľadanie cieľov v programe Excel

  1. V programe Excel kliknite na bunku C1 .
  2. Do riadka vzorcov zadajte nasledujúci kód: | _+_ | Tento vzorec umožní Excelu vypočítať priemer hodnôt v bunkách A1 a B1 a výstup, ktorý v bunke C1 . Môžete zaistiť, aby váš vzorec fungoval, zadaním čísel do A1 a B1. C1 by mala ukazovať ich priemer.
  3. V tomto prípade zmeňte hodnotu A1 bunka do 16 .
  4. Vyberte bunku C1 , a potom na páse s nástrojmi prejdite na ikonu Údaje tab.
  5. Pod Karta Údaje , vyberte Analýza Čo-Ak a potom Hľadanie cieľa . Zobrazí sa okno Hľadanie cieľa.
  6. V okne Hľadanie cieľa nastavte Nastaviť bunku do C1 . Toto bude vaša cieľová bunka. Po zvýraznení C1 pred otvorením okna Hľadanie cieľa sa automaticky nastaví Nastaviť bunku .
  7. V Ohodnotiť , zadajte požadovanú hodnotu cieľa. V tomto prípade chceme, aby bol náš priemer 26, takže cieľová hodnota bude tiež 26.
  8. Nakoniec v Zmenou bunky , vyberte bunku, ktorú chcete zmeniť, aby ste dosiahli cieľ. V tomto prípade to bude bunka B2 .
  9. Kliknite OK aby kúzlo fungovalo hľadanie cieľa.

Po kliknutí na tlačidlo OK sa zobrazí dialógové okno s informáciou, že aplikácia Goal Seek našla riešenie.

Hodnota bunky A1 sa zmení na riešenie a tým sa prepíšu predchádzajúce údaje. Je dobré spustiť funkciu Hľadanie cieľa na kópii listu s údajmi, aby ste neprišli o pôvodné údaje.

Súvisiace: Bláznivé vzorce v Exceli, ktoré robia úžasné veci

Príklad hľadania cieľa 2

Hľadanie cieľa môže byť užitočné, ak ho použijete v skutočných scenároch. Ak však chcete naplno využiť potenciál hľadania cieľov, musíte mať na začiatku správny vzorec.

V tomto prípade si vezmime malý bankový prípad. Predpokladajme, že máte bankový účet, ktorý vám dáva 4% ročný úrok z peňazí, ktoré máte na svojom účte.

Pomocou analýzy What-If a hľadania cieľa môžete vypočítať, koľko peňazí musíte mať na účte, aby ste získali určitú sumu mesačných splátok úrokov.

V tomto prípade povedzme, že chceme každý mesiac získať 350 dolárov z platieb úrokov. Takto to môžete vypočítať:

ako otestovať batériu mobilného telefónu
  1. V bunke A1 typ Zostatok .
  2. V bunke A2 typ Ročná sadzba .
  3. V bunke A4 typ Mesačný zisk .
  4. V bunke B2 typ 4% .
  5. Vyberte bunku B4 a do riadka vzorcov zadajte nasledujúci vzorec: | _+_ | Mesačný zisk sa bude rovnať zostatku na účte vynásobenému ročnou sadzbou a potom vydelenému počtom mesiacov v roku, 12 mesiacov.
  6. Choďte do Údaje kartu, kliknite na Analýza Čo-Ak, a potom vyberte Hľadanie cieľa .
  7. V Hľadanie cieľa okno, napíšte B4 v Nastaviť bunku .
  8. Napíšte 350 do Ohodnotiť bunka. (Toto je mesačný zisk, ktorý chcete dosiahnuť)
  9. Napíšte B1 v Zmenou bunky . (Tým sa zostatok zmení na hodnotu, ktorá prinesie 350 dolárov mesačne)
  10. Kliknite OK . The Stav pri hľadaní cieľa otvorí sa dialóg.

V dialógovom okne Stav hľadania cieľa uvidíte, že Hľadanie cieľa našlo riešenie v bunke B4. V bunke B1 vidíte riešenie, ktoré by malo byť 105 000.

Požiadavky na hľadanie cieľa

Pri hľadaní cieľa vidíte, že môže zmeniť iba jednu bunku na dosiahnutie hodnoty cieľa. To znamená, že Hľadanie cieľa nemôže vyriešiť a dosiahnuť riešenie, ak má viac ako jednu premennú.

V programe Excel môžete stále riešiť viac ako jednu premennú, ale budete musieť použiť iný nástroj s názvom Riešiteľ. Viac informácií o programe Excel's Solver si môžete prečítať v našom článku na Ako používať riešenie Excel .

Obmedzenia pri hľadaní cieľa

Vzorec na nájdenie cieľa používa na dosiahnutie optimálnej hodnoty proces skúšania a zlepšovania, čo môže pri vytváraní nedefinovanej hodnoty predstavovať určité problémy. Takto to môžete vyskúšať sami:

  1. V bunke A1 , typ 3 .
  2. Vyberte bunku C1 , potom do panela vzorcov zadajte nasledujúci vzorec: | _+_ | Tento vzorec bude jeden rozdeliť na A1-5 a ak náhodou bude mať A1-5 0, výsledkom bude nedefinovaná hodnota.
  3. Choďte do Údaje kartu, kliknite na Analýza Čo-Ak a potom vyberte Hľadanie cieľa .
  4. V Nastaviť bunku , typ C1 .
  5. V Ohodnotiť , typ 1 .
  6. Nakoniec, v Zmenou bunky typ A1 . (Tým sa zmení hodnota A1 na 1 v C1)
  7. Kliknite OK .

Dialógové okno Stav hľadania cieľa vám povie, že možno nenašlo riešenie, a môžete znova skontrolovať, či je hodnota A1 ďaleko.

Teraz by riešením tohto Hľadania cieľa bolo jednoducho mať 6 v A1. To poskytne 1/1, čo sa rovná 1. Ale v jednom bode počas procesu testovania a zlepšovania sa Excel pokúsi o 5 v A1, čo dáva 1/0, čo nie je definované, čím sa proces zabije.

Tento problém s hľadaním cieľa je možné obísť tak, že má inú počiatočnú hodnotu, ktorá sa môže vyhnúť nedefinovaným v procese skúšania a zlepšovania.

Napríklad, ak zmeníte A1 na akékoľvek číslo väčšie ako 5 a potom zopakujete rovnaké kroky ako pri Cieľovom hľadaní pre 1, mali by ste získať správny výsledok.

ako vytvoriť odkazy na iphone

Zefektívnite výpočty pri hľadaní cieľov

Analýza What-If a Hľadanie cieľa môžu vaše výpočty pomocou vzorcov výrazne urýchliť. Naučenie sa správnych vzorcov je ďalším dôležitým krokom k tomu, aby ste sa stali vynikajúcim majstrom.

zdieľam zdieľam Tweet E -mail 15 vzorcov programu Excel, ktoré vám pomôžu vyriešiť problémy v reálnom živote

Excel nie je len pre firmy. Tu je niekoľko vzorcov programu Microsoft Excel, ktoré vám pomôžu vyriešiť zložité každodenné problémy.

Čítajte ďalej Súvisiace témy
  • Produktivita
  • Tabuľka
  • Microsoft Excel
  • Tipy pre Microsoft Office
  • Analýza dát
O autorovi Amir M. Inteligencia(39 publikovaných článkov)

Amir je študent farmácie s vášňou pre technológie a hry. Rád hrá hudbu, riadi autá a píše slová.

Viac od Amira M. Bohlooliho

prihlásiť sa ku odberu noviniek

Pripojte sa k nášmu bulletinu a získajte technické tipy, recenzie, bezplatné elektronické knihy a exkluzívne ponuky!

Kliknutím sem sa prihlásite na odber