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.
Weitere Artikel der Serie Datenanalyse:
- Tabelle mit zwei Variablendaten in Excel.
- Szenario-Manager in Excel.
- Zielsuche in Excel.
- Excel-Solver.
Video ansehen - Datentabelle mit einer Variablen in Excel
Eine variable Datentabelle in Excel eignet sich am besten in Situationen, in denen 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.
Wann sollte eine variable Datentabelle in Excel verwendet werden?
Angenommen, Sie haben einen Datensatz wie unten gezeigt:
Im obigen Datensatz haben wir den Kreditbetrag, den Zinssatz und die Anzahl der monatlichen Zahlungen.
Der Wert der monatlichen Zahlung wird basierend auf diesen drei Variablen berechnet (er ist rot, da es sich um einen Geldabfluss handelt). Zur Berechnung der monatlichen Zahlung wird folgende Formel verwendet:
=PMT(B2/12,B3,B1)
Jetzt möchten Sie vielleicht eine Analyse durchführen, um zu sehen, welche Anzahl von monatlichen Zahlungen Ihrem Zustand entspricht und wie sich die monatliche Zahlung basierend darauf ändern würde. Sie möchten beispielsweise die Laufzeit Ihres Darlehens (Anzahl der monatlichen Zahlungen) so belassen, 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 würde automatisch aktualisiert. Wenn Sie jedoch testen möchten, ob für viele verschiedene Anzahlen monatlicher Zahlungen, dann ist eine Ein-Variable-Datentabelle in Excel der richtige Weg.
Einrichten einer Datentabelle mit einer Variablen in Excel
Hier sind die Schritte zum Einrichten einer One-Variablen-Datentabelle in Excel:
- Tragen Sie in einer Spalte alle verschiedenen Werte der Anzahl der monatlichen Zahlungen ein, die Sie testen möchten. In diesem Beispiel testen wir auf 72, 84, 96… 240.
- Geben Sie =B4 in Zelle E1 ein, 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 wurde, die die Zellen B1, B2 und B3 verwendet.
- Jetzt sind alle Daten für die Verwendung in der Datentabelle mit einer Variablen eingestellt.
- Wählen Sie die Daten aus (D1:E16). Gehen Sie zur Registerkarte Daten -> Datentools -> Was wäre wenn Analyse -> Datentabelle.
- Beziehen Sie sich im Dialogfeld Datentabelle auf Zelle B3 im Zellenfeld für die Spalteneingabe. Da wir eine Datentabelle mit einer Variablen verwenden, lassen Sie die Eingabezelle Zeile leer. Außerdem haben wir die Option Spalteneingabezelle gewählt, da die Daten in einer Spalte strukturiert sind.
- OK klicken. Sobald Sie auf OK klicken, werden die Zellen neben den variablen Datenpunkten (Anzahl der monatlichen Zahlungen) automatisch gefüllt.
Wenn Sie beispielsweise berechnen möchten, wie viele monatliche Zahlungen Sie verwenden sollten, damit die monatliche Zahlung weniger als 1.000 US-Dollar beträgt. Sie können ganz einfach 144 Monate (12 Jahre) auswählen.
Notiz:
- Nachdem Sie die Werte mit der Datentabelle berechnet haben, kann dies nicht mehr mit Strg + Z rückgängig gemacht werden. Sie können jedoch alle Werte manuell 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 dieselbe Berechnung durchführen, indem Sie die Daten horizontal anordnen. Der einzige Unterschied besteht darin, dass Sie die Zeileneingabezelle im Datentabellendialogfeld verwenden müssen (anstelle der Spalteneingabezelle).
