Výpočet viacerých výsledkov pomocou tabuľky údajov

Poznámka: Radi by sme vám čo najrýchlejšie poskytovali najaktuálnejší obsah Pomocníka vo vašom vlastnom jazyku. Táto stránka bola preložená automaticky a môže obsahovať gramatické chyby alebo nepresnosti. Naším cieľom je, aby bol tento obsah pre vás užitočný. Dali by ste nám v dolnej časti tejto stránky vedieť, či boli pre vás tieto informácie užitočné? Tu nájdete anglický článok pre jednoduchú referenciu.

Tabuľka údajov je rozsah buniek, v ktorých môžete zmeniť hodnoty v niektorých bunkách a prísť s rôznymi odpoveďami na problém. Dobrým príkladom tabuľky údajov je používanie funkcie PMT s rôznymi výškami pôžičiek a úrokovými sadzbami na výpočet dostupnej čiastky na hypotekárnom úvere na bývanie. Experimentovanie s rôznymi hodnotami na pozorovanie príslušných variácií vo výsledkoch je bežnou úlohou v analýze údajov.

V programe Microsoft Excel sú tabuľky údajov súčasťou sady príkazov známych ako nástroje na analýzu hypotéz. Pri vytváraní a analýze tabuliek údajov robíte analýzu hypotéz.

Analýza hypotéz je procesom zmeny hodnôt v bunkách, ktoré vám pomôžu zistiť, ako tieto zmeny ovplyvnia výsledok vzorcov v hárku. Tabuľku údajov môžete napríklad použiť na zmenu úrokovej miery a dĺžky trvania pôžičky – na vyhodnotenie potenciálnych mesačných čiastok platby.

Poznámka: Môžete vykonávať rýchlejšie výpočty s tabuľkami údajov a programom Visual Basic for Applications (VBA). Ďalšie informácie nájdete v téme excelové údajové tabuľky s údajmi: rýchlejší výpočet pomocou jazyka VBA.

Typy analýzy hypotéz    

Existujú tri typy nástrojov na analýzu hypotéz v Exceli: scenáre, tabuľky údajova hľadanie cieľov. Scenáre a tabuľky údajov používajú množiny vstupných hodnôt na výpočet možných výsledkov. Hľadanie gólov je odlišne odlišné, používa jeden výsledok a vypočíta možné vstupné hodnoty, ktoré by daný výsledok vyprodukovali.

Tabuľky údajov podobne ako scenáre pomáhajú preskúmať množinu možných výsledkov. Na rozdiel od scenárov sa v tabuľkách údajov zobrazujú všetky výsledky v jednej tabuľke v jednom hárku. Používaním tabuliek údajov môžete rýchlo preskúmať škálu možností. Zameriavate sa len na jednu alebo dve premenné, a tak sa výsledky v podobe tabuľky dajú jednoducho čítať a zdieľať.

Tabuľka údajov nedokáže prijať viac ako dve premenné. Ak chcete analyzovať viac ako dve premenné, použite radšej scenáre. Hoci je obmedzená len na jednu alebo dve premenné (jedna pre vstupnú bunku riadka a jedna pre vstupnú bunku stĺpca), tabuľka údajov môže obsahovať toľko rôznych hodnôt premenných, koľko potrebujete. Scenár môže obsahovať maximálne 32 rôznych hodnôt, no môžete vytvoriť ľubovoľný počet scenárov.

Ďalšie informácie nájdete v článku Úvod do analýzyhypotéz.

V závislosti od počtu premenných a vzorcov, ktoré je potrebné otestovať, vytvorte jednu premennú alebo tabuľku údajov s dvoma premennými.

Tabuľky údajov s jednou premennou    

Tabuľku údajov s jednou premennou použite, ak chcete zistiť, ako rôzne hodnoty jednej premennej v jednom alebo vo viacerých vzorcoch zmenia výsledky týchto vzorcov. Môžete napríklad použiť tabuľku údajov s jednou premennou a zistiť, ako rôzne úrokové sadzby ovplyvnia mesačnú hypotekárnu platbu pomocou funkcie PMT. Hodnoty premennej zadáte do jedného stĺpca alebo riadka a výsledky sa zobrazia v susednom stĺpci alebo riadku.

