Januar 2018

Band 33, Nummer 1

Office: Datenanalyse, Machine Learning und Microsoft Excel

Von Michael Saunders | Januar 2018

Nehmen Sie sich einen Moment Zeit, um über Excel als Ganzes nachzudenken: Es handelt sich um ein flexibles, leistungsstarkes, allgegenwärtiges Tool für die Analyse von Daten – von meiner praktischen kleinen Aufgabenlistentabelle bis hin zu massiven Finanzmodell-Arbeitsmappen mit 100 MB bei den weltweit führenden Banken. Hinter all den raffinierten Features, den ausgefeilten Diagrammen und Formatierungen besteht der eigentliche Wert von Excel in den Berechnungen. Mit diesen Berechnungen können Sie Beziehungen zwischen Zellen herstellen: Komplexe Modelle werden durch das Schreiben einfacher Formeln ausgedrückt, um die Abhängigkeitsbäume zwischen den Werten zu beschreiben (siehe Abbildung 1). Wenn Sie anschließend eine Änderung vornehmen, aktualisiert die Berechnung die abhängigen Werte auf der Grundlage dieser Formeln.

Abbildung 1: Mit Berechnungen können Sie Beziehungen zwischen Zellen herstellen

Excel bietet eine Vielzahl hilfreicher Funktionen, die Sie in Ihren Formeln verwenden können, von der unverzichtbaren AVERAGE-Funktion (MITTELWERT) (bit.ly/1Tjynwl) über Zeichenfolgenanalyse-Funktionen wie SEARCH (SUCHEN) zum Auffinden von Teilzeichenfolgen (bit.ly/2jhcEuV) bis hin zu komplexeren Berechnungen wie der Statistikfunktion T.TEST (bit.ly/2ipowKE). Benutzer im Berufsleben und in Bildungseinrichtungen lernen Excel-Funktionen für die Verwendung in Formeln, und sie interagieren mit ihnen auf natürliche Weise bei ihren täglichen Berechnungen. Wenn Sie also ein leistungsstarkes Feature bereitstellen möchten, über das Excel noch nicht verfügt, sollten Sie in Erwägung ziehen, eine Excel-Funktion für diesen Zweck zu erstellen. Ein Beispiel finden Sie in Abbildung 2: Contoso Cryptographers Corp. möchte eine Lösung veröffentlichen, mit der Detektive Daten in Excel analysieren können, um Nachrichten zu decodieren. Und eines der praktischen Tools zum Knacken von Code ist die schnelle Erkennung von Primzahlen. Deshalb würde Contoso gerne eine ISPRIME-Funktion für die Millionen von Detektiven bereitstellen, die bereits Excel verwenden. Später in diesem Artikel werden Sie sehen, wie Contoso diese Funktion und die anderen Funktionen erstellt, die für ein solches Add-In benötigt werden.

Abbildung 2: Die Contoso-Funktion zum Identifizieren von Primzahlen

Was genau kann ich erstellen?

Bevor Sie mit dem Erstellen beginnen, sollten Sie bedenken, dass nicht alle Erweiterungen der Excel-Funktionalität Excel-Formeln verwenden sollten. Um zu entscheiden, ob ihre Erweiterung Formeln verwenden sollte, fragen Sie sich Folgendes: „Muss meine Funktion irgendetwas außer dem Excel-Wert ändern, der zurückgegeben wird?“ Mit anderen Worten, besitzt die Funktion Nebenwirkungen? In Excel erwarten Benutzer, dass Formeln nichts außer der Zelle ändern, in die sie eingegeben werden. Diese Änderung könnte andere Veränderungen auslösen, aber diese wären auch das Ergebnis eigenständiger Formeln ohne Nebenwirkungen. Wenn Sie „=SUM()“ (=SUMME()) in die Zelle A1 eingeben, erwarten Sie nicht, dass ein Diagramm irgendwo auf dem Blatt angezeigt, eine neue Zeile darunter eingefügt oder eine Zahl in einer Finanzdatenbank im Internet geändert wird, obwohl Sie alle diese Verhaltensweisen als Teil einer von Ihnen erstellten Lösung steuern können, die Funktionen und andere Funktionalität enthält.

