Excel 中自定义函数的最佳做法

2025-06-27

本文包含面向自定义函数加载项的新开发人员的提示、最佳做法和 Office 加载项生态系统信息。

下图演示了自定义函数与自定义函数加载项中涉及的两个main组件之间的交互：Excel 和外部服务。

自定义函数加载项与 Excel 和外部服务通信，但 Excel 和外部服务不会直接相互通信。

Excel 允许你将自己的自定义函数集成到应用程序中，并像内置函数一样运行它们。

自定义函数加载项定义函数的逻辑，以及它们与 Excel 和 Office JavaScript API 交互的方式。若要了解如何创建自定义函数加载项，请参阅自定义函数教程。

外部服务是可选的。它可以提供加载项功能，例如从工作簿外部导入数据。自定义函数加载项指定如何将外部数据合并到工作簿中。若要了解详细信息，请参阅使用自定义函数接收和处理数据。

优化自定义函数重新计算效率

通常，自定义函数重新计算遵循 Excel 中已建立的重新计算模式。触发重新计算时，Excel 将进入一个三阶段过程：构造依赖项树、构造计算链，然后重新计算单元格。若要优化外接程序中的重新计算效率，请考虑自定义函数中的嵌套级别、Excel 计算模式以及易失性函数的限制。

在自定义函数中嵌套

自定义函数可以接受另一个自定义函数作为参数，使该参数成为嵌套的自定义函数。外部自定义函数的重新计算取决于嵌套函数的结果，导致每个附加嵌套函数的时间消耗增加。最大程度地减少自定义函数中的嵌套级别数，以提高重新计算效率。以下代码片段演示了在工作簿中添加生成类似输出的值的两种方法。 选项 1 使用数组作为单个参数调用值，而 选项 2 将每个值作为单独的参数调用，因此 选项 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 有三种计算模式：自动、自动（表除外）和“手动”。若要确定哪种计算模式最适合自定义函数设计，请参阅main Excel 重新计算一文中的计算模式、命令、选择性重新计算和数据表部分。

根据方案使用 Excel.CalculationMode 枚举为加载项设置计算模式。请注意， automatic 计算模式可能会经常触发重新计算，并降低加载项的效率。

易失性函数限制

自定义函数允许创建自己的可变函数，类似于 NOW Excel 中的和 TODAY 函数。在重新计算期间，Excel 会计算包含可变函数及其所有依赖单元格的单元格。因此，使用许多易失性函数可能会延长重新计算时间，因此请限制加载项中的可变函数数量以优化效率。有关详细信息，请参阅易失性函数和非易失性函数。

提高效率的设计方法

自定义函数加载项允许灵活的设计，这意味着不同的外接程序设计可以为最终用户生成相同的输出。

多个结果

可以使用多个函数或一个函数从自定义函数返回多个结果。

若要使用一个函数返回多个结果，请使用动态数组。这通常是建议的方法，因为动态数组只需更新单个单元格即可触发所有结果的重新计算。

动态数组的输出。

请记住，使用动态数组的效率越低，数据集越大，因为每次重新计算都会处理更多的数据。若要详细了解自定义函数中的动态数组，请参阅从自定义函数返回多个结果。

返回多个结果的另一种方法是使用多个函数，并为每个函数返回单个结果。使用多个函数的好处是，最终用户可以精确决定要更新的公式，然后仅触发该公式的重新计算。当依赖于响应速度缓慢的外部服务时，这尤其有用。

多个函数而不是动态数组的输出。

复杂数据结构

数据类型是处理自定义函数外接程序中复杂数据结构的最佳方式。数据类型支持将 Excel 错误和格式数字设置为双精度值。数据类型还允许设计实体值卡，将 Excel 数据扩展到 2 维网格之外。

改善调用外部服务的用户体验

自定义函数可以从工作簿以外的远程位置（如 Web 或服务器）提取数据。有关从外部服务提取数据的详细信息，请参阅使用自定义函数接收和处理数据。若要在调用外部服务时保持效率，请考虑对外部调用进行批处理，最大程度地缩短每次调用的往返持续时间，并在外接程序中包含消息，以便将延迟传达给最终用户。

批处理自定义函数远程调用

如果自定义函数调用远程服务，请使用批处理模式减少对远程服务的网络调用数。若要了解详细信息，请参阅对远程服务的自定义函数调用进行批处理。

最小化往返持续时间

远程服务连接可能会对自定义函数性能产生很大影响。若要减少这种影响，请使用以下策略：

应在远程服务器中高效处理服务器密集型处理，以缩短自定义函数的端到端延迟。例如，在服务器上设计并行计算。如果服务部署在 Azure 上，请考虑在 Azure 上使用高性能计算进行优化。
通过优化外接程序流减少服务调用数。例如，仅向远程服务发送必要的调用。

通过外接程序用户体验 (UX) 提高用户感知的性能

当自定义函数调用外部服务时，具有自定义函数的单元格会显示 #BUSY！ 错误。如果调用外部服务时不可避免地出现延迟，请考虑通过加载项任务窗格提供消息，以便向最终用户解释延迟。此信息有助于管理其期望。下图显示了一个示例。

延迟消息显示'可能存在延迟。我们正在为你准备好数据。

有关如何在自定义函数和任务窗格之间共享数据的详细信息，请参阅在 Excel 自定义函数和任务窗格之间共享数据和事件。

若要在外接程序任务窗格中显示通知用户延迟的消息，请在确保外接程序使用共享运行时后进行以下更改。

在 taskpane.js 添加调用通知的函数。

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

在 function.js中，导入 showNotification 函数。

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

在 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;
}

通过