自定义函数参数选项

项目
07/05/2023

自定义函数可以使用许多不同的参数选项进行配置。

重要

请注意，以下平台上可以使用 Excel 自定义函数。

Office 网页版
Windows 版 Office
- Microsoft 365 订阅
- 零售永久 Office 2016 及更高版本
- 批量许可永久Office 2021及更高版本
Mac 版 Office

以下各项当前不支持 Excel 自定义函数：

iPad 版 Office
Windows 上 Office 2019 或更早版本的批量许可永久版本

可选参数

当用户在 Excel 中调用函数时，可选参数将显示在括号中。在以下示例中，add 函数可以选择添加第三个数字。此函数在 Excel 中显示为。=CONTOSO.ADD(first, second, [third])

JavaScript
TypeScript

/**
 * Calculates the sum of the specified numbers
 * @customfunction
 * @param {number} first First number.
 * @param {number} second Second number.
 * @param {number} [third] Third number to add. If omitted, third = 0.
 * @returns {number} The sum of the numbers.
 */
function add(first, second, third) {
  if (third === null) {
    third = 0;
  }
  return first + second + third;
}

/**
 * Calculates the sum of the specified numbers
 * @customfunction
 * @param first First number.
 * @param second Second number.
 * @param [third] Third number to add. If omitted, third = 0.
 * @returns The sum of the numbers.
 */
function add(first: number, second: number, third?: number): number {
  if (third === null) {
    third = 0;
  }
  return first + second + third;
}

注意

如果未为可选参数指定任何值，Excel 将为其分配值 null。这意味着 TypeScript 中默认初始化的参数将无法按预期工作。不要使用语法 function add(first:number, second:number, third=0):number ，因为它不会初始化 third 为 0。请改用 TypeScript 语法，如上一示例所示。

定义包含一个或多个可选参数的函数时，请指定可选参数为 null 时发生的情况。在以下示例中，zipCode 和 dayOfWeek 都是 getWeatherReport 函数的可选参数。如果参数 zipCode 为 null，则默认值设置为 98052。 dayOfWeek如果参数为 null，则将其设置为星期三。

JavaScript
TypeScript

/**
 * Gets a weather report for a specified zipCode and dayOfWeek
 * @customfunction
 * @param {number} [zipCode] Zip code. If omitted, zipCode = 98052.
 * @param {string} [dayOfWeek] Day of the week. If omitted, dayOfWeek = Wednesday.
 * @returns {string} Weather report for the day of the week in that zip code.
 */
function getWeatherReport(zipCode, dayOfWeek) {
  if (zipCode === null) {
    zipCode = 98052;
  }

  if (dayOfWeek === null) {
    dayOfWeek = "Wednesday";
  }

  // Get weather report for specified zipCode and dayOfWeek.
  // ...
}

/**
 * Gets a weather report for a specified zipCode and dayOfWeek
 * @customfunction
 * @param zipCode Zip code. If omitted, zipCode = 98052.
 * @param [dayOfWeek] Day of the week. If omitted, dayOfWeek = Wednesday.
 * @returns Weather report for the day of the week in that zip code.
 */
function getWeatherReport(zipCode?: number, dayOfWeek?: string): string {
  if (zipCode === null) {
    zipCode = 98052;
  }

  if (dayOfWeek === null) {
    dayOfWeek = "Wednesday";
  }

  // Get weather report for specified zipCode and dayOfWeek.
  // ...
}

范围参数

自定义函数可以接受单元格数据范围作为输入参数。函数还可以返回一系列数据。 Excel 将单元格数据区域作为二维数组传递。

例如，假设函数从 Excel 中存储的数字区域返回第二个最高值。以下函数接受参数 values，JSDOC 语法 number[][] 在此函数的 dimensionality JSON 元数据中将参数的属性设置为 matrix 。

/**
 * Returns the second highest value in a matrixed range of values.
 * @customfunction
 * @param {number[][]} values Multiple ranges of values.
 */
function secondHighest(values) {
  let highest = values[0][0],
    secondHighest = values[0][0];
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; 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;
}

重复参数

重复参数允许用户为函数输入一系列可选参数。调用函数时，这些值在参数的数组中提供。如果参数名称以数字结尾，则每个参数的数目将递增，例如 ADD(number1, [number2], [number3],…)。这与用于内置 Excel 函数的约定匹配。

以下函数对输入的数字、单元格地址和范围的总和求和。

/**
* The sum of all of the numbers.
* @customfunction
* @param operands A number (such as 1 or 3.1415), a cell address (such as A1 or $E$11), or a range of cell addresses (such as B3:F12)
*/

function ADD(operands: number[][][]): number {
  let total: number = 0;

  operands.forEach(range => {
    range.forEach(row => {
      row.forEach(num => {
        total += num;
      });
    });
  });

  return total;
}

此函数显示在 =CONTOSO.ADD([operands], [operands]...) Excel 工作簿中。

正在将 ADD 自定义函数输入到 Excel 工作表的单元格中

重复单值参数

重复的单值参数允许传递多个单个值。例如，用户可以输入 ADD (1，B2,3) 。下面的示例演示如何声明单个值参数。

/**
 * @customfunction
 * @param {number[]} singleValue An array of numbers that are repeating parameters.
 */
function addSingleValue(singleValue) {
  let total = 0;
  singleValue.forEach(value => {
    total += value;
  })

  return total;
}

