Berechnen von mehreren Ergebnissen mit einer Datentabelle

In Microsoft Excel sind Datentabellen Teil einer Reihe von Befehlen, die als What-If bezeichnet werden. Beim Erstellen und Analysieren von Datentabellen wird eine Was-wäre-wäre-wenn-Analyse durchgeführt.

Die Was-wäre-wenn-Analyse ist der Vorgang, bei dem die Werte in Zellen geändert werden, um zu sehen, wie sich diese Änderungen auf die Ergebnisse von Formeln im Arbeitsblatt auswirken. Sie können beispielsweise eine Datentabelle verwenden, um den Zinssatz und die Laufzeitlänge eines Kredits zu variieren, um potenzielle monatliche Zahlungsbeträge auszuwerten.

Arten der Was-wäre-wäre-Analyse    

Es gibt drei Arten von Was-wäre-wäre-Analysetools in Excel: Szenarien, Datentabellenund Zielsuche. Szenarien und Datentabellen verwenden Sätze von Eingabewerten, um mögliche Ergebnisse zu berechnen. Die Zielwertsuche unterscheidet sich deutlich, verwendet ein einzelnes Ergebnis und berechnet mögliche Eingabewerte, die zu diesem Ergebnis führen würden.

Wie Szenarien helfen Ihnen auch Datentabellen, eine Reihe möglicher Ergebnisse zu untersuchen. Im Gegensatz zu Szenarien zeigen Datentabellen alle Ergebnisse in einer Tabelle auf einem Arbeitsblatt an. Die Verwendung von Datentabellen erleichtert die Untersuchung eines Bereichs von Möglichkeiten auf einen Blick. Weil Sie sich auf nur eine oder zwei Variablen konzentrieren, sind die Ergebnisse in tabellarischer Form einfach zu lesen und gemeinsam zu nutzen.

In einer Datentabelle können maximal zwei Variablen enthalten sein. Wenn Sie mehr als zwei Variablen analysieren möchten, sollten Sie stattdessen Szenarien verwenden. Obwohl sie auf nur eine oder zwei Variablen beschränkt ist (eine für die Zeileneingabezelle und eine für die Spalteneingabezelle), kann eine Datentabelle so viele unterschiedliche Variablenwerte enthalten, wie Sie möchten. Ein Szenario kann maximal 32 verschiedene Werte aufweisen, doch können Sie beliebig viele Szenarien erstellen.

Weitere Informationen finden Sie im Artikel Einführung in What-If Analyse.

Erstellen Sie je nach Anzahl der Variablen und Formeln, die Sie testen müssen, entweder Eine-Variable- oder zweivariable Datentabellen.

Datentabellen mit einer Variablen    

Verwenden Sie eine Datentabelle mit einer Variablen, wenn Sie feststellen möchten, wie sich unterschiedliche Werte einer Variablen in einer oder mehreren Formeln auf die Ergebnisse dieser Formeln auswirken. Sie können z. B. eine Datentabelle mit einer Variablen verwenden, um zu sehen, wie sich unterschiedliche Zinssätze auf eine monatliche Hypothekenzahlung auswirken, indem Sie die Funktion PMT verwenden. Verwenden Sie dazu die Funktion RMZ, und geben Sie die Variablenwerte in eine Spalte oder Zeile ein, und die Ergebnisse werden dann in einer benachbarten Spalte oder Zeile angezeigt.

In der folgenden Abbildung enthält Zelle D2 die Zahlungsformel =PMT(B3/12;B4;-B5),die sich auf die Eingabezelle B3 bezieht.

Datentabellen mit zwei Variablen    

Verwenden Sie eine Datentabelle mit zwei Variablen, um festzustellen, wie sich unterschiedliche Werte von zwei Variablen in einer Formel auf die Ergebnisse dieser Formel auswirken. Sie können beispielsweise eine Datentabelle mit zwei Variablen verwenden, um festzustellen, wie sich unterschiedliche Kombinationen von Zinssätzen und Kreditbedingungen auf eine monatliche Hypothekenrate auswirken.

In der folgenden Abbildung enthält Zelle C2 die Zahlungsformel =PMT(B3/12;B4;-B5),die zwei Eingabezellen verwendet: B3 und B4.

 

Berechnungen von Datentabelle    

