Excel What-If Analysis nabízí datovou tabulku. To je špatné jméno. Mělo by se to jmenovat Analýza citlivosti. To je bezva. Přečtěte si o tom zde.
Hledání cíle vám umožní najít sadu vstupů, které vedou ke konkrétnímu výsledku. Někdy chcete vidět mnoho různých výsledků z různých kombinací vstupů. Pokud máte k dispozici pouze dvě vstupní buňky, které chcete změnit, poskytuje datová tabulka rychlý způsob porovnání alternativ.
Na příkladu splátky půjčky řekněte, že chcete vypočítat cenu pro různé zůstatky jistiny a pro různé termíny.
Ujistěte se, že vzorec, který chcete modelovat, je v levém horním rohu rozsahu. Vložte různé hodnoty pro jednu proměnnou dolů do levého sloupce a různé hodnoty pro jinou proměnnou v horní části.
Na kartě Data vyberte možnost Analýza dat, tabulka dat.
V horním řádku vstupní tabulky máte hodnoty. Chcete, aby Excel tyto hodnoty připojil do určité vstupní buňky. Zadejte tuto vstupní buňku jako vstupní buňku řádku.
V levém sloupci jsou hodnoty. Chcete je zapojit do jiné vstupní buňky. Zadejte tuto buňku jako vstupní buňku sloupce.
Když kliknete na OK, Excel zopakuje vzorec v levém horním sloupci pro všechny kombinace horního řádku a levého sloupce. Na obrázku níže vidíte 60 různých splátek půjčky na základě různých výsledků.
Všimněte si, že jsem naformátoval výsledky tabulky tak, aby neobsahovaly žádná desetinná místa, a k přidání červeného / žlutého / zeleného stínování jsem použil Home, Conditional Formatting, Color Scale.
Tady je velká část: Tato tabulka je „živá“. Pokud změníte vstupní buňky podél levého sloupce nebo horního řádku, přepočítají se hodnoty v tabulce. Níže jsou hodnoty nalevo zaměřeny na rozmezí 23 000–24 000 $.
Děkujeme Owenovi W. Greenovi za návrhy stolů.
Sledovat video
- Tři nástroje typu „what-if“ v aplikaci Excel
- Včera - Goal Seek
- Dnes - datová tabulka
- Skvělé pro problémy se dvěma proměnnými
- Drobnosti: funkci pole TABLE nelze zadat ručně - nebude to fungovat
- K vybarvení odpovědí použijte barevnou stupnici
- Co když máte 3 proměnné, které chcete změnit? Scénáře? Ne! Zkopírujte list
- Tabulky se počítají pomalu: režim výpočtu pro všechny tabulky kromě tabulky
- Děkujeme Owenovi W. Greenovi za návrh tohoto tipu
Přepis videa
Naučte se Excel z podcastu, epizoda 2034 - Co s datovou tabulkou!
Podcastuji celou tuto knihu, kliknutím na „i“ v pravém horním rohu se dostanete do seznamu skladeb!
Dnes budeme hovořit o druhém nástroji v rámci What-If Analysis, včera jsme mluvili o Goal Seek, dnes budeme pokrývat datovou tabulku. Takže tady máme tento pěkný malý model, to je malý model, 3 vstupní buňky, jeden vzorec. Ale tento model by mohl být stovky vstupních buněk, tisíce řádků, pokud by došlo k jedné konečné odpovědi, a chceme tuto odpověď modelovat pro několik různých hodnot 2-3 (?) Vstupních buněk. Například by nás možná zajímalo podívat se na různá auta, takže kdekoli od 20000 nahoru, tak tam vložím 20 a 21000, uchopím rukojeť výplně a přetáhnu to dolů na 28000. Přes vrchol my ' Podíváme se na různé podmínky, takže 36měsíční půjčka, 42měsíční půjčka, 48měsíční půjčka, 54, 60, 66 a dokonce 72.
Dobře, tento další krok je zcela volitelný, ale opravdu mi to pomáhá přemýšlet, vždy měním barvy hodnot nahoře a hodnot na levé straně. A opravdu důležitá věc je, že ta rohová buňka, ta důležitá rohová buňka, musí být odpovědí, kterou se snažíme modelovat, dobře. Musíte tedy začít s výběrem z této rohové buňky s odpovědí a poté vybrat všechny řádky a všechny sloupce. Pojďme tedy do Data, What-if Analysis a Data Table a zde se ptáme na dvě věci a tady je, jak byste o tom přemýšleli. Říká se, že v horní řadě tabulky je celá řada různých položek, chci tyto položky po jednom vzít a zapojit je do modelu, kam bychom měli vstupovat? Takže tyto položky, to jsou termíny, měly by jít do buňky B2. A pak,podél levého sloupce je celá řada položek, chceme je vzít, jednu po druhé, a zapojit je do B1, takhle, dobře a klikneme na OK, BAM, běží tento model znovu a znovu a znovu .
Teď jen trochu vyčištění, vždy chodím a dělám Home a pravděpodobně 0 desetinných míst. A možná trochu podmíněného formátování, barevných stupnic, a pojďme s červenými čísly pro velká a zelenými čísly pro malá, jen abych mi dal, víš, způsob, jak to vizuálně sledovat. Nyní to vypadá, že když střílíme za 425 $, jsme trochu, víte, na tomto místě nebo na tomto místě, nebo víte, možná tady, všichni nás přiblíží k 425 $. Takže vidím, jaké jsou různé šance, naše různé kombinace, abychom se dostali k těmto hodnotám.
Nyní pár věcí, tato část uvnitř, je vlastně vzorec velkého pole, takže = TABLE (B2, B1), vstup do řádku a sloupce. To je zvláštní, toto nesmíte psát, můžete to vytvořit pouze pomocí Data, What-If Analysis, musíte použít toto dialogové okno. Pokud se pokusíte tento vzorec napsat, stiskněte Ctrl + Shift + Enter, nebude to fungovat, že? Je to tedy funkce v Excelu, ale pokud jste dost chytří na to, abyste to psali, škoda, že to nebude fungovat, ale neustále se to přepočítává. Takže pokud zjistíme, že se díváme pouze na pojmy z 48, a chceme se dívat do skupin po 3 nebo něco takového, takže když změním tato čísla, vše se počítá. V tomto případě to dělá jen jeden vzorec pro každého, ale představte si, že kdybychom dělali 100 vzorců, to se dramaticky zpomalí. Takže tady podle vzorců, tam 'Ve skutečnosti je to možnost Možnosti výpočtu, automatická nebo manuální, je tu třetí, která říká: „Ano, přepočítat vše kromě datových tabulek, nepočítat tabulku dat.“ Protože to může být obrovský nápor na časy výpočtu.
Dobře, datové tabulky jsou úžasné, když máte ke změně dvě proměnné, ale my musíme změnit tři proměnné. Co kdyby byly různé úrokové sazby, doporučuji jít do Správce scénářů? NE, NIKDY nedoporučuji jít do správce scénářů! V tomto případě máme 9x7, to je 63 různých scénářů, které jsme zde vypočítali, vytvořit 63 různých scénářů Scénář správce by trvalo 2 hodiny, to je hrozné. V knize „MrExcel XL“ se tím nezabývám, protože je to 40 nejlepších tipů. Toto je pravděpodobně v mé knize „Power Excel“ s 567 vyřešenými záhadami aplikace Excel, ale jsem si jistý, že jsem si stěžoval na to, jak je mizerné to používat, neuvidíte mě zde dělat správce scénářů. Pokud bychom to opravdu museli udělat pro několik různých rychlostí, nejlepší uděláme jen Ctrl-drag, vezmi tento list, Ctrl-drag, Ctrl-drag,Ctrl a přetáhněte a poté změňte sazby na každém listu. Takže pokud bychom mohli získat 5% nebo 4,75% nebo něco podobného a tak dále, správně, neexistuje snadný způsob, jak to nastavit ve Správci scénářů pro 3 proměnné. Dobře, „40 největších tipů na Excel všech dob“, v této knize si můžete knihu koupit, kliknout na to „i“ v pravém horním rohu.
Rekapitulace epizody z dnešního dne: V aplikaci Excel existují tři nástroje What-If, včera jsme hovořili o Goal Seek, dnes Data Table. Je to úžasné pro problémy se 2 proměnnými, zítra uvidíte problém s problémem s 1 proměnnou. Funkci pole tabulky nelze zadat ručně, nebude to fungovat, musíte použít Data, What-If Analysis, Data Table. K vybarvení odpovědí jsem použil barevnou škálu, Domů, Podmíněné formátování, Barevné škály. Pokud máte 3 proměnné, které chcete změnit, děláte scénáře? Ne, stačí vytvořit kopie listu nebo kopie tabulky, jejich výpočet je pomalý, zejména u složitého modelu. Pro všechny kromě tabulek existuje režim výpočtu pro Automatický a Owen W. Green navrhl zahrnout tuto funkci do knih.
Takže díky němu a díky vám, že jste se zastavili, uvidíme se příště na dalším netcastu od!
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2034.xlsx
