Office: Berechnen von mehreren Ergebnissen mit einer Datentabelle

Berechnen von mehreren Ergebnissen mit einer Datentabelle
Excel für Microsoft 365 Excel für das Web Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Mehr... Weniger Bei einer Datentabelle handelt es sich um einen Zellbereich, in dem Sie Werte in einigen Zellen ändern und verschiedene Antworten auf ein Problem finden können. Ein gutes Beispiel für eine Datentabelle ist die PMT -Funktion mit unterschiedlichen Darlehens Beträgen und Zinssätzen zur Berechnung des günstigen Betrags für ein Hypothekendarlehen. Das Experimentieren mit unterschiedlichen Werten, um die entsprechende Variation in den Ergebnissen zu beobachten, ist eine häufige Aufgabe in der Datenanalyse.

Windows Web  Übersicht
In Microsoft Excel sind Datentabellen Teil einer Reihe von Befehlen, die What-If Analysetools bekannt sind. Wenn Sie DatentabellenErstellen und analysieren, führen Sie die was-wäre-wenn-Analyse aus.

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 beispielsweise eine Datentabelle verwenden, um den Zinssatz und die Laufzeit eines Darlehens zu variieren, um mögliche monatliche Zahlungsbeträge auszuwerten.

Hinweis: Sie können schnellere Berechnungen mit Datentabellen und Visual Basic for Applications (VBA) durchführen. Weitere Informationen finden Sie unter Excel What-If Datentabellen: Schnellere Berechnung mit VBA.

Arten der was-wäre-wenn-Analyse    

Es gibt drei Arten von Tools für die was-wäre-wenn-Analyse in Excel: Szenarien, Datentabellenund Ziel Suche. Szenarien und Datentabellen verwenden Sätze von Eingabewerten, um mögliche Ergebnisse zu berechnen. Ziel-Seek ist deutlich anders, es wird ein einzelnes Ergebnis verwendet, und es werden mögliche Eingabewerte berechnet, die dieses Ergebnis hervorrufen.

Wie Szenarien helfen Ihnen auch Datentabellen, eine Reihe möglicher Ergebnisse zu untersuchen. Im Gegensatz zu Szenarien werden in Datentabellen alle Ergebnisse in einer Tabelle auf einem Arbeitsblatt angezeigt. 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 (eine für die Zeileneingabezelle und eine für die Spalteneingabezelle) limitiert ist, kann eine Datentabelle so viele verschiedene Variablenwerte wie gewünscht umfassen. 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.

Grundlagen von Datentabellen
Erstellen Sie je nach Anzahl der Variablen und Formeln, die Sie testen müssen, entweder eine Variable oder zwei Variable 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. So können Sie beispielsweise eine Datentabelle mit einer Variablen verwenden, um zu sehen, wie sich unterschiedliche Zinssätze auf eine monatliche Hypothekenzahlung auswirken, indem Sie die PMT-Funktionverwenden. 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, B3 und B4, verwendet.




 

Berechnungen von Datentabellen    

Jedes Mal, wenn ein Arbeitsblatt neu berechnet wird, werden alle Datentabellen auch neu berechnet – auch wenn die Daten nicht geändert wurden. Wenn Sie die Berechnung eines Arbeitsblatts mit einer Datentabelle beschleunigen möchten, können Sie die Berechnungs Optionen so ändern, dass das Arbeitsblatt automatisch neu berechnet wird, nicht aber die Datentabellen. Weitere Informationen finden Sie im Abschnitt beschleunigen der Berechnung in einem Arbeitsblatt mit Datentabellen.

Erstellen einer Datentabelle mit einer Variablen
Eine Datentabelle mit einer Variablen enthält die 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 in der Eingabezelle ersetzt werden sollen – entweder nach unten eine Spalte oder in einer Zeile. Belassen 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 (Ihre Variablenwerte befinden sich in einer Spalte), geben Sie die Formel in die Zelle eine Zeile darüber und eine Zelle rechts neben der Spalte mit den 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 (Ihre Variablenwerte befinden sich in einer Zeile), geben Sie die Formel in der Zelle eine Spalte links vom ersten Wert und eine Zelle unter der Wertezeile ein.
     
     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. Klicken Sie auf der Registerkarte Daten auf Was-wäre-wenn-Analyse #b0 Datentabelle (in der Gruppe Daten Tools oder Prognose Gruppe Excel 2016 ). 
   
   
5. Führen Sie eine der folgenden Aktionen aus:
   
   • Wenn die Datentabelle spaltenorientiert ist, geben Sie die Zellbezug für die Eingabezelle in das Feld für die Spalten Eingabe ein. In der obigen Abbildung 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 werden die Ergebniszellen als Währung formatiert.
     
     

