セル値に参照メソッドを追加する

セル値に参照メソッドを追加して、セル値に基づく動的計算にユーザーがアクセスできるようにします。 EntityCellValue型とLinkedEntityCellValue型では、参照メソッドがサポートされています。 たとえば、重みを別の単位に変換する製品エンティティ値にメソッドを追加します。

次の図は、パンケーキ ミックスを表す製品エンティティ値に ConvertWeight メソッドを追加する例を示しています。

DoubleCellValue、BooleanCellValue、およびStringCellValue型も参照メソッドをサポートします。 次の図は、二重値型に ConvertToRomanNumeral メソッドを追加する例を示しています。

参照メソッドは、カードデータ型には表示されません。

エンティティ値に参照メソッドを追加する

エンティティ値に参照メソッドを追加するには、 Excel.JavaScriptCustomFunctionReferenceCellValue 型を使用して JSON で定義します。 次のコード サンプルは、値 27 を返す単純なメソッドを定義する方法を示しています。

const referenceCustomFunctionGet27: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "GET27" 
} 

プロパティについては、次の表を参照してください。

プロパティ	説明
type	参照の種類を指定します。 このプロパティは function のみをサポートし、 Excel.CellValueType.functionに設定する必要があります。
functionType	関数の種類を指定します。 このプロパティは JavaScript 参照関数のみをサポートしており、 Excel.FunctionCellValueType.javaScriptReferenceに設定する必要があります。
Namespace	カスタム関数を含む名前空間。 この値は、統合マニフェストの customFunctions.namespace 要素 で指定された名前空間、またはアドインのみのマニフェストの Namespace 要素 と一致する必要があります。
id	この参照メソッドにマップするカスタム関数の名前。 名前は、カスタム関数名の大文字バージョンです。

エンティティ値を作成するときに、参照メソッドをプロパティ リストに追加します。 次のコード サンプルは、 Math という名前の単純なエンティティ値を作成し、それに参照メソッドを追加する方法を示しています。 Get27 は、ユーザーに表示されるメソッド名です (例: A1.Get27())。

function makeMathEntity(value: number){
  const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: "Math value",
    properties: {
      "value": {
        type: Excel.CellValueType.double,
        basicValue: value,
        numberFormat: "#"
      },
      Get27: referenceCustomFunctionGet27
    }
  };
  return entity;
}

次のコード サンプルは、 Math エンティティのインスタンスを作成し、選択したセルに追加する方法を示しています。

// Add entity to selected cell.
async function addEntityToCell(){
  const entity: Excel.EntityCellValue = makeMathEntity(10);
  await Excel.run( async (context) => {
    const cell = context.workbook.getActiveCell();
    cell.valuesAsJson = [[entity]];
    await context.sync();
  });
}

最後に、カスタム関数を使用して参照メソッドを実装します。 次のコード サンプルは、カスタム関数を実装する方法を示しています。

/**
 * Returns the value 27.
 * @customfunction
 * @excludeFromAutoComplete
 * @returns {number} 27
 */
function get27() {
  return 27;
}

前のコード サンプルでは、 @excludeFromAutoComplete タグを使用すると、ユーザーが検索ボックスに入力したときにカスタム関数が Excel UI に表示されないようにします。 ただし、ユーザーが直接セルに入力した場合でも、ユーザーはエンティティ値とは別にカスタム関数を呼び出すことができます。

コードを実行すると、次の図に示すように、 Math エンティティ値が作成されます。 メソッドは、ユーザーが数式からエンティティ値を参照するときに、数式 AutoComplete に表示されます。

引数を追加する

参照メソッドに引数が必要な場合は、それらをカスタム関数に追加します。 次のコード例は、 x という名前の引数を addValue という名前のメソッドに追加する方法を示しています。 メソッドは、addValue という名前のカスタム関数を呼び出すことによって、x値に 1 つを追加します。

/**
 * Adds a value to 1.
 * @customfunction
 * @excludeFromAutoComplete
 * @param {number} x The value to add to 1.
 * @return {number[][]}  Sum of x and 1.
 */
