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

Tabuľky údajov v Microsoft Exceli sú súčasťou balíka príkazov známych ako What-If analytické nástroje. Pri vytváraní a analýze tabuliek údajov vykonávate analýzu hypotéz.

Analýza hypotéz je proces zmeny hodnôt v bunkách, aby ste zistili, ako tieto zmeny ovplyvnia výsledok vzorcov v hárku. Tabuľku údajov môžete použiť napríklad na zmenu úrokovej sadzby a dĺžky obdobia pôžičky – na vyhodnotenie potenciálnych mesačných splátok.

Typy analýzy hypotéz    

V Exceli existujú tri typy analytických nástrojov hypotéz: scenáre, tabuľky údajov a hľadanie riešenia. 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 riešenia je výrazne odlišné, používa jeden výsledok a vypočíta možné vstupné hodnoty, ktoré by vytvorili tento výsledok.

Tabuľky údajov podobne ako scenáre pomáhajú preskúmať množinu možných výsledkov. Na rozdiel od scenárov tabuľky ú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é (jednu pre vstupnú bunku riadka a jednu pre vstupnú bunku stĺpca), tabuľka údajov môže obsahovať ľubovoľný počet rôznych hodnôt premenných. 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 Úvodné informácie o analýze What-If.

Vytvorte tabuľky údajov s jednou premennou alebo s dvomi premennými v závislosti od počtu premenných a vzorcov, ktoré je potrebné testovať.

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. Pomocou tabuľky údajov s jednou premennou môžete napríklad zistiť, ako rôzne úrokové sadzby ovplyvňujú mesačnú splátku hypotéky 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.

Bunka D2 na nasledujúcom obrázku obsahuje vzorec platby =PMT(B3/12;B4;-B5), ktorý odkazuje na vstupnú bunku B3.

Tabuľky údajov s dvomi 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.

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

 

Výpočty tabuľky údajov    

Pri každom prepočítaní hárka sa prepočítajú aj všetky tabuľky údajov, a to aj v prípade, že nedošlo k žiadnej zmene údajov. Ak chcete urýchliť výpočet hárka, ktorý obsahuje tabuľku údajov, môžete zmeniť možnosti výpočtu tak, aby sa automaticky prepočítal 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 v jednom stĺpci (orientovanom podľa stĺpca) alebo v riadku (orientovanom podľa riadka). Každý vzorec v tabuľke údajov s jednou premennou musí odkazovať iba na jednu vstupná bunka.

Postupujte takto:

1. Zadajte zoznam hodnôt, ktoré chcete nahradiť vo vstupnej bunke – buď o jeden stĺpec nadol, alebo cez jeden riadok. Ponechajte niekoľko prázdnych riadkov a stĺpcov na oboch stranách hodnôt.

2. Použite niektorý z nasledujúcich postupov:

   • 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 o jednu bunku napravo od stĺpca hodnôt. Tabuľka údajov s jednou premennou je orientovaná na stĺpec a vzorec sa nachádza v bunke D2.
     
     
     
     Ak chcete preskúmať účinky rôznych hodnôt na iné vzorce, zadajte ďalšie vzorce do buniek napravo od prvého vzorca.

   • Ak je tabuľka údajov orientovaná podľa riadkov (hodnoty premenných sú 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 na iné vzorce, 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 vyberte položku Analýza hypotéz > tabuľku údajov (v skupine Nástroje pre údaje alebo skupina Prognóza Excel 2016 ).

5. Použite niektorý z nasledujúcich postupov:

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

   • Ak je tabuľka údajov orientovaná podľa riadkov, zadajte odkaz na vstupnú bunku do poľa vstupnej bunky 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 akcií:

   • 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 riadkov, 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 vyberte položku Analýza hypotéz> tabuľku údajov (v skupine Nástroje pre údaje alebo Skupina Prognóza v Excel 2016 ).

4. Vykonajte niektorý z týchto krokov:

   • 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 riadkov, 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.

Postupujte takto:

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

   V nasledujúcom príklade– v ktorom sa počiatočné hodnoty vzorca zadávajú do buniek B3, B4 a B5, zadáte vzorec =PMT(B3/12;B4;-B5) do bunky 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. Do toho istého riadka ako vzorec zadajte druhý zoznam napravo od vzorca.

   Ú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 Skupina Prognóza (v Excel 2016 ) vyberte položku Analýza hypotéz > tabuľku údajov (v skupine Nástroje pre údaje alebo Skupina Prognóza Excel 2016 ).

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

7. Do poľa vstupnej bunky Stĺpec zadajte odkaz na vstupná bunka pre vstupné hodnoty v stĺpci.
   Do poľa vstupnej bunky stĺpca zadajte hodnotu B3.

8. Vyberte položku OK.

Príklad tabuľky údajov s dvomi 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 tomto obrázku bunka C2 obsahuje vzorec platby =PMT(B3/12;B4;-B5), ktorý používa dve vstupné bunky, B3 a B4.

Keď nastavíte túto možnosť výpočtu, pri prepočte v celom zošite sa nevykoná žiadne výpočty tabuľky údajov. Ak chcete tabuľku údajov prepočítať manuálne, vyberte jej vzorce a potom stlačte kláves F9.

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

1. Vyberte položku Možnosti > súboru > vzorce.

2. V časti Možnosti výpočtu vyberte možnosť Automatické.

   Tip: Voliteľne môžete na karte Vzorce vybrať šípku na položke Možnosti výpočtu a potom vybrať položku Automaticky.

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

Hľadanie riešenia

Ak poznáte výsledok, ktorý možno očakávať od vzorca, ale presne neviete, akú vstupnú hodnotu vzorec potrebuje na získanie tohto výsledku, použite funkciu Goal-Seek. Pozrite si článok Použitie funkcie Hľadanie riešenia na vyhľadanie požadovaného výsledku úpravou vstupnej hodnoty.

Riešiteľ programu Excel

Doplnok Riešiteľ programu Excel môžete použiť na vyhľadanie optimálnej hodnoty pre množinu vstupných premenných. Riešiteľ pracuje so skupinou buniek (nazývanými rozhodovacie premenné alebo jednoducho variabilné bunky), ktoré sa používajú pri výpočte vzorcov v cieľových a obmedzených bunkách. 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 riešenie problému pomocou Riešiteľa.