Chtěli jste někdy vyzkoušet různé hodnoty pro vzorec, abyste zjistili, jaký výstup pro každý z nich získáte? Datová tabulka v Excelu je skvělý nástroj, jak se podívat na možnosti.
Analýza What-If v aplikaci Excel: Tabulka dat
Nástroje What-If Analysis v aplikaci Excel jsou vynikající sadou funkcí, které vám umožňují předpovědět změnu ve vašich výstupních datech ohledně změny hodnot. Excel má tři nástroje pro analýzu What-If: Scenario Manager, Goal Seek a Data Table.
Nástroj Tabulka dat vám umožňuje zobrazit, jak různé vstupy ovlivní výsledek vašeho vzorce. Pomocí tabulky dat můžete získat perspektivu vašeho vzorce a zjistit, jaký výstup můžete získat z různých vstupů.
Jak funguje tabulka dat
Tabulka dat v aplikaci Excel převezme sadu vstupů, umístí je do vzorce a nakonec vytvoří tabulku výstupů pro každý vstup.
Abyste mohli tabulku dat využívat v Excelu, musíte mít nejprve připravený vzorec. Potom můžete vzorec odkázat v jiné buňce a zpracovat na něm tabulku dat. Nakonec můžete do tabulky dat přidat dvě sady dat: Vstupní buňka řádku a Vstupní buňka sloupce .
Tabulka dat pak převezme hodnoty v sousedním řádku jako vstup pro vstupní buňku Řádek a hodnoty v sousedním sloupci jako vstup pro vstupní buňku Sloupec a vytvoří tabulku výstupů vzorce.
Příbuzný: Jak vytvořit kontingenční tabulku v aplikaci Excel
Příklad 1: Tabulka dvou proměnných dat
V tomto případě předpokládejme, že máte šest autíček s různými cenami a chcete vědět, jaké tržby získáte prodejem daného množství.
Chcete -li to provést, musíte vynásobit počet prodaných hraček určitého typu cenou a nakonec odečíst sazbu daně, abyste dosáhli příjmů.
Celkově tedy pro tuto tabulku dat budete mít dva variabilní vstupy: množství a cenu. Nejprve vytvoříme vzorec:
- V buňkách A1 , B1 , C1 , a D1 , vepište Cena , Množství , Daň, a Příjmy , resp.
- Vyberte buňky A1 a D1 .
- V Domov záložka z Čísla klikněte na $ symbol pro změnu formátování čísel těchto buněk na Účetnictví (Důvodem je, že tyto buňky budou obsahovat účetní hodnoty).
- Vyberte buňku C2 .
- V Domov záložka z Čísla klikněte na % symbol pro změnu formátování čísla této buňky na Procento .
- Vyberte buňku D2 v části Tržby a zadejte následující vzorec na řádku vzorců a stiskněte Enter: | _+_ | Tento vzorec vynásobí cenu prodaných jednotek (A2) jejich množstvím (B2) a poté z něj odečte daňovou hodnotu (A2*B2*C2).
Můžete pokračovat a dát buňkám ukázkové hodnoty a sledovat, jak Excel vypočítává tržby získané z prodeje.
Příbuzný: Jak skrýt nebo zobrazit sloupce a řádky v aplikaci Excel
oříznout obrázek do kruhu
Tabulka různých vstupů aplikace Excel
Chcete -li vytvořit tabulku dat pro různé vstupy, musíte mít holou tabulku obsahující dva vstupy.
- Vyberte buňku G2 a do řádku vzorců zadejte vzorec níže: | _+_ | Tím se nastaví buňka G2 rovnající se vzorci, který jste vytvořili dříve.
- V buňkách níže G2 (sloupec G), zadejte možná množství prodaných kusů. V tomto případě jsou čísla 5, 10, 15, 20, 25 a 30.
- V buňkách vedle G2 (řádek 2), zadejte ceny každého kusu. V tomto případě jsou ceny 10, 20, 30, 40, 50 a 60.
- Vyberte buňky, do kterých jste vložili ceny, a buňky níže, které zobrazí možné příjmy, a změňte jejich formátování čísel na Účetnictví .
Nakonec, když máte sadu řádků a sloupců, je na čase tuto tabulku převést na tabulku dat.
- Vyberte tabulku kliknutím G1 a přetažením až na doraz M7 .
- Přejděte na Karta Data , a v sekci Prognóza klikněte Analýza What-If . Zobrazí se seznam tří položek.
- Ze seznamu vyberte Datová tabulka . Zobrazí se dialogové okno Tabulka dat.
- Do vstupní buňky řádku zadejte A2 . Řádek tabulky obsahuje ceny a zadaná cena ve vašem původním vzorci je buňka A2.
- Do vstupní buňky Sloupec zadejte B2 . Sloupec v tabulce obsahuje množství prodaných kusů.
- Jakmile nastavíte dva vstupy, klikněte OK . Excel nyní vygeneruje tabulku dat.
Nyní máte tabulku dat pro svůj vzorec! Tabulka dat vám poskytuje cenné informace o vašich potenciálních tržbách.
Nyní například můžete vidět, jak můžete získat tržby ve výši 1 000 $ nebo vyšší, když se podíváte na šest buněk v pravém dolním rohu. Nebo můžete porovnat a vědět, že prodejem 25 aut z 20 dolarů získáte větší příjem než prodejem 15 aut z 30 dolarů.
Příklad 2: Tabulka dat s jednou proměnnou
Protože tabulka dat je koneckonců tabulka, může obsahovat pouze vstupy v řádku a sloupci. To znamená, že v datové tabulce nemůžete mít více než dva variabilní vstupy. Určitě však můžete mít méně než dva: Tabulka dat s jediným proměnným vstupem.
U tohoto příkladu mějte na paměti předchozí příklad. Předpokládejme však, že tentokrát chcete získat tabulku potenciálních příjmů výhradně za 50 $ autíček.
Metoda je stále stejná jako tabulka dvou proměnných dat, i když umístění se mírně liší.
Příbuzný: Jak kopírovat vzorce v aplikaci Microsoft Excel
Chcete -li začít s vytvářením tabulky dat, musíte vytvořit vzorec. Vzorec pro tento příklad je stejný jako předchozí. Jakmile budete mít vzorec připraven, je čas nastavit data.
- Vyberte buňku H1 a do řádku vzorců zadejte vzorec níže a stiskněte Vstupte :
=(A2*B2)-(A2*B2*C2)
- Zadejte čísla do buněk G2 a níže. V tomto případě zadejte do buněk 5, 10, 15, 20, 25 a 30 G2 na G7 .
Nyní je čas vytvořit tabulku dat.
- Vyberte tabulku kliknutím G1 a přetažením na H7 .
- Přejděte na Karta Data a v části Prognóza klikněte na Analýza What-If .
- Ze seznamu What-If Analysis vyberte Datová tabulka .
- V dialogu Tabulka dat klikněte na Vstupní buňka sloupce a zadejte B2 .
- Opustit Vstupní buňka řádku prázdný.
- Vzhledem k tomu, že cílem je získat tabulku údajů o příjmech za pevnou cenu, stačí do tabulky dat přidat pouze počet prodaných autíček a ne jejich ceny. Množství je uvedeno v řádku tabulky dat a jeho vstupem ve vzorci je buňka B2.
- Klikněte OK . Excel vytvoří tabulku dat s jednou proměnnou.
Nyní tedy můžete zjistit, jaké příjmy můžete generovat prodejem autíček za 50 $, samozřejmě bez daní.
Nastavte možnosti v tabulce
Tabulka dat vám dává dobrý přehled o výsledku vašeho vzorce s různými vstupy a nyní víte, jak jej vytvořit.
neznámý požadavek na deskriptor zařízení USB selhal Windows 10
Tabulka dat je pouze jedním z nástrojů analýzy What-If v aplikaci Excel. Pokud chcete v Excelu odpovědět na všechny své otázky typu „co kdyby“, je toho ještě mnoho dalšího.
Podíl Podíl tweet E-mailem Jak používat hledání cílů v aplikaci Microsoft Excel pro scénáře What-IfMáte výstup pro vzorec, ale neznáte vstup? Zpětné řešení s hledáním cílů může být přesně to, co potřebujete.
Číst dále Související témata- Produktivita
- Tabulkový procesor
- Microsoft Excel
- Matematika
- Tipy pro Microsoft Office
Amir je student farmacie s vášní pro technologie a hry. Rád hraje hudbu, řídí auta a píše slova.
Více od Amira M. BohloolihoPřihlaste se k odběru našeho zpravodaje
Připojte se k našemu zpravodaji a získejte technické tipy, recenze, bezplatné elektronické knihy a exkluzivní nabídky!
Kliknutím sem se přihlásíte k odběru