Predictive Analytics mit Excel und R: Positionierung und ... / 3.4 Dynamisches R

Ein großer Vorteil von R ist die Möglichkeit, dynamisch erstellten R-Code auszuführen. Beim Funktionsaufruf von R können Kodierungen zusammengesetzt und direkt ausgeführt werden. Das klingt abstrakt, hat aber den Vorteil, dass R von Excel heraus umfassend gesteuert werden kann.

3.4.1 Funktionen in R übernehmen und ausführen

Bei den bisherigen Darstellungen musste immer eine Funktion geschrieben werden, um R in Excel nutzen zu können. Dies führt bei intensiver Nutzung aber zu einer hohen Anzahl an Funktionen. Dies ist bei einer guten Strukturierung über Funktion-Kategorien unproblematisch. Jedoch erfordert dies immer eine gewisse Vorbereitung. Mit dynamischem R ist auch eine Direkteingabe von R-Code in Excel möglich. Es ist sogar möglich, ganze Skripte von Excel zu R zu übergeben und hier auszuführen. Auch die Parameterübergabe kann deutlich vereinfacht werden. Allerdings ist etwas Vorarbeit notwendig: Es muss eine generische Funktion verwendet werden. Kopieren Sie einfach diesen Code in Ihr Skript.^[1]

E.eval <- function(str,global=TRUE){

if (!is.null(str) ) {

ret <- try(eval(parse(text=str),if(global) envir= .GlobalEnv ),silent=TRUE)

if (class(ret) == “try-error“) ret <- paste(“Fehler: ”, ret, “ mit ”, str)

ret

}

}

"str" ist der Text, der an Excel übergeben wird. Dies kann ein Text, eine Zelle oder ein ganzer Bereich, der R-Code enthält, sein. Dieser Code muss den syntaktischen Regeln von R entsprechen. Die entscheidende Zeile lautet

try(eval(parse(text=str)))

Mit "try" können Fehler abgefangen werden und eine entsprechende Meldung an Excel zurückgegeben werden. Mit "if(class …)" können Sie Fehler selbst abfangen und bspw. Hinweise auf die Ursachen aufgetretene Probleme geben.

Die Funktion "parse" ruft den R-Interpreter auf und kann mehrere Variablen übernehmen (geben Sie "?parse" ein für weitere Informationen). Die Prüfung "(!is.null(str) )" ist notwendig, da R mit Null-Werten nicht so gut umgehen kann. Dies kann zu Abstürzen führen.

[1] Hier wird auch nochmals deutlich, warum ein zweiter Präfix verwendet wird. "eval" ist bereits in R definiert und würde sonst beschrieben werden.

3.4.2 Einfaches Beispiel: Multiplikation

Ein einfaches Beispiel: Speichern Sie das Skript und geben Sie in Excel ein:

=R.E.eval(“10*10”)

Die Excel-Zelle sollte 100 zeigen. Allerdings gibt es durchaus komfortablere Taschenrechner, sodass sinnvollere Abfragen eingegeben werden sollten. Das Gezeigte funktioniert auch mit Referenzen:

=R.E.eval(A18&“*20”)

Zur Verknüpfung von Zeichenketten kann das kaufmännische und verwendet werden. Verändern Sie nun A1 und Sie werden sehen, wie der Ausgabewert angepasst wird. Es können mit dieser Funktion auch ganze Sequenzen abgefragt werden. Im Text müssen die Anweisungen durch ein Semikolon abgegrenzt werden:^[1]

=R.E.eval(“a=10*10;b=a+10;c=b/a”)

Man kann die Zeilen für R auch direkt in Excel kodieren: Zeile für Zeile = Zelle für Zelle. Das erinnert ein wenig an die alte Makro-Sprache, die es im letzten Jahrtausend noch gab. Hierbei braucht kein Semikolon eingegeben werden, da der Zellenwechsel als Zeilenumbruch übergeben wird.

Abb. 10: Mini-R Skript in Excel

[1] Dies entspricht der Syntax von R, um Code kompakter zu halten. Ob dies ein guter Programmierstil ist, sei allerdings dahingestellt.

3.4.3 Erweitertes Praxisbeispiel: Zufallszahlen erzeugen

Machen wir nun etwas Pragmatisches mit R: Es sollen spezifisch verteilte Zufallszahlen erzeugt werden. Excel kennt als Basis für die Generierung von Zufallszahlen nur die Gleichverteilung. Über die Invertierung der in Excel vorhandenen Verteilungsfunktion (z. B. "NORM.INV()") können auch normal verteilte Zufallszahlen erzeugt werden. Aber eine im Risikomanagement gerne verwendete Dreiecksverteilung ist schon deutlich schwieriger in Excel zu implementieren. In R gibt es dazu ein Paket ("triangle"), sodass eine unmittelbare Verwendung ohne Programmierung möglich ist. Die folgende Rahmenfunktion könnte nun je nach Parameter unterschiedliche Verteilungsfunktionen nutzen.

E.zufallszahlen <- function(typ, anzahl=1,a=NA,b=NA,c=NA) {

a=switch(toupper(typ) ,

'GLEICH' = {ret=runif(anzahl)*ifelse(is.na(a),1,a)},

'NORMAL' = {ret=rnorm(anzahl,ifelse(is.na(a),0,a),ifelse(is.na(b),1,b))},

'BINOMIAL' = {ret=rbinom(anzahl,ifelse(is.na(a),1,a),ifelse(is.na(b),0.5,b))},

'DREIECK' = {

library(triangle)

ret <- rtriangle(anzahl,ifelse(is.na(a),0,a),ifelse(is.na(b),1,b),ifelse(is.na(c),0.5,c))

}

)

return(ret)

}

"switch" steuert die bedingte Verarbeitung. Je nach Parameter werden unterschiedlich verteilte Zufallszahlen zurückgegeben. Eine Erweiterung ist einfach möglich.

Die Nutzung dieser Funktion kann in Excel wie folgt vorgenommen werden: 1000 Zufallszahlen sollen binomialverteilt mit den Zuständen 0 und 1 erzeugt werden, wobei die Wahrscheinlichkeit einer 1 20 % betragen soll:

=R.E.zufallszahlen(“binomial”;1000;1;0,2)

Sie können die Funktionsfähigkeit einfach testen. Die Funktion kann als Matrixfunktion über einige hundert Zellen angelegt werden. Die folgende Formel zählt den Anteil der Zellen mit 1:

=ZÄHLENWENN(K8:K553;1)/ZEILEN(K8:K553)

Das Ergebnis ist bei mir (bei Ihnen wahrscheinlich ähnlich): 0,19230769. Das kommt den gewü...