So schaust du mit der TREND-Funktion in die Zukunft

Das Prinzip der kaufmännischen Vorsicht gebietet es, in jeder Periode eines Jahres schon mal einen Blick nach vorn, d.h. zum Jahresende hin zu werfen. Das sollte ein Unternehmer tun, um nicht mit dem Jahresabschluss völlig überrascht zu werden.

Das Bestimmen von Trendwerten ist eine Methode, eine solche Vorschau vorzunehmen.

TREND ist eine statistische Funktion in Excel. Um sie soll es in diesem Beitrag gehen.

Die Funktion TREND

Die Syntax der Funktion lautet:

=TREND(Y_Werte;X_Werte;Neue_X_Werte;Konstante)

Dabei sind die Y_Werte und die X_Werte Werte, die bereits aus der Beziehung y = mx + b bekannt sind. Neue_X_Werte sind die Werte, für die die Trendfunktion die Y_Werte liefern soll und die Konstante gibt schließlich an, ob die Konstante b den Wert 0 annehmen soll.

Das klingt alles kompliziert, ist es aber nicht.

Ein bekanntes Beispiel

In den Artikeln zu Achsenabschnitt und Steigung bzw. zum Bestimmtheitsmass habe ich diese Werte verwendet

und daraus dieses Diagramm erstellt.

Die hinzugefügte Trendfunktion geht einen x-Wert nach vorn, der y-Wert dazu dürfte der Grafik nach bei 5,etwas liegen.

In die Zelle B81 schreibe ich die Formel für die Trendfunktion und rechne:

=TREND(B76:B80;A76:A80;A81)

Ergebnis: 5,2

Die Trendfunktion lautet gemäß Diagramm: y=x – 0,8. Für die Trendfunktion musst du dir die x-Achse gedanklich anders beschriften. Der 1. Wert (zu -2) ist 1, der zweite Wert (zu -1) ist 2 usw.

Somit kannst du die Trendwerte auch mit der Funktion errechnen:

Vergleiche diese Werte einmal mit der Trendgeraden im Diagramm: Diese Werte sind auch im Diagramm ablesbar.

Benennst du die Y_Werte und X_Werte namentlich, z.B. mit “Abhängig” und “Unabhängig”, kannst du die Trendformel auch so schreiben:

=TREND(Abhängig;Unabhängig;A81)

Die Bereiche, in denen sich die y- bzw. x-Werte befinden, können auch so hinterlegt werden:

Dann führt diese Formel ebenso zum Ergebnis.

=TREND(INDIREKT(F107);INDIREKT(F108);A93)

Statt der Bereiche, können auch die Namen der Bereiche so hinterlegt werden:

Dann wird die Formel ebenso verwendet:

=TREND(INDIREKT(F130);INDIREKT(F131);A93)

Noch ein paar Formelvariationen

Gezeigt werden soll auch noch folgende Möglichkeit, bei der mittels der WAHL-FUNKTION unterschiedliche Reihen von y-Werten gewählt werden können. Mit der WAHL-Funktion wird das Argument Y_Werte ersetzt.

Eine Beispieltabelle dazu:

Die x-Werte sollen “Unabhang” heißen. y1 nenne ich Abhang1, y2 Abhang2 und y3 Abhang3. In Zelle F144 steht z.B. der Index für die WAHL-Funktion, z.B. die 1.

Jetzt stelle ich diese Formel zusammen:

=TREND(WAHL($F$144;Abhang1;Abhang2;Abhang3);Unabhang;A138)

und erhalte 6,5 als Ergebnis.

Es geht auch durch Verkettung. Die Formel

=TREND(INDIREKT(“Abhang”&$F$144);Unabhang;A138)

führt ebenfalls zum Ergebnis 6,5.

Hier noch eine weitere Kombinationsmöglichkeit:

=TREND(INDIREKT(“Abhang”&$F$144);A131:A137;A138)

Ergebnis: 6,5

Ebenso lassen sich natürlich auch die x-Werte variabel gestalten.

Ein praktisches Beispiel

Nun können die y-Werte z.B. auch das Ergebnis anderer Berechnungen sein. Denkbar ist, dass die Werte aus Quelldateien mit den Funktionen INDEX oder BEREICH.VERSCHIEBEN in die Tabelle gebracht wurden. Ein kleines Beispiel dazu:

Ich habe eine Tabelle mit 3 Produkten und die Mengen von 3 Monaten. Daraus soll jeweils für ein Produkt ein Trend errechnet werden.

Die x-Werte sollen die Monate darstellen. Über ein DropDown-Feld (B176) wähle ich aus einer Liste das jeweilige Produkt aus, z.B. ProduktC.

Mit der Formel

=TREND(INDIREKT(“Werte”&RECHTS(B176;1));B$168:D$168;E$168)

errechne ich den Trendwert für den 4. Monat: 2.113,3. Wähle ich ProduktA, errechnet sich der Trendwert 3.376,7.

Die Produktliste kann natürlich beliebig lang sein, die Formel ist dynamisch und wird das richtige Ergebnis liefern.

Als neuer x-Wert habe ich bisher den nächstfolgenden x-Wert gewählt. Möglich wäre aber auch, einen x-Wert, der weiter voraus liegt, zu wählen.

Dazu könnte ich ein Drehfeld anlegen, mit dem ich die Auswahl steuern kann. Als Minimalwert nehme ich 4, als Maximalwert z.B. 10. Die Schrittweite soll 1 sein. Ich verknüpfe das Drehfeld z.B. mit der Zelle A190.

Die Formel

=TREND(INDIREKT(“Werte”&RECHTS(B176;1));B$168:D$168;$A$190)

greift auf die Zelle A190 zu und errechnet das Ergebnis: 2.113,3.

Mit dem Drehfeld kann ich nun spielen und alle Werte von 4 bis 10 als neuen x-Wert abrufen.

Gebe ich der Zelle A190 einen Namen, z.B. “Xneu”, verändert sich die Formel ein wenig

=TREND(INDIREKT(“Werte”&RECHTS(B176;1));B$168:D$168;Xneu)

Das Ergebnis ist aber auch hiermit: 2.113,3.

Fazit

Der Trend ist sehr wohl eine Funktion, mit der Vorhersagen auf künftige Werte getroffen werden können.

Allerdings sollte die Art der Trendfunktion (Exponential, Linear, Logarithmisch usw.) sorgfältig ausgewählt werden, um keine falsche Vorhersage zu treffen. Dazu findest Du hier einen weiteren Beitrag.