次の方法で共有

Excel でのカスタム関数のベスト プラクティス

この記事には、カスタム関数アドインの新しい開発者向けのヒント、ベスト プラクティス、Office アドインのエコシステム情報が含まれています。

次の図は、カスタム関数と、カスタム関数アドインに関連する 2 つのメイン コンポーネント (Excel と外部サービス) の間の相互作用を示しています。

カスタム関数アドインは Excel と外部サービスの両方と通信しますが、Excel と外部サービスは直接通信しません。

Excel を使用すると、独自のカスタム関数をアプリケーションに統合し、組み込み関数のように実行できます。

カスタム関数アドインでは、関数のロジックと、関数が Excel および Office JavaScript API とどのように対話するかを定義します。 カスタム関数アドインを作成する方法については、 カスタム関数のチュートリアルを参照してください。

外部サービスは省略可能です。 ブックの外部からデータをインポートするなどのアドイン機能を提供できます。 カスタム関数アドインは、外部データをブックに組み込む方法を指定します。 詳細については、「 カスタム関数を使用してデータを受信して処理する」を参照してください。

カスタム関数の再計算効率を最適化する

一般に、カスタム関数の再計算は 、Excel で再計算の確立されたパターンに従います。 再計算がトリガーされると、Excel は、依存関係ツリーの構築、計算チェーンの構築、セルの再計算という 3 段階のプロセスを開始します。 アドインの再計算効率を最適化するには、カスタム関数内の入れ子のレベル、Excel 計算モード、および揮発性関数の制限事項を考慮してください。

カスタム関数での入れ子

カスタム関数は別のカスタム関数を引数として受け入れることができ、引数は入れ子になったカスタム関数になります。 外部カスタム関数の再計算は、入れ子になった関数の結果に依存し、入れ子になった関数が追加されるたびに時間の消費量が増加します。 再計算効率を向上させるために、カスタム関数の入れ子になったレベルの数を最小限に抑えます。 次のコード スニペットは、同様の出力を生成するブックに値を追加するための 2 つの方法を示しています。 オプション 1 では配列を使用して値を 1 つのパラメーターとして呼び出し、 オプション 2 では 各値を個別のパラメーターとして呼び出すので、 オプション 1 の方が効率的です。

オプション 1: 入れ子に制限がある場合に効率を向上させる

注:

これが推奨アプローチです。 配列を使用して値を 1 つのパラメーターとして呼び出し、不要な入れ子を回避するため、 オプション 2 よりも効率的です。

    /**
    * Returns the sum of input numbers.
    * @customfunction
    */
    function Add(args: number[]): number {
      let total = 0;
      args.forEach(value => {
        total += value;
      });
     
      return total;
    }

オプション 2: より多くの入れ子は非効率的です

注:

この方法は推奨されません。 オプション 1 と オプション 2 は同様の出力を生成しますが、 オプション 2 はより多くのパラメーターを使用し、効率が低くなります。

    /**
    * Returns the sum of two numbers.
    * @customfunction
    */
    function Add(arg1: number, arg2: number): number {
      return arg1 + arg2;
    }

Excel の計算モード

Excel には、自動、自動除外テーブル、および手動の 3 つの計算モードがあります。 カスタム関数設計に最適な計算モードを決定するには、Excel 再計算に関する記事の「計算モード、コマンド、選択的再計算、およびデータ テーブル」セクションメイン参照してください。

シナリオに基づいて Excel.CalculationMode 列挙型 を使用してアドインの計算モードを設定します。 automatic計算モードでは、再計算が頻繁にトリガーされ、アドインの効率が低下する可能性があることに注意してください。

揮発性関数の制限事項

カスタム関数を使用すると、Excel の NOW 関数や TODAY 関数と同様に、独自の揮発性関数を作成できます。 再計算中、Excel は揮発性関数とそのすべての依存セルを含むセルを評価します。 その結果、多くの揮発性関数を使用すると再計算時間が遅くなる可能性があるため、効率を最適化するためにアドイン内の揮発性関数の数を制限します。 詳細については、「 揮発性関数と不揮発性関数」を参照してください。

効率を向上させるための設計アプローチ

カスタム関数アドインを使用すると、柔軟な設計が可能になります。つまり、さまざまなアドイン デザインでエンド ユーザーに同じ出力を生成できます。

複数の結果

複数の関数または 1 つの関数を使用して、カスタム関数から複数の結果を返すことができます。

