Analýza What-If programu Excel ponúka údajovú tabuľku. Toto je zlé meno. Malo by sa to volať Analýza citlivosti. Je to studené. Prečítajte si o tom tu.
Hľadanie cieľov vám umožní nájsť množinu vstupov, ktoré vedú k určitému výsledku. Niekedy chcete vidieť veľa rôznych výsledkov z rôznych kombinácií vstupov. Ak máte na zmenu iba dva vstupné bunky, tabuľka s údajmi poskytuje rýchly spôsob porovnania alternatív.
Na príklade splácania pôžičky povedzte, že chcete vypočítať cenu rôznych zostatkov istiny a rôznych výrazov.
Uistite sa, že vzorec, ktorý chcete modelovať, je v ľavom hornom rohu rozsahu. Dajte rôzne hodnoty pre jednu premennú dole do ľavého stĺpca a rôzne hodnoty pre inú premennú hore.
Na karte Údaje vyberte tabuľku údajov What-If Analysis.
Hodnoty sa nachádzajú v hornom riadku vstupnej tabuľky. Chcete, aby program Excel tieto hodnoty pripojil do určitej vstupnej bunky. Zadajte túto vstupnú bunku ako vstupnú bunku riadku.
Hodnoty sa nachádzajú v ľavom stĺpci. Chceš ich zapojiť do inej vstupnej bunky. Zadajte túto bunku ako vstupnú bunku stĺpca.
Keď kliknete na OK, Excel zopakuje vzorec v ľavom hornom stĺpci pre všetky kombinácie horného a ľavého stĺpca. Na obrázku nižšie vidíte 60 rôznych splátok pôžičky na základe rôznych výsledkov.
Všimnite si, že som naformátoval výsledky tabuľky tak, aby neobsahovali žiadne desatinné miesta, a na pridanie červeného / žltého / zeleného tieňovania som použil Home, Conditional Formatting, Color Scale.
Tu je veľká časť: Táto tabuľka je „živá“. Ak zmeníte vstupné bunky pozdĺž ľavého stĺpca alebo horného riadku, hodnoty v tabuľke sa prepočítajú. Nižšie sú hodnoty vľavo zamerané na rozsah 23 000 až 24 000 USD.
Ďakujem Owenovi W. Greenovi za návrh stolov.
Pozeraj video
- Tri nástroje „what-if“ v programe Excel
- Včera - hľadanie cieľa
- Dnes - tabuľka údajov
- Skvelé pre problémy s dvoma premennými
- Drobnosti: funkciu poľa TABLE nie je možné zadať ručne - nebude to fungovať
- Na zafarbenie odpovedí použite farebnú škálu
- Čo ak chcete zmeniť 3 premenné? Scenáre? Nie! Kopírovať pracovný hárok
- Tabuľky sa počítajú pomaly: režim výpočtu pre všetky tabuľky okrem
- Ďakujeme Owenovi W. Greenovi za navrhnutie tohto tipu
Prepis videa
Naučte sa program Excel z podcastu, epizóda 2034 - Čo s údajovou tabuľkou!
Podcastujem celú túto knihu. Kliknutím na písmeno „i“ v pravom hornom rohu sa dostanete do zoznamu skladieb!
Dnes si povieme o druhom nástroji v rámci What-If Analysis, včera sme hovorili o Goal Seek, dnes sa chystáme pokryť dátovú tabuľku. Takže tu máme tento pekný malý model, toto je malý model, 3 vstupné bunky, jeden vzorec. Ale týmto modelom môžu byť stovky vstupných buniek, tisíce riadkov, pokiaľ dôjde na jednu konečnú odpoveď, a my chceme túto odpoveď modelovať pre niekoľko rôznych hodnôt 2-3 (?) Vstupných buniek. Napríklad by nás možno zaujímali rôzne autá, takže kdekoľvek od 20000 vyššie, tak tam vložím 20 a 21000, chytím rukoväť výplne a potiahnem, znížim to na 28000. Cez vrchol pozeráme na rôzne podmienky, takže 36-mesačná pôžička, 42-mesačná pôžička, 48-mesačná pôžička, 54, 60, 66 a dokonca 72.
V poriadku, tento ďalší krok je úplne voliteľný, ale naozaj mi pomáha pri premýšľaní o tom, vždy striedam farby hodnôt v hornej časti a hodnôt vľavo. A skutočne dôležité je, že táto rohová bunka, tá najdôležitejšia rohová bunka, musí byť odpoveďou, ktorú sa snažíme modelovať, dobre. Musíte teda začať vyberať z tejto rohovej bunky s odpoveďou a potom vybrať všetky riadky a všetky stĺpce. Prejdime teda k údajom, analýze čo-ak a údajovej tabuľke a tu si vyžaduje dve veci a tu je príklad, ako by ste o nich premýšľali. Hovorí sa, že v hornom riadku tabuľky je celá rada rôznych položiek. Chcem tieto položky zobrať, jednu po druhej, a zapojiť ich do modelu, kam by sme ich mali vložiť? Takže tieto položky, to sú výrazy, by mali ísť do bunky B2. A potom,pozdĺž ľavého stĺpca je celá skupina položiek, ktoré chceme zobrať, jednu po druhej, a zapojiť ich do triedy B1, v poriadku, a klikneme na OK, BAM, tento model beží znova a znova a znova .
Teraz tu len trochu vyčistenia, vždy idem a urobím Domov a asi tak 0 desatinných miest. A možno trochu podmieneného formátovania, farebných mierok, a poďme k červeným číslam pre veľké a zeleným číslam pre malé, len aby mi nejako dali vizuálny spôsob, ako to sledovať. Teraz to vyzerá, že ak natáčame za 425 dolárov, sme nejakí, viete, na tomto mieste alebo na tomto mieste, alebo viete, možno tu, všetci sa priblížime k 425 dolárom. Takže vidím, aké sú rôzne kurzy, naše rôzne kombinácie, aby sme sa dostali k týmto hodnotám.
Teraz je niekoľko vecí, táto časť tu, vlastne vzorec veľkého poľa, takže = TABLE (B2, B1), vstup do riadkov a stĺpcov. To je zvedavé, toto nesmiete písať, môžete to vytvoriť iba pomocou údajov, čo-ak analýza, musíte použiť dané dialógové okno. Ak sa pokúsite tento vzorec zadať, stlačte Ctrl + Shift + Enter, nebude to fungovať, však? Je to teda funkcia v programe Excel, ale ak ste dosť inteligentní na to, aby ste to napísali, škoda, že to nebude fungovať, ale neustále sa to prepočítava. Takže ak zistíme, že sa pozeráme iba na výrazy zo 48, a chceme sa pozerať do skupín po troch, alebo tak podobne, tak ako zmením tieto čísla, tak všetko sa počíta. V tomto prípade robí iba jeden vzorec pre každého, ale predstavte si, že keby sme robili 100 vzorcov, dramaticky sa to spomalí. Takže tu podľa vzorcov, tam 's vlastne možnosť Možnosti výpočtu, automatická alebo manuálna, existuje tretia, ktorá hovorí „Áno, prepočítajte všetko okrem údajových tabuliek, neprepočítavajte tabuľku údajov.“ Pretože to môže byť obrovský nápor na časy výpočtu.
V poriadku, dátové tabuľky sú úžasné, keď máte na zmenu dve premenné, ale na zmenu máme tri premenné. Čo ak by boli rôzne úrokové sadzby, odporúčam ísť do Správcu scenárov? NIE, NIKDY neodporúčam ísť do Správcu scenárov! V tomto prípade máme 9x7, čo je 63 rôznych scenárov, ktoré sme tu vypočítali, vytvorenie 63 rôznych scenárov Scenario Manager by trvalo 2 hodiny, je to hrozné. Týmto sa v knihe „MrExcel XL“ nezaoberám, pretože je to 40 najlepších tipov. Toto je pravdepodobne v mojej knihe „Power Excel“ s 567 vyriešenými záhadami programu Excel, ale som si istý, že som sa sťažoval na to, aké nešťastné je jeho použitie, neuvidíte ma tu robiť Správcu scenárov. Ak by sme to skutočne museli robiť pre niekoľko rôznych rýchlostí, najlepšie urobíme, ak klikneme na Ctrl-drag, vezmeme tento hárok, Ctrl-drag, Ctrl-drag,Ctrl-pretiahnite a potom zmeňte sadzby na každom hárku. Takže ak by sme mohli získať 5% alebo 4,75% alebo niečo podobné atď., Nie je ľahký spôsob, ako to nastaviť v Správcovi scenárov pre 3 premenné. Dobre, „40 najväčších tipov na Excel v odbore všetkých čias“, v tejto knihe si môžete knihu kúpiť, kliknite na to „i“ v pravom hornom rohu.
Rekapitulácia epizódy z dnešného dňa: V programe Excel sú tri nástroje Čo-If, včera sme hovorili o Goal Seek, dnes Data Table. Je to úžasné pre problémy s 2 premennými, zajtra uvidíte také, ktoré má problém s 1 premennými. Funkciu tabuľkového poľa nie je možné zadať ručne, nebude fungovať, musíte použiť údaje, analýza údajov, tabuľka údajov. Na zafarbenie odpovedí som použil farebnú škálu, Domov, Podmienené formátovanie, Farebné škály. Ak chcete zmeniť 3 premenné, robíte scenáre? Nie, stačí vytvoriť kópie pracovného hárka alebo tabuľky tabuľky, ich výpočet je pomalý, najmä pri zložitom modeli. Pre všetky okrem tabuliek existuje režim výpočtu pre Automatický a Owen W. Green navrhol zahrnúť túto funkciu do kníh.
Takže vďaka nemu a vďaka vám, že ste sa zastavili, uvidíme sa nabudúce pri ďalšom netcaste od!
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2034.xlsx