Hinzufügen einer Formel zu einer Datentabelle mit einer Variablen
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 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-wenn-Analyse > Datentabelle (in der Gruppe Daten Tools oder Prognose Gruppe 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 Spalte ein .
     
     
   • Wenn die Datentabelle zeilenorientiert ist, geben Sie den Zellbezug für die Eingabezelle in das Feld Zeileneingabezelle ein.
     
     

Erstellen einer Datentabelle mit zwei Variablen
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 Anfangswerte der Formel 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 – rechts.
   
   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 in der Gruppe Prognose (in Excel 2016 ) auf Was-wäre-wenn-Analyse >Datentabelle (in der Gruppe Datentools oder Prognose Gruppe 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. Klicken Sie auf OK.
   
   

Beispiel einer 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, B3 und B4, verwendet.





Beschleunigen der Berechnung auf einem Arbeitsblatt mit Datentabellen
Wenn Sie diese Berechnungsoption setzen, treten keine Datentabellenberechnungen auf, 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.

Gehen Sie wie folgt vor, um die Berechnungsleistung zu verbessern:

1. Klicken Sie auf Datei > Optionen > Formeln.
   
   
2. Klicken Sie im Abschnitt Berechnungsoptionen unter berechnenauf automatisch, außer bei Datentabellen.
   
   Tipp: Optional können Sie auf der Registerkarte Formeln auf den Pfeil unter Berechnungsoptionenund dann auf automatisch mit Ausnahme von Datentabellen (in der Gruppe Berechnung ) klicken.
   
   

Wie geht es weiter?
Sie können einige andere Excel-Tools verwenden, um die was-wäre-wenn-Analyse durchzuführen, wenn Sie über bestimmte Ziele oder größere Sätze von Variablen Daten verfügen.

Zielwertsuche

Wenn Sie das Ergebnis kennen, das von einer Formel erwartet wird, aber nicht genau wissen, welchen Eingabewert die Formel zum Abrufen dieses Ergebnisses benötigt, verwenden Sie das Goal-Seek-Feature. Lesen Sie den Artikel Verwenden der Ziel Suche zum Ermitteln des gewünschten Ergebnisses durch Anpassen eines Eingabewerts.

Excel-Solver

Sie können das Excel Solver-Add-in verwenden, um den optimalen Wert für einen Satz von Eingabevariablen zu finden. Solver arbeitet mit einer Gruppe von Zellen (sogenannte Entscheidungsvariablen oder einfach variablenzellen), die beim 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.

Wenn Sie unterschiedliche Zahlen an eine Zelle anschließen, können Sie schnell verschiedene Antworten auf ein Problem finden. Ein hervorragendes Beispiel ist die Verwendung der PMT -Funktion mit unterschiedlichen Zins-und Darlehens Zeiten (in Monaten), um herauszufinden, wie viel eines Darlehens Sie für ein Haus oder ein Auto leisten können. Sie geben Ihre Zahlen in einen Zellbereich ein, der als Datentabelle bezeichnet wird.

Hier ist die Datentabelle der Zellbereich B2: D8. Sie können den Wert in B4, den Darlehensbetrag und die monatlichen Zahlungen in Spalte D automatisch aktualisieren ändern. Mit einem Zinssatz von 3,75% gibt D2 eine monatliche Zahlung von $1.042,01 mit dieser Formel zurück: = PMT (C2/12, $B $3, $B $4).





Je nach Anzahl der Variablen und Formeln, die Sie testen möchten, können Sie eine oder zwei Variablen verwenden.

Verwenden Sie einen Test mit einer Variablen, um zu sehen, wie sich die Ergebnisse durch unterschiedliche Werte einer Variablen in einer Formel ändern. So können Sie beispielsweise den Zinssatz für eine monatliche Hypothekenzahlung mithilfe der PMT-Funktion ändern. Sie geben die Variablenwerte (die Zinssätze) in einer Spalte oder Zeile ein, und die Ergebnisse werden in einer benachbarten Spalte oder Zeile angezeigt.

In dieser Live Arbeitsmappe enthält Zelle D2 die Zahlungsformel =PMT (C2/12, $B $3, $B $4). Zelle B3 ist die Variable Zelle, in der Sie eine andere Ausdruckslänge (Anzahl der monatlichen Zahlungsperioden) anschließen können. In Zelle D2 verdübelt die Pmt-Funktion den Zinssatz 3,75% 12, 360 Monate und ein $225.000-Darlehen und berechnet eine monatliche $1.042,01-Zahlung.

Verwenden Sie einen Test mit zwei Variablen, um zu sehen, wie sich die Ergebnisse durch unterschiedliche Werte von zwei Variablen in einer Formel ändern. So können Sie beispielsweise verschiedene Kombinationen von Zinssätzen und Anzahl der monatlichen Zahlungsperioden testen, um eine Hypothekenzahlung zu berechnen.

In dieser Live-Arbeitsmappe enthält Zelle C3 die Zahlungsformel =PMT ($B $3/12, $B $2, B4), die zwei Variable Zellen, B2 und B3, verwendet. In Zelle C2 verdübelt die Pmt-Funktion den Zinssatz 3.875% 12, 360 Monate und ein $225.000-Darlehen und berechnet eine monatliche $1.058,03-Zahlung.

Benötigen Sie weitere Hilfe?
Sie können jederzeit einen Experten in der Excel Tech Community fragen, Unterstützung in der Answers Community erhalten oder aber ein neues Feature oder eine Verbesserung auf Excel User Voice vorschlagen.

:)