Ako vytvoriť tabuľku údajov s jednou premennou v Exceli 2016

Tabuľky údajov vám umožňujú zadať sériu možných hodnôt, ktoré potom Excel 2016 zapojí do jedného vzorca. Tabuľka údajov s jednou premennou nahrádza sériu možných hodnôt pre jednu vstupnú hodnotu vo vzorci.

Nasledujúci obrázok zobrazuje tabuľku projekcií predaja na rok 2017, pre ktorú sa má vytvoriť tabuľka údajov s jednou premennou. V tomto pracovnom hárku sa predpokladaná suma predaja v bunke B5 vypočíta pripočítaním minuloročného celkového predaja v bunke B2 k sume, o ktorej sa očakáva nárast v roku 2017 (vypočíta sa vynásobením minuloročného súčtu v bunke B2 percentom rastu v bunke B3 ), čím získate vzorec

Tabuľka projekcie predaja so stĺpcom možných percent rastu, ktorý sa dá pripojiť k tabuľke údajov s jednou premennou.

=B2+(B2*B3)

Pretože ste po výbere A2:B5 klikli na príkazové tlačidlo Vytvoriť z výberu na karte Vzorce na páse s nástrojmi a akceptovali predvolené začiarknutie políčka Ľavý stĺpec, vzorec používa záhlavia riadkov v stĺpci A a znie:

=Predaj_2016+ (Predaj_2016*Rast_2017)

Ako môžete vidieť na tomto obrázku, stĺpec možných mier rastu v rozsahu od 1 % až po 5,0 % bol vložený do stĺpca B v rozsahu B8:B24.

Tabuľka projekcie predaja po vytvorení tabuľky údajov s jednou premennou v rozsahu C8:C24.

Ak chcete vytvoriť zobrazenú tabuľku údajov s jednou premennou, ktorá zapojí každú z týchto hodnôt do vzorca rastu predaja, postupujte podľa týchto jednoduchých krokov:

Skopírujte pôvodný vzorec zadaný v bunke B5 do bunky C7 zadaním = (rovná sa) a kliknutím na bunku B5 vytvorte vzorec =Projected_Sales_2017.

Kópia pôvodného vzorca (na nahradenie série rôznych mier rastu v B8:B24) je teraz hlavičkou stĺpca pre tabuľku údajov s jednou premennou.

Vyberte rozsah buniek B7:C24.

Rozsah tabuľky údajov zahŕňa vzorec spolu s rôznymi rýchlosťami rastu.

Kliknite na Údaje→ Analýza What-If→ Tabuľka s údajmi na páse s nástrojmi.

Excel otvorí dialógové okno Tabuľka údajov.

Kliknite na textové pole Vstupná bunka stĺpca v dialógovom okne Tabuľka údajov a potom kliknite na bunku B3, bunku Rast_2017 s pôvodným percentom v pracovnom hárku.

Excel vloží absolútnu adresu bunky $B$3 do textového poľa Bunka na zadanie stĺpca.

Kliknutím na tlačidlo OK zatvorte dialógové okno Tabuľka údajov.

Hneď ako kliknete na OK, Excel vytvorí tabuľku údajov v rozsahu C8:C24 zadaním vzorca pomocou funkcie TABLE do tohto rozsahu. Každá kópia tohto vzorca v tabuľke údajov používa percento miery rastu v rovnakom riadku v stĺpci B na určenie možného výsledku.

Kliknite na bunku C7, potom kliknite na príkazové tlačidlo Format Painter v skupine Schránka na karte Domov a ťahajte cez rozsah buniek C8:C24.

Excel skopíruje formát účtovného čísla do rozsahu možných výsledkov vypočítaných touto dátovou tabuľkou.

Niekoľko dôležitých vecí, ktoré je potrebné poznamenať o tabuľke údajov s jednou premennou vytvorenej v tejto tabuľke:

• Ak upravíte akékoľvek percentá rýchlosti rastu v rozsahu buniek B8:B24, Excel okamžite aktualizuje priradený výsledok plánovaného predaja v tabuľke údajov. Ak chcete zabrániť Excelu aktualizovať tabuľku údajov, kým nekliknete na príkazové tlačidlo Vypočítať teraz (F9) alebo Vypočítať hárok (Shift+F9) na karte Vzorce, kliknite na tlačidlo Možnosti výpočtu na karte Vzorce a potom kliknite na položku Automaticky okrem tabuliek údajov možnosť (Alt+MXE).

• Ak sa pokúsite odstrániť akýkoľvek vzorec TABUĽKA v rozsahu buniek C8:C24, Excel zobrazí upozornenie Nedá sa zmeniť časť tabuľky údajov. Pred stlačením klávesu Delete alebo kliknutím na tlačidlo Clear alebo Delete na karte Domov musíte vybrať celý rozsah vzorcov (v tomto prípade C8:C24).