Die Art der Lösung, die Sie erstellen sollten, um Ihre Excel-Funktion zu erstellen, hängt von Ihren Fähigkeiten und Zielen ab. Wenn Sie ein professioneller Entwickler sind und einen externen Vertrieb wie Contoso Cryptographers vorsehen oder für Benutzer in Ihrer eigenen Organisation Lösungen entwickeln, ist ein Add-In das richtige Tool für Datenanalyselösungen in Excel, wie im Abschnitt „Benutzerdefinierte JavaScript-Funktionen in Excel-Add-Ins“ erläutert wird. Wenn Sie ein KI-Entwickler oder Data Scientist sind, der intelligente Modelle für Datenanalysten erstellen möchte, die in Ihrem Unternehmen verwendet werden können, lesen Sie den Abschnitt „Azure Machine Learning-Funktionen“ weiter unten in diesem Artikel. Und wenn Sie über keine dieser Fähigkeiten verfügen, wird Microsoft in Zukunft Lösungen für Sie bereitstellen!

Benutzerdefinierte JavaScript-Funktionen in Excel-Add-Ins

Excel-Add-Ins sind Tools, die professionelle Entwickler erstellen können, um Excel zu erweitern und mit den Daten in Arbeitsmappen zu interagieren (bit.ly/2AUOsZk). Tausende von Add-Ins sind bereits verfügbar und können auf allen Excel-Plattformen ausgeführt werden, einschließlich Windows, Mac, Excel Online und sogar iOS. Jeder, der mit Webtechnologien vertraut ist, kann Add-Ins auf einfache Weise erstellen, weil sie wie Webseiten geschrieben werden: Add-Ins führen HTML, JavaScript und CSS aus und bieten die Möglichkeit, externe Webserver aufzurufen. Und das Beste ist, dass die Add-Ins keine Codeänderungen benötigen, um auf allen Plattformen ausgeführt werden zu können. (Excel unterstützt auch andere Arten von Add-Ins (wie unter bit.ly/2qsPfLe beschrieben), aber diese können nicht plattformübergreifend ausgeführt oder im Store bereitgestellt werden.) Abbildung 3 zeigt die wichtigsten Bestandteile eines Add-Ins. Das Kernstück ist die Datei „manifest.xml“, die die Webadresse des restlichen Add-Ins und andere Metadaten angibt. Der Rest des Codes wird vom Entwickler bereitgestellt (wie bei jeder anderen Webanwendung auch).

Abbildung 3: Die wichtigsten Bestandteile eines Add-Ins

Add-Ins bieten viele sinnvolle Möglichkeiten, Excel zu erweitern (siehe Dokumentation unter bit.ly/2AV47rw). Sie können Benutzeroberflächenelemente wie Schaltflächen des Menübands, Kontextmenüoptionen und HTML-Aufgabenbereiche und Dialogfelder hinzufügen. Sie können über Tausende von APIs mit Arbeitsmappen interagieren, z.B. Zeilen hinzufügen und entfernen, Diagramme einfügen und bearbeiten und sogar Zellen formatieren. Und jetzt ist es mit dem neuen Previewrelease der benutzerdefinierten JavaScript-Funktionen möglich, Excel-Formeln zu erweitern.

Benutzerdefinierte Funktionen erlauben es Entwicklern, Excel beliebige JavaScript-Funktionen mithilfe eines Add-Ins (bit.ly/2AYtNUW) hinzuzufügen. Benutzer können dann auf benutzerdefinierte Funktionen wie auf jede andere native Funktion in Excel zugreifen (z.B. AVERAGE (MITTELWERT)). Werfen wir einen genaueren Blick auf die ISPRIME-Funktion von Contoso Cryptographers, um zu sehen, wie sie geschrieben wurde (es ist auch möglich, Primzahlen ausschließlich mit integrierten Excel-Funktionen zu überprüfen, aber das ist für den Benutzer, der die Berechnung anfordert, viel schwieriger):  

function isPrime(n) {
  var root = Math.sqrt(n);
  if (n < 2) return false;
  for (var divisor = 2; divisor <= root; divisor++){
    if(n % divisor == 0) return false;
  }
  return true;
}

Die Funktion überprüft einfach alle möglichen Divisoren bis zur Quadratwurzel, um festzustellen, ob die Eingabe eine Primzahl ist. Dafür sind nur wenige Zeilen JavaScript erforderlich. Die gesamte eigentliche Programmlogik ist bereits fertig. Aber es muss mehr als nur die Funktion selbst geschrieben werden. Um ein effektiver Erweiterungspunkt zu sein, müssen benutzerdefinierte Funktionen genau so aussehen und sich anfühlen wie native Funktionen. Und native Funktionen verfügen über eine Vielzahl von angepassten Informationen. Abbildung 4 zeigt ein Beispiel mit der ISPRIME-Funktion von Contoso Crypographers: Zusätzlich zum Namen der Funktion selbst zeigt Excel eine hilfreiche Beschreibung an, um den Zweck der Funktion zu verdeutlichen.        

