Erstellen Sie eine Excel-Datentabelle, um mehrere Ergebnisse zu vergleichen

Anmerkung des Herausgebers am 21. März 2019: Dieser Excel-Artikel wurde erstmals im Februar 2012 veröffentlicht. Seitdem hat der Autor den Tipp und die Bilder aktualisiert und Demo-Dateien bereitgestellt. Wir haben ein Video-Tutorial hinzugefügt und die zugehörigen Ressourcen aktualisiert.

Manchmal reicht eine Antwort nicht aus - gelegentlich müssen Sie mehrere Möglichkeiten sehen. In diesem Fall sollten Sie Ihrem Blatt eine Datentabelle hinzufügen. Eine Datentabelle ist ein Bereich, der sich ändernde Variablen in einer einzelnen Formel auswertet. Mit anderen Worten, es ist eine einfache Was-wäre-wenn-Analyse: Wie verändert das Ändern eines Eingabewerts die Ergebnisse? Anstatt mehrere Blätter anzuzeigen, können Sie die Möglichkeiten mit einem kurzen Blick auf eine Datentabelle untersuchen. In diesem Artikel werde ich Ihnen zeigen, wie Sie eine Datentabelle implementieren.

Offenlegung: TechRepublic kann eine Provision für einige der auf dieser Seite vorgestellten Produkte verdienen. TechRepublic und der Autor wurden für diese unabhängige Überprüfung nicht entschädigt.

Ich verwende Office 365 Excel (Desktop) auf einem Windows 10 64-Bit-System, aber diese Funktion ist in älteren Versionen verfügbar. Sie können mit Ihren eigenen Daten arbeiten oder die .xlsx- und .xls-Demonstrationsdateien herunterladen. Sie können eine Datentabelle im Browser anzeigen, aber keine erstellen.

MEHR ERFAHREN: Preise und Funktionen für Office 365 Consumer

So erstellen Sie einen Hypothekenrechner in Excel

Um ihre Verwendung zu veranschaulichen, fügen wir dem in Abbildung A gezeigten einfachen Hypothekenrechner eine Datentabelle hinzu. Sie können jeden der Eingabewerte ändern, um die Ergebnisse zu ändern. Wir werden keine Zeit damit verbringen, den Taschenrechner zu bauen. Sie können die Demo-Arbeitsmappe herunterladen oder die Formeln in Tabelle A finden . Die Funktion IFERROR () ist nicht erforderlich. Verwenden Sie sie jedoch zur Behandlung von Fehlern, wenn Sie die Arbeitsmappe an andere verteilen möchten.

Abbildung A.

Wir werden diesem einfachen Hypothekenrechner eine Datentabelle hinzufügen.

Tabelle A.

ZelleFormelFormat
B4: B5
Währung
B6
Allgemeines
B7
Prozentsatz mit zwei Dezimalstellen
E4= B4-B5Währung
E5= IFERROR (PMT (B7 / 12, B6 · 12, -B4 + B5), 0)Währung
E6= IFERROR (E5 * B6 * 12, 0)Währung
E7= IFERROR (E6-E4, 0)Währung

Angenommen, Sie möchten die Ergebnisse vergleichen, wenn sich der Zinssatz nicht nur einmal, sondern für mehrere Zinssätze ändert. Hier kommt eine Datentabelle ins Spiel. Der erste Schritt besteht darin, die Beschriftungen der Datentabelle einzugeben. Sie sollten leicht vom Originalblatt abzulesen sein. Außerdem sollte die erste Spalte der Datentabelle Ihre Variable enthalten. In diesem Fall ist das der sich ändernde Zinssatz.

Ich empfehle die Verwendung von AutoFill, um eine Liste der Zinssätze zu erstellen (siehe Abbildung B) . Wenn Sie Formeln verwenden müssen, müssen Sie die Formeln durch Werte ersetzen, da Datentabellen keine Formeln in der ersten Spalte auswerten. Es spielt keine Rolle, mit welchen Werten Sie die Liste beginnen und beenden.

Abbildung B.

Fügen Sie Beschriftungen hinzu und erstellen Sie die Liste der sich ändernden Variablen in der ersten Spalte der Datentabelle.

Der nächste Schritt besteht darin, die Referenzen der Datentabelle wie folgt einzugeben:

  1. Wählen Sie B11 und geben Sie = E5 ein (die Zelle, die die monatliche Zahlungsformel im Ergebniszellenabschnitt Ihres Originalblatts enthält).
  2. Wählen Sie C11 und geben Sie = E6 ein.
  3. Wählen Sie D11 und geben Sie = E7 ein.

