Excel What-If-analys erbjuder en datatabell. Det här är ett dåligt namn. Det bör kallas känslighetsanalys. Det är coolt. Läs om det här.
Med Goal Seek kan du hitta den uppsättning ingångar som leder till ett visst resultat. Ibland vill du se många olika resultat från olika kombinationer av ingångar. Förutsatt att du bara har två inmatningsceller att ändra, ger datatabellen ett snabbt sätt att jämföra alternativ.
Använd lånebetalningsexemplet och säg att du vill beräkna priset för olika huvudbalanser och för olika villkor.
Se till att formeln du vill modellera finns i det övre vänstra hörnet av ett intervall. Lägg olika värden för en variabel längst ner till vänster och olika värden för en annan variabel överst.
På fliken Data väljer du Vad-om-analys, datatabell.
Du har värden längs den översta raden i inmatningstabellen. Du vill att Excel ska ansluta dessa värden till en viss inmatningscell. Ange den inmatningscellen som radinmatningscellen.
Du har värden längs den vänstra kolumnen. Du vill att de ska anslutas till en annan inmatningscell. Ange den cellen som kolumninmatningscellen.
När du klickar på OK upprepar Excel formeln i den övre vänstra kolumnen för alla kombinationer av den övre raden och den vänstra kolumnen. I bilden nedan ser du 60 olika lånebetalningar baserat på olika resultat.
Observera att jag formaterade tabellresultaten så att de inte hade några decimaler och använde hem, villkorlig formatering, färgskala för att lägga till den röda / gula / gröna skuggningen.
Här är den stora delen: Denna tabell är "live". Om du ändrar inmatningsceller längs den vänstra kolumnen eller den övre raden, beräknar värdena i tabellen om. Nedan är värdena till vänster fokuserade på $ 23K till $ 24K.
Tack till Owen W. Green för att föreslå tabeller.
Kolla på video
- Tre vad-om-verktyg i Excel
- Igår - Målsökning
- Idag - en datatabell
- Perfekt för problem med två variabler
- Trivia: TABLE-arrayfunktionen kan inte anges manuellt - den fungerar inte
- Använd en färgskala för att färga svaren
- Vad händer om du har tre variabler att ändra? Scenarier? Nej! Kopiera kalkylbladet
- Tabellerna är långsamma att beräkna: beräkningsläge för alla utom tabeller
- Tack till Owen W. Green för att du föreslog detta tips
Videoutskrift
Lär dig Excel från podcast, avsnitt 2034 - Vad händer med en datatabell!
Jag podcastar hela denna bok, klicka på “i” i det övre högra hörnet för att komma till spellistan!
Idag ska vi prata om det andra verktyget under What-If-analys, igår pratade vi om Goal Seek, idag ska vi täcka en datatabell. Så vi har den här fina lilla modellen här, det här är en liten modell, 3 ingångsceller, en formel. Men den här modellen kan vara hundratals ingångsceller, tusentals rader, så länge det kommer ner till ett slutligt svar, och vi vill modellera svaret för flera olika värden på 2-3 (?) Ingångsceller. Till exempel kanske vi är intresserade av att titta på olika bilar, så var som helst från 20000 och uppåt, så jag lägger in 20 och 21000 där, tar tag i fyllningshandtaget och drar, tar ner det till 28000. Över toppen vi ' vi tittar på olika villkor, så ett 36-månaderslån, 42-månaderslån, 48-månaderslån, 54, 60, 66 och till och med 72.
Okej nu, det här nästa steget är helt frivilligt, men det hjälper mig verkligen att tänka på det här, jag ändrar alltid färgerna på värdena längst upp och värdena till vänster. Och det riktigt viktiga här är att den hörncellen, den viktiga hörncellen, måste vara svaret som vi försöker modellera, okej. Så du måste börja välja från den hörncellen med svaret och sedan välja alla rader och alla kolumner. Så vi går in i Data, Vad-om-analys och en datatabell, och det ber om två saker här, och här är hur du skulle tänka på det. Det står att det finns en hel massa olika objekt längs den översta raden i tabellen, jag vill ta de här objekten, en i taget, och koppla in dem i modellen, var ska vi mata in? Så dessa objekt, dessa är termer, de borde gå in i cellen B2. Och då,det finns en hel massa objekt längs den vänstra kolumnen, vi vill ta dem, en i taget, och anslut dem till B1, så, okej och vi klickar på OK, BAM, den kör den här modellen om och om igen .
Nu bara lite sanering här, jag går alltid in och gör Home, och förmodligen 0 decimaler, så där. Och kanske lite villkorlig formatering, färgskalor, och låt oss gå med röda siffror för stora och gröna siffror för små, bara för att ge mig ett sätt att spåra detta visuellt. Nu ser det ut som om vi skjuter för $ 425, vi är typ av, du vet, på den här platsen eller den här platsen, eller om du vet, kanske här, kommer vi alla att få oss nära $ 425. Så jag kan se vad som är de olika oddsen, våra olika kombinationer, för att få oss till dessa värden.
Nu är ett par saker, denna del inuti här, faktiskt en stor matrisformel, så = TABELL (B2, B1), rad- och kolumninmatningen. Detta är nyfiken, du får inte skriva detta, du kan bara skapa detta med hjälp av Data, What-If-analys, du måste använda den dialogrutan. Om du försöker skriva den formeln trycker du på Ctrl + Skift + Enter, det fungerar inte, eller hur? Så det är en funktion i Excel, men om du är tillräckligt smart för att skriva det, så dåligt, kommer det inte att fungera, men det beräknar hela tiden. Så om vi bestämmer att vi bara tittar på termer från 48, och vi vill se i grupper om 3 eller något liknande, så när jag ändrar dessa siffror, beräknar allt detta. I det här fallet gör det bara en formel för var och en, men tänk dig att om vi gjorde en 100 formler, saktar detta dramatiskt ner. Så här under Formler, där 's faktiskt ett alternativ Beräkningsalternativ, automatisk eller manuell, det finns en tredje som säger "Ja, beräkna allt förutom datatabellerna, fortsätt inte omberäkna datatabellen." Eftersom detta kan vara ett enormt drag på beräkningstiderna.
Okej nu, datatabeller är fantastiska när du har två variabler att ändra, men vi har tre variabler att ändra. Vad händer om det finns olika räntor, rekommenderar jag att du går till Scenario Manager? NEJ, jag rekommenderar ALDRIG att gå till Scenario Manager! I det här fallet har vi 9x7, det är 63 olika scenarier som vi beräknade här, för att skapa 63 olika Scenario Manager-scenarier skulle ta två timmar, det är hemskt. Jag täcker inte detta i ”MrExcel XL” -boken, för det är de 40 bästa tipsen. Det här är antagligen i min "Power Excel" -bok med 567 Excel-mysterier lösta, men jag är säker på att jag klagade över hur eländigt det är att använda, du kommer inte att se mig göra Scenario Manager här. Om vi verkligen var tvungna att göra detta i flera olika takt är det bästa att göra bara Ctrl-dra, ta det här bladet, Ctrl-dra, Ctrl-dra,Ctrl-dra och ändra sedan hastigheterna på varje ark. Så om vi kunde få 5% eller 4,75% eller något liknande och så vidare, det finns inget enkelt sätt att ställa in det för 3 variabler i Scenario Manager. Okej, "40 bästa Excel-tips genom tiderna", allt i den här boken, du kan köpa boken, klicka på det "i" i det övre högra hörnet.
Avsnittet sammanfattning från idag: Det finns tre What-If-verktyg i Excel, igår pratade vi om Goal Seek, idag datatabellen. Det är fantastiskt för 2-variabla problem, imorgon ser du ett med ett 1-variabelt problem. Tabellmatrisfunktionen kan inte matas in manuellt, den fungerar inte, du måste använda data, vad-om-analys, datatabell. Jag använde en färgskala, hem, villkorlig formatering, färgskalor, för att färga svaren. Om du har tre variabler att ändra, gör du scenarier? Nej, gör bara kopior av kalkylbladet eller kopior av tabellen, de är långsamma att beräkna, särskilt med en komplex modell. Det finns ett beräkningsläge för Automatic för alla utom tabeller, och Owen W. Green föreslog att denna funktion skulle inkluderas i böckerna.
Så tack till honom, och tack till dig för att du stannade förbi, vi ses nästa gång för en ny netcast från!
Nedladdning fil
Ladda ner exempelfilen här: Podcast2034.xlsx
