在 Excel 中创建自定义函数Create custom functions in Excel

开发人员可以借助自定义函数向 Excel 添加新函数,方法是在 JavaScript 中将这些函数定义为加载项的一部分。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 自定义函数在以下平台上可用。Note that Excel custom functions are available on the following platforms.

  • Windows 上的 Office (版本1904或更高版本, 连接到 Office 365 订阅)Office on Windows (version 1904 or later, connected to Office 365 subscription)
  • Office on Mac (版本16.24 或更高版本, 连接到 Office 365 订阅)Office on Mac (version 16.24 or later, connected to Office 365 subscription)
  • 网上的 OfficeOffice on the web

Office 2019 或更早版本中目前不支持 Excel 自定义函数。在 iPad 或一次性购买版本的 Office 中不受支持。Excel custom functions are currently not supported on iPad or in one-time purchase versions of Office 2019 or earlier.

以下动态图像显示调用你使用 JavaScript 或 Typescript 创建的函数的工作簿。The following animated image shows your workbook calling a function you've created with JavaScript or Typescript. 在此示例中,自定义函数 =MYFUNCTION.SPHEREVOLUME 计算球的体积。In this example, the custom function =MYFUNCTION.SPHEREVOLUME calculates the volume of a sphere.

animated image showing an end user inserting the MYFUNCTION.SPHEREVOLUME custom function into a cell of an Excel worksheet

以下代码定义 =MYFUNCTION.SPHEREVOLUME 自定义函数。The following code defines the custom function =MYFUNCTION.SPHEREVOLUME.

/**
 * Returns the volume of a sphere. 
 * @customfunction
 * @param {number} radius
 */
function sphereVolume(radius) {
  return Math.pow(radius, 3) * 4 * Math.PI / 3;
}

备注

本文后面的已知问题部分指定自定义函数的当前限制。The Known issues section later in this article specifies current limitations of custom functions.

如何在代码中定义自定义函数How a custom function is defined in code

如果使用 Yo Office 生成器创建 Excel 自定义函数加载项项目,会发现它可创建全面控制函数、任务窗格和加载项的文件。If you use the Yo Office generator to create an Excel custom functions add-in project, you'll find that it creates files which control your functions, your task pane, and your add-in overall. 我们将专注于对自定义函数至关重要的文件:We'll concentrate on the files that are important to custom functions:

文件File 文件格式File format 说明Description
./src/functions/functions.js./src/functions/functions.js
or
./src/functions/functions.ts./src/functions/functions.ts
JavaScriptJavaScript
or
TypeScriptTypeScript
包含定义自定义函数的代码。Contains the code that defines custom functions.
./src/functions/functions.html./src/functions/functions.html HTMLHTML 提供对定义自定义函数的 JavaScript 文件的<脚本>引用。Provides a <script> reference to the JavaScript file that defines custom functions.
./manifest.xml./manifest.xml XMLXML 指定加载项中所有自定义函数的命名空间以及此表中前面列出的 JavaScript 和 HTML 文件的位置。Specifies the namespace for all custom functions within the add-in and the location of the JavaScript and HTML files that are listed previously in this table. 它还列出了加载项可能使用的其他文件的位置,如任务窗格文件和命令文件。It also lists the locations of other files your add-in might make use of, such as the task pane files and command files.

脚本文件Script file

脚本文件 (./src/functions/functions.js or ./src/functions/functions.ts) 包含定义自定义函数的代码以及定义函数的注释。The script file (./src/functions/functions.js or ./src/functions/functions.ts) contains the code that defines custom functions and comments which define the function.

以下代码定义 add 自定义函数。The following code defines the custom function add. 代码注释用于生成描述 Excel 自定义函数的 JSON 元数据。The code comments are used to generate a JSON metadata file that describes the custom function to Excel. 首先声明所需的 @customfunction 注释,指示这是一个自定义函数。The required @customfunction comment is declared first, to indicate that this is a custom function. 此外,你将注意到声明了两个参数,即 firstsecond,后跟其 description 属性。Additionally, you'll notice two parameters are declared, first and second, which are followed by their description properties. 最后提供了 returns 描述。Finally, a returns description is given. 要详细了解自定义函数需要哪些注释,请参阅为自定义函数创建 JSON 元数据For more information about what comments are required for your custom function, see Create JSON metadata for custom functions.