Abbildung 4: Benutzerdefinierte Funktionen und native Funktionen werden automatisch während der Eingabe vervollständigt

Ein weiteres Beispiel sehen Sie in Abbildung 5. Nachdem CONTOSO.ISPRIME ausgewählt wurde, zeigt Excel den Namen des Parameters (in diesem Fall „n“) an, um die Auswahl der richtigen Eingaben zu erleichtern.

Abbildung 5: Die ISPRIME-Funktion von Contoso

Excel.Script.CustomFunctions["CONTOSO"]["ISPRIME"] = {
  call: isPrime,
  description: "Determines whether the input is prime",
  helpUrl: "https://example.com/help.html",
  result: {
    resultType: Excel.CustomFunctionValueType.boolean,
    resultDimensionality: Excel.CustomFunctionDimensionality.scalar,
  },
  parameters: [{
    name: "n",
    description: "the number to be evaluated",
    valueType: Excel.CustomFunctionValueType.number,
    valueDimensionality: Excel.CustomFunctionDimensionality.scalar,
  }],
  options: { batched: false, streaming: false }
};

Diese beiden Informationen und vieles mehr stellt der Entwickler in der JavaScript-Definition der Metadaten der Funktion bereit, wie Sie in Abbildung 5 sehen können.

Sie sehen, dass die Beschreibung als Zeichenfolge angegeben ist, ebenso wie der Name jedes Parameters. Ich werde hier nicht auf alle Metadaten eingehen, aber Sie finden weitere Informationen in der Dokumentation. Wenn Sie mit dem Add-In-Modell vertraut sind, werden Sie sich vielleicht fragen, warum diese Informationen in JavaScript bereitgestellt werden und nicht statisch irgendwo hartcodiert sind (etwa in der Datei „manifest.xml“). Der Grund dafür ist die Flexibilität. Im Fall von Contoso sind die Kryptografiefunktionen definiert und im Vorfeld bekannt. Aber manchmal möchten Sie vielleicht die Möglichkeit haben, verschiedene Funktionen in verschiedenen Situationen zu aktivieren.

Contoso Cryptographers liebt die Einfachheit der ISPRIME-Funktion, aber das nächste Ziel ist etwas schwieriger: Sie möchten eine Funktion erstellen, um Zufallszahlen für die Verschlüsselung von Text zu generieren. Excel verfügt über eine ausgezeichnete RAND-Funktion (ZUFALLSZAHL), aber das Problem ist, dass RAND nicht ideal für die Verschlüsselung ist, weil die Werte pseudozufällig sind und prozedural generiert werden. Im Gegensatz dazu sind Zahlen, die von random.org generiert werden, für diesen Zweck sicher zu verwenden: Sie werden aus atmosphärischem Rauschen generiert. Natürlich ist es nicht gut, Zufallszahlen hart zu codieren. Stattdessen muss Contoso eine Funktion entwerfen, die random.org aufrufen kann, um Zufallszahlen über eine HTTP-Anforderung abzurufen. Glücklicherweise erleichtern benutzerdefinierte Funktionen das Einbinden von Webanforderungen. So sieht die asynchrone RANDOM-Funktion von Contoso aus:

function getRandom(min, max) {
  return new OfficeExtension.Promise(function(setResult, setError){
    sendRandomOrgHTTP(min, max, function(result){
      if(result.number) setResult(number);
      else setError(result.error);
    });
  });
}

Der Hauptunterschied zwischen dieser Funktion und ISPRIME besteht darin, dass RANDOM asynchron ist: Anstatt einen Wert an Excel zurückzugeben, wird sofort eine JavaScript-Zusage zurückgegeben, dann wird ein XMLHttpRequest für den Dienst random.org ausgeführt (nicht sichtbar in der Funktion, da die Anforderung in der sendRandomOrgHTTP-Methode erfolgt). Sobald der Webdienst geantwortet hat, löst Contoso die Zusage mit der random.org-Zufallszahl auf, um sie in die Zelle zu schreiben. Das Einbinden von Webdaten in Excel ist einer der Hauptgründe, warum Unternehmen Excel-Funktionen erweitern möchten, sodass Microsoft davon ausgeht, dass asynchrone Funktionen beliebt sind.

Ein gemeinsamer Aspekt aller asynchronen Funktionen besteht darin, dass sie einige Zeit benötigen, um ihr Ergebnis zurückzugeben, sodass Excel eine #GETTING_DATA-Meldung in der Zelle anzeigt, während auf die Auflösung der Funktion gewartet wird (siehe Abbildung 6).