V nasledujúcom príklade bunka D2 obsahuje vzorec platby, = PMT (B3/12; B4;-B5), ktorý odkazuje na vstupnú bunku B3.

Tabuľka údajov s jednou premennou

Tabuľky údajov s dvoma premennými    

Tabuľku údajov s dvomi premennými použite, ak chcete zistiť, ako rôzne hodnoty dvoch premenných v jednom vzorci zmenia výsledky tohto vzorca. Pomocou tabuľky údajov s dvomi premennými môžete napríklad zistiť, ako rôzne kombinácie úrokových sadzieb a dôb splácania ovplyvnia výšku mesačnej splátky hypotéky.

V nasledujúcom príklade bunka C2 obsahuje vzorec platby, = PMT (B3/12; B4;-B5), ktorý používa dve vstupné bunky, B3 a B4.

Tabuľka údajov s dvoma premennými
 

Výpočty tabuľky údajov    

Vždy, keď sa hárok prepočíta, prepočítajú sa aj všetky údajové tabuľky, a to aj v prípade, že údaje nedošlo k žiadnej zmene. Ak chcete urýchliť výpočet hárka, ktorý obsahuje tabuľku údajov, môžete zmeniť možnosti výpočtov a automaticky prepočítať hárok, ale nie tabuľky údajov. Ďalšie informácie nájdete v časti zrýchlenie výpočtu v hárku, ktorý obsahuje tabuľky údajov.

Tabuľka údajov s jednou premennou obsahuje vstupné hodnoty buď v jednom stĺpci (orientovaný na stĺpec), alebo cez riadok (orientovaný na riadok). Každý vzorec v tabuľke údajov s jednou premennou musí odkazovať iba na jeden vstupná bunka.

Vykonajte nasledujúce kroky:

  1. Zadajte zoznam hodnôt, ktoré chcete nahradiť vo vstupnej bunke, a to buď v jednom stĺpci, alebo v rámci jedného riadka. Ponechajte niekoľko prázdnych riadkov a stĺpcov na oboch okrajoch hodnôt.

  2. Vykonajte jednu z nasledujúcich akcií:

    • Ak je tabuľka údajov orientovaná podľa stĺpca (hodnoty premenných sa nachádzajú v stĺpci), zadajte vzorec do bunky o jeden riadok vyššie a jednu bunku napravo od stĺpca hodnôt. Táto tabuľka údajov s jednou premennou je orientovaná podľa stĺpca a vzorec sa nachádza v bunke D2.


      Tabuľka údajov s jednou premennou
      Ak chcete preskúmať účinky rôznych hodnôt v iných vzorcoch, zadajte ďalšie vzorce do buniek napravo od prvého vzorca.

    • Ak je tabuľka údajov orientovaná podľa riadka (hodnoty premenných sa nachádzajú v riadku), zadajte vzorec do bunky o jeden stĺpec naľavo od prvej hodnoty a jednu bunku pod riadkom hodnôt.

      Ak chcete preskúmať účinky rôznych hodnôt v iných vzorcoch, zadajte ďalšie vzorce do buniek pod prvým vzorcom.

  3. Vyberte rozsah buniek, ktorý obsahuje vzorce a hodnoty, ktoré chcete nahrádzať. Na obrázku vyššie je tento rozsah C2: D5.

  4. Na karte údaje kliknite na položku Analýza údajov _GT_ (v skupine Nástroje pre údaje alebo v skupine prognózy Excel 2016 ).

  5. Vykonajte jednu z nasledujúcich akcií:

    • Ak je tabuľka údajov orientovaná podľa stĺpca, zadajte odkaz na bunku pre vstupnú bunku do poľa Vstupná bunka stĺpca . Na obrázku vyššie je Vstupná bunka B3.

    • Ak je tabuľka údajov orientovaná podľa riadka, zadajte odkaz na vstupnú bunku do poľa Vstupná bunka riadka .

      Poznámka: Po vytvorení tabuľky údajov môžete zmeniť formát buniek s výsledkami. Na obrázku sú bunky s výsledkami formátované ako mena.

Vzorce, ktoré sa používajú v tabuľke údajov s jednou premennou, musia odkazovať na tú istú vstupnú bunku.

