Excel Hvad-hvis-analyse tilbyder en datatabel. Dette er et dårligt navn. Det skal kaldes følsomhedsanalyse. Det er cool. Læs om det her.
Målsøgning lader dig finde det sæt input, der fører til et bestemt resultat. Nogle gange vil du se mange forskellige resultater fra forskellige kombinationer af input. Forudsat at du kun har to inputceller til at ændre, giver datatabellen en hurtig måde at sammenligne alternativer på.
Brug låneksemplet til at sige, at du vil beregne prisen for en række hovedbalancer og for en række vilkår.
Sørg for, at den formel, du vil modellere, er i øverste venstre hjørne af et interval. Sæt forskellige værdier for en variabel ned i venstre kolonne og forskellige værdier for en anden variabel over toppen.
Fra datafanen skal du vælge Hvad-hvis-analyse, datatabel.
Du har værdier langs den øverste række i inputtabellen. Du vil have, at Excel tilslutter disse værdier til en bestemt inputcelle. Angiv den indlæste celle som rækkeinputcelle.
Du har værdier langs den venstre kolonne. Du vil have dem tilsluttet en anden inputcelle. Angiv den celle som kolonneinputcelle.
Når du klikker på OK, gentager Excel formlen i øverste venstre kolonne for alle kombinationer af den øverste række og venstre kolonne. På billedet nedenfor ser du 60 forskellige lånebetalinger baseret på forskellige resultater.
Bemærk, at jeg formaterede tabelresultaterne uden decimaler og brugte Hjem, Betinget formatering, Farveskala for at tilføje den røde / gule / grønne skygge.
Her er den store del: Denne tabel er "live". Hvis du ændrer inputcellerne langs den venstre kolonne eller øverste række, beregnes værdierne i tabellen igen. Nedenfor er værdierne til venstre fokuseret på $ 23K til $ 24K-området.
Tak til Owen W. Green for at foreslå tabeller.
Se video
- Tre hvad-hvis-værktøjer i Excel
- I går - Målsøgning
- I dag - en datatabel
- Perfekt til problemer med to variabler
- Trivia: TABLE-arrayfunktionen kan ikke indtastes manuelt - den fungerer ikke
- Brug en farveskala til at farve svarene
- Hvad hvis du har 3 variabler at ændre? Scenarier? Ingen! Kopier regneark
- Tabeller er langsomme at beregne: beregningstilstand for alle undtagen tabeller
- Tak til Owen W. Green for at foreslå dette tip
Videoudskrift
Lær Excel fra podcast, afsnit 2034 - Hvad-hvis med en datatabel!
Jeg podcaster hele denne bog, klik på "i" i øverste højre hjørne for at komme til afspilningslisten!
I dag skal vi tale om det andet værktøj under Hvad-hvis-analyse, i går talte vi om Goal Seek, i dag skal vi dække en datatabel. Så vi har denne dejlige lille model her, dette er en lille model, 3 inputceller, en formel. Men denne model kan være hundredvis af inputceller, tusinder af rækker, så længe det kommer ned til et endelig svar, og vi vil modellere dette svar til flere forskellige værdier på 2-3 (?) Inputceller. For eksempel, måske er vi interesserede i at se på forskellige biler, så hvor som helst fra 20000 og op, så jeg lægger 20 og 21000 der, griber fyldhåndtaget og trækker, tager det ned til 28000. På tværs af toppen vi ' ser vi på forskellige vilkår, så et 36-måneders lån, 42-måneders lån, 48-måneders lån, 54, 60, 66 og endda 72.
Okay nu, dette næste trin er helt valgfrit, men det hjælper mig virkelig til at tænke over dette, jeg ændrer altid farverne på værdierne øverst og værdierne til venstre. Og det virkelig vigtige her er, at den hjørnecelle, den vigtige hjørnecelle, skal være svaret, som vi prøver at modellere, okay. Så du skal begynde at vælge fra den hjørnecelle med svaret og derefter vælge alle rækkerne og alle kolonnerne. Så vi går ind i data, hvad-hvis-analyse og en datatabel, og det beder om to ting her, og her er hvordan du tænker på det. Der står, at der er en hel masse forskellige emner langs den øverste række i tabellen, jeg vil tage disse emner en ad gangen og tilslutte dem til modellen, hvor skal vi indtaste? Så disse varer, dette er udtryk, de skal gå ind i cellen B2. Og så,der er en hel masse ting langs venstre kolonne, vi vil tage dem, en ad gangen, og tilslut dem til B1, sådan, okay, og vi klikker OK, BAM, den kører denne model igen og igen og igen .
Nu bare en lille smule oprydning her, jeg går altid ind og laver Hjem, og sandsynligvis 0 decimaler, sådan. Og måske lidt betinget formatering, farveskalaer, og lad os gå med røde tal for store og grønne tal for små, bare for at give mig en, du ved, måde at spore dette visuelt på. Nu ser det ud til, at hvis vi skyder for $ 425, er vi lidt, du ved, på dette sted eller dette sted, eller ved du, måske her, får vi os alle tæt på $ 425. Så jeg kan se, hvad der er de forskellige odds, vores forskellige kombinationer, for at få os til disse værdier.
Nu er et par ting, denne del inde her, faktisk en stor matrixformel, så = TABEL (B2, B1), række- og kolonneinput. Dette er nysgerrig, du har ikke tilladelse til at skrive dette, du kan kun oprette dette ved hjælp af data, hvad hvis analyse, du skal bruge denne dialogboks. Hvis du prøver at skrive denne formel, skal du trykke på Ctrl + Shift + Enter, det fungerer ikke, ikke? Så det er en funktion i Excel, men hvis du er smart nok til at skrive det, alt for dårlig, fungerer det ikke, men det genberegnes konstant. Så hvis vi bestemmer, at vi kun ser på udtryk fra 48, og vi vil se i grupper på 3 eller noget lignende, så når jeg ændrer disse tal, beregner alt dette. I dette tilfælde laver det kun en formel for hver, men forestil dig, at hvis vi lavede 100 formler, bliver dette dramatisk bremset. Så herude under formler, der 's faktisk en mulighed Beregningsindstillinger, automatisk eller manuel, er der en tredje, der siger "Ja, genberegn alt undtagen datatabellerne, bliv ikke ved med at genberegne datatabellen." Fordi dette kan være en enorm træk på beregningstider.
Okay nu, datatabeller er fantastiske, når du har to variabler at ændre, men vi har tre variabler, der skal ændres. Hvad hvis der var forskellige renter, anbefaler jeg at gå til Scenario Manager? NEJ, jeg anbefaler ALDRIG at gå til Scenario Manager! I dette tilfælde har vi 9x7, det er 63 forskellige scenarier, som vi har beregnet her, for at oprette 63 forskellige Scenario Manager-scenarier ville tage 2 timer, det er forfærdeligt. Jeg dækker ikke dette i "MrExcel XL" -bogen, fordi det er de 40 bedste tip. Dette er sandsynligvis i min "Power Excel" -bog med 567 Excel-mysterier løst, men jeg er sikker på, at jeg klagede over, hvor elendigt det er at bruge, du vil ikke se mig gøre Scenario Manager her. Hvis vi virkelig skulle gøre dette i flere forskellige satser, er den bedste ting bare at Ctrl-trække, tage dette ark, Ctrl-træk, Ctrl-træk,Ctrl-træk, og skift derefter satserne på hvert ark. Så hvis vi kunne få en 5% eller 4,75% eller noget lignende osv., Ikke rigtigt, er der ingen nem måde at indstille det på 3 variabler i Scenario Manager. Okay, "40 Greatest Excel Tips of All Time", alt sammen i denne bog, kan du købe bogen, klikke på det "i" i øverste højre hjørne.
Episodeoptagelse fra i dag: Der er tre hvad-hvis-værktøjer i Excel, i går talte vi om Goal Seek, i dag datatabellen. Det er fantastisk til 2-variable problemer, i morgen ser du en med et 1-variabel problem. Tabelarrayfunktionen kan ikke indtastes manuelt, den fungerer ikke, du skal bruge data, hvad-hvis-analyse, datatabel. Jeg brugte en farveskala, Hjem, Betinget formatering, Farveskalaer til at farve svarene. Hvis du har 3 variabler at ændre, gør du scenarier? Nej, lav bare kopier af regnearket eller kopier af tabellen, de er langsomme at beregne, især med en kompleks model. Der er en beregningstilstand for Automatisk for alle undtagen tabeller, og Owen W. Green foreslog at inkludere denne funktion i bøgerne.
Så tak til ham, og tak til dig for at du kom forbi, vi ses næste gang til endnu en netcast fra!
Download fil
Download eksempelfilen her: Podcast2034.xlsx
