Was-wäre-wenn-Analyse mit Datentabellen

Mit einer Datentabelle in Excel können Sie einfach eine oder zwei Eingaben variieren und eine Was-wäre-wenn-Analyse durchführen. Eine Datentabelle ist ein Bereich von Zellen, in dem Sie Werte in einigen Zellen ändern und unterschiedliche Antworten auf ein Problem finden können.

Es gibt zwei Arten von Datentabellen:

• Datentabellen mit einer Variablen
• Datentabellen mit zwei Variablen

Wenn Ihr Analyseproblem mehr als zwei Variablen enthält, müssen Sie das Szenario-Manager-Tool von Excel verwenden. Weitere Informationen finden Sie im Kapitel - Was-wäre-wenn-Analyse mit Szenario-Manager in diesem Lernprogramm.

Datentabellen mit einer Variablen

Eine Datentabelle mit einer Variablen kann verwendet werden, wenn Sie sehen möchten, wie unterschiedliche Werte einer Variablen in einer oder mehreren Formeln die Ergebnisse dieser Formeln ändern. Mit anderen Worten, mit einer Datentabelle mit einer Variablen können Sie bestimmen, wie durch Ändern einer Eingabe eine beliebige Anzahl von Ausgaben geändert wird. Sie werden dies anhand eines Beispiels verstehen.

Example

Es gibt ein Darlehen von 5.000.000 für eine Amtszeit von 30 Jahren. Sie möchten die monatlichen Zahlungen (EMI) für unterschiedliche Zinssätze kennen. Möglicherweise möchten Sie auch wissen, wie hoch die Zinsen und der Kapitalbetrag sind, die im zweiten Jahr gezahlt werden.

Analyse mit Datentabelle mit einer Variablen

Die Analyse mit einer Datentabelle mit einer Variablen muss in drei Schritten erfolgen:

Step 1 - Stellen Sie den gewünschten Hintergrund ein.

Step 2 - Erstellen Sie die Datentabelle.

Step 3 - Führen Sie die Analyse durch.

Lassen Sie uns diese Schritte im Detail verstehen -

Schritt 1: Stellen Sie den gewünschten Hintergrund ein

• Angenommen, der Zinssatz beträgt 12%.

• Listen Sie alle erforderlichen Werte auf.

• Benennen Sie die Zellen, die die Werte enthalten, so dass die Formeln Namen anstelle von Zellreferenzen haben.

• Stellen Sie die Berechnungen für EMI, Cumulative Interest und Cumulative Principal mit den Excel-Funktionen PMT, CUMIPMT und CUMPRINC ein.

Ihr Arbeitsblatt sollte wie folgt aussehen:

Sie können sehen, dass die Zellen in Spalte C wie in den entsprechenden Zellen in Spalte D angegeben benannt sind.

Schritt 2: Erstellen Sie die Datentabelle

• Geben Sie die Liste der Werte, dh der Zinssätze, die Sie ersetzen möchten, in die Eingabezelle in der Spalte E wie folgt ein:

Wie Sie sehen, befindet sich über den Zinswerten eine leere Zeile. Diese Zeile steht für die Formeln, die Sie verwenden möchten.

• Geben Sie die erste Funktion ein (PMT) in der Zelle eine Zeile darüber und eine Zelle rechts von der Wertespalte. Geben Sie die anderen Funktionen ein (CUMIPMT and CUMPRINC) in den Zellen rechts von der ersten Funktion.

  Die beiden Zeilen über den Zinswerten sehen nun wie folgt aus:

Die Datentabelle sieht wie folgt aus:

Schritt 3: Führen Sie die Analyse mit dem Was-wäre-wenn-Analysedatentabellen-Tool durch

• Wählen Sie den Zellbereich aus, der die Formeln und Werte enthält, die Sie ersetzen möchten, dh wählen Sie den Bereich - E2: H13.

• Klicken Sie auf der Multifunktionsleiste auf die Registerkarte DATEN.

• Klicken Sie in der Gruppe Datentools auf Was-wäre-wenn-Analyse.

• Wählen Sie Datentabelle in der Dropdown-Liste.

Data Table Das Dialogfeld wird angezeigt.

• Klicken Sie auf das Symbol im Feld Spalteneingabezelle.
• Klicken Sie auf die Zelle Interest_Rate, das ist C2.

Sie können sehen, dass die Spalteingabezelle als $ C $ 2 verwendet wird. OK klicken.

Die Datentabelle wird mit den berechneten Ergebnissen für jeden der Eingabewerte gefüllt, wie unten gezeigt -

Wenn Sie ein EMI von 54.000 zahlen können, können Sie feststellen, dass der Zinssatz von 12,6% für Sie geeignet ist.

Datentabellen mit zwei Variablen

Eine Datentabelle mit zwei Variablen kann verwendet werden, wenn Sie sehen möchten, wie unterschiedliche Werte von zwei Variablen in einer Formel die Ergebnisse dieser Formel ändern. Mit anderen Worten, mit einer Datentabelle mit zwei Variablen können Sie bestimmen, wie durch Ändern von zwei Eingaben eine einzelne Ausgabe geändert wird. Sie werden dies anhand eines Beispiels verstehen.