Vykonajte nasledujúce kroky

  1. Vykonajte niektorý z týchto krokov:

    • Ak je tabuľka údajov orientovaná podľa stĺpca, zadajte nový vzorec do prázdnej bunky napravo od existujúceho vzorca v hornom riadku tabuľky údajov.

    • Ak je tabuľka údajov orientovaná podľa riadka, zadajte nový vzorec do prázdnej bunky pod existujúcim vzorcom v prvom stĺpci tabuľky údajov.

  2. Vyberte rozsah buniek, ktorý obsahuje tabuľku údajov a nový vzorec.

  3. Na karte údaje kliknite na položku Analýza údajov _GT_ (v skupine Nástroje pre údaje alebo v skupine prognózy Excel 2016 ).

  4. Použite niektorý z týchto postupov:

    • Ak je tabuľka údajov orientovaná podľa stĺpca, zadajte odkaz na vstupnú bunku do poľa Vstupná bunka stĺpca .

    • Ak je tabuľka údajov orientovaná podľa riadka, zadajte odkaz na vstupnú bunku do poľa Vstupná bunka riadka .

V tabuľke údajov s dvoma premennými sa používa vzorec, ktorý obsahuje dva zoznamy vstupných hodnôt. Vzorec musí odkazovať na dve rôzne vstupné bunky.

Vykonajte nasledujúce kroky:

  1. Do bunky v hárku zadajte vzorec, ktorý odkazuje na dve vstupné bunky.

    V nasledujúcom príklade, v ktorom sú v bunke B3, B4 a B5 zadané hodnoty vzorca, zadajte vzorec = PMT (B3/12; B4;-B5) v bunke C2.

  2. Zadajte jeden zoznam vstupných hodnôt do toho istého stĺpca pod vzorec.

    V tomto prípade zadáte rôzne úrokové sadzby do buniek C3, C4 a C5.

  3. Zadajte druhý zoznam v tom istom riadku ako vzorec – doprava.

    Údaje o dobách splácania (v mesiacoch) zadajte do buniek D2 a E2.

  4. Vyberte rozsah buniek, ktorý obsahuje vzorec (C2), riadok a stĺpec s údajmi (C3:C5 a D2:E2) a bunky, v ktorých chcete zobraziť vypočítané hodnoty (D3:E5).

    V tomto prípade vyberte rozsah buniek C2:E5.

  5. Na karte údaje v skupine Nástroje pre údaje alebo v skupine prognóza (v Excel 2016 ) kliknite na položku Analýzaúdajov > tabuľky údajov (v skupine Nástroje pre údaje alebo v skupine prognóza Excel 2016 ).  

  6. Do poľa Vstupná bunka riadka zadajte odkaz na vstupnú bunku pre vstupné hodnoty v riadku.
    Do poľa Vstupná bunka riadka zadajte bunku B4 .

  7. Do poľa Vstupná bunka stĺpca zadajte odkaz na vstupnú bunku pre vstupné hodnoty v stĺpci.
    Do poľa Vstupná bunka stĺpca zadajte text B3 .

  8. Kliknite na tlačidlo OK.

Príklad tabuľky údajov s dvoma premennými

Pomocou tabuľky údajov s dvomi premennými je možné zobraziť vplyv rôznych kombinácií úrokových sadzieb a dôb splácania na mesačné splátky hypotéky. Na obrázku tu bunka C2 obsahuje vzorec platby, = PMT (B3/12; B4;-B5), ktorý používa dve vstupné bunky, B3 a B4.

Tabuľka údajov s dvoma premennými

Pri nastavovaní tejto možnosti výpočtu sa nevyskytnú žiadne výpočty údajových tabuliek, keď sa prepočítanie vykoná v celom zošite. Ak chcete tabuľku údajov prepočítať manuálne, vyberte jej vzorce a potom stlačte kláves F9.

Ak chcete zvýšiť výkon výpočtov, postupujte podľa týchto krokov:

  1. Použite niektorý z týchto postupov:

    • V Excel 2007 kliknite na tlačidlo Microsoft Office Obrázok tlačidla Office , kliknite na položku Možnosti programu Excela potom kliknite na kategóriu vzorce .

    • Vo všetkých ostatných verziách kliknite na položku Možnostisúboru > > vzorce.

  2. V časti Možnosti výpočtu v časti vypočítaťkliknite na položku automaticky okrem tabuliek údajov.

    Tip: Voliteľne na karte vzorce kliknite na šípku na Možnosti výpočtova potom kliknite na položku automaticky okrem tabuliek údajov (v skupine Výpočet ).