function addValue(x): number[][] {  
  return [[x+1]];
}

エンティティ値を呼び出し元オブジェクトとして参照する

一般的なシナリオは、計算を実行するために、メソッドがエンティティ値自体のプロパティを参照する必要があるということです。 たとえば、 addValue メソッドがエンティティ値自体に引数値を追加する場合に便利です。 次のコード例に示すように、 @capturesCallingObject タグをカスタム関数に適用して、エンティティ値を最初の引数として渡すように指定します。

/**
 * Adds x to the calling object.
 * @customfunction
 * @excludeFromAutoComplete
 * @capturesCallingObject
 * @param {any} math The math object (calling object).
 * @param {number} x The value to add.
 * @return {number[][]}  Sum.
 */
function addValue(math, x): number[][] {  
  const result: number = math.properties["value"].basicValue + x;
  return [[result]];
}

数式の [名前] で Excel 構文規則に準拠する任意の引数 名を使用できます。 これは数学エンティティであるため、呼び出し元のオブジェクト引数には math という名前が付けられます。 引数名は、計算で使用できます。

前のコード サンプルについては、次の点に注意してください。

• @excludeFromAutoComplete タグを使用すると、ユーザーが検索ボックスに入力したときにカスタム関数が Excel UI に表示されないようにします。 ただし、ユーザーが直接セルに入力した場合でも、ユーザーはエンティティ値とは別にカスタム関数を呼び出すことができます。
• 呼び出し元オブジェクトは常に最初の引数として渡され、 any型である必要があります。 この場合、 math という名前で、 math オブジェクトから value プロパティを取得するために使用されます。
• 数値の二重配列を返します。
• ユーザーが Excel で参照メソッドを操作すると、呼び出し元のオブジェクトが引数として表示されません。

例: 製品の売上税を計算する

次のコードは、製品の単価の売上税を計算するカスタム関数を実装する方法を示しています。

/**
 * Calculates the price when a sales tax rate is applied.
 * @customfunction
 * @excludeFromAutoComplete
 * @capturesCallingObject
 * @param {any} product The product entity value (calling object).
 * @param {number} taxRate The tax rate (0.11 = 11%).
 * @return {number[][]}  Product unit price with tax rate applied.
 */
function applySalesTax(product, taxRate): number[][] {
  const unitPrice: number = product.properties["Unit Price"].basicValue;
  const result: number = unitPrice * taxRate + unitPrice;
  return [[result]];
}

次のコード サンプルは、参照メソッドを指定し、applySalesTaxカスタム関数のidを含む方法を示しています。

const referenceCustomFunctionCalculateSalesTax: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "APPLYSALESTAX" 
} 

次のコードは、 product エンティティ値に参照メソッドを追加する方法を示しています。

function makeProductEntity(productID: number, productName: string, price: number) {
  const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: productName,
    properties: {
      "Product ID": {
        type: Excel.CellValueType.string,
        basicValue: productID.toString() || ""
      },
      "Product Name": {
        type: Excel.CellValueType.string,
        basicValue: productName || ""
      },
      "Unit Price": {
        type: Excel.CellValueType.formattedNumber,
        basicValue: price,
        numberFormat: "$* #,##0.00"
      },
      applySalesTax: referenceCustomFunctionCalculateSalesTax
    },
  };
  return entity;
}

Excel UI からカスタム関数を除外する

参照メソッドで使用されるカスタム関数の JSDoc タグの @excludeFromAutoComplete タグを使用して、関数が数式オートコンプリートおよび数式ビルダーから除外されることを示します。 これにより、ユーザーがエンティティ値とは別にカスタム関数を誤って使用するのを防ぐことができます。

注:

関数がグリッドに手動で正しく入力された場合、関数は引き続き実行されます。

重要

関数には @excludeFromAutoComplete と @linkedEntityLoadService の両方のタグを含めることはできません。

