チュートリアル: Excel でのカスタム関数の作成

カスタム関数では、関数をアドインの一部として JavaScript で定義することによって、Excel に新しい関数を追加できます。 ユーザーは、Excel 内から、SUM() などの Excel のあらゆるネイティブ関数の場合と同じようにカスタム関数にアクセスできます。 計算のような単純なタスク、または Web からワークシートへのデータのリアルタイム ストリーミングのようなより複雑なタスクを実行するカスタム関数を作成できます。

このチュートリアルの内容:

  • Office アドイン用の Yeoman ジェネレーターを使用して、カスタム関数アドインを作成します。
  • あらかじめ用意されているカスタム関数を使用し、単純な計算を実行します。
  • Web からデータを取得するカスタム関数を作成します。
  • Web からデータをリアルタイムでストリーミングするカスタム関数を作成します。

前提条件

  • Node.js (最新 LTS バージョン)。 Node.js サイトにアクセスして、オペレーティング システムに適したバージョンをダウンロードしてインストールします。

  • 最新バージョンの Yeoman と Office アドイン用の Yeoman ジェネレーター。これらのツールをグローバルにインストールするには、コマンド プロンプトから次のコマンドを実行します。

    npm install -g yo generator-office
    

    注:

    Yeomanのジェネレーターを過去に取付けている場合でも、npmからのパッケージを最新のバージョンにすることをお勧めします。

  • Microsoft 365 サブスクリプションに接続されている Office (Office for the web を含む)。

    注:

    まだ Office をお持ちでない場合は、Microsoft 365 開発者プログラムを通じてMicrosoft 365 E5開発者サブスクリプションを受ける資格があります。詳細については、FAQ を参照してください。 または、 1 か月間の無料試用版にサインアップ するか、 Microsoft 365 プランを購入することもできます。

カスタム関数プロジェクトを作成する

まず、カスタム関数アドインをビルドするコード プロジェクトを作成します。 Office アドインの Yeoman ジェネレーターは、試すことができるいくつかのカスタム関数を使ってプロジェクトをセットアップします。カスタム関数のクイック スタートをすでに実行し、プロジェクトを生成している場合は、そのプロジェクトを引き続き使用し、代わりにこの手順 に進みます。

注:

Yo Office プロジェクトを再作成すると、Office キャッシュに同じ名前の関数のインスタンスが既に存在するため、エラーが発生する可能性があります。 npm run start を実行する前に Office キャッシュをクリアすることにより、これを防ぐことができます。

  1. 次のコマンドを実行し、Yeoman ジェネレーターを使用してアドイン プロジェクトを作成します。 プロジェクトを含むフォルダーが現在のディレクトリに追加されます。

    yo office
    

    注:

    yo officeコマンドを実行すると、Yeoman のデータ収集ポリシーと Office アドイン CLI ツールに関するプロンプトが表示される場合があります。 提供された情報を使用して、必要に応じてプロンプトに応答します。

    プロンプトが表示されたら、以下の情報を入力してアドイン プロジェクトを作成します。

    • プロジェクトの種類を選択します。Excel Custom Functions using a Shared Runtime
    • スクリプトの種類を選択します。JavaScript
    • アドインに何の名前を付けたいですか?My custom functions add-in

    Yeoman Office アドイン ジェネレーターのコマンド ライン インターフェイスは、カスタム関数プロジェクトのプロンプトを表示します。

    Yeoman ジェネレーターはプロジェクト ファイルを作成し、サポートしているノード コンポーネントをインストールします。

    注:

    バージョン 20.0.0 以降 Node.js 使用している場合は、ジェネレーターがインストールを実行するときに、サポートされていないエンジンがあることを示す警告が表示されることがあります。 この問題の修正に取り組んでいます。 その間、警告は生成するジェネレーターまたはプロジェクトに影響しないため、無視できます。

    ヒント

    アドイン プロジェクトの作成後に Yeoman ジェネレーターが提供する次の手順ガイダンスは無視できます。 この記事中の詳しい手順は、このチュートリアルを完了するために必要なすべてのガイダンスを提供します。

  2. プロジェクトのルート フォルダーに移動します。

    cd "My custom functions add-in"
    
  3. プロジェクトをビルドします。

    npm run build
    

    注:

    開発の最中でも、OfficeアドインはHTTPではなくHTTPSを使用する必要があります。 npm run buildの実行後に証明書をインストールするように指示が出された場合は、Yeomanジェネレーターが提供する証明書をインストールする手順に従ってください。

  4. Node.js で実行しているローカル Web サーバーを開始します。 Excel でカスタム関数アドインを試すことができます。

Windows または Mac の Excel でアドインをテストするには、次のコマンドを実行します。 このコマンドを実行すると、ローカル Web サーバーが起動し、アドインが読み込まれたときに Excel が開きます。

npm run start:desktop

注:

Office アドインでは、開発中でも HTTP ではなく HTTPS を使用する必要があります。 次のいずれかのコマンドを実行した後に証明書のインストールを求められた場合は、Yeoman ジェネレーターが提供する証明書をインストールするプロンプトに同意します。 変更を行うには、管理者としてコマンド プロンプトまたはターミナルを実行する必要がある場合もあります。

あらかじめ用意されているカスタム関数を試す

