So erstellen und verwenden Sie eine Datentabelle in Excel

Das Tutorial zeigt, wie Datentabellen für die Was-Wäre-Wenn-Analyse in Excel verwendet werden. Erfahren Sie, wie Sie eine Tabelle mit einer Variablen und zwei Variablen erstellen, um die Auswirkungen von ein oder zwei Eingabewerten auf Ihre Formel anzuzeigen, und wie Sie eine Datentabelle einrichten, um mehrere Formeln gleichzeitig auszuwerten.

Sie haben eine komplexe Formel erstellt, die von mehreren Variablen abhängt, und möchten wissen, wie das Ändern dieser Eingaben die Ergebnisse ändert., Anstatt jede Variable einzeln zu testen, erstellen Sie eine Was-Wäre-Wenn-Analyse-Datentabelle und beobachten Sie alle möglichen Ergebnisse mit einem kurzen Blick!

• Datentabelle in Excel
• Erstellen Sie eine variable Datentabelle
• Erstellen Sie zwei variable Datentabelle
• Datentabelle zur Auswertung mehrerer Formeln
• Excel-Datentabellen-3 Dinge, die Sie wissen sollten
• Löschen Sie eine Datentabelle in Excel
• Bearbeiten Sie Datentabelle Ergebnisse
• Berechnen Sie Datentabelle manuell

Was ist eine Datentabelle in Excel?,

In Microsoft Excel ist eine Datentabelle eines der Was-Wäre-Wenn-Analyse-Tools, mit dem Sie verschiedene Eingabewerte für Formeln ausprobieren und sehen können, wie sich Änderungen in diesen Werten auf die Ausgabe von Formeln auswirken.

Datentabellen sind besonders nützlich, wenn eine Formel von mehreren Werten abhängt und Sie mit verschiedenen Kombinationen von Eingaben experimentieren und die Ergebnisse vergleichen möchten.

Derzeit gibt es eine variable Datentabelle und zwei variable Datentabelle. Obwohl auf maximal zwei verschiedene Eingabezellen beschränkt, können Sie mit einer Datentabelle beliebig viele Variablenwerte testen.,

So erstellen Sie eine Datentabelle mit einer Variablen in Excel

Eine Datentabelle mit einer Variablen in Excel ermöglicht das Testen einer Reihe von Werten für eine einzelne Eingabezelle und zeigt, wie diese Werte das Ergebnis einer verwandten Formel beeinflussen.,

Um Ihnen zu helfen, diese Funktion besser zu verstehen, folgen wir einem bestimmten Beispiel, anstatt generische Schritte zu beschreiben.

Angenommen, Sie erwägen, Ihre Ersparnisse bei einer Bank einzuzahlen, die monatlich 5% Zinsen zahlt. Um verschiedene Optionen zu überprüfen, haben Sie den folgenden Zinseszinsrechner erstellt, bei dem:

• B8 die FV-Formel enthält, die den Abschlusssaldo berechnet.
• B2 ist die variable, die Sie testen möchten (Erstinvestition).,

Und jetzt machen wir eine einfache Was-Wäre-Wenn-Analyse, um zu sehen, was Ihre Ersparnisse in 5 Jahren sein werden, abhängig von der Höhe Ihrer Anfangsinvestition, von $1,000 bis $6,000.

Hier sind die Schritte zum Erstellen einer Datentabelle mit einer Variablen:

1. Geben Sie die Variablenwerte entweder in einer Spalte oder in einer Zeile ein. In diesem Beispiel erstellen wir eine spaltenorientierte Datentabelle, geben also unsere Variablenwerte in eine Spalte ein (D3:D8) und belassen für die Ergebnisse mindestens eine leere Spalte rechts.,
2. Geben Sie Ihre Formel in die Zelle eine Zeile oben und eine Zelle rechts neben den Variablenwerten ein (in unserem Fall E2). Oder verknüpfen Sie diese Zelle mit der Formel in Ihrem ursprünglichen Datensatz (wenn Sie die Formel in Zukunft ändern möchten, müssen Sie nur eine Zelle aktualisieren). Wir wählen die letztere option, und geben Sie diese einfache Formel in E2: =B8
   
   Tip., Wenn Sie die Auswirkungen der Variablenwerte auf andere Formeln untersuchen möchten, die sich auf dieselbe Eingabezelle beziehen, geben Sie die zusätzlichen Formeln rechts neben der ersten Formel ein, wie in diesem Beispiel gezeigt.
