Excel 2013 für Profis

Planen mit den Statistikfunktionen RGP() und TREND()

Testen Sie unsere 1985 Kurse

10 Tage kostenlos!

Jetzt testen Alle Abonnements anzeigen
Zur Planung stellt Excel die zwei Statistikfunktionen RGP() und TREND() zur Verfügung. Neben den Details dieser Regressions- und Trendfunktion sehen Sie im Beispiel, wie Daten in einem Diagramm dargestellt sind, das in einem neuen Blatt angezeigt wird.

Transkript

Unser nächstes Beispiel beschäftigt sich mit der Planung. Für die Planung stellt uns Excel u.A. 2 Funktionen zur Verfügung: das ist einmal die Regressions- oder RGP-Funktion, und die Trendfunktion. Beide dienen dazu, einen linearen Trend sichtbar zu machen. In einem Fertigungsbetrieb werden bestimmte Werkzeuge zur Produktion eingesetzt, die allerdings natürlich einem gewissen Verschleiß unterliegen. Dieser Verschleiß wurde gemessen und Sie sehen hier bei den Stückzahlen, bei 5.000 Stück waren es EUR 780,-, bei 10.000 EUR 1.020,- usw. Diese Reihe setzt sich nach unten fort, bis sich bei 40.000 Stück Kosten von EUR 2.025,- ergeben. Hier handelt es sich um gemessene Kosten. Die Frage, die gestellt wird, ist nun: Wann lohnt es sich, die verwendeten Werkzeuge immer wieder in Stand zu setzen oder wann ist der Verschleiß so groß, dass es sich kostenmäßig nicht mehr lohnt und ich neue Werkzeuge anschaffen muss? Diese Fragestellung möchte ich mit den Funktionen RGP und Trend beantworten. Schauen wir uns das Ganze zunächst grafisch an. Markieren Sie die entsprechenden Zellen, das sind in unserem Beispiel die Zellen von A3 bis B11. Hier wird uns wieder die Schnellanalyse angeboten, die wir auch nutzen wollen. Ich gehe auf "Diagramme" und wähle "Liniendiagramm" aus und er zeigt uns den entsprechenden Verlauf. Nun möchte ich den entsprechenden Ordinatenabschnitt und die Steigung berechnen. Ich schiebe mal unser Diagramm etwas zur Seite, sodass wir einen besseren Blick auf die Zahlen haben. Hier sehe ich, dass ich die Steigung und den Ordinatenabschnitt berechnen kann. Das mache ich mit Hilfe der Funktion RGP. Dazu vielleicht ein paar Worte: Die Funktion RGP basiert auf der Summe der kleinsten Quadrate; eine statistische Funktion, mit der Sie sich aber nicht im Detail auseinandersetzen müssen, das erledigt, wie gesagt, diese Funktion RPG für Sie. Ich gehe nun in die Bearbeitungsleiste, gebe ein: "RGP" und Sie sehen, er bietet mir diese Funktion an. Jetzt könnte ich die y-Werte und die x-Werte gleich auswählen, ich möchte allerdings noch vorher einen Blick auf die Funktionsargumente werfen, und Sie sehen hier - angegeben werden die x-Werte, das ist ein Pflichtargument - x-Werte, Konstante und STATS sind optionale Elemente, die muss ich nicht zwingend wählen. Ich entscheide mich für die y-Werte, also die Größenangaben, das sind meine Kosten, und zum Zweiten für die x-Werte, das sind die Stückzahlen. Jetzt werden Sie sich vielleicht gewundert haben, warum ich 2 Zellen markiert habe. Das liegt daran, dass diese Funktion 2 Rückgabewerte zurückgibt. Sie sehen an den geschweiften Klammern auch, dass es sich hier um eine Matrixfunktion handelt, die einen entsprechenden Matrixwert zurückgibt. Aus diesem Grund muss ich nicht mit Enter, sondern mit Strg+Enter diese Angabe abschließen. Sie sehen, ich habe nun hier ein Steigungsmaß von 0,03075 und einen Ordinatenabschnitt von 671. Das bedeutet nun das Folgende: Dass ich mit diesen Angaben den ersten Trendwert berechnen kann. Wir werden das nachher nochmal mit der Funktion Trend durchführen. Was muss ich tun? Ich wähle das Steigungsmaß und da ich ja hier mit Tausenderwerten arbeite, muss ich dieses Steigunsmaß mit 1.000 multiplizieren, und addiere dazu den Ordinatenabschnitt. Wenn ich nun mit Enter bestätige, sehen Sie, das ergibt den Wert 702. Das heißt, der erste Planwert wäre 702. Wir wollen das aber jetzt nicht für jeden Wert so berechnen, wie ich es Ihnen gezeigt habe, sondern das möchte ich gerne machen mithilfe unserer zweiten Funktion, mit Hilfe der Trendfunktion. Dazu markiere ich den entsprechenden Bereich. Nun ist es so, dass diese Trendfunktion nicht die Anzahl der markierten Zellen als Parameter zurückgibt, sondern jeweils für eine markierte Zelle einen Wert. Auch hier wieder die gleiche Vorgehensweise wie vorher - ich gebe "Trend" ein. Ich muss die y-Werte angeben und die x-Werte - das könnte ich jetzt direkt machen, aber damit Sie sehen, dass die Funktionsargumente sich nicht unterscheiden von meiner Funktion RGP - blende ich diese nochmals ganz kurz ein. Nun wähle ich die y-Werte, das sind wieder meine Kosten, und die x-Werte, das sind meine Stückzahlen, und bestätige das Ganze mit Strg+Umschalt+Enter. Das gibt mir die Werte zurück und Sie sehen, ich habe einen kleinen Denkfehler gemacht: Meine Steigungen sind ja nicht 1.000, sondern 5.000, das heißt ich musste nicht mit 1.000 multiplizieren, sondern mit 5.000. Wenn ich das mache, dann habe ich wieder eine Übereinstimmung erzielt. Ich bekomme diese 825 angezeigt, und das ist genau das, was hier berechnet wird. Was ich nun machen könnte, wäre diese Trendwerte markieren und in mein Diagramm einfügen, aber es geht noch wesentlich besser. Ich gehe nun zu meinem Diagramm, blättere hier etwas nach rechts, markiere diese Linie und klicke auf die rechte Maustaste. Sie sehen, ich hätte hier entweder die Möglichkeit, meine Trendlinie direkt einzufügen - das könnte ich hier machen - und dann sehen Sie, Sie bekommen ein weiteres Dialogmenü, das es mir erlaubt, die Art dieser Trendlinie entsprechend auszuwählen. Dieser Trend könnte zum Beispiel ein linearer Trend sein - das ist das, was ich wähle - oder ich könnte zum Beispiel auch sagen "logarithmisch", "polynomisch", "potenzgleitender Durchschnitt", usw. Sie können ja mal mit diesen Möglichkeiten spielen, Sie werden dann sicherlich wieder zurückkehren zum linearen Trend. Jetzt interessiert mich natürlich nicht, was ich bisher habe, sondern wie die Prognose aussieht. Und zwar möchte ich 3 Perioden, bzw. dreimal 5.000 in die Zukunft planen. Dazu gehe ich hier auf "Vorwärts" und wähle "3" aus. Wenn Sie nun die Grafik betrachten, etwas hier herüber gehen, sehen Sie, dass diese Linie praktisch für 45.000, 50.000 und 55.000 in die Zukunft verschoben wird. Sie könnten hier übrigens auch noch wählen, wie diese Trendlinie weiterzubearbeiten ist. Sie sehen, hier wird "Trendlinie" angeboten. Ich könnte hier noch weitere Optionen wählen, das sind wie gesagt diese Trendlinienoptionen, die hier gerade schon erschienen sind. Die könnte ich auch über diese Schaltfläche hier auswählen. Ich möchte gerne noch eins machen, ich möchte - und dazu gehe ich etwas weiter nach unten - die Formel im Diagramm anzeigen. Was mich dann noch interessiert ist selbstverständlich, wie sieht das mit dem Bestimmtheitsmaß aus? Das Bestimmtheitsmaß ist der quadrierte Korrelationskoeffizient und der sagt wie groß die Ähnlichkeit ist zwischen einer gemessenen und einer prognostizierten Linie. Wäre dieses Maß 1, dann hat man eine exakte Überdeckung; ist es kleiner, dann nimmt der Wahrscheinlichkeitsgrad der Planung natürlich ab. Ich klicke jetzt hier drauf und Sie sehen, wir haben hier r², also das Bestimmtheitsmaß, von 0,9508. Das bedeutet, dass wir einen relativ hohen Wahrscheinlichkeitsgrad haben. Man geht üblicherweise davon aus, dass der Korrelationskoeffizient bei 0,9 liegen sollte; quadriere ich ihn, dann liege ich bei 0,81. Also mit 0,9508 habe ich hier einen recht guten Wert erzielt. Werfen Sie nochmals einen Blick auf diese zurückgegebene Formel, dann sehen Sie, y=153,75x+671,25. Damit hätten Sie jetzt die Möglichkeit, weitere Berechnungen durchzuführen. Übrigens, diese 153,75 ergeben sich aus der Berechnung von 0,03075 x 5.000. Das heißt, wenn Sie dann die Werte mit 10.000, 15.000, usw. einsetzen, dann erhalten Sie die entsprechenden Planwerte. Also, in unserem Fall könnten Sie hier die Trendwerte entsprechend eingeben, das heißt wenn ich hier, oder auch hier, diese Werte eingeben möchte, verwende ich meine Formel. Das heißt, das ist das Steigungsmaß, das allerdings hier absolut sein muss, mal die Stückzahl, die sich hier befindet - die bleibt relativ - plus das Steigungsmaß - ebenfalls wieder als Absolutwert - und er berechnet mir hier den entsprechenden geplanten Wert. Wenn ich den hier nach unten ziehe, dann sehen Sie wie das Ganze sich entwickeln würde. Sie können nun ablesen, ob es sich hier um noch interessante Werte für die Reparatur handelt. Noch ein kleiner Hinweis: Sie sollten natürlich nicht mit so krummen Zahlen für die Planung arbeiten, sondern die entsprechend aufrunden. Also 2.060, 2.210 oder 2.360 oder 2.370 als Werte dann in die Grafik entsprechend einsetzen. In diesem Beispiel haben Sie gesehen, wie sie mithilfe der Regressions- und der Trendfunktion Planungen durchführen können. Allerdings sind natürlich diese Planungen nur so gut, wie auch die Basiszahlen, auf denen sie ermittelt werden.

Excel 2013 für Profis

Holen Sie mehr aus Excel 2013 heraus und lernen Sie, Ihre Zahlen übersichtlicher darzustellen, gezielter zu analysieren sowie eigene Lösungen zu entwickeln.

7 Std. 56 min (96 Videos)
Derzeit sind keine Feedbacks vorhanden...
 
Hersteller:
Exklusiv für Abo-Kunden
Erscheinungsdatum:30.04.2013

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!