Berechnen Sie mehrere Ergebnisse mithilfe einer Datentabelle
Eine Datentabelle ist eine Reihe von Zellen, in denen Sie Werte in einigen Zellen ändern und unterschiedliche Antworten auf ein Problem finden können. Ein gutes Beispiel für eine Datentabelle verwendet die PMT -Funktion mit unterschiedlichen Kreditbeträgen und Zinssätzen, um den erschwinglichen Betrag für ein Hypothekendarlehen zu berechnen. Das Experimentieren mit verschiedenen Werten, um die entsprechende Variation der Ergebnisse zu beobachten, ist eine häufige Aufgabe in der Datenanalyse .
In Microsoft Excel sind Datentabellen Teil einer Reihe von Befehlen, die als What-If-Analysetools bezeichnet werden. Wenn Sie Datentabellen erstellen und analysieren, führen Sie eine Was-wäre-wenn-Analyse durch.
Bei der Was-wäre-wenn-Analyse werden die Werte in Zellen geändert, um zu sehen, wie sich diese Änderungen auf das Ergebnis von Formeln auf dem Arbeitsblatt auswirken. Beispielsweise können Sie eine Datentabelle verwenden, um den Zinssatz und die Laufzeit für ein Darlehen zu variieren, um potenzielle monatliche Zahlungsbeträge zu bewerten.
Hinweis: Mit Datentabellen und Visual Basic for Applications (VBA) können Sie schnellere Berechnungen durchführen. Weitere Informationen finden Sie unter Was-wäre-wenn-Datentabellen in Excel: Schnellere Berechnung mit VBA .
Arten der Was-wäre-wenn-Analyse
Es gibt drei Arten von Was-wäre-wenn-Analysetools in Excel: Szenarien, Datentabellen und Zielsuche . Szenarien und Datentabellen verwenden Sätze von Eingabewerten, um mögliche Ergebnisse zu berechnen. Die Zielsuche ist deutlich anders, sie verwendet ein einzelnes Ergebnis und berechnet mögliche Eingabewerte, die zu diesem Ergebnis führen würden.
Wie Szenarien helfen Ihnen Datentabellen dabei, eine Reihe möglicher Ergebnisse zu untersuchen. Im Gegensatz zu Szenarien zeigen Ihnen Datentabellen alle Ergebnisse in einer Tabelle auf einem Arbeitsblatt. Die Verwendung von Datentabellen macht es einfach, eine Reihe von Möglichkeiten auf einen Blick zu untersuchen. Da Sie sich auf nur eine oder zwei Variablen konzentrieren, sind die Ergebnisse einfach zu lesen und in tabellarischer Form zu teilen.
Eine Datentabelle kann nicht mehr als zwei Variablen aufnehmen. Wenn Sie mehr als zwei Variablen analysieren möchten, sollten Sie stattdessen Szenarien verwenden. Obwohl sie auf nur eine oder zwei Variablen beschränkt ist (eine für die Zeileneingabezelle und eine für die Spalteneingabezelle), kann eine Datentabelle beliebig viele verschiedene Variablenwerte enthalten. Ein Szenario kann maximal 32 verschiedene Werte haben, aber Sie können beliebig viele Szenarien erstellen.
Erfahren Sie mehr im Artikel Einführung in die Was-wäre-wenn-Analyse .
Erstellen Sie abhängig von der Anzahl der zu testenden Variablen und Formeln entweder Datentabellen mit einer oder zwei Variablen.
Datentabellen mit einer Variablen
Verwenden Sie eine Datentabelle mit einer Variablen, wenn Sie sehen möchten, wie verschiedene Werte einer Variablen in einer oder mehreren Formeln die Ergebnisse dieser Formeln ändern. Sie können beispielsweise eine Datentabelle mit einer Variablen verwenden, um zu sehen, wie sich unterschiedliche Zinssätze auf eine monatliche Hypothekenzahlung auswirken, indem Sie die PMT-Funktion verwenden . Sie geben die Variablenwerte in eine Spalte oder Zeile ein, und die Ergebnisse werden in einer angrenzenden Spalte oder Zeile angezeigt.
In der folgenden Abbildung enthält Zelle D2 die Zahlungsformel =PMT(B3/12,B4,-B5) , die sich auf die Eingabezelle B3 bezieht.
Datentabellen mit zwei Variablen
Verwenden Sie eine Datentabelle mit zwei Variablen, um zu sehen, wie sich unterschiedliche Werte von zwei Variablen in einer Formel auf die Ergebnisse dieser Formel auswirken. Sie können beispielsweise eine Datentabelle mit zwei Variablen verwenden, um zu sehen, wie sich unterschiedliche Kombinationen von Zinssätzen und Darlehensbedingungen auf eine monatliche Hypothekenzahlung auswirken.
In der folgenden Abbildung enthält Zelle C2 die Zahlungsformel =PMT(B3/12,B4,-B5) , die zwei Eingabezellen B3 und B4 verwendet.
Berechnungen von Datentabellen
Wenn ein Arbeitsblatt neu berechnet wird, werden auch alle Datentabellen neu berechnet – selbst wenn keine Änderung an den Daten vorgenommen wurde. Um die Berechnung eines Arbeitsblatts zu beschleunigen, das eine Datentabelle enthält, können Sie die Berechnungsoptionen so ändern, dass das Arbeitsblatt, aber nicht die Datentabellen automatisch neu berechnet werden. Weitere Informationen finden Sie im Abschnitt Beschleunigen der Berechnung in einem Arbeitsblatt, das Datentabellen enthält .
Eine Datentabelle mit einer Variablen enthält ihre Eingabewerte entweder in einer einzelnen Spalte (spaltenorientiert) oder über eine Zeile (zeilenorientiert). Jede Formel in einer Datentabelle mit einer Variablen darf sich nur auf eine Eingabezelle beziehen.
Folge diesen Schritten:
Geben Sie die Liste der Werte, die Sie ersetzen möchten, in die Eingabezelle ein – entweder eine Spalte nach unten oder eine Zeile quer. Lassen Sie ein paar leere Zeilen und Spalten auf beiden Seiten der Werte.
Führen Sie einen der folgenden Schritte aus:
Wenn die Datentabelle spaltenorientiert ist (Ihre Variablenwerte befinden sich in einer Spalte), geben Sie die Formel in die Zelle eine Zeile über und eine Zelle rechts von der Wertespalte ein. Diese Datentabelle mit einer Variablen ist spaltenorientiert, und die Formel ist in Zelle D2 enthalten.
Wenn Sie die Auswirkungen verschiedener Werte auf andere Formeln untersuchen möchten, geben Sie die zusätzlichen Formeln in die Zellen rechts neben der ersten Formel ein.Wenn die Datentabelle zeilenorientiert ist (Ihre Variablenwerte befinden sich in einer Zeile), geben Sie die Formel in die Zelle eine Spalte links vom ersten Wert und eine Zelle unterhalb der Wertezeile ein.
Wenn Sie die Auswirkungen verschiedener Werte auf andere Formeln untersuchen möchten, geben Sie die zusätzlichen Formeln in Zellen unterhalb der ersten Formel ein.
Wählen Sie den Zellbereich aus, der die Formeln und Werte enthält, die Sie ersetzen möchten. In der obigen Abbildung ist dieser Bereich C2:D5.
Klicken Sie auf der Registerkarte Daten auf Was-wäre-wenn-Analyse > Datentabelle (in der Gruppe Datentools oder Prognose von Excel 2016).
Führen Sie einen der folgenden Schritte aus:
Wenn die Datentabelle spaltenorientiert ist, geben Sie den Zellbezug für die Eingabezelle in das Feld Spalte Eingabezelle ein . In der obigen Abbildung ist die Eingabezelle B3.
Wenn die Datentabelle zeilenorientiert ist, geben Sie den Zellbezug für die Eingabezelle in das Feld Zeileneingabezelle ein .
Hinweis: Nachdem Sie Ihre Datentabelle erstellt haben, möchten Sie möglicherweise das Format der Ergebniszellen ändern. In der Abbildung sind die Ergebniszellen als Währung formatiert.
Formeln, die in einer Datentabelle mit einer Variablen verwendet werden, müssen sich auf dieselbe Eingabezelle beziehen.
Folge diesen Schritten
Führen Sie eine der folgenden Aktionen aus:
Wenn die Datentabelle spaltenorientiert ist, geben Sie die neue Formel in eine leere Zelle rechts neben einer vorhandenen Formel in der obersten Zeile der Datentabelle ein.
Wenn die Datentabelle zeilenorientiert ist, geben Sie die neue Formel in eine leere Zelle unterhalb einer vorhandenen Formel in der ersten Spalte der Datentabelle ein.
Wählen Sie den Zellbereich aus, der die Datentabelle und die neue Formel enthält.
Klicken Sie auf der Registerkarte Daten auf Was-wäre-wenn-Analyse > Datentabelle (in der Gruppe Datentools oder Prognosegruppe von Excel 2016).
Führen Sie einen der folgenden Schritte aus:
Wenn die Datentabelle spaltenorientiert ist, geben Sie den Zellbezug für die Eingabezelle in das Feld Spalteneingabezelle ein.
Wenn die Datentabelle zeilenorientiert ist, geben Sie den Zellbezug für die Eingabezelle in das Feld Zeileneingabezelle ein.
Eine Datentabelle mit zwei Variablen verwendet eine Formel, die zwei Listen mit Eingabewerten enthält. Die Formel muss sich auf zwei verschiedene Eingabezellen beziehen.
Folge diesen Schritten:
Geben Sie in einer Zelle auf dem Arbeitsblatt die Formel ein, die sich auf die beiden Eingabezellen bezieht.
Im folgenden Beispiel, in dem die Formelstartwerte in die Zellen B3, B4 und B5 eingegeben werden, geben Sie die Formel =PMT(B3/12,B4,-B5) in Zelle C2 ein.
Geben Sie eine Liste mit Eingabewerten in dieselbe Spalte unterhalb der Formel ein.
Geben Sie in diesem Fall die unterschiedlichen Zinssätze in die Zellen C3, C4 und C5 ein.
Geben Sie die zweite Liste in dieselbe Zeile wie die Formel ein – rechts davon.
Geben Sie die Kreditbedingungen (in Monaten) in die Zellen D2 und E2 ein.
Wählen Sie den Zellbereich aus, der die Formel enthält (C2), sowohl die Zeile als auch die Spalte mit Werten (C3:C5 und D2:E2) und die Zellen, in denen Sie die berechneten Werte haben möchten (D3:E5).
Wählen Sie in diesem Fall den Bereich C2:E5.
Klicken Sie auf der Registerkarte Daten in der Gruppe Datentools oder Prognose (in Excel 2016) auf Was-wäre-wenn-Analyse > Datentabelle (in der Gruppe Datentools oder Prognose von Excel 2016).
Geben Sie im Feld Zeile Eingabezelle den Verweis auf die Eingabezelle für die Eingabewerte in der Zeile ein.
Geben Sie Zelle B4 in das Eingabezellenfeld Zeile ein .Geben Sie im Feld Spalte Eingabezelle den Verweis auf die Eingabezelle für die Eingabewerte in der Spalte ein.
Geben Sie B3 in das Spalteneingabezellenfeld ein .Klicken Sie auf OK .
Beispiel einer Datentabelle mit zwei Variablen
Eine Datentabelle mit zwei Variablen kann zeigen, wie sich verschiedene Kombinationen von Zinssätzen und Darlehensbedingungen auf eine monatliche Hypothekenzahlung auswirken. In der Abbildung hier enthält Zelle C2 die Zahlungsformel =PMT(B3/12,B4,-B5) , die zwei Eingabezellen B3 und B4 verwendet.
Wenn Sie diese Berechnungsoption festlegen, finden keine Datentabellenberechnungen statt, wenn eine Neuberechnung für die gesamte Arbeitsmappe durchgeführt wird. Um Ihre Datentabelle manuell neu zu berechnen, wählen Sie ihre Formeln aus und drücken Sie dann F9.
Befolgen Sie diese Schritte, um die Berechnungsleistung zu verbessern:
Klicken Sie auf Datei > Optionen > Formeln.
Klicken Sie im Abschnitt Berechnungsoptionen unter Berechnen auf Automatisch außer für Datentabellen .
Tipp: Optional können Sie auf der Registerkarte „ Formeln " auf den Pfeil bei „ Berechnungsoptionen" und dann auf „ Automatisch außer Datentabellen " (in der Gruppe „ Berechnung ") klicken.
Sie können einige andere Excel-Tools verwenden, um Was-wäre-wenn-Analysen durchzuführen, wenn Sie bestimmte Ziele oder größere Sätze variabler Daten haben.
Zielsuche
Wenn Sie das von einer Formel zu erwartende Ergebnis kennen, aber nicht genau wissen, welchen Eingabewert die Formel benötigt, um dieses Ergebnis zu erhalten, verwenden Sie die Goal-Seek-Funktion. Lesen Sie den Artikel Verwenden der Zielwertsuche, um das gewünschte Ergebnis durch Anpassen eines Eingabewerts zu finden .
Excel-Solver
Sie können das Excel Solver-Add-In verwenden, um den optimalen Wert für eine Reihe von Eingabevariablen zu finden. Solver arbeitet mit einer Gruppe von Zellen (als Entscheidungsvariablen oder einfach variable Zellen bezeichnet), die zum Berechnen der Formeln in den Ziel- und Einschränkungszellen verwendet werden. Solver passt die Werte in den Entscheidungsvariablenzellen an, um die Grenzwerte für Einschränkungszellen zu erfüllen und das gewünschte Ergebnis für die Zielzelle zu erzeugen. Weitere Informationen finden Sie in diesem Artikel: Definieren und Lösen eines Problems mithilfe von Solver .
Indem Sie verschiedene Zahlen in eine Zelle stecken, können Sie schnell unterschiedliche Antworten auf ein Problem finden. Ein gutes Beispiel ist die Verwendung der PMT -Funktion mit unterschiedlichen Zinssätzen und Kreditlaufzeiten (in Monaten), um herauszufinden, wie viel Kredit Sie sich für ein Haus oder ein Auto leisten können. Sie geben Ihre Zahlen in eine Reihe von Zellen ein, die als Datentabelle bezeichnet werden.
Hier ist die Datentabelle der Zellbereich B2:D8. Sie können den Wert in B4 ändern, den Kreditbetrag und die monatlichen Zahlungen in Spalte D automatisch aktualisieren. Bei einem Zinssatz von 3,75 % gibt D2 eine monatliche Zahlung von 1.042,01 $ zurück, wobei diese Formel verwendet wird: =PMT(C2/12,$B$3,$B$4).
Sie können eine oder zwei Variablen verwenden, je nachdem, wie viele Variablen und Formeln Sie testen möchten.
Verwenden Sie einen Ein-Variablen-Test, um zu sehen, wie unterschiedliche Werte einer Variablen in einer Formel die Ergebnisse verändern. Sie können beispielsweise den Zinssatz für eine monatliche Hypothekenzahlung ändern, indem Sie die PMT-Funktion verwenden. Sie geben die Variablenwerte (die Zinssätze) in eine Spalte oder Zeile ein, und die Ergebnisse werden in einer benachbarten Spalte oder Zeile angezeigt.
In dieser Live-Arbeitsmappe enthält Zelle D2 die Zahlungsformel = PMT(C2/12,$B$3,$B$4). Zelle B3 ist die variable Zelle, in der Sie eine andere Laufzeit (Anzahl der monatlichen Zahlungsperioden) eingeben können. In Zelle D2 fügt die PMT-Funktion den Zinssatz 3,75 %/12, 360 Monate und ein Darlehen von 225.000 USD ein und berechnet eine monatliche Zahlung von 1.042,01 USD.
Verwenden Sie einen Zwei-Variablen-Test, um zu sehen, wie sich unterschiedliche Werte von zwei Variablen in einer Formel auf die Ergebnisse auswirken. Sie können beispielsweise verschiedene Kombinationen aus Zinssätzen und Anzahl der monatlichen Zahlungsperioden testen, um eine Hypothekenzahlung zu berechnen.
In dieser Live-Arbeitsmappe enthält Zelle C3 die Zahlungsformel = PMT($B$3/12,$B$2,B4), die zwei variable Zellen, B2 und B3, verwendet. In Zelle C2 fügt die PMT-Funktion den Zinssatz 3,875 %/12, 360 Monate und ein Darlehen in Höhe von 225.000 USD ein und berechnet eine monatliche Zahlung in Höhe von 1.058,03 USD.
Benötigen Sie weitere Hilfe?
Sie können jederzeit einen Experten in der Excel Tech Community fragen oder Unterstützung in der Answers-Community erhalten.
No comments:
Post a Comment