Freigeben über

Lernprogramm: Erstellen benutzerdefinierter Funktionen in Excel

  • Artikel

Mithilfe benutzerdefinierter Funktionen können Sie Excel neue Funktionen hinzufügen, indem Sie diese in JavaScript als Teil eines Add-Ins definieren. Benutzer können auf benutzerdefinierte Funktionen in Excel auf die gleiche Weise zugreifen wie auf die nativen Funktionen in Excel (beispielsweise SUM()). Sie können benutzerdefinierte Funktionen erstellen, die einfache Aufgaben wie Berechnungen oder auch komplexere Aufgaben wie etwa das Streamen von Echtzeitdaten aus dem Web in ein Arbeitsblatt ausführen.

In diesem Lernprogramm wird Folgendes vermittelt:

  • Erstellen eines Add-Ins für eine benutzerdefinierte Funktion mithilfe des Yeoman-Generators für Office Add-Ins.
  • Verwenden einer vorgefertigten benutzerdefinierten Funktion für eine einfache Berechnung.
  • Erstellen einer benutzerdefinierten Funktion, die Daten aus dem Web abruft.
  • Erstellen einer benutzerdefinierten Funktion, die Echtzeitdaten aus dem Web streamt.

Voraussetzungen

  • Node.js (die aktuellsteLTS-Version). Besuchen Sie die Node.js Website , um die richtige Version für Ihr Betriebssystem herunterzuladen und zu installieren.

  • Die neueste Version von Yeoman und des Yeoman-Generators für Office-Add-Ins. Um diese Tools global zu installieren, führen Sie den folgenden Befehl an der Eingabeaufforderung aus.

    npm install -g yo generator-office

    Hinweis

    Selbst wenn Sie bereits den Yeoman-Generator installiert haben, empfehlen wir Ihnen, das npm-Paket auf die neueste Version zu aktualisieren.

  • Office in Verbindung mit einem Microsoft 365-Abonnement (einschließlich Office im Internet).

    Hinweis

    Wenn Sie noch nicht über Office verfügen, können Sie sich über das Microsoft 365-Entwicklerprogramm für ein Microsoft 365 E5-Entwicklerabonnement qualifizieren. Weitere Informationen finden Sie in den häufig gestellten Fragen. Alternativ können Sie sich für eine kostenlose 1-monatige Testversion registrieren oder einen Microsoft 365-Plan erwerben.

Erstellen eines Projekts für benutzerdefinierte Funktionen

Erstellen Sie zu Beginn das Codeprojekt, um dass Add-In für die benutzerdefinierte Funktion zu erstellen. Der Yeoman-Generator für Office-Add-Ins wird Ihr Projekt mit einigen vordefinierten benutzerdefinierten Funktionen einrichten, die Sie ausprobieren können. Wenn Sie bereits die Schnellstartanleitung für benutzerdefinierte Funktionen befolgt und ein Projekt generiert haben, fahren Sie mit diesem Projekt fort, und gehen Sie zu diesem Schritt über.

Hinweis