@excludeFromAutoComplete タグはビルド中に処理され、Custom-Functions-Metadata パッケージによってfunctions.json ファイルが生成されます。 このパッケージは、Office アドイン用 Yeoman ジェネレーターを使用してアドインを作成し、カスタム関数テンプレートを選択すると、ビルド プロセスに自動的に追加されます。 Custom-Functions-Metadata パッケージを使用していない場合は、excludeFromAutoComplete プロパティをfunctions.json ファイルに手動で追加する必要があります。

次のコード サンプルは、functions.json ファイルで JSON を使用してAPPLYSALESTAXカスタム関数を手動で定義する方法を示しています。 excludeFromAutoComplete プロパティは true に設定されています。

{
    "description": "Calculates the price when a sales tax rate is applied.",
    "id": "APPLYSALESTAX",
    "name": "APPLYSALESTAX",
    "options": {
        "excludeFromAutoComplete": true,
        "capturesCallingObject": true
    },
    "parameters": [
        {
            "description": "The product entity value (calling object).",
            "name": "product",
            "type": "any"
        },
        {
            "description": "The tax rate (0.11 = 11%).",
            "name": "taxRate",
            "type": "number"
        }
    ],
    "result": {
        "dimensionality": "matrix",
        "type": "number"
    }
},

詳細については、「 カスタム関数の JSON メタデータを手動で作成する」を参照してください。

基本値型に関数を追加する

Boolean、double、stringの基本的な値の種類に関数を追加するには、エンティティ値の場合と同じプロセスを使用します。 次のコード サンプルは、 addValue というカスタム関数を使用して二重の基本値を作成する方法を示しています。 関数は、基本値に x 値を追加します。

/**
 * Adds the value x to the number value.
 * @customfunction
 * @capturesCallingObject
 * @param {any} numberValue The number value (calling object).
 * @param {number} x The value to add.
 * @return {number[][]}  Sum of the number value and x.
 */
export function addValue(numberValue: any, x: number): number[][] {
  return [[x+numberValue.basicValue]];
}

次のコード サンプルは、前のサンプル addValue カスタム関数を JSON で定義し、それを createSimpleNumber というメソッドで参照する方法を示しています。

const referenceCustomFunctionAddValue: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "ADDVALUE" 
} 

async function createSimpleNumber() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const range = sheet.getRange("A1");
    range.valuesAsJson = [
      [
        {
          type: Excel.CellValueType.double,
          basicType: Excel.RangeValueType.double,
          basicValue: 6.0,
          properties: {
            addValue: referenceCustomFunctionAddValue
          }
        }
      ]
    ];
    await context.sync();
  });
}

Optional 引数

次のコード サンプルは、省略可能な引数を受け入れる参照メソッドを作成する方法を示しています。 参照メソッドは generateRandomRange という名前で、ランダムな値の範囲を生成します。

const referenceCustomFunctionOptional: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "GENERATERANDOMRANGE" 
}

function makeProductEntity(productID: number, productName: string, price: number) {
  const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: productName,
    properties: {
      "Product ID": {...},
      "Product Name": {...},
      "Unit Price": {...},
      generateRandomRange: referenceCustomFunctionOptional
    },
  };
  return entity;
}

次のコード サンプルは、 generateRandomRange という名前のカスタム関数としての参照メソッドの実装を示しています。 指定した rows と columns の数に一致するランダム値の動的配列を返します。 min引数とmax引数は省略可能であり、指定しない場合は既定で1と10になります。

/**
 * Generates a dynamic array of random numbers.
 * @customfunction
 * @excludeFromAutoComplete
 * @param {number} rows Number of rows to generate.
 * @param {number} columns Number of columns to generate.
 * @param {number} [min] Lowest number that can be generated. Default is 1.
 * @param {number} [max] Highest number that can be generated. Default is 10.
 * @returns {number[][]} A dynamic array of random numbers.
 */
