Excel wertetabelle

Was ist eine Datentabelle in Excel?

In Excel ist eine Datentabelle eines die 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 Veränderlichen abhängt.

In Excel gibt es zwei Gärten von Datentabellen:

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

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

Spaltenorientierte Datentabelle

Eine spaltenorientierte Datentabelle ist nützlich, wenn Sie testen wünschen, wie sich unterschiedliche Werte einer einzelnen Variablen an die Ausgabe einer Formel auswirken, wobei die Variablenwerte in einer Spalte aufgelistet werden. Betrachten wir einer 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 wünschen. Um zu beurteilen, welche monatliche Zahlung auf die Grundlage Ihres Gehalts erschwinglich wäre, möchten Sie untersuchen, wie sich verschiedene Zinssätze auf den Betrag bewirken würden, den Sie jeden Monat zahlen müssen.

Schritt 1: Richten Sie die Grundformel ein

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

Schritt 2: Listen Sie das 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 selbst ändern sollen, wenn Sie den Zinssatz variieren.

2. Wählen Siehe den Bereich aus, der Ihre Formel, die Liste der Zinssätze und die angrenzenden Zellen für das Ergebnisse enthält (wählen Sie z. B. D2 bis E10). Siehe Screenshot:
   
3. Gehen Sie zum Menüband und klicken Sie auf Daten-Management Registerkarte klicken Sie dann auf Was, wenn die Analyse > Datentabelle, siehe Screenshot:
   Datentabelle"/>
4. Im Datentabelle Klicken Sie im Dialogfeld an Spalte Eingabezelle Feld (da wir eine Spalte für Eingabewerte verwenden) und wählen Sie die Variablenzelle weg, 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 Knopf, siehe Screenshot:
   
5. Excel füllt die leeren Zellen neben jedem Ihrer Variablenwerte (verschiedene Zinssätze) automatisch mit den entsprechend Ergebnissen. Siehe Screenshot:
   
6. Wenden Sie das gewünschte Zahlenformat an die Ergebnisse (Währung) entsprechend Ihrem Bedarf an. Nun ist die spaltenorientierte Datentabelle erfolgreich erstellt und Siehe können die Ergebnisse schnell durchsehen, um zu bewerten, welche monatlichen Zahlungsbeträge für Sie erschwinglich wären, wenn sich der Zinssatz ändert. Siehe Screenshot:
   

Zeilenorientierte Datentabelle

Beim Herstellen einer zeilenorientierten Datentabelle in Excel müssen Sie Ihren 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 das Erstellung einer zeilenorientierten Datentabelle in Excel abzuschließen.

Schritt 1: Listen Sie die Zinssätze hintereinander auf

Platzieren Sie das Variablenwerte (Zinssätze) in einer Zeile und stellen Siehe 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: Herstellen Sie die Datentabelle

1. Geben Sie in Zelle A9 diese Formel ein: = B4.
   
2. Wählen Sie den Bereich weg, der Ihre Formel, die Liste der Zinssätze und angrenzende Zellen für die Ergebnisse enthält (z. B. A8 bis I9). Dann klick Daten-Management > Was, wenn die Analyse > Datentabelle.
3. Im Datentabelle Klicken Siehe im Dialogfeld auf Zeileneingabezelle Feld (da wir eine Zeile für Eingabewerte verwenden) und wählen Sie das Variablenzelle aus, auf die in Ihrer Formel verwiesen wird. In diesem Beispiel wählen wir B1 weg, das den Zinssatz enthält. Zum Schluss klicken Siehe 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 Siehe in Excel eine Datentabelle mit einer Variablen und mehreren Formeln erstellen, können Sie sehen, wie selbst 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: Hinzufügen Sie eine neue Formel zur Berechnung des Gesamtzinses hinzu

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

Schritt 2: Zuweisen Sie die Quelldaten der Datentabelle an

Listen Sie in einer Spalte die verschiedenen Zinssätze auf, die Siehe 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, das Liste der Zinssätze und die angrenzenden Zellen für das Ergebnis enthält (wählen Sie z. B. D2 bis F10). Dann klick Daten-Management > 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 an 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 Veränderlichen

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 Form 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 mittels 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 an 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 Siehe in einer Spalte die verschiedenen Zinssätze auf, das 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 Siehe die Datentabelle

1. Platzieren Sie Ihre Formel am Schnittpunkt die Zeile und der Spalte, in der Sie das Variablenwerte aufgelistet haben. In diesem Fall gebe du diese Formel ein: = B4 in Zelle E2. Siehe Screenshot:
   
2. Wählen Sie den Bereich aus, die Ihre Kreditbeträge, Zinssätze, die Formelzelle und die Zellen umfasst, in denen die Ergebnisse angezeigt werden.
   
3. Dann klick Daten-Management > Was, wenn die Analyse > Datentabelle. Im Dialogfeld „Datentabelle“:
   • Im Zeileneingabezelle Wählen Sie im Feld „B3“ den Zellbezug zur Eingabezelle für das Variablenwerte in der Zeile aus (in diesem Beispiel enthält BXNUMX den Kreditbetrag).
   • Im SpalteneingabezelleWählen Sie im Acker 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 siehe 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 die Datentabelle, der Änderung ihrer Ergebnisse und der Durchführung manueller Neuberechnungen.

Löschen Sie eine Datentabelle

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

Wählen Siehe 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 Siehe normalerweise die vorhandene Datentabelle löschen und dann eine neue mit den gewünschten Änderungen erstellen. Dazu mühelen Sie Ihre Basisformel oder die Eingabewerte anpassen und anschließend die Datentabelle erneut einrichten, um diese Veränderungen widerzuspiegeln.

Datentabelle manuell neu berechnen

Normalerweise berechnet Excel bei jed Ä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 Umstellung in der Arbeitsmappe automatisch Berechnungen für alle Datenblätter durchführt, können Sie den Berechnungsmodus von „Automatisch“ an „Manuell“ umstellen. Bitte gehen Sie wie folgt vor:

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

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

Wenn Sie Ihre Datentabelle handbetrieben aktualisieren müssen, wählen Sie einfach die Zellen weg, in denen die Ergebnisse angezeigt werden (Zellen mittels den TABLE()-Formeln), und drücken Sie dann F9 Taste halten.

Indem Sie die in diesem Leitfaden beschriebenen Schritte befolgen und die wichtigsten Punkte im Hinterkopf beibehalten, 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 unser darauf, Ihnen in Zukunft weitere hilfreiche Informationen zur Verfügung zu stellen!

Beste Office-Produktivitätstools

Verbessern Sie Ihre Excel-Kenntnisse mittels Kutools für Excel und erleben Sie Effizienz wie nie zuvor. Kutools für Excel bietet über 300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen.  Klicken Sie hier, um das Funktion zu erhalten, die Sie am meisten benötigen ...

Weitere Details zu Kutools für Excel ...Kostenfreier Download...

Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich

• Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
• Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten gleichen Fensters und nicht in neuen Fenstern.
• Steigert Ihre Effizienz um 50 % und reduziert jeden Tag Tausende von Mausklicks für Sie!