Jak vytvořit tabulku dat se dvěma proměnnými v Excelu 2016

V Excelu 2016 tabulka dat se dvěma proměnnými nahrazuje řadu možných hodnot pro dvě vstupní hodnoty v jednom vzorci. Chcete-li vytvořit tabulku dat se dvěma proměnnými, zadejte do dialogového okna Tabulka dat dva rozsahy možných vstupních hodnot pro vzorec.

Zadejte rozsah hodnot pro vstupní buňku řádku v prvním řádku tabulky a rozsah hodnot pro vstupní buňku sloupce v prvním sloupci tabulky. Vzorec (nebo jeho kopii) pak můžete zadat do buňky umístěné na průsečíku tohoto řádku a sloupce vstupních hodnot.

Tento typ situace ilustruje následující obrázek. Tato verze tabulky předpokládaných prodejů používá k výpočtu předpokládaných prodejů na rok 2017 dvě proměnné: tempo růstu jako procento nárůstu oproti loňským prodejům (v buňce B3 s názvem Growth_2017) a výdaje vypočítané jako procento loňských prodejů (v buňka B4 s názvem Výdaje_2017). V tomto příkladu je původní vzorec vytvořený v buňce B5 o něco složitější:

Tabulka projekce prodeje se sérií možných procent růstu a nákladů, kterou lze připojit k tabulce dat se dvěma proměnnými.

=Prodej_2016+(Prodej_2016*Růst_2017) - (Prodej_2016*Výdaje_2017)

Chcete-li nastavit tabulku dat se dvěma proměnnými, přidejte řádek možných procentních podílů Expenses_2017 od 10 % do 35 % v rozsahu C7:H7 do sloupce možných procentních podílů Growth_2017 od 1,00 % do 5,00 % v rozsahu B8:B24. Poté zkopírujte původní vzorec s názvem Projected_Sales_2017 z buňky B5 do buňky B7, buňky na průsečíku tohoto řádku procent Expenses_2017 a sloupce procent Growth_2017 se vzorcem:

=Projected_Sales_2017

Pomocí těchto několika kroků vytvořte datovou tabulku se dvěma proměnnými, kterou vidíte na následujícím obrázku:

Vyberte rozsah buněk B7:H24.

Tento rozsah buněk zahrnuje kopii původního vzorce spolu s řadou možných výdajů a procenty růstu.

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

Excel otevře dialogové okno Tabulka dat s kurzorem v textovém poli Vstupní buňka řádku.

Klepnutím na buňku B4 zadejte do textového pole Buňka pro vstup řádku absolutní adresu buňky, $B$4.

Klepněte na textové pole Buňka pro zadání sloupce a poté klepněte na buňku B3 a zadejte do tohoto textového pole absolutní adresu buňky $B$3.

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

Excel vyplní prázdné buňky datové tabulky vzorcem TABLE pomocí B4 jako vstupní buňky řádku a B3 jako vstupní buňky sloupce.

Klepněte na buňku B7, potom klepněte na příkazové tlačítko Kopírovat formát ve skupině Schránka na kartě Domů a tažením přes oblast buněk C8:H24 zkopírujte formát účetního čísla bez desetinných míst do tohoto rozsahu.

Tento formát účetního čísla je příliš dlouhý na zobrazení vzhledem k aktuální šířce sloupců C až F — indikováno symboly ######. S rozsahem C8:H24 stále vybraným pomocí nástroje Format Painter, krok 7 tento problém řeší.

Klikněte na příkazové tlačítko Formát ve skupině Buňky na kartě Domů a poté v rozevírací nabídce klikněte na možnost Přizpůsobit šířku sloupce.

Tabulka projekce prodeje po vytvoření datové tabulky dvou proměnných v rozsahu C8:H24.

Maticový vzorec {=TABLE(B4,B3)}, který Excel vytvoří pro tabulku dat se dvěma proměnnými v tomto příkladu, určuje argument vstupní buňka řádku (B4) i argument vstupní buňka sloupce (B3). Protože tento jediný maticový vzorec je zadán do celého rozsahu datové tabulky C8:H24, jakákoliv úprava (ve smyslu přesouvání nebo mazání) je omezena na tento rozsah.