Predictive Analytics mit Excel und R: Positionierung und ... / 3 Der Einstieg

3.1 Die Basis: Definition einer Funktion

Nun können Sie direkt loslegen. Aus didaktischen Gründen wähle ich zunächst eine einfache Funktion, die Mittelwertberechnung, die natürlich schon in Excel verfügbar ist. Man kann anhand dieser Funktion das Zusammenspiel zwischen Excel und R gut zeigen. Der Mittelwert wird in R über die Standardfunktion "mean()" abgebildet.

Wie in VBA wird eine Funktionsdefinition in R für die Bereitstellung in Excel benötigt. Ich nenne diese Funktion "E.Mittelwert()". Damit soll kenntlich gemacht werden, dass es sich hierbei um eine speziell für Excel entwickelte Funktion handelt. So werden Überschneidungen mit bestehenden R-Funktionen vermieden.

Eine Funktion wird in R durch das Schlüsselwort "function" definiert. Der Funktionsrumpf wird in geschweifte Klammern gesetzt. Ohne auf die syntaktischen Feinheiten einzugehen, sei auf die folgende Definition verwiesen:

E.Mittelwert <- function(area)

{

mean(area,na.rm=TRUE)

}

R unterscheidet zwischen Groß- und Kleinschreibung. Dabei dominiert eindeutig die Kleinschreibung bei Befehlen und Variablen. Zuweisungen können mittels "=" oder "<-" erfolgen. Letzteres ist ungewöhnlich, kennzeichnet aber den R-Liebhaber.

"area" ist der Parameter, der aus Excel übergeben wird. Dieser wird an die Funktion "mean()" weitergeleitet. Weiterhin ist es wichtig, zu entscheiden, wie mit NULL- Werten umgegangen werden soll. Falls diese auftreten können, sind sie bei der Funktion "mean()" explizit durch den Parameter "na.rm" ("remove not applicable") auszuklammern.

Geben Sie diese Funktion in das Skript in der BERT-Konsole ein und wählen Sie "Speichern" im Datei-Menü (alternativ STRG + S). Beim Speichern werden die Funktionen automatisch für Excel aktualisiert bzw. bekannt gegeben.

Passen Sie aber auf, dass Ihnen kein Syntax-Fehler unterläuft. R moniert das zwar, aber man kann dies leicht übersehen:

Error in source(“D:\\MCT\\OneDrive\\Projekte_DB\\BERTI\\functions\\functions.R”) :

D:\MCT\OneDrive\Projekte_DB\BERTI\functions\functions.R:95:7: unexpected '*'

94: # eval(parse(text=paste0("s$",lapply(ls(s),paste))[2]))[1]

95: s = 1+*

Die Folge ist, dass nichts aus dem jeweiligen Script aktualisiert wird und somit auch keine neue Funktion erstellt wird.^[1]

Wenn Ihnen die Syntax nicht bekannt ist, geben Sie im Direktfenster ein Fragezeichen ein, gefolgt von dem Befehl. Dadurch öffnet sich der Browser mit hilfreichen Informationen.

Abb. 5: Hilfe zu mean()

[1] Änderungen in der Funktionsbezeichnung werden übrigens auch nicht aktualisiert. Es stehen dann 2 Funktionen bereit. Erst bei Neustart von Excel findet eine Bereinigung statt.

3.2 Funktionen von BERT in Excel testen

Nach dem Speichern können Sie diese Funktionalität in Excel direkt testen. BERT setzt für die Nutzung noch mal ein Präfix "R." davor. Unsere Funktion heißt jetzt "R.E.Mittelwert()". Damit werden Überschneidungen zu Excel-Funktionen vermieden. Das ist zwar nun etwas gewöhnungsbedürftig, aber damit sind Sie auf der sicheren Seite. Seiteneffekte erhöhen die Fehleranfälligkeit, was Ihnen jeder Softwareentwickler bestätigen kann.

Die Funktion können Sie am einfachsten auswählen, indem Sie nach dem Gleichheitszeichen "=" "R.E" eingeben. Dann erscheint eine Liste der bereits erstellten und verfügbaren R-Funktionen zur Auswahl.

Abb. 6: Funktionseingabe in Excel

Sie können auch den Formel-Wizard nutzen. Dazu ist es allerdings hilfreich, die Funktion für diese Verwendung in R/BERT genauer zu beschreiben. Auch das kann direkt im R-Skript erfolgen. Geben Sie im Skript in die Konsole ein:

attr(E.Mittelwert,“category”) <- “Komplexe Statistik”

In Excel haben Sie jetzt eine neue Formel-Gruppierung.

Abb. 7: Funktionskategorien in Excel

Dann können Sie die Funktion und die Parameter noch genauer beschreiben, wenn Ihr Werk nicht nur für Sie alleine ist. Geben Sie dazu im Skript die untenstehende Formel ein (nach der Funktionsdefinition). In Excel sieht das wie in Abb. 8 dargestellt aus.

attr( E.Mittelwert, “description”) <-

list( “Mittelwertbildung mit R”,

Bereich = “der Zell-Bereich muss angegeben werden”,

optional = “Machen Sie doch was Sie wollen!”);

Abb. 8: BERT-Funktionsauswahl in Excel

3.3 Arbeiten mit der neuen Funktion

Lassen Sie uns nun mit unserer neuen Funktion arbeiten. Markieren Sie einen Zellbereich, der einige Zahlen und eine leere Zelle enthält. Löschen Sie jetzt eine oder mehrere Zellen aus dem Wertebereich (z. B. Zelle I31 aus Abb. 6. Sie sehen, diese Zellen-Werte werden nicht mehr mitgezählt.

Die Übergabe von Zellbereichen nach R ist offensichtlich recht einfach. Allerdings besteht häufig die Notwendigkeit, nicht nur einen einzigen Parameter zurückzugeben, sondern ganze Bereiche, z. B. eine Datenbankfrage oder die Regelmenge eines Entscheidungsbaumverfahrens.^[1] Diese Notwendigkeit besteht auch bei Excel-Funktionen und wird von Excel schon lange unterstützt. Sog. Matrix-Funktionen erlauben die Rückgabe mehrerer Zellen. Die Ergebnisse einer Regression ("=RGP()"), z. B. die Regressionskoeffizienten, können nur mittels Matrixfunktionen dargestellt werden. Matrix-Funktionen haben aber auch andere Vorteile. Gegenüber dem Kopieren von Forme...