Excel What-If Analysis biedt een gegevenstabel. Dit is een slechte naam. Het zou gevoeligheidsanalyse moeten worden genoemd. Het is cool. Lees er hier meer over.
Met Doel zoeken kunt u de set invoer vinden die tot een bepaald resultaat leidt. Soms wilt u veel verschillende resultaten zien van verschillende combinaties van invoer. Op voorwaarde dat u slechts twee invoercellen hoeft te wijzigen, biedt de gegevenstabel een snelle manier om alternatieven te vergelijken.
Stel aan de hand van het voorbeeld van de leningbetaling dat u de prijs wilt berekenen voor verschillende hoofdsaldi en voor verschillende voorwaarden.
Zorg ervoor dat de formule die u wilt modelleren zich in de linkerbovenhoek van een bereik bevindt. Zet verschillende waarden voor een variabele in de linkerkolom en verschillende waarden voor een andere variabele bovenaan.
Selecteer op het tabblad Gegevens de optie What-If Analysis, gegevenstabel.
Je hebt waarden in de bovenste rij van de invoertabel. U wilt dat Excel die waarden in een bepaalde invoercel plugt. Specificeer die invoercel als de rijinvoercel.
U heeft waarden in de linkerkolom. U wilt die in een andere invoercel steken. Specificeer die cel als de Kolominvoercel.
Wanneer u op OK klikt, herhaalt Excel de formule in de kolom linksboven voor alle combinaties van de bovenste rij en de linkerkolom. In de onderstaande afbeelding zie je 60 verschillende leningbetalingen op basis van verschillende resultaten.
Merk op dat ik de tabelresultaten zo heb geformatteerd dat ze geen decimalen bevatten en Home, Conditionele opmaak, Kleurenschaal heb gebruikt om de rood / geel / groene arcering toe te voegen.
Hier is het geweldige deel: deze tafel is "live". Als u de invoercellen in de linkerkolom of de bovenste rij wijzigt, worden de waarden in de tabel opnieuw berekend. Hieronder zijn de waarden aan de linkerkant gericht op het bereik van $ 23K tot $ 24K.
Met dank aan Owen W. Green voor het voorstellen van tafels.
Bekijk video
- Drie wat-als-tools in Excel
- Gisteren - Goal Seek
- Vandaag - een gegevenstabel
- Geweldig voor problemen met twee variabelen
- Trivia: de TABLE-arrayfunctie kan niet handmatig worden ingevoerd - het zal niet werken
- Gebruik een kleurenschaal om de antwoorden in te kleuren
- Wat als u 3 variabelen moet wijzigen? Scenario's? Nee! Kopieer het werkblad
- Tabellen zijn traag te berekenen: berekeningsmodus voor alle behalve tabellen
- Met dank aan Owen W. Green voor het voorstellen van deze tip
Videotranscriptie
Leer Excel van podcast, aflevering 2034 - What-Ifs met een gegevenstabel!
Ik podcast dit hele boek, klik op de "i" in de rechterbovenhoek om naar de afspeellijst te gaan!
Vandaag gaan we het hebben over de tweede tool onder What-If Analysis, gisteren hebben we het gehad over Goal Seek, vandaag gaan we een gegevenstabel behandelen. Dus we hebben hier een leuk klein model, dit is een klein model, 3 invoercellen, één formule. Maar dit model kan honderden invoercellen zijn, duizenden rijen, zolang het maar aankomt op één definitief antwoord, en we willen dit antwoord modelleren voor verschillende waarden van 2-3 (?) Invoercellen. Misschien zijn we bijvoorbeeld geïnteresseerd in het bekijken van verschillende auto's, dus ergens vanaf 20000, dus ik zet daar 20 en 21000 in, pak de vulhendel en sleep, breng dat naar beneden naar 28000. Aan de overkant hebben we ' kijken naar verschillende voorwaarden, dus een lening van 36 maanden, 42 maanden lening, 48 maanden lening, 54, 60, 66 en zelfs 72.
Oké, deze volgende stap is volledig optioneel, maar het helpt me echt om hier over na te denken, ik verander altijd de kleuren van de waarden bovenaan en de waarden aan de linkerkant. En het echt belangrijke hier is dat die hoekcel, die allerbelangrijkste hoekcel, het antwoord moet zijn dat we proberen te modelleren, oké. U moet dus beginnen met het selecteren vanuit die hoekcel met het antwoord en vervolgens alle rijen en alle kolommen selecteren. We gaan dus in op Data, What-if-analyse en een gegevenstabel, en het vraagt hier om twee dingen, en hier is hoe je erover zou denken. Er staat dat er een heleboel verschillende items op de bovenste rij van de tafel staan. Ik wil die items een voor een nemen en ze in het model steken, waar moeten we ze invoeren? Dus deze items, dit zijn termen, zouden in cel B2 moeten worden geplaatst. En dan,er staan een heleboel items in de linkerkolom, we willen die een voor een nemen en ze zo op B1 aansluiten, oké en we klikken op OK, BAM, het draait dit model keer op keer .
Nu even een klein beetje opruimen hier, ik ga altijd naar huis en doe waarschijnlijk 0 decimalen, zoals dat. En misschien een beetje voorwaardelijke opmaak, kleurenschalen, en laten we gaan met rode cijfers voor grote en groene cijfers voor kleine, gewoon om me een soort, weet je, manier te geven om dit visueel bij te houden. Nu lijkt het erop dat als we schieten voor $ 425, we een beetje, weet je, op deze plek of deze plek zijn, of weet je, misschien hier, we zullen ons allemaal in de buurt van $ 425 brengen. Dus ik kan zien wat de verschillende kansen zijn, onze verschillende combinaties, om ons bij die waarden te krijgen.
Een paar dingen, dit deel hierbinnen, is eigenlijk een grote matrixformule, dus = TABEL (B2, B1), de invoer van rijen en kolommen. Dit is merkwaardig, je mag dit niet typen, je kunt dit alleen maken met Data, What-If Analysis, je moet dat dialoogvenster gebruiken. Als u die formule probeert te typen, drukt u op Ctrl + Shift + Enter, het zal niet werken, toch? Het is dus een functie in Excel, maar als je slim genoeg bent om het te typen, jammer genoeg, het zal niet werken, maar het wordt constant opnieuw berekend. Dus als we vaststellen dat we alleen naar termen van 48 kijken, en we willen kijken in groepen van 3 of iets dergelijks, dus als ik deze getallen verander, is dat allemaal een berekening. In dit geval doet het slechts één formule voor elk, maar stel je voor dat als we 100 formules zouden doen, dit dramatisch wordt vertraagd. Dus hier onder Formules, daar 'Het is eigenlijk een optie Berekeningsopties, Automatisch of Handmatig, er is een derde die zegt: "Ja, herbereken alles behalve de gegevenstabellen, blijf de gegevenstabel niet herberekenen." Omdat dit de rekentijden enorm kan belemmeren.
Oké, gegevenstabellen zijn geweldig als je twee variabelen hebt om te wijzigen, maar we hebben drie variabelen om te wijzigen. Wat als er verschillende rentetarieven zijn, raad ik dan aan om naar de Scenario Manager te gaan? NEE, ik raad NOOIT aan om naar de Scenario Manager te gaan! In dit geval hebben we 9x7, dat zijn 63 verschillende scenario's die we hier hebben berekend, om 63 verschillende Scenario Manager-scenario's te maken zou 2 uur duren, het is vreselijk. Ik behandel dit niet in het "MrExcel XL" -boek, omdat het de 40 beste tips zijn. Dit staat waarschijnlijk in mijn "Power Excel" -boek met 567 opgeloste Excel-mysteries, maar ik ben er zeker van dat ik heb geklaagd over hoe ellendig het is om te gebruiken, je zult me hier de Scenario Manager niet zien doen. Als we dit echt voor verschillende snelheden zouden moeten doen, is het het beste om Ctrl-slepen, dit blad te nemen, Ctrl-slepen, Ctrl-slepen,Ctrl-slepen en vervolgens de tarieven op elk blad wijzigen. Dus als we een 5% of 4,75% of iets dergelijks zouden kunnen krijgen, enzovoort, is er geen gemakkelijke manier om dat in te stellen voor 3 variabelen in Scenario Manager. Oké, "40 beste Excel-tips aller tijden", allemaal in dit boek, je kunt het boek kopen, klik op die "i" in de rechterbovenhoek.
Afleveringsoverzicht vanaf vandaag: er zijn drie What-If-tools in Excel, gisteren hadden we het over Goal Seek, vandaag de gegevenstabel. Het is geweldig voor problemen met 2 variabelen, morgen zie je er een met een probleem met 1 variabele. De tabelmatrixfunctie kan niet handmatig worden ingevoerd, het zal niet werken, u moet Data, What-If Analysis, Data Table gebruiken. Ik heb een kleurenschaal, Home, Conditionele opmaak, Kleurenschalen gebruikt om de antwoorden in te kleuren. Als je 3 variabelen hebt om te veranderen, doe je dan scenario's? Nee, maak gewoon kopieën van het werkblad of kopieën van de tabel, ze zijn traag te berekenen, vooral bij een complex model. Er is een berekeningsmodus voor Automatisch voor iedereen behalve tabellen, en Owen W. Green stelde voor om deze functie in de boeken op te nemen.
Dus dankzij hem, en bedankt voor het langskomen, we zien je de volgende keer voor een nieuwe netcast van!
Download bestand
Download het voorbeeldbestand hier: Podcast2034.xlsx