3. Wählen Sie den Datentabellenbereich aus, einschließlich Ihrer Formel, Zellen mit Variablenwerten und leeren Zellen für die Ergebnisse (D2:E8).,
4. Gehen Sie zur Registerkarte Daten > Data Tools group, klicken Sie auf die Schaltfläche Was-Wäre-Wenn-Analyse und dann auf Data Table…
   
5. Klicken Sie im Dialogfenster Data Table auf das Feld Column Input cell (da sich unsere Investitionswerte in einer Spalte befinden) und wählen Sie die variable Zelle aus, auf die in Ihrer Formel verwiesen wird. In diesem Beispiel wählen wir B3 aus, das den anfänglichen Anlagewert enthält.
6. Klicken Sie auf OK, und Excel füllt sofort die leeren Zellen mit Ergebnissen, die dem Variablenwert in derselben Zeile entsprechen.,
7. Wenden Sie das gewünschte Zahlenformat auf die Ergebnisse an (Währung in unserem Fall), und Sie können loslegen!

Jetzt können Sie einen kurzen Blick auf Ihre Ein-Variablen – Datentabelle werfen, die möglichen Salden untersuchen und die optimale Einzahlungsgröße auswählen:

Zeilenorientierte Datentabelle

Das obige Beispiel zeigt, wie Sie eine vertikale oder spaltenorientierte Datentabelle in Excel einrichten., Wenn Sie ein horizontales Layout bevorzugen, müssen Sie Folgendes tun:

1. Geben Sie die Variablenwerte in eine Zeile ein und lassen Sie mindestens eine leere Spalte links (für die Formel) und eine leere Zeile unten (für die Ergebnisse). In diesem Beispiel geben wir die Variablenwerte in die Zellen F3: J3 ein.
2. Geben Sie die Formel in die Zelle ein, die eine Spalte links von Ihrem ersten Variablenwert und eine Zelle darunter ist (in unserem Fall E4).,
3. Erstellen Sie eine Datentabelle wie oben beschrieben, geben Sie jedoch den Eingabewert (B3) in das Feld Zeileneingabezelle ein:
   
4. Klicken Sie auf OK, und Sie erhalten das folgende Ergebnis:
   

So erstellen Sie eine Datentabelle mit zwei Variablen in Excel

Eine Datentabelle mit zwei Variablen zeigt, wie verschiedene Kombinationen von 2 Sätzen variabler Werte beeinflussen das Formelergebnis. Mit anderen Worten, es zeigt, wie das Ändern von zwei Eingabewerten derselben Formel die Ausgabe ändert.,

Die Schritte zum Erstellen einer Datentabelle mit zwei Variablen in Excel sind grundsätzlich dieselben wie im obigen Beispiel, mit der Ausnahme, dass Sie zwei Bereiche möglicher Eingabewerte eingeben, einen in einer Zeile und einen in einer Spalte.

Um zu sehen, wie es funktioniert, verwenden wir den gleichen Zinseszinsrechner und untersuchen die Auswirkungen der Größe der Anfangsinvestition und der Anzahl der Jahre auf den Saldo. Richten Sie dazu Ihre Datentabelle folgendermaßen ein:

