Ako vytvoriť tabuľku údajov pre analýzu typu „čo keby“ v Exceli

Program Microsoft Excel ponúka niekoľko funkcií na vykonávanie analýz možných scenárov. Jedným z týchto nástrojov je tabuľka údajov. S ním môžete experimentovať s rôznymi hodnotami, aby ste videli rôzne výsledky.

Je to užitočný spôsob, ako vidieť veci, ako sú pôžičky, investície, prirážky produktov a podobne, s rôznymi premennými. Môžete napríklad zadať niekoľko rôznych úrokových sadzieb pre pôžičku, aby ste videli, aká by bola vaša platba s každou sadzbou, a to všetko na jednom mieste.

Vytvorte tabuľku údajov s jednou premennou

Tabuľky údajov v Exceli môžu obsahovať jeden alebo dva rôzne vstupy. Začnime teda dátovou tabuľkou s jednou premennou. Vypočítali sme budúcu hodnotu našej investície pomocou funkcie FV a konštánt pre ročnú úrokovú sadzbu, počet platieb a výšku platby.

Tu uvidíme, aká by bola budúca hodnota našej investície s rôznymi úrokovými sadzbami.

Hodnoty môžete zadať do stĺpca alebo riadka, ale umiestnenie ovplyvňuje, kam by ste mali vzorec umiestniť. Ak sú vaše hodnoty v stĺpci, umiestnite vzorec o bunku vyššie a vpravo. Ak sú vaše hodnoty v riadku, umiestnite vzorec o bunku nižšie a vľavo.

V našom príklade používame tabuľku údajov orientovaných na stĺpce. Ako môžete vidieť na obrázku nižšie, náš vzorec sme presunuli do stĺpca D a naše úrokové sadzby sme uviedli v stĺpci C.

Vyberte bunky obsahujúce vzorec, hodnoty a očakávané výstupy. Pre nás je to C2 až D6.

Prejdite na kartu Údaje, kliknite na rozbaľovaciu šípku Analýza What-If a vyberte „Tabuľka údajov“.

Do poľa Tabuľka údajov, ktoré sa otvorí, zadajte odkaz na bunku pre meniacu sa premennú a podľa vášho nastavenia. V našom príklade zadáme odkaz na bunku B3 pre meniacu sa úrokovú sadzbu do poľa Vstupná bunka stĺpca. Opäť používame tabuľku údajov založenú na stĺpcoch.

Kliknite na „OK“ a uvidíte svoje výsledky. Teraz máme budúcu hodnotu našej investície s každou úrokovou sadzbou, ktorú sme zadali.

Vytvorte tabuľku údajov s dvoma premennými

Pre podrobnejšiu analýzu možno budete chcieť pridať dve premenné do tabuľky s údajmi. Ak použijeme rovnaký príklad, chceme nielen budúcu hodnotu našej investície s rôznymi úrokovými sadzbami, ale tiež by sme radi videli rôzne budúce hodnoty zmenou počtu platieb.

Keďže používate dve premenné, budete používať tabuľku údajov orientovanú na stĺpce aj riadky. Vzorec teda umiestnite priamo nad stĺpec hodnôt a naľavo od riadka hodnôt, ako je znázornené nižšie.

Teraz vyberte všetky bunky obsahujúce vzorec, hodnoty a očakávané výstupy. Pre nás je to C2 až F6.

Prejdite na kartu Údaje a vyberte položku Čo keby analýza > Tabuľka údajov. Keď sa zobrazí pole, zadajte odkazy na vstupné bunky riadkov aj stĺpcov.

Pomocou nášho príkladu zadáme B3 pre bunku vstupu stĺpca, ktorá je pre našu úrokovú sadzbu a B4 pre bunku vstupu riadka, ktorá je pre náš počet platieb.

Kliknite na „OK“ a mali by ste vidieť všetky výstupy vašich premenných. Tu máme budúcu hodnotu našej investície pomocou rôznych úrokových sadzieb a rôzneho počtu platieb, všetko zobrazené v peknej a prehľadnej tabuľke.

Keď budete nabudúce chcieť analyzovať údaje v hárku s rôznymi číslami alebo množstvami, zvážte použitie tabuľky údajov. Nielenže sa za vás vykonajú výpočty, ale výsledok vám poskytne štruktúrovanú tabuľku pre jednoduchú analýzu.