Berechnen von mehreren Ergebnissen mit einer Datentabelle

In Microsoft Excel sind Datentabellen Teil einer Reihe von Befehlen, die als What-If Analysetools bezeichnet werden. Wenn Sie Datentabellen erstellen und analysieren, führen Sie Was-wäre-wenn-Analysen durch.

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. Beispielsweise können Sie eine Datentabelle verwenden, um den Zinssatz und die Laufzeit für einen Kredit zu variieren, um potenzielle monatliche Zahlungsbeträge auszuwerten.

Typen der Was-wäre-wenn-Analyse    

Es gibt drei Arten von Was-wäre-wenn-Analysetools in Excel: Szenarien, Datentabellen und Zielsuche. Szenarien und Datentabellen verwenden Sätze von Eingabewerten, um mögliche Ergebnisse zu berechnen. Die Zielsuche unterscheidet sich deutlich, verwendet ein einzelnes Ergebnis und berechnet mögliche Eingabewerte, die dieses Ergebnis erzeugen 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 es nur auf eine oder zwei Variablen beschränkt ist (eine für die Zeileneingabezelle und eine für die Spalteneingabezelle), kann eine Datentabelle beliebig viele verschiedene Variablenwerte enthalten. 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 Analysis.

Erstellen Sie je nach Anzahl der Variablen und Formeln, die Sie testen müssen, datentabellen mit einer oder zwei Variablen.

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 beispielsweise eine Datentabelle mit einer Variablen verwenden, um zu sehen, wie sich unterschiedliche Zinssätze auf eine monatliche Hypothekenzahlung auswirken, indem Sie die PMT-Funktion 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.

 

Datentabellenberechnungen    

Jedes Mal, wenn ein Arbeitsblatt neu berechnet wird, werden auch alle Datentabellen neu berechnet – auch wenn keine Änderung an den Daten erfolgt ist. 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, aber 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 die Eingabewerte entweder in einer einzelnen Spalte (spaltenorientiert) oder zeilenübergreifend (zeilenorientiert). Jede Formel in einer Datentabelle mit einer Variablen darf nur auf eine Eingabefeld verweisen.

Führen Sie die folgenden Schritte aus:

1. Geben Sie die Liste der Werte ein, die Sie in der Eingabezelle ersetzen möchten – entweder nach unten oder in einer Zeile. Lassen Sie einige 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 die Zelle ein, die eine Zeile darüber und eine Zelle rechts neben der Spalte mit Werten liegt. 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 ein, die eine Spalte links neben dem ersten Wert und eine Zelle unterhalb der Zeile der Werte liegt.
     
     Wenn Sie die Auswirkungen verschiedener Werte auf andere Formeln untersuchen möchten, geben Sie die zusätzlichen Formeln in Zellen unterhalb der ersten Formel ein.

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

4. Wählen Sie auf der Registerkarte Datendie Option Was-wäre-wäre-wenn-Analyse > Datentabelle aus (in der Gruppe Datentools oder in der Gruppe Prognose von Excel 2016 ).

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

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

   • Wenn die Datentabelle zeilenorientiert ist, geben Sie den Zellbezug für die Eingabezelle in das Feld Zeileneingabezelle 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. Führen Sie eine der folgenden Aktionen aus:

   • Wenn die Datentabelle spaltenorientiert ist, geben Sie die neue Formel in einer leeren 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 unterhalb 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. Wählen Sie auf der Registerkarte Datendie Option Was-wäre-wäre-wenn-Analyse > Datentabelle aus (in der Gruppe Datentools oder in der Gruppe Prognose von Excel 2016 ).

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

   • 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 Formel-Startwerte 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 ein – auf der rechten Seite.

   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. Wählen Sie auf der Registerkarte Daten in der Gruppe Datentools oder der Gruppe Prognose (in Excel 2016 ) die Option Was-wäre-wenn-Analyse > Datentabelle aus (in der Gruppe Datentools oder in der Gruppe Prognose von Excel 2016 ).

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

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

8. Wählen Sie OK aus.

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 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 Datentabellenberechnungen ausgefü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. Wählen Sie Datei > Optionen > Formeln aus.

2. Wählen Sie im Abschnitt Berechnungsoptionen die Option Automatisch aus.

   Tipp: Wählen Sie optional auf der Registerkarte Formeln den Pfeil unter Berechnungsoptionen und dann Automatisch aus.

Sie können einige andere Excel-Tools verwenden, um Was-wäre-wenn-Analysen durchzuführen, wenn Sie bestimmte Ziele oder größere Mengen von Variablendaten haben.

Zielwertsuche

Wenn Sie wissen, welches Ergebnis von einer Formel erwartet werden soll, aber nicht genau wissen, welchen Eingabewert die Formel benötigt, um dieses Ergebnis zu erhalten, verwenden Sie das feature Goal-Seek. Lesen Sie den Artikel Zielsuche verwenden, um das gewünschte 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 (sogenannte Entscheidungsvariablen oder einfach Variablenzellen), 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 Lösen eines Problems mithilfe von Solver.