Wenn Sie das Yo Office-Projekt neu erstellen, erhalten Sie möglicherweise einen Fehler, da der Office-Cache bereits über eine instance einer Funktion mit demselben Namen verfügt. Sie können dies verhindern, indem Sie den Office-Cache löschen, bevor Sie npm run start ausführen.

  1. Führen Sie den folgenden Befehl aus, um ein Add-In-Projekt mit dem Yeoman-Generator zu erstellen: Ein Ordner, der das Projekt enthält, wird dem aktuellen Verzeichnis hinzugefügt.

    yo office

    Hinweis

    Wenn Sie den yo office-Befehl ausführen, werden möglicherweise Eingabeaufforderungen zu den Richtlinien für die Datensammlung von Yeoman und den CLI-Tools des Office-Add-In angezeigt. Verwenden Sie die bereitgestellten Informationen, um auf die angezeigten Eingabeaufforderungen entsprechend zu reagieren.

    Wenn Sie dazu aufgefordert werden, geben Sie die folgenden Informationen an, um das Add-In-Projekt zu erstellen:

    • Wählen Sie einen Projekttyp aus:Excel Custom Functions using a Shared Runtime
    • Wählen Sie einen Skripttyp aus:JavaScript
    • Wie möchten Sie Ihr Add-In benennen?My custom functions add-in

    Die Befehlszeilenschnittstelle des Yeoman Office-Add-In-Generators fordert sie für Projekte mit benutzerdefinierten Funktionen auf.

    Der Yeoman-Generator erstellt die Projektdateien und installiert die unterstützenden Node-Komponenten.

  2. Navigieren Sie zum Stammordner des Projekts.

    cd "My custom functions add-in"

  3. Erstellen Sie das Projekt.

    npm run build

    Hinweis

    Auch von Ihnen erstellte Office-Add-Ins sollten HTTPS und nicht HTTP verwenden. Wenn Sie aufgefordert werden, ein Zertifikat zu installieren, nachdem Sie npm run build ausgeführt haben, akzeptieren Sie die Eingabeaufforderung, um das Zertifikat zu installieren, das der Yeoman-Generator bereitstellt.

  4. Starten Sie den lokalen Webserver, auf dem Node.js ausgeführt wird. Sie können das Add-In für benutzerdefinierte Funktionen in Excel ausprobieren.

Der Befehl zum Testen Ihres Add-Ins in Excel unter Windows oder Mac hängt davon ab, wann das Projekt erstellt wurde. Wenn der Abschnitt "scripts" der package.json Datei des Projekts über ein "start:desktop"-Skript verfügt, führen Sie aus npm run start:desktop, andernfalls führen Sie den Befehl aus npm run start. Der lokale Webserver wird gestartet, und Excel wird mit geladenem Add-In geöffnet.

Hinweis

  • Office-Add-Ins sollten auch während der Entwicklung HTTPS und nicht HTTP verwenden. Wenn Sie aufgefordert werden, ein Zertifikat zu installieren, nachdem Sie einen der folgenden Befehle ausgeführt haben, akzeptieren Sie die Eingabeaufforderung, um das Zertifikat zu installieren, das der Yeoman-Generator bereitstellt. Möglicherweise ist es auch erforderlich, dass Sie Ihre Eingabeaufforderung oder Ihr Terminal als Administrator ausführen, damit die Änderungen vorgenommen werden können.

  • Wenn Sie zum ersten Mal ein Office-Add-In auf Ihrem Computer entwickeln, werden Sie möglicherweise in der Befehlszeile aufgefordert, Microsoft Edge WebView eine Loopback-Ausnahme zu gewähren ("Localhost-Loopback für Microsoft Edge WebView zulassen?"). Wenn Sie dazu aufgefordert werden, geben Sie ein Y , um die Ausnahme zuzulassen. Beachten Sie, dass Sie Administratorrechte benötigen, um die Ausnahme zuzulassen. Sobald dies zulässig ist, sollten Sie nicht zur Eingabe einer Ausnahme aufgefordert werden, wenn Sie Office-Add-Ins in Zukunft querladen (es sei denn, Sie entfernen die Ausnahme von Ihrem Computer). Weitere Informationen finden Sie unter "Wir können dieses Add-In nicht über localhost öffnen", wenn Sie ein Office-Add-In laden oder Fiddler verwenden.

    Die Eingabeaufforderung in der Befehlszeile, um Microsoft Edge WebView eine Loopbackausnahme zu ermöglichen.

Testen einer vorgefertigten benutzerdefinierten Funktion

Das Projekt für benutzerdefinierte Funktionen, das Sie erstellt haben, enthält einige vordefinierte benutzerdefinierte Funktionen. Diese sind in der Datei ./src/functions/functions.js definiert. Die Datei manifest.xml gibt an, dass alle benutzerdefinierten Funktionen zum Namespace CONTOSO gehören. Verwenden Sie den CONTOSO-Namespace, um auf die benutzerdefinierten Funktionen in Excel zuzugreifen.