/**
 * Adds two numbers.
 * @customfunction 
 * @param first First number.
 * @param second Second number.
 * @returns The sum of the two numbers.
 */

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

请注意,控制自定义函数运行时加载的 functions.html 文件必须链接至自定义函数的当前 CDN。Note that the functions.html file, which governs the loading of the custom functions runtime, must link to the current CDN for custom functions. 准备有当前版本的 Yo Office 生成器的项目引用正确的 CDN。Projects prepared with the current version of the Yo Office generator reference the correct CDN. 如果更新 2019 年 3 月或更早的自定义函数项目,则需要将以下代码复制到 functions.html 页面。If you are retrofitting a previous custom function project from March 2019 or earlier, you need to copy in the code below to the functions.html page.

<script src="https://appsforoffice.microsoft.com/lib/1/hosted/custom-functions-runtime.js" type="text/javascript"></script>

清单文件Manifest file

定义自定义函数的加载项的 XML 清单文件(Yo Office 生成器创建的项目中的 ./manifest.xml)指定加载项中所有自定义函数的命名空间以及 JavaScript、JSON 和 HTML 文件的位置。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.

下面的基本 XML 标记显示了 <ExtensionPoint><Resources> 元素的一个示例,必须在加载项清单中包含这些元素才能启用自定义函数。The following basic 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. 如果使用 Yo Office 生成器,生成的自定义函数文件将包含更复杂的清单文件,可以在此 Github 存储库中对其进行比较。If using the Yo Office generator, your generated custom function files will contain a more complex manifest file, which you can compare on this Github repository.

备注

在自定义函数 JavaScript、JSON 和 HTML 文件的清单文件中指定的 URL 必须可公开访问,并具有相同的子域。The URLs specified in the manifest file for the custom functions JavaScript, JSON, and HTML files must be publicly accessible and have the same subdomain.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<OfficeApp xmlns="http://schemas.microsoft.com/office/appforoffice/1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:bt="http://schemas.microsoft.com/office/officeappbasictypes/1.0" xmlns:ov="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="TaskPaneApp">
  <!--IMPORTANT! Id must be unique for each add-in. If you copy this manifest ensure that you change this id to your own GUID. -->
  <Id>6f4e46e8-07a8-4644-b126-547d5b539ece</Id>
  <Version>1.0.0.0</Version>
  <ProviderName>Contoso</ProviderName>
  <DefaultLocale>en-US</DefaultLocale>
  <DisplayName DefaultValue="helloworld"/>
  <Description DefaultValue="Samples to test custom functions"/>
  <SupportUrl DefaultValue="[Insert the URL of a page that provides support information for the app]" />
  <Hosts>
    <Host Name="Workbook"/>
  </Hosts>
  <DefaultSettings>
    <SourceLocation DefaultValue="https://localhost:8081/index.html"/>
  </DefaultSettings>
  <Permissions>ReadWriteDocument</Permissions>
  <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"/>
            </Script>
            <Page>
              <SourceLocation resid="HTML-URL"/>
            </Page>
            <Metadata>
              <SourceLocation resid="JSON-URL"/>
            </Metadata>
            <Namespace resid="namespace"/>
          </ExtensionPoint>
        </AllFormFactors>
      </Host>
    </Hosts>
    <Resources>
      <bt:Urls>
        <bt:Url id="JSON-URL" DefaultValue="https://subdomain.contoso.com/config/customfunctions.json"/>
        <bt:Url id="JS-URL" DefaultValue="https://subdomain.contoso.com/dist/win32/ship/index.win32.bundle"/>
        <bt:Url id="HTML-URL" DefaultValue="https://subdomain.contoso.com/index.html"/>
      </bt:Urls>
      <bt:ShortStrings>
        <bt:String id="namespace" DefaultValue="CONTOSO"/>
      </bt:ShortStrings>
    </Resources>
  </VersionOverrides>
