Wer möchte nicht manchmal gerne ein wenig in die Zukunft sehen können? Ein Blick in die Glaskugel oder den Kaffeesatz wären sicherlich mögliche Ansätze.
Aber Excel wäre nicht Excel, wenn es für die Hobby-Wahrsager unter uns nicht auch ein paar passende Funktionen dafür bieten könnte. Einen sehr einfachen Vertreter aus dem Bereich der statistischen Trendanalyse sehen wir uns heute an:
Die SCHÄTZER-Funktion.
Und so geht’s:
Die Trendfunktion SCHÄTZER
Auch wenn es vielleicht etwas komisch klingt: Diese Excel-Funktion heißt tatsächlich so. Mit ihrer Hilfe lassen sich auf einfache Art und Weise lineare Trends berechnen.
Wenn man also eine Reihe von bekannten Werten hat (z.B. Umsätze, Bevölkerungszahlen, Webseitenbesucher etc.), dann lassen sich auf dieser Basis mit der SCHÄTZER-Funktion zukünftige Werte errechnen.
=SCHÄTZER(x;Y-Werte;X-Werte)
Dabei ist x der Datenpunkt, für den der Schätzwert ermittelt werden soll. Der zweite Parameter „Y-Werte“ ist eine Reihe von abhängigen Datenwerten (z.B. die Besucherzahlen). Der dritte Parameter „X-Werte“ gibt eine Reihe von unabhängigen Datenwerten an (z.B. die Monate).
Die Bereiche für die Y- und die X-Werte müssen gleich groß sein, ansonsten liefert die Funktion einen Fehler zurück.
Kommen wir nach dieser kurzen Theorie zur praktischen Anwendung.
Beispiel: Webseiten-Analyse
Nehmen wir an, wir sind Betreiber einer Website. Mit Hilfe eines Analysetools haben wir die monatlichen Besucherzahlen für die vergangenen 18 Monate ermittelt:
Beispieltabelle: Webseiten-Besucher
Zur besseren Veranschaulichung erstellen wir dazu ein einfaches Liniendiagramm. Dazu markieren wir die Datentabelle und wählen aus dem Einfügen-Menü den Diagrammtyp „2D-Linien“:
Einfügen eines Liniendiagramms
Wenn wir nun im Diagramm einen Rechtsklick auf die Linie mit den Besucherzahlen machen, können wir eine Trendlinie einfügen lassen:
Trendlinie einfügen
Liniendiagramm mit Trendlinie
Standardmäßig wird hier ein linearer Trend angenommen. Da wir zu Beginn bereits die zukünftigen 6 Monate für unser Diagramm markiert hatten, wird die Trendlinie auch gleich für diesen Zeitraum fortgeschrieben.
Nun wollen wir mit Hilfe der SCHÄTZER-Funktion diese Trendwerte auch berechnen lassen. Dazu schreiben wir in eine Zelle rechts neben unserer Tabelle folgende Funktion:
=SCHÄTZER(A2;$B$2:$B$19;$A$2:$A$19)
Die SCHÄTZER-Funktion
Wichtig ist hierbei, dass die Bereiche für die Y-Werte und die X-Werte mit den Dollarzeichen als absolute Bereiche festgeschrieben werden. Wenn wir die Formel nun nach unten in die restlichen 23 Monate kopieren, ergibt sich folgendes Bild:
Vergleich der Werte mit der Trendlinie
Wie man sieht, decken sich die mit der SCHÄTZER-Funktion errechneten Werte mit der Trendlinie in unserem Diagramm (ich habe zur besseren Lesbarkeit noch ein paar Gitternetzlinien eingefügt).
Wenn man sich das Diagramm ansieht, erkennt man in den Monaten 6 bis 9 einen relativ steilen Anstieg, der sich später nicht mehr so steil fortsetzt. Daher wollen wir eine zweite, etwas konservativere Trendberechnung vornehmen, welche nur die Monate 9 bis 18 berücksichtigt. Da uns nur die zukünftigen Monate für die Schätzung interessieren, beginne ich mit meiner SCHÄTZER-Funktion erst im Monat 19:
=SCHÄTZER(A20;$B$10:$B$19;$A$10:$A$19)
Verkürzte Datenbasis
Auch hier unbedingt wieder auf die absoluten Bezüge für die Y- und X-Werte achten.
Und damit ergeben sich verglichen mit unserer ersten Kalkulation etwas konservativere Besucherzahlen für das nächste halbe Jahr:
Die konservativeren Ergebnisse
Bonus-Tipp
Wer es sich ganz einfach machen möchte: Die SCHÄTZER-Funktion verbirgt sich auch hinter dem Ausfüllkästchen an der rechten unteren Zellenecke. Markieren wird dazu mit der Maus den gleichen Datenbereich wie den Y-Bereich in der SCHÄTZER-Funktion und ziehen dann die Reihe mit dem Ausfüllkästchen weiter nach unten.
Und schon erhalten wir auch die gleichen Ergebnisse wie mit der SCHÄTZER-Funktion:
Trick mit dem Ausfüllkästchen
Die SCHÄTZER-Funktion eignet sich natürlich nur für sehr einfache Anwendungsfälle. Für einen schnellen und unkomplizierten Ausblick kann sie aber doch recht nützlich sein.
Was man aber nie vergessen sollte: Es handelt sich eben nur um einen statistischen Trend. Die Realität sieht manchmal ganz anders aus. Vielleicht sollte man zur Kontrolle doch auch noch im Kaffeesatz lesen…
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo,
erst mal vielen Dank für deine tollen Tipps, Erklärungen und Beispiele
Ich hoffe, ich habe die Funktionen SCHÄTZER und auch PROGNOSE.LINEAR richtig verstanden, und diese Funktionen prognostizieren ausschließlich unbekannte y-Werte zu einem gegebenen x-Wert.
Daher stellt sich mir folgende Frage: Wie kann man einen zukünftigen x Wert zu einem gegebenen y-Wert berechnen??
Beispiel: Die x-Werte sind Datums Werte. Die Y-Werte geben den Stand meines Budgets wieder.
Hierfür habe ich verschiedene Vergangenheitswerte.
Ich möchte nun wissen, zu welchem Zeitpunkt (x=?) mein Budget voraussichtlich aufgebraucht sein wird (y=0).
Dies müsste sich doch auch prognostizieren lassen, oder?
Viele Grüße
Daniela
Guten Tag Her Weiss,
Ich hoffe sie hatten eine tolle Weihnachtszeit und konnten die Tage zwischen Weihnacht und Neujahrswechsel genießen.
Vorab möchte Ich Ihnen recht herzlich für das Teilen ihres Wissens über Excel danken – es hilft mir sehr.
Bitte erlauben Sie mir jedoch eine Frage.
Beim Anwenden der Forecast (Schätzen) Funktion bekomme ich Werte, welche negativ sind, und so gar nicht in das Verhältnis zum Original Wert passen wollen (Ermitteln der Werte zur Trendlinie). Wie kann das sein?
Was hat es mit den negativ Werten auf sich?
Danke und Gruss
Philipp
Guten Tag Herr Frenzel,
vielen Dank für die freundlichen Worte und es freut mich zu hören, dass Ihnen meine Artikel weiterhelfen.
Was Ihre Frage angeht: die von SCHÄTZER gelieferten Werte können grundsätzlich durchaus negativ werden. Es hängt einfach davon ab, wie sich der lineare Trend zur angegebenen Datenbasis weiterentwickeln würde. Aus der Ferne ist das für Ihren konkreten Fall natürlich schwer zu beurteilen. In ein Diagramm gezeichnet ist das Ergebnis ja immer eine Trendgerade. Am besten, Sie erstellen wie oben im Artikel ein Diagramm aus Ihren Daten, vielleicht ergibt sich ja doch das Bild einer negativen Geraden.
Schöne Grüße,
Martin