Immer wenn ein Arbeitsblatt neu berechnet wird, werden auch alle Datentabellen neu berechnet – auch wenn die Daten nicht geändert wurden. Um die Berechnung eines Arbeitsblatts zu beschleunigen, das eine Datentabelle enthält, können Sie die Berechnungsoptionen so ändern, dass das Arbeitsblatt automatisch neu berechnet wird, jedoch nicht die Datentabellen. Weitere Informationen finden Sie im Abschnitt Beschleunigen der Berechnung in einem Arbeitsblatt, das Datentabellen enthält.

Eine Datentabelle mit einer Variablen enthält ihre Eingabewerte entweder in einer einzelnen Spalte (spaltenorientiert) oder in einer Zeile (zeilenorientiert). Jede Formel in einer Datentabelle mit einer Variablen darf sich nur auf eine Eingabefeld.

Führen Sie die folgenden Schritte aus:

1. Geben Sie die Liste der Werte ein, die Sie in der Eingabezelle ersetzen möchten – entweder um eine Spalte nach unten oder über eine Zeile. Lassen Sie ein paar leere Zeilen und Spalten auf beiden Seiten der Werte.

2. Führen Sie eine der folgenden Aktionen aus:

   • Wenn die Datentabelle spaltenorientiert ist (die Variablenwerte befinden sich in einer Spalte), geben Sie die Formel in der Zelle eine Zeile darüber und eine Zelle rechts neben der Spalte mit Werten ein. Diese Datentabelle mit einer Variablen ist spaltenorientiert, und die Formel ist in Zelle D2 enthalten.
     
     
     
     Wenn Sie die Auswirkungen verschiedener Werte auf andere Formeln untersuchen möchten, geben Sie die zusätzlichen Formeln in Zellen rechts neben der ersten Formel ein.

   • Wenn die Datentabelle zeilenorientiert ist (die Variablenwerte befinden sich in einer Zeile), geben Sie die Formel in der Zelle eine Spalte links neben dem ersten Wert und eine Zelle unterhalb der Zeile der Werte ein.
     
     Wenn Sie die Auswirkungen verschiedener Werte auf andere Formeln untersuchen möchten, geben Sie die zusätzlichen Formeln in Zellen unter der ersten Formel ein.

3. Markieren Sie den Zellbereich mit den zu ersetzenden Formeln und Werten. In der abbildung oben ist dieser Bereich C2:D5.

4. Klicken Sie auf der Registerkarte Daten auf Was-wäre-wäre-wenn-Analyse >Datentabelle (in der Gruppe Datentools oder Gruppe Prognose von Excel 2016 ).  

5. Führen Sie eine der folgenden Aktionen aus:

   • Wenn die Datentabelle spaltenorientiert ist, geben Sie die Zellbezug für die Eingabezelle im Feld Spalteneingabezelle ein. In der abbildung oben ist die Eingabezelle B3.

   • Wenn die Datentabelle zeilenorientiert ist, geben Sie den Zellbezug für die Eingabezelle in das Eingabezellenfeld Zeile ein.

     Hinweis: Nachdem die Datentabelle erstellt wurde, möchten Sie das Format der Ergebniszellen möglicherweise ändern. In der Abbildung sind die Ergebniszellen als Währung formatiert.

Formeln, die in einer Datentabelle mit einer Variablen verwendet werden, müssen auf das gleiche Eingabefeld verweisen.

Führen Sie die folgenden Schritte aus

1. Gehen Sie wie im Folgenden vor:

   • Wenn die Datentabelle spaltenorientiert ist, geben Sie die neue Formel in eine leere Zelle rechts neben einer vorhandenen Formel in der obersten Zeile der Datentabelle ein.

   • Wenn die Datentabelle zeilenorientiert ist, geben Sie die neue Formel in eine leere Zelle unter einer vorhandenen Formel in der ersten Spalte der Datentabelle ein.

2. Markieren Sie den Zellbereich, der die Datentabelle und die neue Formel enthält.

3. Klicken Sie auf der Registerkarte Daten auf Was-wäre-wäre-wenn-Analyse> Datentabelle (in der Gruppe Datentools oder Gruppe Prognose von Excel 2016 ).

4. Gehen Sie wie folgt vor:

   • Wenn die Datentabelle spaltenorientiert ist, geben Sie den Zellbezug für die Eingabezelle in das Feld Spalteneingabezelle ein.

   • Wenn die Datentabelle zeilenorientiert ist, geben Sie den Zellbezug für die Eingabezelle in das Feld Zeileneingabezelle ein.

In Datentabellen mit zwei Variablen wird eine Formel mit zwei Wertelisten verwendet. Die Formel muss sich auf zwei verschiedene Eingabefelder beziehen.

