Excel What-If-analyse tilbyr en datatabell. Dette er et dårlig navn. Det skal hetes Sensitivity Analysis. Det er kult. Les om det her.
Goal Seek lar deg finne settet med innganger som fører til et bestemt resultat. Noen ganger vil du se mange forskjellige resultater fra forskjellige kombinasjoner av innganger. Forutsatt at du bare har to inngangsceller å endre, gir datatabellen en rask måte å sammenligne alternativer på.
Bruk låneksemplet til å si at du vil beregne prisen for en rekke hovedbalanser og for en rekke vilkår.
Forsikre deg om at formelen du vil modellere, er øverst til venstre i et område. Sett forskjellige verdier for en variabel nede i venstre kolonne og forskjellige verdier for en annen variabel over toppen.
Fra datatabellen velger du Hva-hvis-analyse, datatabell.
Du har verdier langs den øverste raden i inndatatabellen. Du vil at Excel skal koble disse verdiene til en bestemt inndatacelle. Spesifiser den inndatacellen som radinntakscelle.
Du har verdier langs venstre kolonne. Du vil at de skal kobles til en annen inndatacelle. Spesifiser den cellen som kolonneinntakscelle.
Når du klikker OK, gjentar Excel formelen i øverste venstre kolonne for alle kombinasjoner av øverste rad og venstre kolonne. På bildet nedenfor ser du 60 forskjellige lånebetalinger basert på ulike resultater.
Merk at jeg formaterte resultatene fra tabellen uten desimaler og brukte Hjem, Betinget formatering, Fargeskala for å legge til rød / gul / grønn skyggelegging.
Her er den store delen: Denne tabellen er "live". Hvis du endrer inngangscellene langs venstre kolonne eller øverste rad, beregnes verdiene i tabellen på nytt. Nedenfor er verdiene til venstre fokusert på $ 23K til $ 24K-området.
Takk til Owen W. Green for å foreslå bord.
Se på video
- Tre hva-hvis-verktøy i Excel
- I går - Målsøk
- I dag - en datatabell
- Flott for problemer med to variabler
- Trivia: TABLE-arrayfunksjonen kan ikke angis manuelt - den vil ikke fungere
- Bruk en fargeskala for å fargelegge svarene
- Hva om du har 3 variabler å endre? Scenarier? Nei! Kopier regneark
- Tabeller er treg å beregne: beregningsmodus for alle unntatt tabeller
- Takk til Owen W. Green for å foreslå dette tipset
Videoutskrift
Lær Excel fra podcast, episode 2034 - Hva-hvis med en datatabell!
Jeg podcaster hele denne boken, klikk på "i" øverst til høyre for å komme til spillelisten!
I dag skal vi snakke om det andre verktøyet under What-If Analysis, i går snakket vi om Goal Seek, i dag skal vi dekke en datatabell. Så vi har denne fine lille modellen her, dette er en liten modell, 3 inngangsceller, en formel. Men denne modellen kan være hundrevis av inngangsceller, tusenvis av rader, så lenge det kommer ned til ett endelig svar, og vi vil modellere dette svaret for flere forskjellige verdier på 2-3 (?) Inngangsceller. For eksempel, kanskje vi er interessert i å se på forskjellige biler, så hvor som helst fra 20000 og oppover, så jeg legger inn 20 og 21000 der, tar tak i fyllhåndtaket og drar, tar det ned til 28000. Over toppen vi ' ser på forskjellige vilkår, så et 36-måneders lån, 42-måneders lån, 48-måneders lån, 54, 60, 66 og til og med 72.
OK nå, dette neste trinnet er helt valgfritt, men det hjelper meg virkelig å tenke på dette, jeg endrer alltid fargene på verdiene langs toppen og verdiene til venstre. Og det som er veldig viktig her er at den hjørnecellen, den viktige hjørnecellen, må være svaret vi prøver å modellere, ok. Så du må begynne å velge fra den hjørnecellen med svaret, og deretter velge alle radene og alle kolonnene. Så vi går inn i data, hva-om-analyse og en datatabell, og det ber om to ting her, og her er hvordan du vil tenke på det. Det står at det er en hel haug med forskjellige varer langs den øverste raden i tabellen, jeg vil ta disse elementene, en om gangen, og koble dem til modellen, hvor skal vi legge inn? Så disse elementene, dette er vilkår, de bør gå inn i cellen B2. Og så,det er en hel haug med elementer langs venstre kolonne, vi vil ta dem, en om gangen, og koble dem til B1, sånn, ok, og vi klikker OK, BAM, den kjører denne modellen om og om igjen .
Nå bare litt opprydding her, jeg går alltid inn og gjør Hjem, og sannsynligvis 0 desimaler, slik. Og kanskje litt betinget formatering, fargeskalaer, og la oss gå med røde tall for store og grønne tall for små, bare for å gi meg en, du vet, måte å spore dette visuelt på. Nå ser det ut som om vi skyter for $ 425, vi er liksom, vet du, på dette stedet eller dette stedet, eller du vet, kanskje her, vil vi alle få oss nær $ 425. Så jeg kan se hva som er de forskjellige oddsene, de forskjellige kombinasjonene våre, for å få oss til disse verdiene.
Nå er et par ting, denne delen inni her, faktisk en stor matriseformel, så = TABELL (B2, B1), rad- og kolonneinngangen. Dette er nysgjerrig, du har ikke lov til å skrive dette, du kan bare opprette dette ved hjelp av Data, Hva-hvis-analyse, du må bruke den dialogboksen. Hvis du prøver å skrive den formelen, trykker du Ctrl + Shift + Enter, den vil ikke fungere, ikke sant? Så det er en funksjon i Excel, men hvis du er smart nok til å skrive det, så ille, det kommer ikke til å fungere, men det beregnes kontinuerlig på nytt. Så hvis vi finner ut at vi bare ser på begreper fra 48, og vi vil se i grupper på 3 eller noe sånt, så når jeg endrer disse tallene, er alt dette beregningene. I dette tilfellet gjør det bare en formel for hver, men forestill deg at hvis vi gjorde en 100 formler, blir dette redusert dramatisk. Så her ute under formler, der 's faktisk et alternativ Beregningsalternativer, automatisk eller manuell, er det en tredje som sier "Ja, beregne alt på nytt bortsett fra datatabellene, ikke fortsett å beregne datatabellen på nytt." Fordi dette kan være et enormt drag på beregningstidene.
OK nå, datatabeller er kjempebra når du har to variabler å endre, men vi har tre variabler å endre. Hva om det var forskjellige renter, anbefaler jeg å gå til Scenario Manager? NEI, jeg anbefaler ALDRI å gå til Scenario Manager! I dette tilfellet har vi 9x7, det er 63 forskjellige scenarier som vi beregnet her, for å lage 63 forskjellige Scenario Manager-scenarier vil ta to timer, det er fryktelig. Jeg dekker ikke dette i "MrExcel XL" -boka, fordi det er de 40 beste tipsene. Dette er sannsynligvis i min "Power Excel" -bok med 567 Excel-mysterier løst, men jeg er sikker på at jeg klaget over hvor elendig det er å bruke, du vil ikke se meg gjøre Scenario Manager her. Hvis vi virkelig måtte gjøre dette for flere forskjellige priser, er det beste å bare Ctrl-dra, ta dette arket, Ctrl-dra, Ctrl-dra,Ctrl-dra, og endre deretter hastighetene på hvert ark. Så hvis vi kunne få 5% eller 4,75% eller noe sånt og så videre, ikke sant, det er ingen enkel måte å sette opp for 3 variabler i Scenario Manager. Greit, “40 Greatest Excel Tips of All Time”, alt i denne boka kan du kjøpe boken, klikke på “i” øverst til høyre.
Episodeoppsummering fra i dag: Det er tre What-If-verktøy i Excel, i går snakket vi om Goal Seek, i dag datatabellen. Det er kjempebra for problemer med 2 variabler, i morgen ser du en med et problem med 1 variabel. Tabelloppsettfunksjonen kan ikke angis manuelt, den fungerer ikke, du må bruke Data, hva-hvis-analyse, datatabell. Jeg brukte en fargeskala, Hjem, Betinget formatering, Fargeskalaer, for å fargelegge svarene. Hvis du har 3 variabler å endre, gjør du scenarier? Nei, bare lag kopier av regnearket eller kopier av tabellen, de er treg å beregne, spesielt med en kompleks modell. Det er en beregningsmodus for Automatic for alle unntatt tabeller, og Owen W. Green foreslo å inkludere denne funksjonen i bøkene.
Så takk til ham, og takk til deg for at du var innom, vi sees neste gang for en ny netcast fra!
Last ned fil
Last ned eksempelfilen her: Podcast2034.xlsx