1 つの関数で複数の結果を返すには、動的配列を使用します。 動的配列では、すべての結果の再計算をトリガーするために 1 つのセルのみを更新する必要があるため、これは通常、推奨される方法です。

動的配列の出力。

動的配列を使用すると、データセットが大きくなるほど効率が低下します。これは、再計算ごとに処理されるデータが増えるためです。 カスタム関数の動的配列の詳細については、「カスタム関数 から複数の結果を返す」を参照してください。

複数の結果を返すもう 1 つの方法は、複数の関数を使用し、関数ごとに 1 つの結果を返す方法です。 複数の関数を使用する利点は、エンド ユーザーが更新する数式を正確に決定し、その数式の再計算のみをトリガーできることです。 これは、応答が遅くなる可能性がある外部サービスに依存する場合に特に役立ちます。

動的配列ではなく、複数の関数の出力。

複雑なデータ構造

データ型 は、カスタム関数アドインで複雑なデータ構造を処理するための最良の方法です。データ型では 、Excel エラー と書式設定された数値が 倍精度でサポートされます。 データ型を使用すると、 エンティティ値カードを設計し、2 次元グリッドを超えて Excel データを拡張することもできます。

外部サービスへの呼び出しのユーザー エクスペリエンスを向上させる

カスタム関数は、Web やサーバーなど、ブック以外のリモートの場所からデータをフェッチできます。 外部サービスからのデータのフェッチの詳細については、「カスタム関数を使用した データの受信と処理」を参照してください。 外部サービスを呼び出すときの効率を維持するには、外部呼び出しをバッチ処理し、呼び出しごとのラウンドトリップ期間を最小限に抑え、エンド ユーザーに遅延を伝えるためのメッセージをアドインに含めることを検討してください。

Batch カスタム関数のリモート呼び出し

カスタム関数がリモート サービスを呼び出す場合は、バッチ処理パターンを使用して、リモート サービスへのネットワーク呼び出しの数を減らします。 詳細については、「 リモート サービスのカスタム関数呼び出しのバッチ処理」を参照してください。

ラウンドトリップ期間を最小限に抑える

リモート サービス接続は、カスタム関数のパフォーマンスに大きな影響を与える可能性があります。 この影響を軽減するには、次の方法を使用します。

  • サーバー負荷の高い処理は、カスタム関数のエンドツーエンドの待機時間を短縮するために、リモート サーバーで効率的に処理する必要があります。 たとえば、サーバー上で並列コンピューティングを設計します。 サービスが Azure にデプロイされている場合は、 Azure でハイ パフォーマンス コンピューティングを使用した最適化を検討してください。
  • アドイン フローを最適化することで、サービス呼び出しの数を減らします。 たとえば、必要な呼び出しのみをリモート サービスに送信します。

アドイン ユーザー エクスペリエンス (UX) を使用してユーザーが認識するパフォーマンスを向上させる

カスタム関数が外部サービスを呼び出している間、カスタム関数を持つセルに #BUSY! エラーが表示されます。 外部サービスの呼び出し中に遅延が避けられない場合は、アドイン作業ウィンドウを介してメッセージを提供して、エンド ユーザーに遅延を説明することを検討してください。 この情報は、期待の管理に役立ちます。 次の図は、例を示しています。

遅延メッセージには、「遅延が発生する可能性があります」と表示されます。データの準備が整いました。

カスタム関数と作業ウィンドウ間でデータを共有する方法の詳細については、「 Excel カスタム関数と作業ウィンドウ間でデータとイベントを共有する」を参照してください。

ユーザーに遅延を通知するメッセージをアドイン作業ウィンドウに表示するには、アドインで共有ランタイムが使用されていることを確認した後、次の変更を行います。

  1. taskpane.js で、通知を呼び出す関数を追加します。

    export function showNotification(message){
  const label = document.getElementById("item-subject");
  label.innerHTML = message;
}

  2. function.jsで、showNotification関数をインポートします。

    export function showNotification(message){
  const label = document.getElementById("item-subject");
  label.innerHTML = message;
}

  3. function.jsで、遅延を含む計算を実行するときにshowNotificationを呼び出します。

    export async function longCalculation(param) {
  await Office.addin.showAsTaskpane();
  showNotification("It may take some time as we prepare the data.");
  // Perform long operation
  // ...
  // ...
  return answer;
}

関連項目