1. Geben Sie Ihre Formel in eine leere Zelle ein oder verknüpfen Sie diese Zelle mit Ihrer ursprünglichen Formel., Stellen Sie sicher, dass Sie rechts und unten genügend leere Spalten und leere Zeilen haben, um Ihre Variablenwerte aufzunehmen. Nach wie vor verknüpfen wir die Zelle E2 mit der ursprünglichen FV-Formel, die den Saldo berechnet: =B8
2. Geben Sie einen Satz von Eingabewerten unterhalb der Formel in dieselbe Spalte ein (Investitionswerte in E3:E8).
3. Geben Sie den anderen Satz variabler Werte rechts neben der Formel in derselben Zeile ein (Anzahl der Jahre in F2:H2).,

   Zu diesem Zeitpunkt sollten Ihre beiden Variablendatentabellen wie folgt aussehen:
   

4. Wählen Sie den gesamten Datentabellenbereich aus, einschließlich der Formel, der Zeile und Spalte der Variablenwerte sowie der Zellen, in denen die berechneten Werte angezeigt werden. Wir wählen den Bereich E2:H8.,
5. Erstellen Sie eine Datentabelle auf die bereits bekannte Weise: Registerkarte Daten > Was-wäre-wenn-Analyse-Schaltfläche > Datentabelle…
6. Geben Sie im Feld Zeileneingabezelle den Verweis auf die Eingabezelle für die Variablenwerte in der Zeile ein (in diesem Beispiel enthält sie nur den Jahreswert).
7. Geben Sie im Feld Spalteneingabezelle den Verweis auf die Eingabezelle für die Variablenwerte in der Spalte ein (B3 mit dem anfänglichen Investitionswert).
8. Klicken Sie auf OK.,n unserem Fall), und analysieren Sie die Ergebnisse:

   

Datentabelle zum Vergleich mehrerer Ergebnisse

Wenn Sie mehr als eine Formel gleichzeitig auswerten möchten, erstellen Sie Ihre Datentabelle wie in den vorherigen Beispielen gezeigt und geben Sie die zusätzliche Formel(n) ein:

• Rechts von der ersten Formel im Falle einer vertikalen Datentabelle, die in Spalten
• die erste Formel im Falle einer horizontalen Datentabelle, die in Zeilen

organisiert ist, damit die Datentabelle „multi-formula“ korrekt funktioniert, sollten sich alle Formeln auf dieselbe Eingabezelle beziehen.,

Fügen wir beispielsweise unserer Tabelle mit einer Variablen eine weitere Formel hinzu, um die Zinsen zu berechnen und zu sehen, wie sie von der Größe der Anfangsinvestition beeinflusst werden. Folgendes tun wir:

1. Berechnen Sie in Zelle B10 das Interesse mit dieser Formel: =B8-B3
2. Ordnen Sie die Quelldaten der Datentabelle wie zuvor an:Variablenwerte in D3: D8 und E2 verknüpft mit B8 (Balance formula).,
3. Fügen Sie dem Datentabellenbereich eine weitere Spalte hinzu (Spalte F) und verknüpfen Sie F2 mit B10 (Interessenformel):
   
4. Wählen Sie den erweiterten Datentabellenbereich aus (D2:F8).
5. Öffnen Sie das Dialogfeld Datentabelle, indem Sie auf die Registerkarte Daten klicken > Was-wäre-wenn-Analyse > Datentabelle…
6. Geben Sie im Feld Spalteneingabezelle die Eingabezelle (B3) an und klicken Sie auf OK.,

Voilà, Sie können jetzt die Auswirkungen Ihrer Variablenwerte auf beide Formeln beobachten:

Datentabelle in Excel – 3 Dinge, die Sie wissen sollten

Um Datentabellen in Excel effektiv zu verwenden, beachten Sie bitte diese 3 einfachen Fakten:

