Wenn Sie über eine komplexe Formel verfügen, die von mehreren Variablen abhängt, und verstehen möchten, wie sich die Änderung dieser Eingaben effizient auf die Ergebnisse auswirkt, ist eine Was-wäre-wenn-Analysedatentabelle in Excel ein leistungsstarkes Werkzeug. So können Sie alle möglichen Ergebnisse auf einen Blick erkennen. Hier finden Sie eine Schritt-für-Schritt-Anleitung zum Erstellen einer Datentabelle in Excel. Darüber hinaus besprechen wir einige wichtige Punkte für die effektive Nutzung von Datentabellen und demonstrieren andere Vorgänge wie das Löschen, Bearbeiten und Neuberechnen von Datentabellen.

Was ist eine Datentabelle in Excel?

Erstellen Sie eine Datentabelle mit einer Variablen

• Spaltenorientierte Datentabelle
• Zeilenorientierte Datentabelle
• Mehrere Formeln für eine Datentabelle mit einer Variablen

Erstellen Sie eine Datentabelle mit zwei Variablen

Wichtige Punkte anhand der Datentabellen

Andere Operationen zur Verwendung von Datentabellen

• Löschen Sie eine Datentabelle
• Ergebnis der Datentabelle bearbeiten
• Datentabelle manuell neu berechnen

Was ist eine Datentabelle in Excel?

In Excel ist eine Datentabelle eines der Was-wäre-wenn-Analysetools, mit denen Sie mit verschiedenen Eingabewerten für Formeln experimentieren und die Änderungen in der Formelausgabe beobachten können. Dieses Tool ist äußerst nützlich für die Untersuchung verschiedener Szenarien und die Durchführung von Sensitivitätsanalysen, insbesondere wenn eine Formel von mehreren Variablen abhängt.

In Excel gibt es zwei Arten von Datentabellen:

Datentabelle mit einer Variablen: Damit können Sie analysieren, wie sich unterschiedliche Werte einer Variablen auf eine Formel auswirken. Sie können eine Datentabelle mit einer Variablen entweder in einem zeilenorientierten oder spaltenorientierten Format einrichten, je nachdem, ob Sie Ihre Eingabe über eine Zeile oder eine Spalte nach unten variieren möchten.

Datentabelle mit zwei Variablen: Mit diesem Typ können Sie die Auswirkungen der Änderung zweier verschiedener Variablen auf das Ergebnis einer Formel sehen. In einer Datentabelle mit zwei Variablen variieren Sie die Werte entlang einer Zeile und einer Spalte.

Erstellen Sie eine Datentabelle mit einer Variablen

Das Erstellen einer Datentabelle mit einer Variablen in Excel ist eine wertvolle Fähigkeit zur Analyse, wie sich Änderungen an einer einzelnen Eingabe auf verschiedene Ergebnisse auswirken können. Dieser Abschnitt führt Sie durch den Prozess der Erstellung von spaltenorientierten, zeilenorientierten und Mehrformel-Datentabellen mit einer Variablen.

Toll! Verwenden Sie effiziente Tabs in Excel wie Chrome, Edge, Firefox und Safari!
Sparen Sie 50% Ihrer Zeit und reduzieren Sie jeden Tag Tausende von Mausklicks für Sie!

---

Spaltenorientierte Datentabelle

Eine spaltenorientierte Datentabelle ist nützlich, wenn Sie testen möchten, wie sich unterschiedliche Werte einer einzelnen Variablen auf die Ausgabe einer Formel auswirken, wobei die Variablenwerte in einer Spalte aufgelistet werden. Betrachten wir ein einfaches Finanzbeispiel:

Angenommen, Sie erwägen einen Kredit in Höhe von 50,000 US-Dollar, den Sie über einen Zeitraum von 3 Jahren (entspricht 36 Monaten) zurückzahlen möchten. Um zu beurteilen, welche monatliche Zahlung auf der Grundlage Ihres Gehalts erschwinglich wäre, möchten Sie untersuchen, wie sich verschiedene Zinssätze auf den Betrag auswirken würden, den Sie jeden Monat zahlen müssen.

Schritt 1: Richten Sie die Grundformel ein

Um die Zahlung zu berechnen, stelle ich hier den Zinssatz auf 5 % ein. Geben Sie in Zelle B4 die PMT-Formel ein. Sie berechnet die monatliche Zahlung basierend auf dem Zinssatz, der Anzahl der Perioden und dem Kreditbetrag. Siehe Screenshot:

= -PMT($B$1/12, $B$2*12, $B$3)

