Excel でカスタム関数を作成する (プレビュー)Create custom functions in Excel (Preview)

• 09/27/2018
• 共同作成者
  • 
  • 

開発者はカスタム関数を使用すると、JavaScriptでこれらの関数をアドインの一部として定義することにより、Excelに新しい関数を追加できます。Custom functions enable developers to add new functions to Excel by defining those functions in JavaScript as part of an add-in. Excel内のユーザーは、Excel の他のネイティブ関数（SUM() など）と同様に、カスタム関数にアクセスできます。Users within Excel can access custom functions just as they would any native function in Excel, such as SUM(). この記事では、Excel でカスタム関数を作成する方法について説明します。This article describes how to create custom functions in Excel.

次の図は、Excel ワークシートのセルにカスタム関数を挿入する、エンド ユーザーを示します。 CONTOSO.ADD42 カスタム関数は、関数への入力パラメーターとしてユーザーが指定した数値のペアに 42 を追加するように設計されています。The following illustration shows an end user inserting a custom function into a cell of an Excel worksheet. The CONTOSO.ADD42 custom function is designed to add 42 to the pair of numbers that the user specifies as input parameters to the function.

次のコードは、ADD42 カスタム関数を定義します。The following code defines the ADD42 custom function.

function add42(a, b) {
  return a + b + 42;
}

カスタム関数アドインプロジェクトのコンポーネントComponents of a custom functions add-in project

Yo Office ジェネレーター を使用して Excel カスタム関数アドイン プロジェクトを作成する場合は、ジェネレーターが作成するプロジェクトに以下のようなファイルが表示されます。If you use the Yo Office generator to create an Excel custom functions add-in project, you'll see the following files in the project that the generator creates:

次のセクションでは、これらのファイルに関する詳細について説明します。The following sections provide more details about these settings.

スクリプト ファイルScript file

スクリプト ファイル (Yo Office ジェネレーターが作成するプロジェクト内の ./src/customfunctions.js または /src/customfunctions.ts) には、カスタム関数を定義して、カスタム関数の名前を JSON メタデータ ファイルのオブジェクトにマップするコードが含まれています。The script file (./src/customfunctions.js or ./src/customfunctions.ts in the project that the Yo Office generator creates) contains the code that defines custom functions and maps the names of the custom functions to objects in the JSON metadata file.

例えば、次のコードでカスタム関数 add と increment を定義し、両方の関数のマッピング情報を指定します。 add 関数は、JSON メタデータ ファイル内のオブジェクトにマップされ、 この場所にid プロパティの値が追加されます。increment 関数は、メタデータ ファイル内のオブジェクトにマップされ、この場所にid プロパティの値がインクリメントします。JSON メタデータ ファイル内のオブジェクトへのスクリプト ファイル内関数名のマッピングの詳細については、 カスタム関数のベスト ・ プラクティス を参照してください。For example, the following code defines the custom functions add and increment and then specifies mapping information for both functions. The add function is mapped to the object in the JSON metadata file where the value of the id property is ADD, and the increment function is mapped to the object in the metadata file where the value of the id property is INCREMENT. See Custom functions best practices for more information about mapping function names in the script file to objects in the JSON metadata file.

function add(first, second){
  return first + second;
}

function increment(incrementBy, callback) {
  var result = 0;
  var timer = setInterval(function() {
    result += incrementBy;
    callback.setResult(result);
  }, 1000);

  callback.onCanceled = function() {
    clearInterval(timer);
  };
}

// map `id` values in the JSON metadata file to the JavaScript function names
CustomFunctionMappings.ADD = add;
CustomFunctionMappings.INCREMENT = increment;

JSON メタデータ ファイルJSON metadata file

カスタム関数のメタデータ ファイル (Yo Office ジェネレーターが作成するプロジェクトでは ./config/customfunctions.json ) は、Excel がカスタム関数の登録を要求し、エンドユーザーが利用できるよう、情報を提供します。カスタム関数は、ユーザーがアドインを初めて実行するときに登録されます。その後は、同じユーザーに対しては、（アドインが最初に実行されたワークブック内のみでなく）すべてのワークブック内で利用が可能になります。The custom functions metadata file (./config/customfunctions.json in the project that the Yo Office generator creates) provides the information that Excel requires to register custom functions and make them available to end users. Custom functions are registered when a user runs an add-in for the first time. After that, they are available to that same user in all workbooks (i.e., not only in the workbook where the add-in initially ran.)