</OfficeApp>

备注

Excel 中的函数在前面追加 XML 清单文件中指定的命名空间作为前缀。Functions in Excel are prepended by the namespace specified in your XML manifest file. 函数的命名空间在函数名称之前,并用句点分隔。A function's namespace comes before the function name and they are separated by a period. 例如,若要在 Excel 工作表的单元格中调用函数 ADD42,需输入 =CONTOSO.ADD42,因为 CONTOSO 是命名空间,ADD42 是 JSON 文件中指定的函数的名称。For example, to call the function ADD42 in the cell of an Excel worksheet, you would type =CONTOSO.ADD42, because CONTOSO is the namespace and ADD42 is the name of the function specified in the JSON file. 命名空间旨在作为公司或加载项的标识符使用。The namespace is intended to be used as an identifier for your company or the add-in. 命名空间只能包含字母数字字符和句点。A namespace can only contain alphanumeric characters and periods.

提示

如果要在多个环境中测试外接程序(例如,在开发、暂存、演示等)中,我们建议您为每个环境维护一个不同的 XML 清单文件。If you'll be testing your add-in across multiple environments (for example, in development, staging, demo, etc.), we recommend that you maintain a different XML manifest file for each environment. 在每个清单文件中,可以执行以下操作:In each manifest file, you can:

  • 指定与环境对应的 Url。Specify the URLs that correspond to the environment.
  • 在中Resources自定义DisplayName元数据值(如和标签)以指明环境,以便最终用户能够识别旁加载外接程序的相应环境。Customize metadata values like DisplayName and labels within Resources to indicate the environment, so that end users will be able to identify a sideloaded add-in's corresponding environment.
  • 如果外接程序namespace定义了自定义函数,则自定义用于指示环境的自定义函数。Customize the custom functions namespace to indicate the environment, if your add-in defines custom functions.

通过遵循本指南,您将简化测试过程,并避免在外接程序同时旁加载多个环境时出现的问题。By following this guidance, you'll streamline the testing process and avoid issues that would otherwise occur when an add-in is simultaneously sideloaded for multiple environments.

共同创作Coauthoring

借助已连接到 Office 365 订阅的 Excel 网页版和 Windows 版 Excel,可以共同创作文档;此功能可与自定义函数结合使用。Excel on the web and Windows connected to an Office 365 subscription allow you to coauthor documents and this feature works with custom functions. 如果你的工作簿使用自定义函数,系统会提示你的同事加载自定义函数的加载项。If your workbook uses a custom function, your colleague will be prompted to load the custom function's add-in. 当你们均加载此加载项后,自定义函数会通过共同创作共享结果。Once you both have loaded the add-in, the custom function will share results through coauthoring.

若要详细了解共同创作,请参阅关于 Excel 中的共同创作For more information on coauthoring, see About coauthoring in Excel.

已知问题Known issues

Excel 自定义功能 GitHub 存储库上查看已知问题。See known issues on our Excel Custom Functions GitHub repo.

后续步骤Next steps

想要试用自定义函数?Want to try out custom functions? 检查简单的自定义函数入门或更深入的自定义函数教程(如果还没有)。Check out the simple custom functions quick start or the more in-depth custom functions tutorial if you haven't already.

另一个尝试自定义函数的简单方法就是使用脚本实验室,这是一个允许您在 Excel 中试验自定义函数的加载项。Another easy way to try out custom functions is to use Script Lab, an add-in that allows you to experiment with custom functions right in Excel. 可以尝试创建自己的自定义函数或使用提供的示例。You can try out creating your own custom function or play with the provided samples.

准备详细了解自定义函数的功能?Ready to read more about the capabilities custom functions? 了解自定义函数架构的概述。Learn about an overview of the custom functions architecture.

另请参阅See also