Ak máte konkrétne ciele alebo väčšie množiny premenných údajov, môžete použiť niekoľko ďalších nástrojov Excelu na vykonanie analýzy hypotéz.

Hľadanie riešenia

Ak viete, že výsledok očakávate zo vzorca, ale neviete presne, akú vstupnú hodnotu musí vzorec získať, použite funkciu hľadanie cieľa. Ďalšie informácie nájdete v článku Použitie funkcie Hľadať pri vyhľadávaní požadovaného výsledku úpravou vstupnej hodnoty.

Riešiteľ v Exceli

Ak chcete nájsť optimálnu hodnotu pre množinu vstupných premenných, môžete použiť doplnok Excel Riešiteľ. Riešiteľ pracuje so skupinou buniek (nazývanými aj premennými alebo jednoducho premennými bunky), ktoré sa používajú pri výpočte vzorcov v bunkách cieľ a obmedzenie. Tento doplnok upravuje hodnoty v bunkách s rozhodovacími premennými tak, aby sa neprekročili limity v bunkách s obmedzeniami a získal sa požadovaný výsledok pre cieľovú bunku. Ďalšie informácie nájdete v tomto článku: definovanie a vyriešenie problému pomocou Riešiteľa.

Pripojením rôznych čísel do bunky môžete rýchlo prísť s rôznymi odpoveďami na problém. Skvelým príkladom je použitie funkcie PMT s rôznymi úrokovými sadzbami a obdobiami pôžičiek (v mesiacoch), aby ste zistili, koľko pôžičky si môžete dovoliť doma alebo v aute. Čísla zadáte do rozsahu buniek, ktoré sa označujú ako tabuľka údajov.

Tabuľka údajov je rozsah buniek B2: D8. Môžete zmeniť hodnotu v poli B4, čiastku pôžičky a mesačné platby v stĺpci D sa automaticky aktualizujú. Pri použití 3,75% úrokovej sadzby vráti D2 mesačnú platbu vo výške $1 042,01 pomocou tohto vzorca: = PMT (C2/12, $B $3, $B $4).

Tento rozsah buniek, B2:D8, je tabuľka údajov

V závislosti od počtu premenných a vzorcov, ktoré chcete testovať, môžete použiť jednu alebo dve premenné.

Ak chcete zistiť, ako rôzne hodnoty jednej premennej vo vzorci zmenia výsledky, použite test s jednou premennou. Úroková sadzba mesačnej splátky hypotéky môžete napríklad zmeniť pomocou funkcie PMT. Zadáte hodnoty premenných (úrokové sadzby) v jednom stĺpci alebo riadku a výsledky sa zobrazia v neďalekom stĺpci alebo riadku.

V tomto živom zošite obsahuje bunka D2 vzorec Payment =PMT (C2/12, $B $3, $B $4). Bunka B3 je premenná bunka, v ktorej môžete pripojiť inú dĺžku obdobia (počet mesačných splátok). Funkcia PMT v bunke D2 zapojí do úrokovej sadzby 3,75%/12, 360 mesiacov a $225 000 pôžičky a vypočíta $1 042,01 mesačnú platbu.

Ak chcete zistiť, ako rozdielne hodnoty dvoch premenných vo vzorci zmenia výsledky, použite test s dvomi premennými. Môžete napríklad otestovať rôzne kombinácie úrokových sadzieb a počet mesačných platobných lehôt na výpočet splátky hypotéky.

V tomto živom zošite obsahuje bunka C3 vzorec platby =PMT ($B $3/12, $B $2; B4), ktorý používa dve premenné bunky, B2 a B3. Funkcia PMT v bunke C2 zapojí do úrokovej sadzby 3.875%/12, 360 mesiacov a $225 000 úver a vypočíta $1 058,03 mesačnú platbu.

Potrebujete ďalšiu pomoc?

Vždy sa môžete opýtať odborníka v komunite technikov pre Excel, získať podporu v rámci komunity lokality Answers alebo navrhnúť novú funkciu či vylepšenie na lokalite Excel User Voice.

Rozšírte svoje zručnosti práce s balíkom Office
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×