单范围参数

从技术上讲，单个范围参数不是重复参数，但此处包含，因为声明与重复参数非常相似。它将在用户面前显示为 ADD (A2：B3) 从 Excel 传递单个区域。下面的示例演示如何声明单个范围参数。

/**
 * @customfunction
 * @param {number[][]} singleRange
 */
function addSingleRange(singleRange) {
  let total = 0;
  singleRange.forEach(setOfSingleValues => {
    setOfSingleValues.forEach(value => {
      total += value;
    })
  })
  return total;
}

重复范围参数

重复范围参数允许传递多个范围或数字。例如，用户可以输入 ADD (5，B2，C3,8，E5：E8) 。重复范围通常由类型 number[][][] 指定，因为它们是三维矩阵。有关示例，请参阅为重复参数列出的main示例。

声明重复参数

在 Typescript 中，指示参数是多维的。例如， ADD(values: number[]) 将指示一维数组， ADD(values:number[][]) 表示二维数组，等等。

在 JavaScript 中，将用于 @param values {number[]} 一维数组， @param <name> {number[][]} 将用于二维数组，等等用于更多维度。

对于手动创作的 JSON，请确保参数在 JSON 文件中指定为 "repeating": true ，并检查参数标记为 "dimensionality": matrix。

调用参数

每个自定义函数都会自动传递参数作为最后一个 invocation 输入参数，即使它未显式声明。此参数 invocation 对应于 Invocation 对象。对象 Invocation 可用于检索其他上下文，例如调用自定义函数的单元格的地址。若要访问对象 Invocation ，必须在自定义函数中声明 invocation 为最后一个参数。

注意

参数 invocation 不显示为 Excel 中用户的自定义函数参数。

下面的示例演示如何使用 invocation 参数返回调用自定义函数的单元格的地址。此示例使用对象的 address 属性 Invocation 。若要访问对象 Invocation ，请先在 JSDoc 中声明 CustomFunctions.Invocation 为参数。接下来，在 JSDoc 中声明 @requiresAddress 以访问 address 对象的属性 Invocation 。最后，在函数中，检索并返回属性 address 。

/**
 * Return the address of the cell that invoked the custom function. 
 * @customfunction
 * @param {number} first First parameter.
 * @param {number} second Second parameter.
 * @param {CustomFunctions.Invocation} invocation Invocation object. 
 * @requiresAddress 
 */
function getAddress(first, second, invocation) {
  const address = invocation.address;
  return address;
}

在 Excel 中，调用 address 对象的属性的 Invocation 自定义函数将返回调用函数的单元格中格式 SheetName!RelativeCellAddress 后面的绝对地址。例如，如果输入参数位于单元格 F6 中名为 “价格” 的工作表上，则返回的参数地址值为 Prices!F6。

注意

如果工作表名称中存在空格或以下任何字符： ~ ' ！ @ # $ % ^ & ( ) - _ = + { } | ; ：， ' < ，>则返回地址中的工作表名称用单引号引起，因此格式为 'SheetName'!RelativeCellAddress;例如。 'Latest Prices'!F6 如果单引号 (撇号) 字符'，则返回的地址在一行中包含两个此类字符：例如。 'Bob''s Region'!F6

参数 invocation 还可用于将信息发送到 Excel。有关详细信息，请参阅创建流式处理函数。

检测参数的地址

结合调用参数，可以使用 Invocation 对象检索自定义函数输入参数的地址。调用时，对象的 parameterAddresses 属性 Invocation 允许函数返回所有输入参数的地址。

这在输入数据类型可能有所不同的情况下非常有用。输入参数的地址可用于检查输入值的数字格式。然后，如有必要，可以在输入之前调整数字格式。输入参数的地址还可用于检测输入值是否具有可能与后续计算相关的任何相关属性。

注意

如果使用手动创建的 JSON 元数据来返回参数地址而不是 Office 外接程序的 Yeoman 生成器，则 options 对象必须将 requiresParameterAddresses 属性设置为 true，并且 result 对象必须将 dimensionality 属性设置为 matrix。

以下自定义函数采用三个输入参数，检索 parameterAddresses 每个参数的 Invocation 对象的属性，然后返回地址。

/**
 * Return the addresses of three parameters. 
 * @customfunction
 * @param {string} firstParameter First parameter.
 * @param {string} secondParameter Second parameter.
 * @param {string} thirdParameter Third parameter.
 * @param {CustomFunctions.Invocation} invocation Invocation object. 
 * @returns {string[][]} The addresses of the parameters, as a 2-dimensional array. 
 * @requiresParameterAddresses
 */
function getParameterAddresses(firstParameter, secondParameter, thirdParameter, invocation) {
  const addresses = [
    [invocation.parameterAddresses[0]],
    [invocation.parameterAddresses[1]],
    [invocation.parameterAddresses[2]]
  ];
  return addresses;
}

当调用属性的 parameterAddresses 自定义函数运行时，将按照调用函数的单元格中的格式 SheetName!RelativeCellAddress 返回参数地址。例如，如果输入参数位于单元格 D8 中名为 “成本” 的工作表上，则返回的参数地址值为 Costs!D8。如果自定义函数具有多个参数并返回多个参数地址，则返回的地址将溢出到多个单元格，从调用函数的单元格垂直下降。

注意

后续步骤

了解如何在自定义函数中使用可变值。