Testen Sie nun die benutzerdefinierte Funktion ADD, indem Sie die folgenden Schritte ausführen.

  1. Gehen Sie in Excel zu einer beliebigen Zelle, und geben Sie =CONTOSO ein. Beachten Sie, dass das Menü „AutoVervollständigen“ eine Liste mit allen Funktionen im Namespace CONTOSO anzeigt.

  2. Führen Sie die Funktion CONTOSO.ADD mit den Zahlen 10 und 200 als Eingabeparameter aus, indem Sie den Wert =CONTOSO.ADD(10,200) in der Zelle angeben und die EINGABETASTE drücken.

Die benutzerdefinierte Funktion ADD berechnet die Summe der beiden Zahlen, die Sie angegeben haben, und gibt das Ergebnis 210 zurück.

Wenn der CONTOSO Namespace im Menü "AutoVervollständigen" nicht verfügbar ist, führen Sie die folgenden Schritte aus, um das Add-In in Excel zu registrieren.

  1. Wählen SieStart-Add-Ins> und dann Weitere Einstellungen aus.

  2. Wählen Sie im Dialogfeld Office-Add-Insdie Option Mein Add-In hochladen aus.

  3. Wählen Sie Durchsuchen... aus, und navigieren Sie zum Stammverzeichnis des Projekts, das der Yeoman-Generator erstellt hat.

  4. Wählen Sie die Datei manifest.xml und anschließend Öffnen > Hochladen aus.

  5. Testen Sie die neue Funktion. Geben Sie in Zelle B1 den Text =CONTOSO.GETSTARCOUNT("OfficeDev", "Excel-Custom-Functions") ein, und drücken Sie die EINGABETASTE. Das Ergebnis in Zelle B1 sollte die aktuelle Anzahl der Sterne darstellen, die dem Excel-Custom-Functions GithubGitHub-Repository zugewiesen sind.

Hinweis

Wenn beim Querladen des Add-Ins Fehler auftreten, lesen Sie den Abschnitt Problembehandlung in diesem Artikel.

Erstellen einer benutzerdefinierten Funktion, die Daten aus dem Web anfordert

Das Integrieren von Daten aus dem Internet ist eine hervorragende Möglichkeit, um Excel über benutzerdefinierte Funktionen zu erweitern. Als Nächstes erstellen Sie eine benutzerdefinierte Funktion mit der Bezeichnung getStarCount, die angibt, über wie viele Sterne ein bestimmtes GitHub-Repository verfügt.

  1. Suchen Sie im Projekt My custom functions add-in die Datei ./src/functions/functions.js und öffnen Sie sie in Ihrem Code-Editor.

  2. Fügen Sie in function.js den folgenden Code hinzu.

    /**
  * Gets the star count for a given Github repository.
  * @customfunction 
  * @param {string} userName string name of Github user or organization.
  * @param {string} repoName string name of the Github repository.
  * @return {number} number of stars given to a Github repository.
  */
  async function getStarCount(userName, repoName) {
    try {
      //You can change this URL to any web request you want to work with.
      const url = "https://api.github.com/repos/" + userName + "/" + repoName;
      const response = await fetch(url);
      //Expect that status code is in 200-299 range
      if (!response.ok) {
        throw new Error(response.statusText)
      }
        const jsonResponse = await response.json();
        return jsonResponse.watchers_count;
    }
    catch (error) {
      return error;
    }
  }

  3. Führen Sie den folgenden Befehl aus, um das Projekt erneut zu erstellen.

    npm run build

  4. Führen Sie die folgenden Schritte (für Excel im Web, Windows oder Mac) aus, um das Add-In in Excel erneut zu registrieren. Sie müssen diese Schritte abschließen, bevor die neue Funktion verfügbar wird.

  1. Schließen Sie Excel, und öffnen Sie es wieder.

  2. Wählen Sie imExcel-Menüband Start-Add-Ins> aus.

  3. Wählen Sie im Abschnitt Entwickler-Add-Insdie Option Mein Add-In für benutzerdefinierte Funktionen aus, um es zu registrieren.

    Das Dialogfeld

  4. Testen Sie die neue Funktion. Geben Sie in Zelle B1 den Text =CONTOSO ein. GETSTARCOUNT("OfficeDev", "Office-Add-in-Samples") und drücken Sie die EINGABETASTE. Sie sollten sehen, dass das Ergebnis in Zelle B1 die aktuelle Anzahl von Sternen ist, die dem Office-Add-in-Samples-Repository zugewiesen werden.