Abbildung 6: Warten auf die Rückgabe einer asynchronen Funktion

In diesem Fall kann die HTTP-Anforderung schnell abgeschlossen werden (in etwa einer Zehntelsekunde). Wenn aber erforderlich, kann die Funktion viele Aufrufe vornehmen, und die Auswertung kann in diesem Fall länger dauern. Bei benutzerdefinierten JavaScript-Funktionen bewirkt die Auflösung des Werts in JavaScript, dass er sofort in der Zelle angezeigt wird. Abbildung 7 zeigt eine Excel-Ansicht der gleichen RANDOM-Funktion, nachdem der Wert zurückgegeben wurde.

Abbildung 7: Eine Excel-Ansicht der RANDOM-Funktion, nachdem der Wert zurückgegeben wurde

Natürlich kann ein Add-In, das benutzerdefinierte Funktionen verwendet, auch viele andere Features aufweisen: Contoso Cryptographers möchte vielleicht einen Aufgabenbereich und eine Registerkarte des Menübands erstellen, um Kunden einfachen Zugriff auf die benutzerdefinierten Funktionen, die Einstellungen für ihr Verhalten und die Anleitungen für ihre Verwendung zu ermöglichen. Möglicherweise könnte die JavaScript-API sogar dazu verwendet werden, Kunden ein ganzes Arbeitsblatt mit einem Klick auf eine Schaltfläche verschlüsseln zu lassen. Unabhängig davon, wie das Unternehmen sich entscheidet, kann es sich sicher sein, dass das gesamte Add-In ohne Codeänderungen auf Excel-Plattformen ausgeführt werden kann.

Es gibt je nach Zielgruppe zwei Hauptwege, um ein Add-In zu implementieren, das diese benutzerdefinierten JavaScript-Funktionen enthält. Der öffentliche Office Store (bit.ly/2A70L5o) steht jedem Benutzer zur Verfügung, der über Excel verfügt. Es ist sogar eine Schaltfläche verfügbar, um den Store im Menüband von Excel zu durchsuchen. Der Store unterstützt kostenlose und kostenpflichtige Add-Ins und erfordert die Übermittlung und Validierung durch Microsoft. Contoso Cryptographers Corp. führt die Bereitstellung jedoch direkt für seine kleinen Geschäftskunden durch, sodass diese auf nichts klicken müssen, um das Add-In zu installieren. Stattdessen wird jedem Kunden die Manifestdatei zur Verfügung gestellt. Dann kann ein IT-Administrator über das O365 Admin Center auswählen, welche Benutzer Zugriff erhalten (siehe Benutzeroberfläche in Abbildung 8). Für diese Benutzer wird das Add-In automatisch installiert.

Abbildung 8: Bereitstellen einer benutzerdefinierten Funktion in einer Organisation

Hoffentlich haben Sie sich vom findigen Unternehmen Contoso Cryptographers inspirieren lassen und versuchen, Ihre eigenen benutzerdefinierten Funktionen zu schreiben (verwenden Sie die Anleitung und die Beispiele unter aka.ms/customfunctions). Nachfolgend werde ich den anderen neuen Weg zur Erweiterung von Excel-Formeln untersuchen.

Azure Machine Learning-Funktionen

Die zweite Art der erweiterbaren Funktionen, die Microsoft angekündigt hat, sind Azure Machine Learning-Funktionen (abgekürzt Azure ML). Im Gegensatz zu benutzerdefinierten JavaScript-Funktionen werden Azure ML-Funktionen von KI-Entwicklern (häufig Data Scientists und andere Experten) erstellt, damit sie Analysten in ihrer Organisation verwenden können. Daher ist es nicht erforderlich, ein Add-In-Paket zu erstellen, um eine Azure ML-Funktion bereitzustellen.

Die Azure ML-Funktion selbst basiert auf einem Dienst, der Werte auf der Grundlage eines ML-Modells (Machine Learning) berechnet oder vorhersagt. Sobald das Modell erstellt wurde, können die Entwickler es für jeden Benutzer aktivieren, den sie auswählen. Jedes Mal, wenn ein Benutzer die Funktion ausführen möchte, nimmt er einfach eine Eingabe in einer Zelle vor, genau wie bei jeder anderen Excel-Funktion. Die Funktion ruft einen Livewebdienst für das Azure-Abonnement des Unternehmens auf und gibt das Ergebnis asynchron zurück.