Example

Es gibt ein Darlehen von 50.000.000. Sie möchten wissen, wie sich unterschiedliche Kombinationen von Zinssätzen und Kreditlaufzeiten auf die monatliche Zahlung (EMI) auswirken.

Analyse mit Datentabelle mit zwei Variablen

Die Analyse mit einer Datentabelle mit zwei Variablen muss in drei Schritten erfolgen:

Step 1 - Stellen Sie den gewünschten Hintergrund ein.

Step 2 - Erstellen Sie die Datentabelle.

Step 3 - Führen Sie die Analyse durch.

Schritt 1: Stellen Sie den gewünschten Hintergrund ein

• Angenommen, der Zinssatz beträgt 12%.

• Listen Sie alle erforderlichen Werte auf.

• Benennen Sie die Zellen, die die Werte enthalten, so dass die Formel Namen anstelle von Zellreferenzen enthält.

• Stellen Sie die Berechnung für EMI mit der Excel-Funktion ein - PMT.

Ihr Arbeitsblatt sollte wie folgt aussehen:

Sie können sehen, dass die Zellen in Spalte C wie in den entsprechenden Zellen in Spalte D angegeben benannt sind.

Schritt 2: Erstellen Sie die Datentabelle

• Art =EMI in Zelle F2.

• Geben Sie die erste Liste der Eingabewerte, dh die Zinssätze, in die Spalte F ein, beginnend mit der Zelle unter der Formel, dh F3.

• Geben Sie die zweite Liste der Eingabewerte ein, dh die Anzahl der Zahlungen in Zeile 2, beginnend mit der Zelle rechts neben der Formel, dh G2.

  Die Datentabelle sieht wie folgt aus:

Führen Sie die Analyse mit der Datentabelle des Was-wäre-wenn-Analysetools durch

• Wählen Sie den Zellenbereich aus, der die Formel enthält, und die beiden Wertesätze, die Sie ersetzen möchten, dh wählen Sie den Bereich aus - F2: L13.

• Klicken Sie auf der Multifunktionsleiste auf die Registerkarte DATEN.

• Klicken Sie in der Gruppe Datentools auf Was-wäre-wenn-Analyse.

• Wählen Sie Datentabelle aus der Dropdown-Liste.

Das Dialogfeld Datentabelle wird angezeigt.

• Klicken Sie auf das Symbol im Feld Zeileneingabezelle.
• Klicken Sie auf die Zelle NPER, das ist C3.
• Klicken Sie erneut auf das Symbol im Feld Zeileneingabezelle.
• Klicken Sie anschließend auf das Symbol im Feld Spalteneingabezelle.
• Klicken Sie auf die Zelle Interest_Rate (C2).
• Klicken Sie erneut auf das Symbol im Feld Spalteneingabezelle.

Sie werden sehen, dass die Zeileneingabezelle als $ C $ 3 und die Spalteneingabezelle als $ C $ 2 angenommen wird. OK klicken.

Die Datentabelle wird mit den berechneten Ergebnissen für jede Kombination der beiden Eingabewerte gefüllt -

Wenn Sie ein EMI von 54.000 bezahlen können, sind der Zinssatz von 12,2% und 288 EMI für Sie geeignet. Dies bedeutet, dass die Laufzeit des Darlehens 24 Jahre betragen würde.

Datentabellenberechnungen

Datentabellen werden jedes Mal neu berechnet, wenn das sie enthaltende Arbeitsblatt neu berechnet wird, auch wenn sie sich nicht geändert haben. Um die Berechnungen in einem Arbeitsblatt zu beschleunigen, das eine Datentabelle enthält, müssen Sie die Berechnungsoptionen in ändernAutomatically Recalculate das Arbeitsblatt, aber nicht die Datentabellen, wie im nächsten Abschnitt angegeben.

Beschleunigen der Berechnungen in einem Arbeitsblatt

Sie können die Berechnungen in einem Arbeitsblatt mit Datentabellen auf zwei Arten beschleunigen:

• Aus Excel-Optionen.
• Aus dem Band.

Aus Excel-Optionen

• Klicken Sie auf der Multifunktionsleiste auf die Registerkarte DATEI.
• Wählen Sie Optionen aus der Liste im linken Bereich.

Das Dialogfeld Excel-Optionen wird angezeigt.

• Wählen Sie im linken Bereich die Option aus Formulas.

• Wählen Sie die Option Automatic except for data tables unter Workbook Calculationim Abschnitt Berechnungsoptionen. OK klicken.

Aus dem Band

• Klicken Sie auf der Multifunktionsleiste auf die Registerkarte FORMELN.

• Drücke den Calculation Options in der Gruppe Berechnungen.

• Wählen Automatic Except for Data Tables in der Dropdown-Liste.