So erstellen Sie eine Datentabelle mit einer Variablen in Excel 2016

In Datentabellen können Sie eine Reihe möglicher Werte eingeben, die Excel 2016 dann in eine einzige Formel einfügt. Eine Datentabelle mit einer Variablen ersetzt einen einzelnen Eingabewert in einer Formel durch eine Reihe möglicher Werte.

Die folgende Abbildung zeigt eine Tabelle mit Verkaufsprognosen für 2017, für die eine Datentabelle mit einer Variablen erstellt werden soll. In diesem Arbeitsblatt wird der prognostizierte Umsatz in Zelle B5 berechnet, indem der Umsatz des letzten Jahres in Zelle B2 zu dem Betrag addiert wird, der 2017 voraussichtlich wachsen wird (berechnet durch Multiplizieren des Gesamtumsatzes des letzten Jahres in Zelle B2 mit dem Wachstumsprozentsatz in Zelle B3 ), gibt dir die Formel

Kalkulationstabelle für Verkaufsprognosen mit einer Spalte mit möglichen Wachstumsprozentsätzen zum Einfügen in eine Datentabelle mit einer Variablen.

=B2+(B2*B3)

Da Sie auf der Registerkarte Formeln des Menübands auf die Befehlsschaltfläche Aus Auswahl erstellen geklickt haben, nachdem Sie A2:B5 zur Auswahl gemacht und das Kontrollkästchen Linke Spalte als Standard akzeptiert haben, verwendet die Formel die Zeilenüberschriften in Spalte A und lautet:

=Umsatz_2016+(Umsatz_2016*Wachstum_2017)

Wie Sie in dieser Abbildung sehen können, wurde in Spalte B im Bereich B8:B24 eine Spalte mit möglichen Wachstumsraten im Bereich von 1% bis 5,0% eingetragen.

Kalkulationstabelle für Verkaufsprognosen nach dem Erstellen der Datentabelle mit einer Variablen im Bereich C8:C24.

Führen Sie die folgenden einfachen Schritte aus, um die gezeigte Datentabelle mit einer Variablen zu erstellen, die jeden dieser Werte in die Umsatzwachstumsformel einfügt:

Kopieren Sie die in Zelle B5 eingegebene Originalformel in Zelle C7, indem Sie = (gleich) eingeben und dann auf Zelle B5 klicken, um die Formel =Projected_Sales_2017 zu erstellen.

Die Kopie der Originalformel (um die Reihe der unterschiedlichen Wachstumsraten in B8:B24 zu ersetzen) ist jetzt die Spaltenüberschrift für die Datentabelle mit einer Variablen.

Wählen Sie den Zellbereich B7:C24 aus.

Der Umfang der Datentabelle umfasst die Formel sowie die verschiedenen Wachstumsraten.

Klicken Sie in der Multifunktionsleiste auf Daten→Was-wäre-wenn-Analyse→Datentabelle.

Excel öffnet das Dialogfeld Datentabelle.

Klicken Sie im Dialogfeld Datentabelle auf das Textfeld Spalteneingabezelle, und klicken Sie dann im Arbeitsblatt auf Zelle B3, die Zelle Wachstum_2017 mit dem ursprünglichen Prozentsatz.

Excel fügt die absolute Zellenadresse, $B$3, in das Textfeld Spalteneingabezelle ein.

Klicken Sie auf OK, um das Dialogfeld Datentabelle zu schließen.

Sobald Sie auf OK klicken, erstellt Excel die Datentabelle im Bereich C8:C24, indem in diesem Bereich eine Formel mit seiner TABLE-Funktion eingegeben wird. Jede Kopie dieser Formel in der Datentabelle verwendet den Prozentsatz der Wachstumsrate in derselben Zeile in Spalte B, um das mögliche Ergebnis zu bestimmen.

Klicken Sie auf Zelle C7, klicken Sie dann auf die Befehlsschaltfläche Format Painter in der Gruppe Zwischenablage auf der Registerkarte Start und ziehen Sie durch den Zellbereich C8:C24.

Excel kopiert das Buchhaltungszahlenformat in den Bereich möglicher Ergebnisse, die von dieser Datentabelle berechnet werden.

Einige wichtige Dinge, die Sie über die in dieser Tabelle erstellte Datentabelle mit einer Variablen beachten sollten:

• Wenn Sie Wachstumsratenprozentsätze im Zellbereich B8:B24 ändern, aktualisiert Excel sofort das zugehörige prognostizierte Verkaufsergebnis in der Datentabelle. Um zu verhindern, dass Excel die Datentabelle aktualisiert, bis Sie auf der Registerkarte Formeln auf die Befehlsschaltfläche Jetzt berechnen (F9) oder Blatt berechnen (Umschalt+F9) klicken, klicken Sie auf die Schaltfläche Berechnungsoptionen auf der Registerkarte Formeln und dann auf die Schaltfläche Automatisch außer für Datentabellen (Alt+MXE).

• Wenn Sie versuchen, eine einzelne TABLE-Formel im Zellbereich C8:C24 zu löschen, zeigt Excel die Warnung Teil einer Datentabelle kann nicht geändert werden. Sie müssen den gesamten Formelbereich (in diesem Fall C8:C24) auswählen, bevor Sie die Entf-Taste drücken oder auf die Schaltfläche Löschen oder Löschen auf der Registerkarte Start klicken.