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

V Microsoft Exceli sú tabuľky údajov súčasťou súboru príkazov známych ako nástroje analýzy hypotéz. Keď vytváraš a analyzuješ tabuľky s údajmi, robíš analýzu hypotéz.

Analýza hypotéz je proces zmeny hodnôt v bunkách s cieľom zistiť, ako tieto zmeny ovplyvnia výsledok vzorcov v pracovnom hárku. Napríklad pomocou tabuľky údajov môžete meniť úrokovú sadzbu a dĺžku splatnosti úveru, aby ste mohli vypočítať potenciálne výšky mesačných splátok.

Typy analýzy hypotéz    

V Exceli existujú tri typy nástrojov na analýzu hypotéz: scenáre, údaje a hľadanie cieľa. Scenáre a tabuľky údajov používajú súbory vstupných hodnôt na výpočet možných výsledkov. Funkcia Goal-seek je výrazne odlišná, používa jediný výsledok a vypočíta možné vstupné hodnoty, ktoré by viedli k tomuto výsledku.

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 na 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 riadku a jednu pre vstupnú bunku stĺpca), tabuľka údajov môže obsahovať toľko rôznych hodnôt premenných, koľko chcete. 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ýzy hypotéz.

Vytvorte tabuľky údajov s jednou alebo dvoma premennými v závislosti od počtu premenných a vzorcov, ktoré potrebujete otestovať.

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

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

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.

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

 

Výpočty v tabuľke údajov    

Pri každom prepočítaní pracovného hárku sa prepočítajú aj všetky tabuľky údajov, aj keď nedošlo k žiadnej zmene údajov. Ak chcete urýchliť výpočet pracovného hárku, ktorý obsahuje tabuľku údajov, môžete zmeniť možnosti Výpočet, aby sa automaticky prepočítal pracovný hárok, ale nie tabuľky údajov. Ďalšie informácie nájdete v časti Urýchlenie výpočtov v pracovnom hárku, ktorý obsahuje tabuľky s údajmi.

Tabuľka údajov s jednou premennou obsahuje svoje vstupné hodnoty buď v jednom stĺpci (orientovaná na stĺpce), alebo v jednom riadku (orientovaná na riadky). Každý vzorec v tabuľke údajov s jednou premennou sa musí vzťahovať iba na jednu vstupná bunka.

Postupujte takto:

1. Do vstupnej bunky zadajte zoznam hodnôt, ktoré chcete nahradiť – buď v jednom stĺpci alebo v jednom riadku. Nechajte niekoľko prázdnych riadkov a stĺpcov na oboch stranách hodnôt.

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

   • Ak sú údaje orientované na stĺpce (hodnoty premenných sú v stĺpci), zadajte vzorec do bunky o jeden riadok vyššie a o jednu bunku vpravo od stĺpca hodnôt. Táto tabuľka s jednou premennou je orientovaná na stĺpce a vzorec je uvedený v bunke D2.
     
     
     
     Ak chcete preskúmať vplyv rôznych hodnôt na ostatné vzorce, zadajte ďalšie vzorce do buniek napravo od prvého vzorca.

   • Ak sú údaje v tabuľke orientované na riadky (hodnoty premenných sú v riadku), zadajte vzorec do bunky jeden stĺpec vľavo od prvej hodnoty a jednu bunku pod riadkom hodnôt.
     
     Ak chcete preskúmať vplyv rôznych hodnôt na ostatné 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 Analýza hypotéz >Tabuľka dát (v skupine Nástroje pre prácu s údajmi alebo Predpoveď skupiny Excel 2016).

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

   • Ak sú údaje orientované na stĺpce, zadajte odkaz na bunku pre vstupnú bunku v poli Vstupná bunka stĺpca. Na obrázku vyššie je vstupná bunka B3.

   • Ak sú údaje orientované na riadky, zadajte odkaz na bunku pre vstupnú bunku do poľa Vstupná bunka riadku.

     Poznámka: Po vytvorení tabuľky údajov môžete zmeniť formát buniek s výsledkami. Na obrázku sú výsledkové bunky 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. Urobte niektorú z týchto možností:

   • Ak sú údaje orientované na stĺpce, zadajte nový vzorec do prázdnej bunky vpravo od existujúceho vzorca v hornom riadku tabuľky údajov.

   • Ak sú údaje orientované na riadky, 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ľka údajov (v skupine Nástroje údajov alebo Predpoveď v skupine Excel 2016).

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

   • Ak sú údaje orientované na stĺpce, zadajte odkaz na bunku pre vstupnú bunku do poľa Vstupná bunka stĺpca.

   • Ak sú údaje orientované na riadky, zadajte odkaz na bunku pre vstupnú bunku do poľa Vstupná bunka riadku.

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 pracovnom hárku zadajte vzorec, ktorý odkazuje na dve vstupné bunky.

   V nasledujúcom príklade, v ktorom sú počiatočné hodnoty vzorca zadané v bunkách B3, B4 a B5, zadáte do bunky C2 vzorec =PMT(B3/12,B4,-B5).

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 do toho istého riadku ako vzorec – napravo od neho.

   Ú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 údajov alebo Predpoveď (v Excel 2016) vyberte položku Analýza hypotéz >Tabuľka údajov (v skupine Nástroje údajov alebo Predpoveď skupiny Excel 2016).

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

7. Do poľa Stĺpec vstupnej bunky zadajte odkaz na vstupnú bunku pre vstupné hodnoty v stĺpci.
   Do poľa Stĺpec vstupnej bunky zadajte B3.

8. Vyberte položku 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 platobný vzorec =PMT(B3/12,B4,-B5), ktorý používa dve vstupné bunky, B3 a B4.

Ak nastavíte túto možnosť výpočtu, pri prepočte celého zošitu sa nevykonajú žiadne výpočty v tabuľke údajov. Ak chcete tabuľku údajov prepočítať manuálne, vyberte jej vzorce a potom stlačte kláves F9.

Postupujte podľa týchto krokov, aby ste zlepšili výkon výpočtov:

1. Vyberte Súbor > Možnosti > Vzorce.

2. V časti Možnosti výpočtu vyberte Automaticky.

   Tip: Voliteľne na karte Vzorce vyberte šípku na Možnosti výpočtu a potom vyberte Automaticky.

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

Hľadanie riešenia

Ak poznáte výsledok, ktorý môžete očakávať od vzorca, ale neviete presne, akú vstupnú hodnotu vzorec potrebuje na dosiahnutie tohto výsledku, použite funkciu Hľadanie cieľa. Pozrite si článok Pomocou funkcie Hľadať cieľ nájdite požadovaný výsledok úpravou vstupnej hodnoty.

Riešiteľ programu Excel

Pomocou doplnku Excel Solver môžete nájsť optimálnu hodnotu pre súbor vstupných premenných. Solver pracuje so skupinou buniek (nazývaných rozhodovacie premenné alebo jednoducho premenné bunky), ktoré sa používajú pri výpočte vzorcov v cieľových a obmedzujúcich 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 nástroja Solver.