Schritt 2: Listen Sie die Zinssätze in einer Spalte auf

Listen Sie in einer Spalte die verschiedenen Zinssätze auf, die Sie testen möchten. Listen Sie beispielsweise Werte von 4 % bis 11 % in Schritten von 1 % in einer Spalte auf und lassen Sie rechts mindestens eine leere Spalte für die Ergebnisse. Siehe Screenshot:

Schritt 3: Erstellen Sie die Datentabelle

1. Geben Sie in Zelle E2 diese Formel ein: = B4.
   
   Note: B4 ist die Zelle, in der sich Ihre Hauptformel befindet. Dies ist die Formel, deren Ergebnisse sich ändern sollen, wenn Sie den Zinssatz variieren.
   
2. Wählen Sie den Bereich aus, der Ihre Formel, die Liste der Zinssätze und die angrenzenden Zellen für die Ergebnisse enthält (wählen Sie z. B. D2 bis E10). Siehe Screenshot:
   
3. Gehen Sie zum Menüband und klicken Sie auf Datum Registerkarte klicken Sie dann auf Was, wenn die Analyse > Datentabelle, siehe Screenshot:
   
4. Im Datentabelle Klicken Sie im Dialogfeld auf Spalte Eingabezelle Feld (da wir eine Spalte für Eingabewerte verwenden) und wählen Sie die Variablenzelle aus, auf die in Ihrer Formel verwiesen wird. In diesem Beispiel wählen wir B1 aus, das den Zinssatz enthält. Zum Schluss klicken Sie OK Schaltfläche, siehe Screenshot:
   
5. Excel füllt die leeren Zellen neben jedem Ihrer Variablenwerte (verschiedene Zinssätze) automatisch mit den entsprechenden Ergebnissen. Siehe Screenshot:
   
6. Wenden Sie das gewünschte Zahlenformat auf die Ergebnisse (Währung) entsprechend Ihrem Bedarf an. Nun ist die spaltenorientierte Datentabelle erfolgreich erstellt und Sie können die Ergebnisse schnell durchsehen, um zu beurteilen, welche monatlichen Zahlungsbeträge für Sie erschwinglich wären, wenn sich der Zinssatz ändert. Siehe Screenshot:
   

Zeilenorientierte Datentabelle

Beim Erstellen einer zeilenorientierten Datentabelle in Excel müssen Sie Ihre Daten so anordnen, dass die Variablenwerte in einer Zeile und nicht in einer Spalte aufgelistet werden. Nehmen wir das obige Beispiel als Referenz und fahren wir mit den Schritten fort, um die Erstellung einer zeilenorientierten Datentabelle in Excel abzuschließen.

Schritt 1: Listen Sie die Zinssätze hintereinander auf

Platzieren Sie die Variablenwerte (Zinssätze) in einer Zeile und stellen Sie sicher, dass links mindestens eine leere Spalte für die Formel und eine leere Zeile darunter für die Ergebnisse vorhanden ist, siehe Screenshot:

Schritt 2: Erstellen Sie die Datentabelle

1. Geben Sie in Zelle A9 diese Formel ein: = B4.
   
2. Wählen Sie den Bereich aus, der Ihre Formel, die Liste der Zinssätze und angrenzende Zellen für die Ergebnisse enthält (z. B. A8 bis I9). Dann klick Datum > Was, wenn die Analyse > Datentabelle.
3. Im Datentabelle Klicken Sie im Dialogfeld auf Zeileneingabezelle Feld (da wir eine Zeile für Eingabewerte verwenden) und wählen Sie die Variablenzelle aus, auf die in Ihrer Formel verwiesen wird. In diesem Beispiel wählen wir B1 aus, das den Zinssatz enthält. Zum Schluss klicken Sie OK Schaltfläche, siehe Screenshot:
   
4. Wenden Sie das gewünschte Zahlenformat auf die Ergebnisse (Währung) entsprechend Ihrem Bedarf an. Nun wird die zeilenorientierte Datentabelle erstellt, siehe Screenshot:
   

Mehrere Formeln für eine Datentabelle mit einer Variablen

Wenn Sie in Excel eine Datentabelle mit einer Variablen und mehreren Formeln erstellen, können Sie sehen, wie sich die Änderung einer einzelnen Eingabe auf mehrere verschiedene Formeln gleichzeitig auswirkt. Was wäre, wenn Sie im obigen Beispiel die Änderung der Zinssätze sowohl für die Rückzahlungen als auch für die Gesamtzinsen sehen möchten? So richten Sie es ein:

