Jak vytvořit tabulku dat s jednou proměnnou v Excelu 2016

Datové tabulky umožňují zadat řadu možných hodnot, které pak Excel 2016 zapojí do jediného vzorce. Datová tabulka s jednou proměnnou nahrazuje ve vzorci řadu možných hodnot pro jednu vstupní hodnotu.

Následující obrázek ukazuje tabulku projekcí prodeje 2017, pro kterou má být vytvořena datová tabulka s jednou proměnnou. V tomto listu se předpokládaná částka prodeje v buňce B5 vypočítá tak, že se celkový objem prodeje za loňský rok v buňce B2 přičte k částce, u které se očekává růst v roce 2017 (vypočte se vynásobením loňského součtu v buňce B2 procentem růstu v buňce B3 ), což vám dává vzorec

Tabulka projekce prodeje se sloupcem možných procent růstu, který lze zapojit do tabulky dat s jednou proměnnou.

=B2+(B2*B3)

Protože jste po provedení výběru A2:B5 klikli na příkazové tlačítko Vytvořit z výběru na kartě Vzorce na pásu karet a přijali jste výchozí zaškrtávací políčko Levý sloupec, vzorec používá záhlaví řádků ve sloupci A a zní:

=Sales_2016+ (Sales_2016*Růst_2017)

Jak můžete vidět na tomto obrázku, sloupec možných temp růstu v rozmezí od 1 % až po 5,0 % byl vložen do sloupce B v rozsahu B8:B24.

Tabulka projekce prodeje po vytvoření tabulky dat s jednou proměnnou v rozsahu C8:C24.

Chcete-li vytvořit zobrazenou tabulku dat s jednou proměnnou, která zapojí každou z těchto hodnot do vzorce růstu prodeje, postupujte takto:

Zkopírujte původní vzorec zadaný v buňce B5 do buňky C7 zadáním = (rovná se) a kliknutím na buňku B5 vytvořte vzorec =Projected_Sales_2017.

Kopie původního vzorce (k nahrazení řady různých rychlostí růstu v B8:B24) je nyní záhlavím sloupce pro tabulku dat s jednou proměnnou.

Vyberte oblast buněk B7:C24.

Rozsah tabulky dat zahrnuje vzorec spolu s různými rychlostmi růstu.

Klikněte na Data→ What-If Analysis→ Data Table na pásu karet.

Excel otevře dialogové okno Tabulka dat.

Klikněte na textové pole Buňka pro zadání sloupce v dialogovém okně Tabulka dat a poté v listu klikněte na buňku B3, buňku Growth_2017 s původním procentem.

Excel vloží absolutní adresu buňky $B$3 do textového pole Buňka pro zadání sloupce.

Klepnutím na tlačítko OK zavřete dialogové okno Tabulka dat.

Jakmile klepnete na OK, Excel vytvoří datovou tabulku v rozsahu C8:C24 zadáním vzorce pomocí funkce TABLE do tohoto rozsahu. Každá kopie tohoto vzorce v datové tabulce používá k určení možného výsledku procento rychlosti růstu ve stejném řádku ve sloupci B.

Klikněte na buňku C7, poté klikněte na příkazové tlačítko Formátovat malíř ve skupině Schránka na kartě Domů a přetáhněte přes oblast buněk C8:C24.

Excel zkopíruje formát Účetního čísla do rozsahu možných výsledků vypočítaných touto datovou tabulkou.

Několik důležitých věcí k poznámce o tabulce dat s jednou proměnnou vytvořené v této tabulce:

• Pokud upravíte jakákoli procenta tempa růstu v oblasti buněk B8:B24, Excel okamžitě aktualizuje související předpokládaný výsledek prodeje v datové tabulce. Chcete-li zabránit Excelu v aktualizaci datové tabulky, dokud neklepnete na příkazové tlačítko Vypočítat (F9) nebo Vypočítat list (Shift+F9) na kartě Vzorce, klikněte na tlačítko Možnosti výpočtu na kartě Vzorce a poté klikněte na tlačítko Automaticky kromě datových tabulek možnost (Alt+MXE).

• Pokud se pokusíte odstranit jakýkoli jeden vzorec TABLE v oblasti buněk C8:C24, Excel zobrazí výstrahu Nelze změnit část datové tabulky. Než stisknete klávesu Delete nebo klepnete na tlačítko Clear nebo Delete na kartě Domů, musíte vybrat celý rozsah vzorců (v tomto případě C8:C24).