Jak vytvořit a používat datové tabulky v Excelu

tutoriál ukazuje, jak používat datové tabulky pro What-if analýzu v aplikaci Excel. Naučte se, jak vytvořit jednu proměnnou a dva-variabilní stůl vidět účinky jeden nebo dva vstupní hodnoty na své vzorce, a jak nastavit datové tabulky k vyhodnocení více vzorců najednou.

vytvořili jste komplexní vzorec závislý na více proměnných a chcete vědět, jak změna těchto vstupů mění výsledky., Namísto testování každé proměnné jednotlivě, aby co-if analýza dat tabulky a sledovat všechny možné výsledky s rychlým pohledem!

• Data tabulky v aplikaci Excel
• Vytvořit jednu proměnnou tabulka dat
• Vytvořit dvě proměnné data tabulky
• tabulka Dat k vyhodnocení více vzorců,
• data aplikace Excel tabulek – 3 věci, které byste měli vědět
• Delete dat tabulky v aplikaci Excel
• Upravit údaje tabulka výsledků
• Přepočítat data tabulky ručně

Co je datová tabulka v aplikaci Excel?,

v aplikaci Microsoft Excel je datová tabulka jedním z nástrojů analýzy What-If, který vám umožňuje vyzkoušet různé vstupní hodnoty vzorců a zjistit, jak změny těchto hodnot ovlivňují výstup vzorců.

Data tabulky jsou zvláště užitečné, když vzorec závisí na několika hodnot, a chcete experimentovat s různými kombinacemi vstupů a porovnat výsledky.

V současné době existuje jedna proměnná datová tabulka a dvě proměnné datové tabulky. Přestože je omezena na maximálně dvě různé vstupní buňky, datová tabulka umožňuje testovat tolik proměnných hodnot, kolik chcete.,

Jak vytvořit jednu proměnnou tabulka dat v aplikaci Excel

Jednou proměnnou tabulka dat v aplikaci Excel umožňuje testování řady hodnot pro jeden vstupní buňka a ukazuje, jak tyto hodnoty ovlivňují výsledek a související vzorce.,

abychom vám pomohli lépe porozumět této funkci, budeme spíše následovat konkrétní příklad než popisovat obecné kroky.

Předpokládejme, že uvažujete o uložení svých úspor v bance, která platí 5% úrok, který se skládá měsíčně. Chcete-li zkontrolovat různé možnosti, vytvořili jste následující složenou kalkulačku úroků, kde:

• B8 obsahuje vzorec FV, který vypočítává konečný zůstatek.
• B2 je proměnná, kterou chcete otestovat (počáteční investice).,

A teď, pojďme udělat jednoduchý What-if analýzy, aby viděli, co vaše úspory budou za 5 let v závislosti na výši počáteční investice, v rozmezí od $1,000 na $6,000.

zde jsou kroky k vytvoření tabulky dat s jednou proměnnou:

1. zadejte hodnoty proměnných buď do jednoho sloupce nebo do jednoho řádku. V tomto příkladu vytvoříme sloupec orientované datové tabulce, a tak jsme náš typ proměnné hodnoty ve sloupci (D3:D8) a nechte je alespoň jeden prázdný sloupec vpravo pro výsledky.,
2. zadejte svůj vzorec do buňky o jeden řádek výše a jednu buňku vpravo od proměnných hodnot (v našem případě E2). Nebo propojte tuto buňku se vzorcem v původním datovém souboru (pokud se rozhodnete změnit vzorec v budoucnu, musíte aktualizovat pouze jednu buňku). Volíme druhou možnost, a zadejte tento jednoduchý vzorec v buňce E2: =B8
   
   Tip., Pokud chcete zkoumat vliv proměnných hodnot na jiné vzorce, které odkazují ke stejné vstupní buňce, zadejte další vzorce(s) vpravo od prvního vzorce, jak je znázorněno v tomto příkladu.
3. Vyberte rozsah datových tabulek, včetně vzorce, buněk proměnných hodnot a prázdných buněk pro výsledky (D2:E8).,
4. Přejděte na kartu Data > Nástroje pro Data skupinu, klepněte na What-if Analýzy tlačítko, a potom klepněte na tlačítko Datové Tabulky…
   
5. V dialogu Tabulka Dat okna, klepněte do pole Vstupní buňka Sloupce (protože naše Investiční hodnoty jsou ve sloupci), a vyberte proměnnou buňku, na kterou odkazuje vaše vzorce. V tomto příkladu vybereme B3, který obsahuje počáteční investiční hodnotu.
   