*Customfunctions.json* の次のコードは、add 関数のメタデータと increment 、上述の関数を指定します。このコード サンプルを基にした表は、この JSON オブジェクト内の個別のプロパティについての詳細情報を提供します。id\の値とJSON のメタデータ ファイル内のname\プロパティの指定に関する詳細については、 ベスト プラクティスのカスタム関数 を参照してください。The following code in customfunctions.json specifies the metadata for the add function and the increment function that were described previously. The table that follows this code sample provides detailed information about the individual properties within this JSON object. See Custom functions best practices for more information about specifying the value of id and name properties in the JSON metadata file.

{
  "$schema": "https://developer.microsoft.com/en-us/json-schemas/office-js/custom-functions.schema.json",
  "functions": [
    {
      "id": "ADD",
      "name": "ADD",
      "description": "Add two numbers",
      "helpUrl": "http://www.contoso.com",
      "result": {
        "type": "number",
        "dimensionality": "scalar"
      },
      "parameters": [
        {
          "name": "first",
          "description": "first number to add",
          "type": "number",
          "dimensionality": "scalar"
        },
        {
          "name": "second",
          "description": "second number to add",
          "type": "number",
          "dimensionality": "scalar"
        }
      ]
    },
    {
      "id": "INCREMENT",
      "name": "INCREMENT",
      "description": "Periodically increment a value",
      "helpUrl": "http://www.contoso.com",
      "result": {
          "type": "number",
          "dimensionality": "scalar"
    },
    "parameters": [
        {
            "name": "increment",
            "description": "Amount to increment",
            "type": "number",
            "dimensionality": "scalar"
        }
    ],
    "options": {
        "cancelable": true,
        "stream": true
      }
    }
  ]
}

次の表は、通常、JSON メタデータ ファイルに格納されているプロパティの一覧表示です。JSON メタデータ ファイルの詳細については、 カスタム関数のメタデータを参照してください。The following table lists the properties that are typically present in the JSON metadata file. For more detailed information about the JSON metadata file, see Custom functions metadata.

マニフェスト ファイルManifest file

カスタム関数 (Yo Office ジェネレーターが作成するプロジェクトでは ./manifest.xml ) を定義するアドインの XML マニフェスト ファイルは、アドインとJavaScript、JSON、および HTML のロケーション内のすべてのカスタム関数の名前空間を指定します。次の XML マークアップでは、 <ExtensionPoint> と <Resources> カスタム関数を有効にするアドインのマニフェストに含める必要がある要素の一例を示します。The XML manifest file for an add-in that defines custom functions (./manifest.xml in the project that the Yo Office generator creates) specifies the namespace for all custom functions within the add-in and the location of the JavaScript, JSON, and HTML files. The following XML markup shows an example of the <ExtensionPoint> and <Resources> elements that you must include in an add-in's manifest to enable custom functions.

<VersionOverrides xmlns="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="VersionOverridesV1_0">
    <Hosts>
        <Host xsi:type="Workbook">
            <AllFormFactors>
                <ExtensionPoint xsi:type="CustomFunctions">
                    <Script>
                        <SourceLocation resid="JS-URL" /> <!--resid points to location of JavaScript file-->
                    </Script>
                    <Page>
                        <SourceLocation resid="HTML-URL"/> <!--resid points to location of HTML file-->
                    </Page>
                    <Metadata>
                        <SourceLocation resid="JSON-URL" /> <!--resid points to location of JSON file-->
                    </Metadata>
                    <Namespace resid="namespace" />
                </ExtensionPoint>
            </AllFormFactors>
        </Host>
    </Hosts>
    <Resources>
        <bt:Urls>
            <bt:Url id="JSON-URL" DefaultValue="http://127.0.0.1:8080/customfunctions.json" /> <!--specifies the location of your JSON file-->
            <bt:Url id="JS-URL" DefaultValue="http://127.0.0.1:8080/customfunctions.js" /> <!--specifies the location of your JavaScript file-->
            <bt:Url id="HTML-URL" DefaultValue="http://127.0.0.1:8080/index.html" /> <!--specifies the location of your HTML file-->
        </bt:Urls>
        <bt:ShortStrings>
            <bt:String id="namespace" DefaultValue="CONTOSO" /> <!--specifies the namespace that will be prepended to a function's name when it is called in Excel. -->
        </bt:ShortStrings>
    </Resources>
</VersionOverrides>

外部ソースからデータを返す関数Functions that return data from external sources

カスタム関数が外部ソースからデータを取得する場合には、以下のことを実行する必要があります。If a custom function retrieves data from an external source such as the web, it must:

1. JavaScript Promise を Excel に返す。Return a JavaScript Promise to Excel.

2. コールバック関数を使用して Promise を最終値で解決する。Resolve the Promise with the final value using the callback function.

カスタム関数は、 Excelが #GETTING_DATA セルの最終結果を待っている間、一時的な結果を表示します。ユーザーは、結果待機中も通常はワークシートの残りの部分を操作することができます。Custom functions display a #GETTING_DATA temporary result in the cell while Excel waits for the final result. Users can interact normally with the rest of the worksheet while they wait for the result.

次のコード例は、 現在の温度計の温度を取得する getTemperature() カスタム関数です。 sendWebRequest は、温度 web サービスを呼び出す XHR を使用した仮想関数 (ここでは指定なし) であることに留意してください。In the following code sample, the getTemperature() custom function retrieves the current temperature of a thermometer. Note that sendWebRequest is a hypothetical function (not specified here) that uses XHR to call a temperature web service.

function getTemperature(thermometerID){
    return new Promise(function(setResult){
        sendWebRequest(thermometerID, function(data){
            setResult(data.temperature);
        });
    });
}

ストリーミング関数Streaming functions

ストリーミングのカスタム関数を使用すると、データ更新を明確に要求するユーザーを必要とせず、時間の経過と共に繰り返しセルにデータを出力します。次のコード サンプルは、1 秒ごとの結果の数値を追加するカスタム関数です。このコードについては、以下のことに留意してください。Streamed custom functions enable you to output data to cells repeatedly over time, without requiring a user to explicitly request data refresh. The following code sample is a custom function that adds a number to the result every second. Note the following about this code:

• Excel は、setResultコールバックを使用して自動的に新しい値を表示します。Excel displays each new value automatically using the setResult callback.

• 2 番目のパラメーター入力 handler は、[オートコンプリート] メニューから関数を選択する場合には、Excelのエンドユーザーには表示されません。The second input parameter, handler, is not displayed to end users in Excel when they select the function from the autocomplete menu.

• onCanceled コールバックは、関数がキャンセルされたときに実行される関数を定義します。どのストリーミング関数に対してもキャンセル ハンドラーを実装する必要があります。詳細については、 関数をキャンセルするを参照してください。The onCanceled callback defines the function that executes when the function is canceled. You must implement a cancellation handler like this for any streamed function. For more information, see Canceling a function.

function incrementValue(increment, handler){
  var result = 0;
  setInterval(function(){
    result += increment;
    handler.setResult(result);
  }, 1000);

  handler.onCanceled = function(){
    clearInterval(timer);
  }
}

JSON メタデータ ファイルでストリーミング関数にメタデータを指定する場合には、以下の例のように、プロパティ"cancelable": true および options オブジェクト "stream": true 内を設定する必要があります。When you specify metadata for a streamed function in the JSON metadata file, you must set the properties "cancelable": true and "stream": true within the options object, as shown in the following example.

{
  "id": "INCREMENT",
  "name": "INCREMENT",
  "description": "Periodically increment a value",
  "helpUrl": "http://www.contoso.com",
  "result": {
    "type": "number",
    "dimensionality": "scalar"
  },
  "parameters": [
    {
      "name": "increment",
      "description": "Amount to increment",
      "type": "number",
      "dimensionality": "scalar"
    }
  ],
  "options": {
    "cancelable": true,
    "stream": true
  }
}

関数をキャンセルするCanceling a function

ある状況では、帯域幅の消費、作業メモリ、および CPU の負荷を減らすためにストリーミングカスタム関数の実行をキャンセルする必要があります。Excel では、次のような関数の実行をキャンセルします。In some situations, you may need to cancel the execution of a streamed custom function to reduce its bandwidth consumption, working memory, and CPU load. Excel cancels the execution of a function in the following situations:

• ユーザーが、関数への参照があるセルを編集または削除した場合。The user edits or deletes a cell that references the function.

• 関数の引数 (入力) のいずれかが変更されたとき。この例では、キャンセルの後、新しい関数の呼び出しがトリガーされます。When one of the arguments (inputs) for the function changes. In this case, a new function call is triggered following the cancellation.

• ユーザーが手動で再計算をトリガーしたとき。この例では、キャンセルの後、新しい関数の呼び出しがトリガーされます。When the user triggers recalculation manually. In this case, a new function call is triggered following the cancellation.