Schritt 1: Fügen Sie eine neue Formel zur Berechnung des Gesamtzinses hinzu

Geben Sie in Zelle B5 die folgende Formel ein, um den Gesamtzins zu berechnen:

=B4*B2*12-B3

Schritt 2: Ordnen Sie die Quelldaten der Datentabelle an

Listen Sie in einer Spalte die verschiedenen Zinssätze auf, die Sie testen möchten, und lassen Sie rechts mindestens zwei leere Spalten für die Ergebnisse. Siehe Screenshot:

Schritt 3: Erstellen Sie die Datentabelle:

1. Geben Sie in Zelle E2 diese Formel ein: = B4 um einen Bezug zur Tilgungsberechnung in den Originaldaten herzustellen.
2. Geben Sie in Zelle F2 diese Formel ein: = B5 um einen Bezug zum Gesamtinteresse an den Originaldaten herzustellen.
   
3. Wählen Sie den Bereich aus, der Ihre Formeln, die Liste der Zinssätze und die angrenzenden Zellen für das Ergebnis enthält (wählen Sie z. B. D2 bis F10). Dann klick Datum > Was, wenn die Analyse > Datentabelle.
4. Im Datentabelle Klicken Sie im Dialogfeld auf Spalteneingabezelle Feld (da wir eine Spalte für Eingabewerte verwenden) und wählen Sie die Variablenzelle aus, auf die in Ihrer Formel verwiesen wird. In diesem Beispiel wählen wir B1 aus, das den Zinssatz enthält. Zum Schluss klicken Sie OK Schaltfläche, siehe Screenshot:
   
5. Wenden Sie das gewünschte Zahlenformat auf die Ergebnisse (Währung) entsprechend Ihrem Bedarf an. Und Sie können die Ergebnisse für jede Formel basierend auf den unterschiedlichen Variablenwerten sehen.
   

Erstellen Sie eine Datentabelle mit zwei Variablen

Eine Datentabelle mit zwei Variablen in Excel zeigt die Auswirkungen verschiedener Kombinationen von zwei Sätzen von Variablenwerten auf das Ergebnis einer Formel und zeigt, wie sich Änderungen an zwei Eingaben einer Formel gleichzeitig auf das Ergebnis auswirken.

Hier habe ich eine einfache Skizze gezeichnet, um Ihnen zu helfen, das Aussehen und die Struktur einer Datentabelle mit zwei Variablen besser zu verstehen.

Aufbauend auf dem Beispiel zum Erstellen einer Datentabelle mit einer Variablen lernen wir nun, wie man in Excel eine Datentabelle mit zwei Variablen erstellt.

Im folgenden Datensatz haben wir den Zinssatz, die Kreditlaufzeit und den Kreditbetrag angegeben und die monatliche Zahlung mithilfe dieser Formel berechnet: =-PMT($B$1/12, $B$2*12, $B$3) sowie. Hier konzentrieren wir uns auf zwei Schlüsselvariablen aus unseren Daten: Zinssatz und Kreditbetrag, um zu beobachten, wie sich Änderungen dieser beiden Faktoren gleichzeitig auf die Rückzahlungsbeträge auswirken.

Schritt 1: Richten Sie die beiden sich ändernden Variablen ein

1. Listen Sie in einer Spalte die verschiedenen Zinssätze auf, die Sie testen möchten. siehe Screenshot:
   
2. Geben Sie in einer Zeile die verschiedenen Kreditbetragswerte direkt über den Spaltenwerten ein (beginnend bei einer Zelle rechts von der Formelzelle), siehe Screenshot:
   

Schritt 2: Erstellen Sie die Datentabelle

1. Platzieren Sie Ihre Formel am Schnittpunkt der Zeile und der Spalte, in der Sie die Variablenwerte aufgelistet haben. In diesem Fall gebe ich diese Formel ein: = B4 in Zelle E2. Siehe Screenshot:
   
2. Wählen Sie den Bereich aus, der Ihre Kreditbeträge, Zinssätze, die Formelzelle und die Zellen umfasst, in denen die Ergebnisse angezeigt werden.
   
3. Dann klick Datum > Was, wenn die Analyse > Datentabelle. Im Dialogfeld „Datentabelle“:
   • Im Zeileneingabezelle Wählen Sie im Feld „B3“ den Zellbezug zur Eingabezelle für die Variablenwerte in der Zeile aus (in diesem Beispiel enthält BXNUMX den Kreditbetrag).
   • Im SpalteneingabezelleWählen Sie im Feld l den Zellbezug zur Eingabezelle für die Variablenwerte in der Spalte aus (B1 enthält den Zinssatz).
   • Und dann klicken OK Taste.
     
