Dies ist der erste Artikel der fünfteiligen Serie zur Datenanalyse in Excel. In diesem Abschnitt zeige ich Ihnen, wie Sie eine Datentabelle mit einer Variablen in Excel erstellen und verwenden.

Beispieldatei herunterladen

Video ansehen – Eine variable Datentabelle in Excel

Eine variable Datentabelle in Excel ist am besten geeignet, wenn Sie sehen möchten, wie sich das Endergebnis ändert, wenn Sie eine der Eingabevariablen ändern. Wenn Sie zwei Variablen ändern möchten, verwenden Sie die Datentabelle mit zwei Variablen oder den Szenario-Manager.

Verwendung einer Variablen Datentabelle in Excel

Angenommen, Sie haben einen Datensatz wie unten gezeigt:

Im obigen Datensatz haben wir den Kreditbetrag, den Zinssatz und die Anzahl der monatlichen Zahlungen.

Der monatliche Zahlungswert wird basierend auf diesen drei Variablen berechnet (er ist rot dargestellt, da es sich um einen Geldabfluss handelt). Die folgende Formel wird verwendet, um die monatliche Zahlung zu berechnen:

=PMT(B2/12,B3,B1)

Jetzt möchten Sie vielleicht eine Analyse durchführen, um zu sehen, welche Anzahl von monatlichen Zahlungen zu Ihrem Zustand passt und wie die monatliche Zahlung darauf basiert. Beispielsweise möchten Sie möglicherweise die Laufzeit Ihres Darlehens (Anzahl der monatlichen Zahlungen) so beibehalten, dass Ihr monatlicher Zahlungsbetrag nicht mehr als 1.000 USD beträgt.

Eine schnelle Möglichkeit besteht darin, die Daten in dieser Tabelle zu ändern, und die Formel wird automatisch aktualisiert. Wenn Sie jedoch testen möchten, ob für viele verschiedene monatliche Zahlungen eine Variable Datentabelle in Excel der richtige Weg ist.

Einrichten einer Datentabelle mit einer Variablen in Excel

Hier sind die Schritte zum Einrichten einer Datentabelle mit einer Variablen in Excel:

• Haben Sie in einer Spalte alle verschiedenen Werte der Anzahl der monatlichen Zahlungen, die Sie testen möchten. In diesem Beispiel testen wir für 72, 84, 96… 240.
• Type =B4 in Zelle E1, die eine Zeile über den Werten in der angrenzenden Spalte rechts liegt. Dies ist ein Konstrukt, das befolgt werden muss, wenn Sie mit einer variablen Datentabelle in Excel arbeiten. Stellen Sie außerdem sicher, dass der Wert in Zelle E1 von der Anzahl der monatlichen Zahlungen abhängt. Es funktioniert nicht, wenn Sie den Wert manuell in Zelle E1 eingeben.
  In diesem Fall bezieht sich Zelle E1 auf Zelle B4, deren Wert mit einer Formel berechnet wird, die die Zellen B1, B2 und B3 verwendet.
• Jetzt sind die Daten so eingestellt, dass sie in der einen Variablen Datentabelle verwendet werden.
• Wählen Sie die Daten (D1:E16). Gehen Sie zur Registerkarte Daten -> Datentools –> Was ist, wenn Analyse –> Datentabelle.
• Im Dialogfeld Datentabelle beziehen Sie sich auf Zelle B3 im Feld Spalteneingabezelle. Da wir eine Datentabelle mit einer Variablen verwenden, lassen Sie die Zeileneingabezelle leer. Außerdem haben wir die Option Spalteneingabezelle ausgewählt, da die Daten in einer Spalte strukturiert sind.
• Klicken Sie auf OK. Sobald Sie auf OK klicken, werden die Zellen neben den variablen Datenpunkten (Anzahl der monatlichen Zahlungen) automatisch ausgefüllt.

Wenn Sie beispielsweise berechnen möchten, wie viele monatliche Zahlungen Sie verwenden sollten, um die monatliche Zahlung unter 1.000 USD zu halten. Sie können ganz einfach 144 Monate (12 Jahre) auswählen.

Hinweis:

• Sobald Sie die Werte mit Datentabelle berechnet haben, kann es nicht rückgängig gemacht werden mit Strg + Z. Sie können jedoch manuell alle Werte auswählen und löschen.
• Sie können keine Zelle im gesamten Satz berechneter Werte löschen/ändern. Da es sich um ein Array handelt, müssen Sie alle Werte löschen.
• Sie können die gleiche Berechnung durchführen, indem Sie die Daten horizontal anordnen. Der einzige Unterschied besteht darin, dass Sie die Zeileneingabezelle im Dialogfeld Datentabelle verwenden müssen (anstelle der Spalteneingabezelle).

Datei herunterladen.. Probieren Sie es selbst aus

Sie können auch die folgenden Excel-Tutorials mögen:

• Berechnung der Standardabweichung in Excel.
• Histogramm in Excel erstellen.
• Berechnung des gewichteten Durchschnitts in Excel.
• Berechnung der CAGR in Excel.