Abbildung C zeigt diesen Schritt implementiert.

Abbildung C.

Fügen Sie Verweise auf die Formeln des Originalblatts hinzu.

Bevor Sie die Datentabelle generieren, müssen Sie noch einen Schritt tun. Die variable Eingabezelle - in diesem Fall der Zinswert - muss dem ersten Wert in Ihrer Datentabelle entsprechen. Der erste Zinssatz in der Datentabelle beträgt 2, 5%. Sie müssen den Eingabewert in B7 auf 2, 5% ändern, bevor Sie die Datentabelle generieren (siehe Abbildung D) . Überspringen Sie diesen Schritt nur, wenn die Werte identisch sind.

Abbildung D.

Jetzt können Sie die Datentabelle wie folgt generieren:

  1. Wählen Sie den Datentabellenbereich aus. In diesem Fall ist das A11: D29 (die Beschriftungen in Zeile 10 nicht einschließen).
  2. Klicken Sie auf die Registerkarte Daten.
  3. Wählen Sie Datentabelle aus der Option Was-wäre-wenn-Analyse in der Gruppe Datentools aus ( Abbildung E ).

Abbildung E.

Geben Sie im daraufhin angezeigten Dialogfeld die Eingabezelle B7 in die Spalte Eingabezelle ein, da sich die Zinswerte in einer Spalte gegenüber einer Zeile befinden ( Abbildung F ). Dies ist der Eingabewert, den Excel für jede Zeile in der Datentabelle ändert.

Klicken Sie auf OK und formatieren Sie den Rest der Tabelle nach Bedarf.

Die Option Datentabelle befindet sich in der Gruppe Datentools.

Abbildung F.

Verweisen Sie auf die Eingabezelle, die sich ändert - in diesem Fall ist es der Zinssatz in B7.

Die resultierende Datentabelle in Abbildung G zeigt, wie sich die monatliche Zahlung und andere Werte ändern, wenn sich der Zinssatz ändert. Durch die Kombination des Originalrechnerblatts mit einer Datentabelle haben Sie das Beste aus beiden Welten. Sie können weiterhin den Rechnerteil verwenden, um alle Eingabewerte zu ändern, und die Datentabelle gibt die sich ändernden Werte mit den festgelegten Zinsbereichen (2, 5% bis 11, 50%) für jedes Hypothekenszenario wieder.

Abbildung G.

Die Datentabelle zeigt Zahlungen und Summen für viele Zinssätze.

Sobald die Datentabelle vorhanden ist, können Sie keine der referenzierenden Zellen in den Spalten B, C und D ändern. Excel schützt diese Zellen. Darüber hinaus sind die Datentabellenformeln eine Reihe von = TABLE () -Funktionen - diese Funktion dient nur zur Anzeige. Sie können es in keinem anderen Kontext verwenden.

Senden Sie mir Ihre Frage zu Office

Ich beantworte die Fragen der Leser, wenn ich kann, aber es gibt keine Garantie. Senden Sie keine Dateien, es sei denn, Sie werden dazu aufgefordert. Erste Hilfeanfragen, die mit angehängten Dateien eingehen, werden ungelesen gelöscht. Sie können Screenshots Ihrer Daten senden, um Ihre Frage zu klären. Seien Sie so genau wie möglich, wenn Sie mich kontaktieren. Beispiel: "Bitte beheben Sie Fehler in meiner Arbeitsmappe und beheben Sie, was falsch ist" wird wahrscheinlich keine Antwort erhalten, aber "Können Sie mir sagen, warum diese Formel nicht die erwarteten Ergebnisse zurückgibt?" könnte. Bitte erwähnen Sie die App und Version, die Sie verwenden. TechRepublic erstattet mir weder meine Zeit noch mein Fachwissen, wenn ich Lesern helfe, noch verlange ich von den Lesern, denen ich helfe, eine Gebühr. Sie können mich unter kontaktieren.

Microsoft Weekly Newsletter

Seien Sie der Microsoft-Insider Ihres Unternehmens mithilfe dieser Windows- und Office-Tutorials und der Analysen unserer Experten zu Microsoft-Unternehmensprodukten. Wird montags und mittwochs geliefert

Heute anmelden

© Copyright 2021 | mobilegn.com