6. klikněte na OK a Excel okamžitě naplní prázdné buňky výsledky odpovídající proměnné hodnotě ve stejném řádku.,
7. použijte požadovaný formát čísla na výsledky (měna v našem případě) a je dobré jít!

Nyní, můžete se rychle podívat na vaše one-proměnné, datové tabulky, zkoumat možné váhy a vybrat si optimální velikost vklad:

Řádek orientované na data tabulky

výše uvedený příklad ukazuje, jak nastavit vertikální nebo sloupci-orientovaný, dat tabulky v aplikaci Excel., Pokud dáváte přednost horizontální rozložení, tady je to, co je třeba udělat:

1. Typ proměnné hodnoty v řadě, přičemž alespoň jeden prázdný sloupec vlevo (pro vzorec) a jeden prázdný řádek níže (výsledky). Pro tento příklad zadáme proměnné hodnoty v buňkách F3: J3.
2. zadejte vzorec do buňky, která je jeden sloupec vlevo od první proměnné hodnoty a jedna buňka níže (E4 v našem případě).,
3. Vytvořit tabulku s údaji, jak je popsáno výše, ale zadejte vstupní hodnotu (B3) ve vstupní buňka Řádku pole:
   
4. Klepněte na tlačítko OK, a budete mít následující výsledek:
   

Jak vytvořit dvě proměnné, datové tabulky v Excelu

dvou-proměnné, datové tabulky, ukazuje, jak různé kombinace 2 sady proměnných hodnot ovlivní výsledek vzorce. Jinými slovy, ukazuje, jak změna dvou vstupních hodnot stejného vzorce mění výstup.,

kroky k vytvoření dvou proměnných dat tabulky v aplikaci Excel jsou v podstatě stejné jako v příkladu výše, s výjimkou, že zadáte dva rozsahy možné vstupní hodnoty, jeden v řadě a další ve sloupci.

Chcete-li vidět, jak to funguje, použijeme stejnou složenou úrokovou kalkulačku a prozkoumáme účinky velikosti počáteční investice a počtu let na zůstatku. Chcete-li to provést, nastavte tabulku dat tímto způsobem:

1. zadejte svůj vzorec do prázdné buňky nebo propojte tuto buňku s původním vzorcem., Ujistěte se, že máte dostatek prázdných sloupců na pravé a prázdné řádky níže, aby vyhovovaly vašim proměnným hodnotám. Stejně jako dříve, jsme odkaz na buňku E2 původní FV vzorec, který vypočítá zůstatek: =B8
2. Typ jednu sadu vstupních hodnot pod vzorec, ve stejném sloupci (hodnoty investic v E3:E8).
3. zadejte druhou sadu proměnných hodnot napravo od vzorce ve stejném řádku (počet let v F2:H2).,

   V tomto bodě, vaše dvě proměnné údaje tabulka by měla vypadat podobně jako tento:
   

4. Vyberte celou tabulku dat rozsah včetně vzorec, řádek a sloupec proměnné hodnoty, a buňky, ve kterých vypočtené hodnoty se objeví. Vybereme rozsah E2: H8.,
5. Vytvoření tabulky dat v již známém způsobem: záložka Data > What-if Analýzy tlačítko > Data Tabulky…
6. V vstupní buňka Řádku zadejte odkaz na vstupní buňku pro hodnoty proměnných v řádku (v tomto příkladu, to je to, B6 obsahující Letech hodnota).
7. do pole vstupní buňky sloupce zadejte odkaz na vstupní buňku pro proměnné hodnoty ve sloupci (B3 obsahující počáteční investiční hodnotu).
8. klikněte na OK.,n našem případě), a analyzovat výsledky:

   

Data tabulka pro porovnání více výsledků

Pokud chcete hodnotit více než jeden vzorec, ve stejné době, stavět své datové tabulky, jak je uvedeno v předchozích příkladech, a zadejte další vzorce(s):

• vpravo od prvního vzorce v případě vertikální tabulka dat ve sloupcích
• Pod první vzorec v případě horizontální data tabulky organizované v řadách

Pro „multi-kategorie“ datové tabulky, aby správně fungovat, všechny vzorce by měla odkazovat ke stejné vstupní buňce.,

jako příklad přidáme ještě jeden vzorec do naší Tabulky s jednou proměnnou pro výpočet úroků a uvidíme, jak je ovlivněna velikostí počáteční investice. Tady je to, co musíme udělat:

