Dies ist der zweite Artikel der fünfteiligen Serie zur Datenanalyse in Excel. In diesem Abschnitt zeige ich Ihnen, wie Sie zwei variable Datentabellen in Excel verwenden.
Weitere Artikel dieser Reihe:
- Eine variable Datentabelle in Excel.
- Szenario-Manager in Excel.
- Zielsuche in Excel.
- Excel-Solver.
Video ansehen - Datentabelle mit zwei Variablen in Excel
Die Datentabelle mit zwei Variablen eignet sich am besten in Situationen, in denen Sie sehen möchten, wie sich das Endergebnis ändert, wenn sich zwei der Eingabevariablen gleichzeitig ändern (im Gegensatz zu einer Datentabelle mit einer Variablen, bei der sich nur eine der Eingabevariablen ändert).
Wenn Sie Daten analysieren möchten, wenn sich mehr als 2 Variablen ändern, ist der Szenario-Manager der richtige Weg.
Wann verwendet man zwei variable Datentabellen 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. Basierend auf diesen 3 Eingabevariablen wird die monatliche Zahlung berechnet (sie ist rot markiert, 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, was die ideale Kombination aus Darlehensbetrag und Anzahl der monatlichen Zahlungen für Ihre Anforderungen sein sollte. Sie möchten beispielsweise die monatliche Zahlung bei 500 USD oder weniger belassen und analysieren, welche Kombination aus Darlehensbetrag und Laufzeit Ihnen dies bieten kann.
In einer solchen Situation sollte eine Datentabelle mit zwei Variablen verwendet werden.
Einrichten von zwei variablen Datentabellen in Excel
Hier sind die Schritte zum Einrichten einer Tabelle mit zwei variablen Daten in Excel:
- Tragen Sie in einer Spalte alle verschiedenen Werte ein, die Sie für die Anzahl der monatlichen Zahlungen testen möchten. In diesem Beispiel testen wir auf 72, 84, 96… 240. Gleichzeitig haben wir die verschiedenen Kreditbetragswerte in der Zeile direkt über den Spaltenwerten (beginnend eine Zelle nach rechts), wie im Bild unten gezeigt.
- Geben Sie =B4 in Zelle D1 ein, die eine Zeile über den Werten in der Spalte liegt. Dies ist ein Konstrukt, das befolgt werden muss, wenn Sie mit zwei variablen Datentabellen arbeiten. Stellen Sie außerdem sicher, dass der Wert in Zelle D1 von beiden Variablen (Anzahl der monatlichen Zahlungen und Kreditbetrag) abhängt. Es funktioniert nicht, wenn Sie den Wert manuell in Zelle D1 eingeben.
In diesem Fall bezieht sich Zelle D1 auf Zelle B4, deren Wert mit einer Formel berechnet wurde, die die Zellen B1, B2 und B3 verwendet.
- Jetzt sind die Daten bereit, für eine Tabellenberechnung mit zwei Variablen verwendet zu werden.
- Wählen Sie die Daten aus (D1:J16). Gehen Sie zur Registerkarte Daten -> Datentools -> Was wäre wenn Analyse -> Datentabelle
- Verwenden Sie im Dialogfeld Datentabelle die folgenden Verweise:
- Zeileneingabezelle: $B$1
- Spalteneingabezelle: $B$3
- OK klicken. Sobald Sie auf OK klicken, werden sofort alle leeren Zellen im ausgewählten Datenbereich gefüllt. Es gibt Ihnen schnell einen Überblick über die monatlichen Zahlungen für verschiedene Kombinationen aus Kreditbetrag und Anzahl der monatlichen Zahlungen.
Wenn Sie beispielsweise die Kombinationen aus Darlehensbetrag und Anzahl der monatlichen Zahlungen ermitteln möchten, die zu einer monatlichen Zahlung von weniger als 500 USD pro Monat führen würden, können Sie einfach diese 2-Tabellenmethode mit variablen Daten verwenden.
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.
