Predictive Analytics mit Excel und R: Positionierung und ... / 3.5 Einige Klippen

Nun kann es vorkommen, dass die R-Aufrufe in Excel-Zellen voneinander abhängig sind. So kann eine R-Funktion eine Variable verändern, die von einer anderen Funktion aufgerufen wird. So berechnet bspw. eine Zelle eine Regression, wobei R hier sehr viele Informationen (Bestimmtheitsmaß, Testergebnisse etc.) intern in einer Ergebnisvariablen speichert. Die Inhalte sollen in anderen Excel-Zellen immer aktuell angezeigt werden.

Damit ist auch die Reihenfolge der Ausführung wichtig. Die Reihenfolge der Abarbeitung der Berechnungen bleibt allerdings Excel vorbehalten, wenn diese nicht voneinander abhängig sind, also eine Zelle auf eine andere referenziert. Wie kann dies also aktiv gesteuert werden? Eine Steuerung kann durch eine künstliche Verkettung der Excel-Zellen erfolgen. Excel erzeugt dann einen optimalen Kalkulationsplan, der unseren Wünschen entspricht. Ein Beispiel: K3 enthält einen Basiswert von 14. Ohne Verkettung wird bei Änderung der Zelle K3 nur K4 automatisch aktualisiert. K5 bleibt auf dem alten Wert. K6 enthält hingegen eine Referenz auf K4.

Abb. 11: Abhängigkeiten der R-Funktionsaufrufe

Eine weitere Herausforderung liegt in der Übergabe von Zahlen mit Nachkommastellen. Bei der Übergabe über explizite Variablen werden diese korrekt als Werte deklariert. Bei der Übergabe über Zeichenverkettungen wird nicht richtig zwischen Dezimalpunkt und –komma konvertiert. R benötigt generell den Punkt, in Excel hängt das von der Benutzereinstellung ab. Ein Beispiel:

In der Zelle steht J24 steht 14,1. Die Funktion

=R.E.eval(“a=”&J24)

läuft auf einen R-Fehler, da "a=14,1" und nicht "a=14.1" übergeben wird.

Dieses Problem tritt nur auf, wenn R-Code aus Excel interpretiert werden muss. Zwar kann man das über die Excel-Funktion "wechseln" vermeiden, aber das kann durchaus mühselig und auch unübersichtlich werden:

=R.E.eval("a="&WECHSELN(J24;",";"."))

Über eine R-Funktion hingegen kann dieses Problem elegant gelöst werden: Die Funktion "E.d()" tauscht einfach Komma gegen Punkt aus:

d = function(s)

{

gsub(“,”,“.”,s)

}

"gsub" steht für "global substitution". Die Parametrisierung tauscht alle Kommas gegen Punkte aus.

=R.E.eval(“a=”&R.d(J24))

In Excel kann, wie dargestellt, das Script i. d. R. auf Basis anderer Zellen zusammengesetzt werden. Wenden wir uns nochmal unserer Mittelwertfunktion zu. So richtig elegant sieht das mit der dynamischen Version über "R.E.eval()" nicht aus:

=R.E.eval("mean("&E15&","&E17&")")

Der Verweis auf Zellbereiche funktioniert dynamisch nicht. Problematischer wird es, wenn noch ganze Zellbereiche übernommen werden sollen.

Wenn also Blöcke übergeben werden, empfiehlt sich eine andere Vorgehensweise. Zu übergebende Parameter werden explizit als Variablen definiert. Hierzu wird eine weitere selbst geschriebene Funktion eingesetzt. Dies ist die R-Funktion.

E E.define = function(x,vl){

tempval <<- vl; asString <<-asString; x <<- x

try(eval(parse(text = paste(x, “<<-vl”)) ),silent=TRUE)

}

Damit ist es möglich, eine Variable mit einem Zellbereich zu belegen. Diese Funktion kann auch noch objektspezifisch aufgewertet werden. So können Matrizen in R bspw. Zeilen- und Spaltennamen enthalten. Dies erleichtert die Adressierung. Hier als Beispiel die Definitionsfunktion für eine Matrix:

E.defineM = function(var,rng) {

eval(parse(text=paste (var, “<<- matrix(as.numeric(rng[2:nrow(rng),2:ncol(rng)]),c(nrow(rng)-1,c(ncol(rng)-1)),dimnames = list(rng[2:nrow(rng),1],rng[1,2:ncol(rng)]))” )))

0

}

Genutzt werden kann die Funktion wie folgt:

Abb. 12: Definition einer Matrix und Referenzierung in R