関数をキャンセルする機能を有効にするには、JavaScript 関数内にキャンセル ハンドラーを実装し、関数を記述するJSONのメタデータのoptions オブジェクト内のプロパティ"cancelable": trueを指定する必要があります。この記事の前のセクションのコード サンプルに、これらの手法の例が示されています。To enable the ability to cancel a function, you must implement a cancellation handler within the JavaScript function and specify the property "cancelable": true within the options object in the JSON metadata that describes the function. The code samples in the previous section of this article provide an example of these techniques.

状態の保存と共有Saving and sharing state

カスタム関数は、JavaScript のグローバル変数にデータを保存できます。以降の呼び出しで、カスタム関数は、これらの変数に保存されている値を使用することができます。ユーザーが複数のセルに同じカスタム関数を追加するとき、関数のすべてのインスタンスが状態を共有できるため、状態が保存されているのは役に立ちます。例えば、同一の web リソースへの追加の呼び出しを避けるため、 web リソースへの呼び出しから返されたデータを保存することができます。Custom functions can save data in global JavaScript variables. In subsequent calls, your custom function may use the values saved in these variables. Saved state is useful when users add the same custom function to more than one cell, because all the instances of the function can share the state. For example, you may save the data returned from a call to a web resource to avoid making additional calls to the same web resource.

次のコード サンプルでは、グローバル状態を保存する温度ストリーミング関数の実装を示します。このコードについては、以下のことにに留意してください。The following code sample shows an implementation of a temperature-streaming function that saves state globally. Note the following about this code:

• refreshTemperature 1 秒ごとに特定の温度計の温度を読み取るストリーミング関数です。新しい温度が savedTemperatures 変数に保存されますが、セルの値を直接更新することはありません。ワークシートのセルから直接呼び出されないので、JSON ファイルには登録されません。refreshTemperature is a streamed function that reads the temperature of a particular thermometer every second. New temperatures are saved in the savedTemperatures variable, but does not directly update the cell value. It should not be directly called from a worksheet cell, so it is not registered in the JSON file.

• streamTemperature 毎秒、セルに表示される温度の値を更新し、 savedTemperatures 変数をデータ ソースとして使用します。それには、JSON ファイルに登録されており、大文字で、 STREAMTEMPERATURE名前を付けられている必要があります。streamTemperature updates the temperature values displayed in the cell every second and it uses savedTemperatures variable as its data source. It must be registered in the JSON file, and named with all upper-case letters, STREAMTEMPERATURE.

• ユーザーが、Excel UI の複数のセルからstreamTemperature を呼び出すことができます。各呼び出しは、同じ savedTemperatures 変数のデータを読み取ります。Users may call streamTemperature from several cells in the Excel UI. Each call reads data from the same savedTemperatures variable.

var savedTemperatures;

function streamTemperature(thermometerID, handler){
  if(!savedTemperatures[thermometerID]){
    refreshTemperatures(thermometerID); // starts fetching temperatures if the thermometer hasn't been read yet
  }

  function getNextTemperature(){
    handler.setResult(savedTemperatures[thermometerID]); // setResult sends the saved temperature value to Excel.
    setTimeout(getNextTemperature, 1000); // Wait 1 second before updating Excel again.
  }
  getNextTemperature();
}

function refreshTemperature(thermometerID){
  sendWebRequest(thermometerID, function(data){
    savedTemperatures[thermometerID] = data.temperature;
  });
  setTimeout(function(){
    refreshTemperature(thermometerID);
  }, 1000); // Wait 1 second before reading the thermometer again, and then update the saved temperature of thermometerID.
}

データの範囲を使用するWorking with ranges of data

カスタム関数は、入力パラメーターとしてのデータの範囲を受け入れる、もしくは、データの範囲を返すことがあります。JavaScript では、データの範囲は、2 次元配列として表されます。Your custom function may accept a range of data as an input parameter, or it may return a range of data. In JavaScript, a range of data is represented as a 2-dimensional array.

例えば、関数がExcel に保存されている数値の範囲から 2 番目に大きい値を返すとします。次の関数は、種類Excel.CustomFunctionDimensionality.matrixのパラメータvaluesを受け取ります。この関数の JSON のメタデータには、パラメーターの type プロパティを matrixに設定するよう留意してください。For example, suppose that your function returns the second highest value from a range of numbers stored in Excel. The following function accepts the parameter values, which is of type Excel.CustomFunctionDimensionality.matrix. Note that in the JSON metadata for this function, you would set the parameter's type property to matrix.

