Die Excel-Was-wäre-wenn-Analyse bietet eine Datentabelle. Das ist ein schlechter Name. Es sollte Sensitivitätsanalyse genannt werden. Es ist cool. Lesen Sie hier darüber.
Mit der Zielsuche können Sie die Eingaben finden, die zu einem bestimmten Ergebnis führen. Manchmal möchten Sie viele verschiedene Ergebnisse aus verschiedenen Eingabekombinationen sehen. Vorausgesetzt, Sie müssen nur zwei Eingabezellen ändern, bietet die Datentabelle eine schnelle Möglichkeit, Alternativen zu vergleichen.
Angenommen, Sie möchten den Preis für eine Vielzahl von Kapitalguthaben und für eine Vielzahl von Konditionen berechnen.
Stellen Sie sicher, dass sich die Formel, die Sie modellieren möchten, in der oberen linken Ecke eines Bereichs befindet. Fügen Sie verschiedene Werte für eine Variable in die linke Spalte und verschiedene Werte für eine andere Variable oben ein.
Wählen Sie auf der Registerkarte Daten die Option Was-wäre-wenn-Analyse, Datentabelle aus.
Sie haben Werte in der oberen Zeile der Eingabetabelle. Sie möchten, dass Excel diese Werte in eine bestimmte Eingabezelle einfügt. Geben Sie diese Eingabezelle als Zeileneingabezelle an.
Sie haben Werte in der linken Spalte. Sie möchten, dass diese in eine andere Eingabezelle eingesteckt werden. Geben Sie diese Zelle als Spalteneingabezelle an.
Wenn Sie auf OK klicken, wiederholt Excel die Formel in der oberen linken Spalte für alle Kombinationen der oberen Zeile und der linken Spalte. In der Abbildung unten sehen Sie 60 verschiedene Darlehenszahlungen, die auf verschiedenen Ergebnissen basieren.
Beachten Sie, dass ich die Tabellenergebnisse so formatiert habe, dass sie keine Dezimalstellen enthalten, und Home, Bedingte Formatierung, Farbskala verwendet habe, um die rot / gelb / grüne Schattierung hinzuzufügen.
Hier ist der große Teil: Dieser Tisch ist "live". Wenn Sie die Eingabezellen in der linken Spalte oder in der oberen Zeile ändern, werden die Werte in der Tabelle neu berechnet. Unten konzentrieren sich die Werte links auf den Bereich von 23.000 bis 24.000 US-Dollar.
Vielen Dank an Owen W. Green für die Vorschläge für Tabellen.
Schau Video
- Drei Was-wäre-wenn-Tools in Excel
- Gestern - Zielsuche
- Heute - eine Datentabelle
- Ideal für Probleme mit zwei Variablen
- Wissenswertes: Die TABLE-Array-Funktion kann nicht manuell eingegeben werden - sie funktioniert nicht
- Verwenden Sie eine Farbskala, um die Antworten einzufärben
- Was ist, wenn Sie 3 Variablen ändern müssen? Szenarien? Nein! Arbeitsblatt kopieren
- Tabellen sind langsam zu berechnen: Berechnungsmodus für alle außer Tabellen
- Vielen Dank an Owen W. Green für diesen Tipp
Video-Transkript
Lernen Sie Excel aus dem Podcast, Folge 2034 - Was-wäre-wenn mit einer Datentabelle!
Ich podcaste das gesamte Buch. Klicken Sie auf das "i" in der oberen rechten Ecke, um zur Wiedergabeliste zu gelangen!
Heute werden wir über das zweite Tool unter Was-wäre-wenn-Analyse sprechen, gestern haben wir über Zielsuche gesprochen, heute werden wir eine Datentabelle behandeln. Wir haben hier also dieses nette kleine Modell, dies ist ein kleines Modell, 3 Eingabezellen, eine Formel. Dieses Modell kann jedoch aus Hunderten von Eingabezellen und Tausenden von Zeilen bestehen, sofern es sich um eine endgültige Antwort handelt. Wir möchten diese Antwort für mehrere unterschiedliche Werte von 2-3 (?) Eingabezellen modellieren. Vielleicht sind wir zum Beispiel daran interessiert, verschiedene Autos zu betrachten, also ab 20000, also setze ich dort 20 und 21000 ein, greife nach dem Füllgriff und ziehe, nimm das auf 28000 herunter. Betrachten Sie verschiedene Bedingungen, so ein 36-Monats-Darlehen, 42-Monats-Darlehen, 48-Monats-Darlehen, 54, 60, 66 und sogar 72.
Okay, dieser nächste Schritt ist völlig optional, aber es hilft mir wirklich, darüber nachzudenken. Ich ändere immer die Farben der Werte oben und links. Und das wirklich Wichtige hier ist, dass diese Eckzelle, diese alles entscheidende Eckzelle, die Antwort sein muss, die wir zu modellieren versuchen, in Ordnung. Sie müssen also mit der Antwort aus dieser Eckzelle auswählen und dann alle Zeilen und alle Spalten auswählen. Wir gehen also zu Daten, Was-wäre-wenn-Analyse und einer Datentabelle, und hier werden zwei Dinge gefragt, und so würden Sie darüber denken. Es heißt, es gibt eine ganze Reihe verschiedener Elemente in der obersten Zeile der Tabelle. Ich möchte diese Elemente einzeln nehmen und in das Modell einfügen. Wo sollen wir sie eingeben? Diese Elemente, das sind Begriffe, sollten in die Zelle B2 gelangen. Und dann,Es gibt eine ganze Reihe von Elementen in der linken Spalte. Wir möchten diese einzeln nehmen und sie so in B1 einstecken. Okay, und wir klicken auf OK, BAM. Dieses Modell wird immer und immer wieder ausgeführt .
Jetzt nur ein bisschen Aufräumen hier, ich gehe immer rein und mache Home und wahrscheinlich 0 Dezimalstellen, so. Und vielleicht ein bisschen bedingte Formatierung, Farbskalen, und lassen Sie uns mit roten Zahlen für große und grünen Zahlen für kleine gehen, nur um mir eine Möglichkeit zu geben, dies visuell zu verfolgen. Jetzt sieht es so aus, als würden wir für 425 Dollar drehen, wir sind irgendwie an diesem oder jenem Ort, oder Sie wissen, vielleicht bringen wir uns hier alle in die Nähe von 425 Dollar. So kann ich sehen, was die verschiedenen Chancen sind, unsere verschiedenen Kombinationen, um uns zu diesen Werten zu bringen.
Nun, ein paar Dinge, dieser Teil hier drinnen, sind eigentlich eine große Array-Formel, also = TABELLE (B2, B1), die Zeilen- und Spalteneingabe. Das ist merkwürdig, Sie dürfen dies nicht eingeben, Sie können dies nur mit Daten erstellen, Was-wäre-wenn-Analyse, Sie müssen dieses Dialogfeld verwenden. Wenn Sie versuchen, diese Formel einzugeben, drücken Sie Strg + Umschalt + Eingabetaste, es wird nicht funktionieren, oder? Es ist also eine Funktion in Excel, aber wenn Sie klug genug sind, sie einzugeben, schade, wird sie nicht funktionieren, aber sie wird ständig neu berechnet. Wenn wir also feststellen, dass wir nur Begriffe aus 48 betrachten und in Dreiergruppen oder ähnlichem suchen möchten, wird all das berechnet, wenn ich diese Zahlen ändere. In diesem Fall wird nur eine Formel für jede Formel ausgeführt. Stellen Sie sich jedoch vor, wenn wir 100 Formeln erstellen, wird dies dramatisch verlangsamt. Also hier draußen unter Formeln, da 's tatsächlich eine Option Berechnungsoptionen, automatisch oder manuell, gibt es eine dritte, die besagt: "Ja, berechnen Sie alles außer den Datentabellen neu, berechnen Sie die Datentabelle nicht ständig neu." Weil dies die Berechnungszeiten erheblich belasten kann.
In Ordnung, Datentabellen sind fantastisch, wenn Sie zwei Variablen ändern müssen, aber wir müssen drei Variablen ändern. Was ist, wenn es unterschiedliche Zinssätze gibt? Empfehle ich, zum Szenariomanager zu gehen? NEIN, ich empfehle NIEMALS, zum Szenariomanager zu gehen! In diesem Fall haben wir 9x7, das sind 63 verschiedene Szenarien, die wir hier berechnet haben. Das Erstellen von 63 verschiedenen Szenario-Manager-Szenarien würde 2 Stunden dauern. Es ist schrecklich. Ich werde dies im Buch „MrExcel XL“ nicht behandeln, da es die 40 besten Tipps sind. Dies ist wahrscheinlich in meinem "Power Excel" -Buch mit 567 gelösten Excel-Rätseln enthalten, aber ich bin mir sicher, dass ich mich darüber beschwert habe, wie miserabel es ist, es zu verwenden. Sie werden mich hier nicht als Szenario-Manager sehen. Wenn wir dies wirklich für mehrere verschiedene Raten tun mussten, ist das Beste, was wir tun können, nur Strg-Ziehen, nehmen Sie dieses Blatt, Strg-Ziehen, Strg-Ziehen,Ziehen Sie bei gedrückter Strg-Taste und ändern Sie dann die Raten auf jedem Blatt. Wenn wir also 5% oder 4,75% oder ähnliches usw. erhalten könnten, gibt es keine einfache Möglichkeit, dies für 3 Variablen im Szenario-Manager einzurichten. Okay, "40 beste Excel-Tipps aller Zeiten", alle in diesem Buch können Sie das Buch kaufen, klicken Sie auf das "i" in der oberen rechten Ecke.
Episodenrückblick von heute: Es gibt drei Was-wäre-wenn-Tools in Excel. Gestern haben wir über Goal Seek gesprochen, heute über die Datentabelle. Es ist fantastisch für Probleme mit zwei Variablen. Morgen sehen Sie eines mit einem Problem mit einer Variablen. Die Tabellenarray-Funktion kann nicht manuell eingegeben werden, sie funktioniert nicht. Sie müssen Daten, Was-wäre-wenn-Analyse, Datentabelle verwenden. Ich habe eine Farbskala verwendet, Home, Bedingte Formatierung, Farbskalen, um die Antworten einzufärben. Wenn Sie 3 Variablen ändern müssen, machen Sie Szenarien? Nein, machen Sie einfach Kopien des Arbeitsblatts oder der Tabelle, sie sind langsam zu berechnen, insbesondere bei einem komplexen Modell. Es gibt einen Berechnungsmodus für Automatisch für alle außer Tabellen, und Owen W. Green schlug vor, diese Funktion in die Bücher aufzunehmen.
Also danke an ihn und danke an dich für deinen Besuch, wir sehen uns beim nächsten Mal für einen weiteren Netcast von!
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2034.xlsx