function generateRandomRange(rows, columns, min, max) {
  // Set defaults for any missing optional arguments.
  if (min === undefined) min = 1;
  if (max === undefined) max = 10;

  let numbers = new Array(rows);
  for (let r = 0; r < rows; r++) {
    numbers[r] = new Array(columns);
    for (let c = 0; c < columns; c++) {
      numbers[r][c] = Math.round(Math.random() * (max - min) ) + min;
    }
  }
  return numbers;
}

ユーザーが Excel でカスタム関数を入力すると、AutoComplete は関数のプロパティを表示し、省略可能な引数を角かっこ ([]) で囲んで示します。 次の図は、 generateRandomRange 参照メソッドを使用して省略可能なパラメーターを入力する例を示しています。

複数のパラメーター

参照メソッドは、Excel SUM 関数が複数のパラメーターをサポートする方法と同様に、複数のパラメーターをサポートします。 次のコード サンプルは、products 配列に渡された 0 個以上の製品名を連結する参照関数を作成する方法を示しています。 関数は、 concatProductNames([products], ...)としてユーザーに表示されます。

/** 
 * @customfunction 
 * @excludeFromAutoComplete 
 * @description Concatenate the names of given products, joined by " | " 
 * @param {any[]} products - The products to concatenate.
 * @returns A string of concatenated product names. 
 */ 
function concatProductNames(products: any[]): string { 
  return products.map((product) => product.properties["Product Name"].basicValue).join(" | "); 
}

次のコード サンプルは、 concatProductNames 参照メソッドを使用してエンティティを作成する方法を示しています。

const referenceCustomFunctionMultiple: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "CONCATPRODUCTNAMES" 
} 

function makeProductEntity(productID: number, productName: string, price: number) {
  const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: productName,
    properties: {
      "Product ID": {...},
      "Product Name": {...},
      "Unit Price": {...},
      concatProductNames: referenceCustomFunctionMultiple,
    },
  };
  return entity;
}

次の図は、 concatProductNames 参照メソッドを使用して複数のパラメーターを入力する例を示しています。

範囲を持つ複数のパラメーター

B1:B3 などの参照メソッドへの範囲の渡しをサポートするには、多次元配列を使用します。 次のコード サンプルは、範囲を含めることができる 0 個以上のパラメーターを合計する参照関数を作成する方法を示しています。

/** 
 * @customfunction 
 * @excludeFromAutoComplete 
 * @description Calculate the sum of arbitrary parameters. 
 * @param {number[][][]} operands - The operands to sum. 
 * @returns The sum of all operands. 
 */ 
function sumAll(operands: number[][][]): number { 
  let total: number = 0; 
 
  operands.forEach(range => { 
    range.forEach(row => { 
      row.forEach(num => { 
        total += num; 
      }); 
    }); 
  }); 
 
  return total; 
} 

次のコード サンプルは、 sumAll 参照メソッドを使用してエンティティを作成する方法を示しています。

const referenceCustomFunctionRange: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "SUMALL" 
} 

function makeProductEntity(productID: number, productName: string, price: number) {
  const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: productName,
    properties: {
      "Product ID": {...},
      "Product Name": {...},
      "Unit Price": {...},
      sumAll: referenceCustomFunctionRange
    },
  };
  return entity;
}

次の図は、 sumAll 参照メソッドを使用して、範囲パラメーターを含む複数のパラメーターを入力する例を示しています。

サポートの詳細

参照メソッドは、 揮発性 関数や ストリーミング 関数など、すべてのカスタム関数型でサポートされています。 すべてのカスタム関数の戻り値の型 (マトリックス、スカラー、エラー) がサポートされています。

重要

リンクされたエンティティには、参照メソッドとデータ プロバイダーの両方を組み合わせたカスタム関数を使用することはできません。 リンクされたエンティティを開発するときは、これらの種類のカスタム関数を個別に保持します。

関連項目

• Excel でカスタム関数を作成する
• カスタム関数用の JSON メタデータの自動生成
• カスタム関数の JSON メタデータを手動で作成する