Hinweis

Wenn beim Querladen des Add-Ins Fehler auftreten, lesen Sie den Abschnitt Problembehandlung in diesem Artikel.

Erstellen einer asynchronen benutzerdefinierten Streamingfunktion

Die Funktion getStarCount gibt die Anzahl der Sterne zurück, die das Repository zu einem bestimmten Zeitpunkt hat. Benutzerdefinierte Funktionen geben auch Daten zurück, die sich ständig ändern. Diese Funktionen werden als Streamingfunktionen bezeichnet. Sie müssen einen invocation-Parameter enthalten, der auf die Zelle verweist, die die Funktion abgerufen hat. Der invocation-Parameter wird verwendet, um den Inhalt der Zelle jederzeit zu aktualisieren.

Beachten Sie im folgenden Codebeispiel, dass es zwei Funktionen gibt: currentTime und clock. Bei der currentTime-Funktion handelt es sich um eine statische Funktion, die kein Streaming verwendet. Sie gibt das Datum als Zeichenfolge zurück. Die clock-Funktion verwendet die currentTime-Funktion, um die neue Zeit jede Sekunde in einer Zelle in Excel bereitzustellen. Sie verwendet invocation.setResult, um die Zeit an die Excel-Zelle zu übermitteln, und invocation.onCanceled, um den Funktionsabbruch zu verarbeiten.

Das Add-in-Projekt My custom functions enthält bereits die folgenden beiden Funktionen in der Datei ./src/functions/functions.js.

/**
 * Returns the current time
 * @returns {string} String with the current time formatted for the current locale.
 */
function currentTime() {
  return new Date().toLocaleTimeString();
}
    
/**
 * Displays the current time once a second
 * @customfunction
 * @param {CustomFunctions.StreamingInvocation<string>} invocation Custom function invocation
 */
function clock(invocation) {
  const timer = setInterval(() => {
    const time = currentTime();
    invocation.setResult(time);
  }, 1000);
    
  invocation.onCanceled = () => {
    clearInterval(timer);
  };
}

Um die Funktionen auszuprobieren, geben Sie den Text =CONTOSOCLOCK() in Zelle C1 ein, und drücken Sie die EINGABETASTE. Es sollte das aktuelle Datum angezeigt werden, das jede Sekunde aktualisiert wird. Obwohl diese Uhr nur ein Timer in einer Schleife ist, können Sie diese Vorstellung des Einstellens eines Timers für komplexere Funktionen verwenden, die Webanforderungen für Echtzeitdaten stellen.

Problembehandlung

Wenn Sie das Tutorial mehrmals ausführen, können Probleme auftreten. Wenn der Office-Cache bereits über eine Instanz einer Funktion mit demselben Namen verfügt, erhält Ihr Add-In beim Querladen einen Fehler.

Sie können diesen Konflikt verhindern, indem Sie den Office-Cache löschen , bevor Sie ausführen npm run start. Wenn Ihr npm-Prozess bereits ausgeführt wird, geben Sie ein npm stop, löschen Sie den Office-Cache, und starten Sie npm neu.

Eine Fehlermeldung in Excel mit dem Titel „Fehler beim Installieren von Funktionen“. Sie enthält den Text „Dieses Add-In wurde nicht installiert, weil bereits eine benutzerdefinierte Funktion mit demselben Namen vorhanden ist“.

Nächste Schritte

Herzlichen Glückwunsch! Sie haben ein neues Projekt für benutzerdefinierte Funktionen erstellt, eine vorgefertigte Funktion getestet, eine benutzerdefinierte Funktion erstellt, die Daten aus dem Web anfordert, und eine benutzerdefinierte Funktion erstellt, die Daten streamt. Als Nächstes erfahren Sie, wie Sie benutzerdefinierte Funktionsdaten für den Aufgabenbereich freigeben.