1. V buňce B10, výpočet úroky s tímto vzorcem: =B8-B3
2. Uspořádat data, tabulky, zdroje dat, jako jsme to udělali dříve: hodnoty proměnných v D3:D8 a E2 spojené s B8 (Bilance vzorec).,
3. Přidáme ještě jeden sloupec údajů, oblast tabulky (sloupec F), a odkaz F2 pro B10 (úroky vzorec):
   
4. Vyberte rozšířené údaje tabulka rozsahu (D2:F8).
5. Open Data Tabulka dialogové okno, klepnutím na kartu Data > What-if Analýzy > Data Tabulky…
6. V Vstupní buňka Sloupce box, napájení vstupní buňky (B3), a klepněte na tlačítko OK.,

Voila, nyní můžete pozorovat vlivy proměnné hodnoty na obou vzorců:

Data tabulky v aplikaci Excel – 3 věci, které byste měli vědět

Chcete-li efektivně používat datové tabulky v Excelu, prosím, mějte na paměti tyto 3 jednoduchých faktů:

1. Pro tabulku s údaji, které mají být vytvořeny úspěšně, vstupní buňka(y) musí být na stejném listu jako data tabulky.,
2. Microsoft Excel používá TABULKA(row_input_cell, colum_input_cell) funkce pro výpočet data tabulka výsledky:
   • V jedné-proměnné, datové tabulky, jedním z argumentů je vynechán, v závislosti na rozložení (column-oriented nebo řádek-oriented). Například v naší horizontální tabulce dat s jednou proměnnou je vzorec =TABLE(, B3), kde B3 je Vstupní buňka sloupce.
   • v tabulce dvou proměnných dat jsou oba argumenty na svém místě. Například =TABLE(B6, B3) kde B6 je Vstupní buňka řádku a B3 je Vstupní buňka sloupce.,

   funkce tabulky se zadává jako vzorec pole. Ujistěte se, vyberte jakoukoli buňku s vypočtenou hodnotou, podívejte se na řádku vzorců, a vědomí, {složené závorky} kolem formule. Nejedná se však o normální vzorec pole – nemůžete jej zadat do lišty vzorců ani upravit existující. Je to jen „pro show“.

3. protože výsledky tabulky dat jsou vypočteny pomocí vzorce pole, výsledné buňky nelze upravovat jednotlivě. Můžete pouze upravit nebo odstranit celou řadu buněk, jak je vysvětleno níže.,

jak odstranit datovou tabulku v Excelu

Jak bylo uvedeno výše, Excel neumožňuje mazání hodnot v jednotlivých buňkách obsahujících výsledky. Kdykoli se o to pokusíte, zobrazí se chybová zpráva „Nelze změnit část datové tabulky“.

můžete však snadno vymazat celé pole výsledných hodnot. Zde je návod:

1. v závislosti na vašich potřebách vyberte všechny buňky tabulky dat nebo pouze buňky s výsledky.
2. stiskněte klávesu Delete.

Hotovo!, 🙂

jak upravit výsledky tabulky dat

protože není možné změnit část pole v aplikaci Excel, nelze upravovat jednotlivé buňky s vypočtenými hodnotami. Všechny tyto hodnoty můžete nahradit pouze vlastním provedením těchto kroků:

1. Vyberte všechny výsledné buňky.
2. odstraňte vzorec tabulky v panelu vzorců.
3. zadejte požadovanou hodnotu a stiskněte Ctrl + Enter.,

do všech vybraných buněk se vloží stejná hodnota:

jakmile je vzorec tabulky pryč, bývalá datová tabulka se stává obvyklým rozsahem a můžete normálně upravovat libovolnou jednotlivou buňku.

jak ručně přepočítat datovou tabulku

Pokud velká datová tabulka s více proměnnými hodnotami a vzorci zpomaluje Excel, můžete zakázat automatické přepočty v této a všech ostatních datových tabulkách.,

přejděte na kartě Vzorce > Výpočet skupinu, klepněte na tlačítko Výpočet tlačítko Možnosti a potom klepněte na možnost Automaticky s Výjimkou Tabulek Dat.

vypnout automatické datové tabulky, výpočty a urychlit přepočty celý sešit.

ručně přepočítat údaje, tabulky, vyberte jeho výsledné buňky, tj. buňky TABULKY() vzorce a stiskněte klávesu F9.

takto vytvoříte a použijete datovou tabulku v Excelu., Chcete-li se blíže podívat na příklady diskutované v tomto tutoriálu, můžete si stáhnout náš vzorový sešit datových tabulek Excel. Děkuji vám za přečtení a rád bych vás znovu viděl příští týden!

• co-If analýza s cílem hledat v Excelu
• jak používat Řešitel v Excelu: krok za krokem s příklady
• jak seskupovat řádky v Excelu