Lernprogramm: Erstellen benutzerdefinierter Funktionen in Excel

Erstellen Sie ein Excel-Add-In, das benutzerdefinierte JavaScript-Funktionen zusammen mit integrierten Funktionen wie SUMbereitstellt. Sie erstellen Funktionen, die eine Berechnung ausführen, Daten aus dem Web abrufen und Echtzeitupdates in ein Arbeitsblatt streamen.

In diesem Tutorial führen Sie Folgendes aus:

Voraussetzungen

  • Node.js (die neueste Active LTS-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 FAQ. 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 das Codeprojekt für Ihr benutzerdefiniertes Funktions-Add-In. Der Yeoman-Generator für Office-Add-Ins richtet das Projekt mit vordefinierten benutzerdefinierten Funktionen ein, die sie ausprobieren können. Wenn Sie bereits im Schnellstart zu benutzerdefinierten Funktionen ein Projekt generiert haben, verwenden Sie dieses Projekt, und fahren Sie unter Erstellen einer benutzerdefinierten Funktion, die Daten aus dem Web anfordert, fort.

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. Um diesen Fehler zu verhindern, löschen Sie den Office-Cache , bevor Sie ausführen npm run start.

  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 unterstützende Node-Komponenten.

  2. Wechseln 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 verwenden, und nicht HTTP. Wenn Sie nach dem Ausführen npm run buildvon aufgefordert werden, ein Zertifikat zu installieren, akzeptieren Sie die Aufforderung, 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 Sie das Projekt erstellt haben. Wenn der "scripts" Abschnitt der package.json-Datei des Projekts ein start:desktop Skript enthält, führen Sie aus npm run start:desktop. Führen Sie andernfalls 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.

  • Wenn Sie den Yeoman-Generator zum ersten Mal zum Entwickeln eines Office-Add-Ins verwenden, öffnet Ihr Standardbrowser ein Fenster, in dem Sie aufgefordert werden, sich bei Ihrem Microsoft 365-Konto anzumelden. Wenn kein Anmeldefenster angezeigt wird und ein Sideloading- oder Anmeldetimeoutfehler auftritt, führen Sie aus atk auth login m365.

Testen einer vordefinierten benutzerdefinierten Funktion

Das Projekt enthält vordefinierte benutzerdefinierte Funktionen in ./src/functions/functions.js. Die Datei ./manifest.xml weist sie dem CONTOSO Namespace zu, den Sie für den Zugriff auf die Funktionen in Excel verwenden.

Probieren Sie als Nächstes die ADD benutzerdefinierte Funktion aus, 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. Geben Sie =CONTOSO.ADD(10,200) in die Zelle ein, und drücken Sie dann die EINGABETASTE.

Die ADD benutzerdefinierte Funktion gibt zurück 210.

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 ein. GETSTARCOUNT("OfficeDev", "Excel-Custom-Functions") 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

Die Integration von Daten aus dem Web ist eine hervorragende Möglichkeit, Excel über benutzerdefinierte Funktionen zu erweitern. Erstellen Sie eine getStarCount benutzerdefinierte Funktion, die die Anzahl der Sterne für ein GitHub-Repository abruft.

  1. Öffnen Sie im Add-In-Projekt Meine benutzerdefinierten Funktionen./src/functions/functions.js in Ihrem Code-Editor.

  2. Fügen Sie functions.jsden folgenden Code hinzu.

    /**
 * Gets the star count for a GitHub repository.
 * @customfunction
 * @param {string} userName GitHub user or organization name.
 * @param {string} repoName GitHub repository name.
 * @returns {number} Number of stars given to the GitHub repository.
 */
async function getStarCount(userName, repoName) {
  try {
    const url = `https://api.github.com/repos/${userName}/${repoName}`;
    const response = await fetch(url);

    if (!response.ok) {
      throw new Error(response.statusText);
    }

    const jsonResponse = await response.json();
    return jsonResponse.stargazers_count;
  } catch (error) {
    throw new CustomFunctions.Error(CustomFunctions.ErrorCode.notAvailable, String(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 ausführen, bevor die neue Funktion verfügbar ist.

  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. Geben Sie in Zelle B1 ein =CONTOSO.GETSTARCOUNT("OfficeDev", "Office-Add-in-Samples"), und drücken Sie dann die EINGABETASTE. Die Zelle zeigt die aktuelle Anzahl von Sternen für das Office-Add-in-Samples-Repository an.

Hinweis

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

Erstellen einer asynchronen benutzerdefinierten Streamingfunktion

Die getStarCount Funktion gibt die Anzahl der Sterne zu einem bestimmten Zeitpunkt zurück. Eine Streamingfunktion kann dagegen eine Zelle wiederholt aktualisieren. Sie enthält einen invocation Parameter, der die Zelle darstellt, die die Funktion aufgerufen hat.

Das folgende Beispiel enthält zwei Funktionen. currentTime gibt die aktuelle Uhrzeit als Zeichenfolge zurück. Die Streamingfunktion clock ruft auf invocation.setResult , um die Zelle jede Sekunde zu aktualisieren, und verwendet , invocation.onCanceled um den Timer zu beenden, wenn Excel die Funktion abbricht.

Das Add-In-Projekt My custom functions enthält diese Funktionen bereits in ./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 Streamingfunktion zu testen, geben Sie =CONTOSO.CLOCK() in Zelle C1 ein, und drücken Sie dann die EINGABETASTE. Die Zelle zeigt die aktuelle Uhrzeit an und aktualisiert jede Sekunde. Sie können das gleiche Timermuster für Funktionen verwenden, die Echtzeitdaten aus dem Web anfordern.

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.

Um diesen Konflikt zu vermeiden, löschen Sie den Office-Cache , bevor Sie ausführen npm run start. Wenn Ihr npm-Prozess bereits ausgeführt wird, geben Sie ein npm run 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

Sie haben ein neues Projekt für benutzerdefinierte Funktionen erstellt, eine vordefinierte Funktion ausprobiert, 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.

  • Last updated on