Führen Sie die folgenden Schritte aus:

1. Geben Sie in einer Zelle auf dem Arbeitsblatt die Formel ein, die sich auf die beiden Eingabezellen bezieht.

   Im folgenden Beispiel, in dem die Formelnstartwerte in die Zellen B3, B4 und B5 eingegeben werden, geben Sie die Formel =PMT(B3/12;B4;-B5) in Zelle C2 ein.

2. Geben Sie eine Liste von Eingabewerten in dieselbe Spalte unter der Formel ein.

   Geben Sie in diesem Fall die unterschiedlichen Zinssätze in die Zellen C3, C4 und C5 ein.

3. Geben Sie die zweite Liste in derselben Zeile wie die Formel rechts ein.

   Geben Sie die Laufzeit (in Monaten) in die Zellen D2 und E2 ein.

4. Markieren Sie den Zellbereich mit der Formel (C2), den Wertespalten und -zeilen (C3:C5 und D2:E2) und die Zellen, die die berechneten Werte enthalten sollen (D3:E5).

   Markieren Sie in diesem Fall den Bereich C2:E5.

5. Klicken Sie auf der Registerkarte Daten in der Gruppe Datentools oder Prognose (in Excel 2016 ) auf Was-wäre-wenn-Analyse >Datentabelle (in der Gruppe Datentools oder Prognosegruppe von Excel 2016 ).  

6. Geben Sie im Eingabezellenfeld Zeile den Bezug auf die Eingabezelle für die Eingabewerte in der Zeile ein.
   Geben Sie Zelle B4 in das Feld Eingabezelle Zeile ein.

7. Geben Sie im Feld Spalteneingabezelle den Bezug auf die Eingabezelle für die Eingabewerte in der Spalte ein.
   Geben Sie B3 in das Feld Spalteneingabezelle ein.

8. Klicken Sie auf OK.

Beispiel für eine Datentabelle mit zwei Variablen

In einer Datentabelle mit zwei Variablen können unterschiedliche Kombinationen von Zinssätzen und Laufzeiten angezeigt werden, die Auswirkungen auf die monatliche Rückzahlung des Kredits haben. In der abbildung hier enthält Zelle C2 die Zahlungsformel =PMT(B3/12;B4;-B5),die zwei Eingabezellen verwendet: B3 und B4.

Wenn Sie diese Berechnungsoption festlegen, werden keine Berechnungen der Datentabelle durchgeführt, wenn eine Neuberechnung für die gesamte Arbeitsmappe erfolgt. Um Ihre Datentabelle manuell neu zu berechnen, markieren Sie die hierin befindlichen Formeln, und drücken Sie F9.

Führen Sie die folgenden Schritte aus, um die Berechnungsleistung zu verbessern:

1. Klicken Sie auf > Optionen > Formeln.

2. Klicken Sie im Abschnitt Berechnungsoptionen unter Berechnenauf Automatisch mit Ausnahme von Datentabellen.

   Tipp: Optional klicken Sie auf der Registerkarte Formeln auf den Pfeil unter Berechnungsoptionen, und klicken Sie dann auf Automatisch außer Datentabellen (in der Gruppe Berechnung).

Sie können ein paar andere Tools Excel, um was-wäre-wäre-wenn-Analysen durchzuführen, wenn Sie bestimmte Ziele oder größere Mengen variabler Daten haben.

Zielwertsuche

Wenn Sie das Von einer Formel zu erwartende Ergebnis kennen, aber nicht genau wissen, welchen Eingabewert die Formel benötigt, um dieses Ergebnis zu erhalten, verwenden Sie das Goal-Seek Feature. Lesen Sie den Artikel Verwenden der Zielwertsuche, um das gesuchte Ergebnis zu finden, indem Sie einen Eingabewert anpassen.

Excel Solver

Sie können das Excel Solver-Add-In verwenden, um den optimalen Wert für eine Reihe von Eingabevariablen zu finden. Solver arbeitet mit einer Gruppe von Zellen (so genannten Entscheidungsvariablen oder einfach variablen Zellen), die zum Berechnen der Formeln in den Ziel- und Einschränkungszellen verwendet werden. Solver passt die Werte in den Variablenzellen an, sodass sie den Einschränkungen für Nebenbedingungszellen entsprechen und das für die Zielzelle gewünschte Ergebnis erzeugt wird. Weitere Informationen finden Sie in diesem Artikel: Definieren und Beheben eines Problems mithilfe von Solver.