Berechnen von mehreren Ergebnissen mit einer Datentabelle

In Microsoft Excel sind Datentabellen Teil einer Befehlsgruppe, die als Was-wäre-wenn-Analyse-Tools bekannt ist. Wenn Sie Datentabellen verwenden, erstellen Sie eine Was-wäre-wenn-Analyse.

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. So können Sie eine Datentabelle beispielsweise verwenden, um den Zinssatz und die Laufzeit eines Kredits zu variieren, um potenzielle monatliche Rückzahlungsbeträge zu bewerten.

Arten der Was-wäre-wenn-Analyse    

Es gibt drei Arten von Tools für Was-wäre-wenn-Analysen in Excel: Szenarien, Datentabellen und Zielwertsuche. Szenarien und Datentabellen verwenden Gruppen von Eingabewerten, um mögliche Ergebnisse zu berechnen. Die Zielsuche unterscheidet sich deutlich: Sie 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 sie nur auf eine oder zwei Variablen (eine für „Werte aus Zeile“ und eine „Werte aus Spalte“) beschränkt ist, 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 die Was-wäre-wenn-Analyse.

Erstellen Sie entweder Datentabellen mit einer Variablen oder mit zwei Variablen, je nachdem, wie viele Variablen und Formeln Sie testen müssen.

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 mithilfe der PMT-Funktion auf eine monatliche Hypothekenzahlung auswirken. 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 mit Datentabellen    

Jedes Mal, wenn ein Arbeitsblatt neu berechnet wird, werden auch alle Datentabellen neu berechnet – selbst wenn sich die Daten nicht geändert haben. Um die Berechnung eines Arbeitsblatts mit einer Datentabelle zu beschleunigen, 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 mit Datentabellen.

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 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 – nach unten oder in einer Zeile ersetzen möchten. Lassen Sie auf beiden Seiten der Werte einige leere Zeilen und Spalten frei.

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

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

   • Wenn die Datentabelle zeilenorientiert ist (die Variablenwerte stehen in einer Zeile), geben Sie die Formel in die Zelle ein, die eine Spalte links vom ersten Wert und eine Zeile unterhalb der Wertezeile liegt.
     
     Wenn Sie die Auswirkungen verschiedener Werte auf andere Formeln untersuchen möchten, geben Sie die zusätzlichen Formeln in die Zellen unterhalb 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. Wählen Sie auf der Registerkarte Daten die Option Was-wäre-wenn-Analyse > Datentabelle aus (in der Gruppe Datentools oder 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 Abbildung oben ist die Eingabezelle B3.

   • Wenn die Datentabelle zeilenorientiert ist, geben Sie den Zellbezug für die Eingabezelle im 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 eine leere Zelle rechts von 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 Daten die Option Was-wäre-wenn-Analyse > Datentabelle aus (in der Gruppe Datentools oder 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 des Arbeitsblatts die Formel ein, die sich auf die beiden Eingabezellen bezieht.

   Im folgenden Beispiel, in dem die Startwerte der Formel in die Zellen B3, B4 und B5 eingegeben wurden, 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 daneben 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. 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 der Gruppe Prognose von Excel 2016).

6. Geben Sie im Feld Werte aus Zeile den Bezug 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 Werte aus Eingabespalte den Bezug auf die Eingabezelle für die Eingabewerte in der Spalte ein.
   Geben Sie B3 in das Feld Spalte für Eingabezelle 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 folgenden 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 bei einer Neuberechnung der gesamten Arbeitsmappe keine Berechnungen für Datentabellen durchgeführt. 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: Optional können Sie auf der Registerkarte Formeln den Pfeil bei Berechnungsoptionen auswählen und dann Automatisch anklicken.

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

Zielwertsuche

Wenn Sie das Ergebnis kennen, das Sie mit einer Formel erzielen möchten, aber nicht genau wissen, welchen Eingabewert die Formel dafür benötigt, verwenden Sie die Zielwertsuche. Weitere Informationen: Verwenden der Zielwertsuche zum Ermitteln des gewünschten Ergebnisses durch Anpassen eines Eingabewerts.

Excel-Löser

Sie können das Excel-Löser-Add-In verwenden, um den optimalen Wert für eine Reihe von Eingabevariablen zu ermitteln. Der Löser arbeitet mit einer Gruppe von Zellen (den sogenannten veränderbaren Zellen oder einfach Variablenzellen), die zur Berechnung der Formeln in den Ziel- und Nebenbedingungszellen 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 mit dem Löser.