Beispielsweise könnte ein Marketinganalyst bei einem Einzelhändler die Nachfrage nach neuen Produkten in verschiedenen geografischen Regionen vorhersagen wollen. Der Analyst verfügt über einige Daten zu diesem Produkt und den Zielmärkten in Excel. Das Ziel besteht darin, eine einfache Funktion abzurufen, mit der der Analyst die Nachfrage in jedem Markt prognostizieren kann, ohne Excel jemals verlassen zu müssen. Abbildung 9 zeigt eine Beispieltabelle mit dieser Art von Daten.

Abbildung 9: Beispiel für Produkt- und Marktdaten, die mit einer Azure ML-Funktion analysiert werden sollen

Ein KI-Entwickler in dieser Organisation verwendet Azure ML-Dienste (bit.ly/2nwa0WP), um das Modell zu erstellen: Er beginnt mit der Vorbereitung von Trainingsdaten (normalerweise aus einer externen Datenbank), um ein ML-Experiment zu erstellen, das auf historischen Verkäufen für neue Produkte basiert. Azure Machine Learning Workbench ist ein Tool, das die Datenvorbereitung vereinfacht und den Data Scientist Python-Code schreiben lässt, um Modelle zu trainieren und auszuwerten. Abbildung 10 zeigt die Ansicht „Dashboard ausführen“ in Azure ML Workbench, die Statistiken zu einem Experiment anzeigt.

Abbildung 10: Azure Machine Learning Workbench

Sobald das Modell fertig ist, kann es als Webdienst im Azure-Abonnement des Unternehmens mit Zugriffsberechtigungen für die richtige Gruppe von Benutzern bereitgestellt werden. Anstatt die Metadaten in einer lokal ausgeführten Instanz von JavaScript zu definieren, werden die Metadaten in einem Swagger-Format (RESTful API) definiert, das automatisch zusammen mit dem bereitgestellten Dienst erstellt wird. Damit der Dienst als Funktion in Excel angezeigt wird, muss der Analyst in Excel lediglich die Berechtigung für den Zugriff auf den Dienst besitzen. Abbildung 11 zeigt die bereitgestellten Funktionen, die den Datenanalysten in Excel angezeigt werden. Die Analysten können die Funktion dann normal ausführen. Genau wie bei den asynchronen JavaScript-Funktionen zeigt die Zelle eine #GETTING_DATA-Meldung an, während der Dienst das Ergebnis berechnet.

Abbildung 11: Die in Excel angezeigten bereitgestellten Funktionen

Die Zukunft erweiterbarer Funktionen in Excel

Ich hoffe, dass Sie die Technologiepreviews für erweiterbare Funktionen in Excel testen werden: benutzerdefinierte JavaScript-Funktionen und -Add-Ins, wenn Sie ein Software- oder Dienstleistungsanbieter sind, und Azure ML-Funktionen, wenn Sie ein KI-Entwickler oder Data Scientist sind. Microsoft berücksichtigt während der Previewphase das gesamte Feedback (posten Sie Ideen auf der UserVoice-Seite unter bit.ly/2jRJQsu), sodass Sie nicht nur einen Vorsprung, sondern auch die Möglichkeit nutzen können, das endgültige veröffentlichte Produkt zu gestalten. Es gibt noch zahlreiche weitere Verbesserungen für diese beiden Funktionstypen und einige völlig neue Funktionstypen. Bleiben Sie also auf dem Laufenden mit den zukünftigen Ankündigungen!

Michael Saunders  ist Program Manager im Office-Team, in dem er Excel-Features für Entwickler erstellt. Er stammt ursprünglich aus Toronto (Kanada) und hat Management und Materialwissenschaften an der University of Pennsylvania und in Wharton studiert. In seiner Freizeit singt Saunders bei den Seattle Esoterics und entwickelt Add-Ins.

Unser Dank gilt den folgenden technischen Experten von Microsoft für die Durchsicht dieses Artikels: Yina Arenas, Ashvini Sharma, Sandhya Vankamamidi
Yina Arenas arbeitet als Principal Program Manager bei Microsoft und ist Leiterin der Microsoft Graph-Gruppe. Sie überführt Office- und Microsoft-APIs aus veralteten, unzusammenhängenden Technologien in eine neue, vereinheitlichte API-Welt.
Ashvini Sharma arbeitet in leitender Funktion als Program Manager im Excel-Team und ist für die Vision und Roadmap von Excel Analytics verantwortlich.
Sandhya Vankamamidi arbeitet in leitender Funktion als Program Manager im Information and Content Consumption Experiences-Team. Sandhya ist verantwortlich für den Entwurf und die Bereitstellung von beeindruckenden Lösungen für die gesamte Produktfamilie der Bing-Apps.

Diesen Artikel im MSDN Magazine-Forum diskutieren