作成したカスタム関数プロジェクトには、あらかじめ用意されているカスタム関数がいくつか含まれており、./src/functions/functions.js ファイル内で定義されています。 ./manifest.xml ファイルによって、カスタム関数はすべて CONTOSO 名前空間に属することが指定されます。 Excel でカスタム関数にアクセスするには、CONTOSO 名前空間を使用します。

次に、以下の手順を実行し、ADD カスタム関数を試してみてください。

  1. Excel で、任意のセルに移動し、=CONTOSO と入力します。 CONTOSO 名前空間にあるすべての関数がオートコンプリート メニューに一覧表示されます。

  2. セル内で値 =CONTOSO.ADD(10,200) を入力して Enter キーを押し、入力パラメーターとして数値 10200 を指定して、CONTOSO.ADD 関数を実行します。

ADD カスタム関数によって、指定した 2 つの数字の合計が計算され、210 という結果が返されます。

オートコンププリート メニューで CONTOSO 名前空間を使用できない場合、次の手順でアドインを Excel に登録します。

  1. [Excel] リボンで、[ホーム> アドイン] を選択します

  2. [ 開発者アドイン ] セクションで、[ マイ カスタム関数アドイン ] を選択して登録します。

    [マイ カスタム関数アドイン] ボタンが強調表示されている、アクティブなアドインを表示する [マイ アドイン] ダイアログ。

注:

アドインをサイドローディングするときにエラーが発生した場合は、この記事の 「トラブルシューティング 」セクションを参照してください。

Web からデータを要求するカスタム関数を作成する

Web からデータを統合することは、カスタム関数を使用して Excel を拡張する優れた方法です。 次に、特定の Github リポジトリが所有する星の数を示す getStarCount という名前のカスタム関数を作成します。

  1. マイ カスタム関数アドイン プロジェクトで、./src/functions/functions.js ファイルを見つけて、コード エディターで開きます。

  2. function.js で、次のコードを追加します。

    /**
      * 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. 次のコマンドを実行してプロジェクトを再構築します。

    npm run build
    
  4. Excel のアドインを再登録するには、次の手順を完了します (Web、Windows または Mac 上の Excel の場合)。 新しい関数を使用するには、次の手順を完了する必要があります。

  1. Excel を閉じて再び開きます。

  2. [Excel] リボンで、[ホーム> アドイン] を選択します

  3. [ 開発者アドイン ] セクションで、[ マイ カスタム関数アドイン ] を選択して登録します。

    [マイ カスタム関数アドイン] ボタンが強調表示されている、アクティブなアドインを表示する [マイ アドイン] ダイアログ。

  4. 新しい関数をお試しください。 セル B1 にテキスト =CONTOSO を入力します。GETSTARCOUNT("OfficeDev", "Office-Add-in-Samples") を 押して Enter キーを押します。 セル B1 の結果は、 Office-Add-in-Samples リポジトリに与えられた星の現在の数であることがわかります。

注:

アドインをサイドローディングするときにエラーが発生した場合は、この記事の 「トラブルシューティング 」セクションを参照してください。

非同期でデータをストリーミングするカスタム関数を作成する

getStarCount 関数は、ある時点でリポジトリに存在する星の数を返します。 カスタム関数は、継続的に変更されているデータも返します。 これらの関数は、ストリーミング関数と呼ばれます。 関数を呼び出したセルを参照する invocation パラメーターを含める必要があります。 invocation パラメーターは、セルのコンテンツをいつでも更新するために使用します。

次のコード例では、currentTimeclock という 2 つの関数があることがわかります。 currentTime 関数は、ストリーミングを使わない静的な関数です。 日付を表す文字列を返します。 clock 関数は、currentTime 関数を使用して、Excel 内のセルに毎秒新しい時間を提します。 invocation.setResultを使用して Excel セルに時間を配信し、関数のキャンセルを処理するinvocation.onCanceledを使用します。

マイ カスタム関数アドイン プロジェクトには、./src/functions/functions.js ファイルに次の 2 つの関数が既に含まれています。

/**
 * 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);
  };
}

関数を試すには、セル C1に、テキスト=CONTOSO.CLOCK()を入力して、Enter キーを押します。 現在の日付が表示されます。この日付は 1 秒ごとにアップデートされます。 このクロックはループ上の単なるタイマーですが、リアルタイム データの Web 要求を行うより複雑な関数にタイマーを設定するという同じ考え方を使用できます。

トラブルシューティング

チュートリアルを複数回実行すると、問題が発生する可能性があります。 Office キャッシュに同じ名前を持つ関数のインスタンスが既に存在する場合、アドインのサイドロード時にエラーが発生します。

を実行npm run startする前に Office キャッシュをクリアすることで、この競合を防ぐことができます。 npm プロセスが既に実行されている場合は、「」と入力 npm stopして Office キャッシュをクリアしてから、npm を再起動します。

Excelで '関数のインストール中にエラーが発生しました' というタイトルのエラー メッセージが表示されます。これには、'同じ名前を持つカスタム関数が既に存在するため、このアドインはインストールされませんでした' というテキストが含まれます。

次の手順

おめでとうございます! 新しいカスタム関数プロジェクトを作成し、あらかじめ用意されている関数を試し、Web にデータを要求するカスタム関数を作成し、ストリーミング データであるカスタム関数を作成しました。 次に、 作業ウィンドウでカスタム関数データを共有する方法について説明します。