Predictive Analytics mit Excel und "R": Einführung in die Verfahren

Zusammenfassung

• Predictive Analytics beginnt mit einfachen Verfahren wie Regression und Entscheidungsbäumen.
• Hierzu gibt es etablierte Algorithmen in R, die in Excel genutzt werden können.
• Die Ergebnisse können direkt innerhalb von Excel interpretiert und in bestehende Verfahren wie bspw. den Planungsprozess eingebunden werden.
• Komplexe Grafiken wie beispielsweise ein Entscheidungsbaum können dynamisch in Excel eingebunden werden.

1 Einige Predictive-Analytics-Methoden

Aufbauend auf den Grundlagen, die im Beitrag "Predictive Analytics mit Excel und R: Positionierung und erste Schritte" vorgestellt werden, beleuchtet dieser Beitrag nun die statistischen Methoden. Automatisierte Prognose heißt, aus den Daten der Vergangenheit zu lernen. Dies gilt für einfache statistische Methoden genauso wie für Machine Learning mit neusten Verfahren.

Mit der Regression und dem Entscheidungsbaumverfahren werden 2 einfache Verfahren vorgestellt. Diese können dann nach entsprechender Einarbeitung gegen Verfahren wie Neuronale Netze, Random Forest usw. ausgetauscht werden. Voraussetzung ist allerdings ein gutes Verständnis der Methoden, was den Rahmen einer solchen Einführung sprengen würde.

1.1 Zeitreihenanalyse

Der Klassiker der Statistik ist die Regression und diese ist in der Regel schnell erstellt. Eine solche Berechnung ist natürlich auch in Excel möglich, allerdings ist die Implementierung in R deutlich leistungsfähiger und mit den entsprechenden Vorkenntnissen auch einfach zu implementieren. Wir haben glücklicherweise Absatzdaten über 3 Jahre, so dass eine vernünftige Analyse möglich ist (s. Abb. 1).

Abb. 1: Zeitreihe in Excel

Nun gilt es, hieraus die Daten für 2018 zunächst mittels Regression zu schätzen. Bildlich gesprochen, wird eine Linie durch die zeitlich geordneten Punkte gezogen, wobei der vertikale Abstand zwischen der Linie und den Punkten minimiert wird (Abb. 2).

1.1.1 Daten aus Excel nach R übertragen

Wie kommen die Daten allerdings nach R? Übertragen werden sie aus Excel mit einer weiteren selbst geschriebenen BERT-Funktion:^[1]

E.defineDT = function(var,rng) {

library(data.table)

eval(parse(text=paste (var, "<<- data.table(range.to.data.frame(rng,TRUE))" )))

TRUE

}

Die Funktion "E.defineDT()" steht für "define DataTable". Damit kann R die Daten in einer für unsere Aufgabenstellung geeigneten Struktur übernehmen.^[2]"var" ist die Variable, die angelegt werden soll. Mit "range.to.data.frame" findet eine Konvertierung zur Tabellenstruktur statt. Der Excel-Name "Daten" umfasst den Datenausschnitt einschließlich der Feldbeschreibung. Tragen Sie einfach folgende Zeile in eine Excel-Zelle ein

=R.E.defineDT("Daten";Daten)

Durch "Return" wird die Funktion in R ausgeführt. Damit kennt R das Tabellenobjekt "Daten".

[1] Wie diese Funktion eingebunden wird erläutert der Beitrag "Predictive Analytics mit Excel und R: Positionierung und erste Schritte".

[2] Die Verwendung der Klasse "data.table" ist nicht unbedingt notwendig. Aber dieses Objekt ist sehr schnell und außerdem auch sehr komfortabel.

1.1.2 Regression in R ausführen

Eine Regression wird in R durch die Funktion "lm()" abgebildet. Die abhängige Variable wird durch eine Tilde von den unabhängigen Variablen (im Fall der Zeit die fortlaufende Variable "Period_Index") getrennt. Der folgende Code kann in ein oder 2 Excel-Zellen eingetragen werden und über die "R.E.eval()"-Funktion ausgeführt werden.

lin_model<-lm(Absatz ~ Period_Index,data=Daten)

pred=predict(lin_model,data.frame(Period_Index=61:72))

Über "predict()" kann eine Vorschau erzeugt werden. Die X-Achse wird quasi um die Werte 61 bis 72 verlängert. Daher möchte "predict()" auch den gleichen Datentyp haben: "Period_Index" wird als Vektor mit den Werten 61 bis 72 definiert.^[1] Über "data.frame()" findet eine Umwandlung in eine Datentabelle statt.

"lin_model" ist ein R-Objekt mit zahlreichen Ergebnisinformationen. Über "summary()" können Details zu diesem Objekt abgerufen werden. So können bspw. die folgenden Werte abgerufen werden:

• Regressionsparameter über "summary(lin_model)$coefficients";
• F-Test-Werte über "summary(lin_model)$fstatistic";
• T-Test über "summary(lin_model)$fstatistic".

[1] Der Doppelpunkt zwischen 61 und 72 erzeugt in R einen Vektor mit 12 Werten.

1.1.3 Ergebnis als Grafik darstellen

Das Ergebnis kann mit einer Grafik dargestellt werden. Die Grafik-Komponente in R funktioniert anders als in Excel, ist in vieler Hinsicht leistungsfähiger. In diversen Packages gibt es umfassende Erweiterungen.

Eingeleitet wird die Grafikerstellung über "BERT:graphics.device(<Name>)". "BERT.grahics.device()" überträgt eine in R erstellte Grafik (Plot) nach Excel. Diese Grafik ist dynamisch und wird bei jeder Code-Ausführung aktualisiert. Dieser Funktion kann ein Name als Parameter mitgegeben werden. Das hieraus erstellte Objekt hat in Excel die Kennung "BGD_<name>".

"plot()" ist der Standardbefehl für Diagramme in R. Über "xlim" und "ylim" werden die Achsenweiten begrenzt. Achsenbeschriftungen werden über "xlab" und "ylab" festgelegt. Mit dem "type" wird die Art (hier Linie) festgelegt. Mit "abline()" kann die Regressionsgerade gezeichnet werden. Hierzu werden die Regressionsparameter eingesetzt.

BERT.graphics...