1. Damit eine Datentabelle erfolgreich erstellt werden kann, müssen sich die Eingabezellen auf demselben Blatt wie die Datentabelle befinden.,
2. Microsoft Excel verwendet die Funktion TABELLE(row_input_cell, colum_input_cell), um die Ergebnisse der Datentabelle zu berechnen:
   • In der Datentabelle mit einer Variablen wird je nach Layout (spaltenorientiert oder zeilenorientiert) eines der Argumente weggelassen. In unserer horizontalen Datentabelle mit einer Variablen lautet die Formel beispielsweise =TABLE(, B3) wobei B3 die Spalteneingabezelle ist.
   • In der Datentabelle mit zwei Variablen sind beide Argumente vorhanden. Zum Beispiel =TABLE(B6, B3) wobei B6 die Zeileneingabezelle und B3 die Spalteneingabezelle ist.,

   Die Tabellenfunktion wird als Array-Formel eingegeben. Um dies sicherzustellen, wählen Sie eine beliebige Zelle mit dem berechneten Wert aus, sehen Sie sich die Formelleiste an und notieren Sie sich die {geschweiften Klammern} um die Formel. Es ist jedoch keine normale Array-Formel – Sie können sie weder in die Formelleiste eingeben noch eine vorhandene bearbeiten. Es ist nur „für Show“.

3. Da die Ergebnisse der Datentabelle mit einer Array-Formel berechnet werden, können die resultierenden Zellen nicht einzeln bearbeitet werden. Sie können nur das gesamte Array von Zellen bearbeiten oder löschen, wie unten erläutert.,

So löschen Sie eine Datentabelle in Excel

Wie oben erwähnt, erlaubt Excel das Löschen von Werten in einzelnen Zellen, die die Ergebnisse enthalten, nicht. Wenn Sie dies versuchen, wird die Fehlermeldung „Teil einer Datentabelle kann nicht geändert werden“ angezeigt.

Sie können jedoch ganz einfach das gesamte Array der resultierenden Werte löschen. So geht ‚ s:

1. Wählen Sie je nach Bedarf alle Datentabellenzellen oder nur die Zellen mit den Ergebnissen aus.
2. Drücken Sie die Entf-Taste.

Fertig!, 🙂

So bearbeiten Sie die Ergebnisse der Datentabelle

Da es in Excel nicht möglich ist, einen Teil eines Arrays zu ändern, können Sie einzelne Zellen mit berechneten Werten nicht bearbeiten. Sie können alle diese Werte nur durch Ausführen dieser Schritte durch Ihren eigenen ersetzen:

1. Wählen Sie alle resultierenden Zellen aus.
2. Löschen Sie die Tabellenformel in der Formelleiste.
3. Geben Sie den gewünschten Wert ein und drücken Sie Strg + Enter.,

Dadurch wird derselbe Wert in alle ausgewählten Zellen eingefügt:

Sobald die Tabellenformel weg ist, wird die frühere Datentabelle zu einem üblichen Bereich und Sie können jede einzelne Zelle normal bearbeiten.

So berechnen Sie die Datentabelle manuell

Wenn eine große Datentabelle mit mehreren Variablenwerten und Formeln Ihre Excel verlangsamt, können Sie die automatische Neuberechnung in dieser und allen anderen Datentabellen deaktivieren.,

Wechseln Sie dazu zur Registerkarte Formeln > Berechnungsgruppe, klicken Sie auf die Schaltfläche Berechnungsoptionen und dann auf Automatisch Außer Datentabellen.

Dadurch werden automatische Datentabellenberechnungen deaktiviert und Neuberechnungen der gesamten Arbeitsmappe beschleunigt.

Um Ihre Datentabelle manuell neu zu berechnen, wählen Sie die resultierenden Zellen aus, dh die Zellen mit TABLE () – Formeln, und drücken Sie F9.

So erstellen und verwenden Sie eine Datentabelle in Excel., Um einen genaueren Blick auf die in diesem Tutorial diskutierten Beispiele zu werfen, können Sie gerne unsere Beispiel-Excel-Datentabellen-Arbeitsmappe herunterladen. Ich danke Ihnen für das Lesen und würde glücklich sein, um zu sehen Sie wieder in der nächsten Woche!

• Was-wäre-wenn-Analyse mit Zielsuche in Excel
• So verwenden Sie Solver in Excel: Schritt für Schritt mit Beispielen
• So gruppieren Sie Zeilen in Excel