function secondHighest(values){
  let highest = values[0][0], secondHighest = values[0][0];
  for(var i = 0; i < values.length; i++){
    for(var j = 1; j < values[i].length; j++){
      if(values[i][j] >= highest){
        secondHighest = highest;
        highest = values[i][j];
      }
      else if(values[i][j] >= secondHighest){
        secondHighest = values[i][j];
      }
    }
  }
  return secondHighest;
}

エラーの処理Handling errors

カスタム関数を定義するアドインをビルドする場合は、ランタイムエラーを考慮するためのエラー処理 ロジックを含めるようにしてください。カスタム関数のエラー処理は、 大規模な Excel の JavaScript API のエラー処理と同じです。次のコード サンプルでは、 .catchがコード内で以前に発生したエラーを処理します。When you build an add-in that defines custom functions, be sure to include error handling logic to account for runtime errors. Error handling for custom functions is the same as error handling for the Excel JavaScript API at large. In the following code sample, .catch will handle any errors that occur previously in the code.

function getComment(x) {
  let url = "https://www.contoso.com/comments/" + x;

  return fetch(url)
    .then(function (data) {
      return data.json();
    })
    .then((json) => {
      return json.body;
    })
    .catch(function (error) {
      throw error;
    })
}

既知の問題Known issues

• ヘルプの URL とパラメーターの説明。Excel ではまだ使用されていません。Help URLs and parameter descriptions are not yet used by Excel.
• カスタム関数は現在、モバイル クライアント用の Excel では使用できません。Custom functions are not currently available on Excel for mobile clients.
• 揮発性関数（スプレッドシート内の無関係なデータが変更されたときに自動的に再計算する関数）はまだサポートされていません。Volatile functions (those which recalculate automatically whenever unrelated data changes in the spreadsheet) are not yet supported.
• Office 365 管理ポータルと AppSource による展開は、まだ有効になっていません。Deployment via the Office 365 Admin Portal and AppSource are not yet enabled.
• Excel Onlineでのカスタム関数は、一定期間動作していないと、セッション中に停止することがあります。ブラウザーのページを更新 (F5) し、機能を復元するカスタム関数を再入力します。Custom functions in Excel Online may stop working during a session after a period of inactivity. Refresh the browser page (F5) and re-enter a custom function to restore the feature.
• WindowsのExcelで複数のアドイン Excel for Windows で実行されている場合は、 #GETTING_DATA の一時的な結果がワークシートのセル内に表示されることがあります。その場合には、Excel のウィンドウをすべて閉じ、Excel を再起動します。You may see the #GETTING_DATA temporary result within the cell(s) of a worksheet if you have multiple add-ins running on Excel for Windows. Close all Excel windows and restart Excel.
• カスタム関数向けのデバッグ ツールが、将来利用できるようになります。それまでは、F12 開発者ツールを使用してExcel Onlineでデバッグすることができます。詳細は、カスタム関数のベスト プラクティスを参照してください。Debugging tools specifically for custom functions may be available in the future. In the meantime, you can debug on Excel Online using F12 developer tools. See more details in Custom functions best practices.

変更ログChangelog

• 2017 年 11 月 7 日: カスタム関数のプレビューとサンプルを公開*Nov 7, 2017: Shipped the custom functions preview and samples
• 2017 年 11 月 20 日: ビルド 8801 以降を使用しているユーザー向けに互換性バグを修正Nov 20, 2017: Fixed compatibility bug for those using builds 8801 and later
• 2017 年 11 月 28 日: 非同期関数のキャンセルへのサポートを公開* (ストリーム関数への変更が必要)Nov 28, 2017: Shipped support for cancellation on asynchronous functions (requires change for streaming functions)
• 2018 年 5 月 7 日: Mac、Excel Online、およびインプロセスで実行される同期関数へのサポートを公開*May 7, 2018: Shipped support for Mac, Excel Online, and synchronous functions running in-process
• 2018 年 9 月 20日: JavaScript の実行時のカスタム関数へのサポートを公開。詳細については、 Excel のカスタム関数ランタイムを参照してください。September 20, 2018: Shipped support for custom functions JavaScript runtime. For more information, see Runtime for Excel custom functions.

* Office Insiders チャネル対象* to the Office Insiders Channel

関連項目See also

• カスタム関数のメタデータCustom functions metadata
• Excel カスタム関数のランタイムRuntime for Excel custom functions
• カスタム関数のベスト プラクティスCustom functions best practices
• Excel カスタム関数のチュートリアルExcel custom functions tutorial