4. Nun füllt Excel die Datentabelle mit den Ergebnissen für jede Kombination aus Kreditbetrag und Zinssatz aus. Es bietet einen direkten Überblick darüber, wie sich unterschiedliche Kombinationen von Kreditbeträgen und Zinssätzen auf die monatliche Zahlung auswirken, und ist somit ein wertvolles Werkzeug für die Finanzplanung und -analyse.
   
5. Abschließend sollten Sie je nach Bedarf das gewünschte Zahlenformat auf die Ergebnisse (Währung) anwenden.
   

Wichtige Punkte anhand der Datentabellen

• Die neu erstellte Datentabelle muss sich im selben Arbeitsblatt wie die Originaldaten befinden.
• Die Ausgabe der Datentabelle hängt von der Formelzelle im Quelldatensatz ab und alle Änderungen an dieser Formelzelle führen automatisch zu einer Aktualisierung der Ausgabe.
• Sobald Werte mithilfe der Datentabelle berechnet wurden, können sie nicht mehr rückgängig gemacht werden Strg + Z. Sie können jedoch alle Werte manuell auswählen und löschen.
• Die Datentabelle generiert Arrayformeln, sodass einzelne Zellen in der Tabelle nicht geändert oder gelöscht werden können.

Andere Operationen zur Verwendung von Datentabellen

Sobald Sie eine Datentabelle erstellt haben, sind möglicherweise zusätzliche Vorgänge erforderlich, um sie effektiv zu verwalten, einschließlich des Löschens der Datentabelle, der Änderung ihrer Ergebnisse und der Durchführung manueller Neuberechnungen.

Löschen Sie eine Datentabelle

Da die Ergebnisse der Datentabelle mit einer Array-Formel berechnet werden, können Sie keinen einzelnen Wert aus der Datentabelle löschen. Sie können jedoch nur die gesamte Datentabelle löschen.

Wählen Sie alle Zellen der Datentabelle oder nur die Zellen mit den Ergebnissen aus und drücken Sie dann Löschen Taste auf der Tastatur.

Ergebnis der Datentabelle bearbeiten

Tatsächlich ist es nicht möglich, einzelne Zellen innerhalb einer Datentabelle direkt zu bearbeiten, da sie Arrayformeln enthalten, die Excel automatisch generiert.

Um Änderungen vorzunehmen, müssen Sie normalerweise die vorhandene Datentabelle löschen und dann eine neue mit den gewünschten Änderungen erstellen. Dazu müssen Sie Ihre Basisformel oder die Eingabewerte anpassen und anschließend die Datentabelle erneut einrichten, um diese Änderungen widerzuspiegeln.

Datentabelle manuell neu berechnen

Normalerweise berechnet Excel bei jeder Änderung alle Formeln in allen geöffneten Arbeitsmappen neu. Wenn eine große Datentabelle mit zahlreichen Variablenwerten und komplexen Formeln dazu führt, dass Ihre Excel-Arbeitsmappe langsamer wird.

Um zu vermeiden, dass Excel bei jeder Änderung in der Arbeitsmappe automatisch Berechnungen für alle Datentabellen durchführt, können Sie den Berechnungsmodus von „Automatisch“ auf „Manuell“ umstellen. Bitte gehen Sie wie folgt vor:

Gehen Sie zum Formeln Und klicken Sie dann auf Berechnungsoptionen > Automatisch, außer bei Datentabellen, siehe Screenshot:

Sobald Sie diese Einstellung ändern und Ihre gesamte Arbeitsmappe neu berechnen, aktualisiert Excel die Berechnungen in Ihren Datentabellen nicht mehr automatisch.

Wenn Sie Ihre Datentabelle manuell aktualisieren müssen, wählen Sie einfach die Zellen aus, in denen die Ergebnisse angezeigt werden (Zellen mit den TABLE()-Formeln), und drücken Sie dann F9 Key.

Indem Sie die in diesem Leitfaden beschriebenen Schritte befolgen und die wichtigsten Punkte im Hinterkopf behalten, können Sie Datentabellen effizient für Ihre Datenanalyseanforderungen nutzen. Wenn Sie mehr über Excel-Tipps und -Tricks erfahren möchten, finden Sie auf unserer Website Tausende von Tutorials. Bitte klicken Sie hier, um darauf zuzugreifen. Vielen Dank fürs Lesen und wir freuen uns darauf, Ihnen in Zukunft weitere hilfreiche Informationen zur Verfügung zu stellen!