Tento prehliadač už nie je podporovaný.
Inovujte na Microsoft Edge a využívajte najnovšie funkcie, aktualizácie zabezpečenia a technickú podporu.
Custom functions enable developers to add new functions to Excel by defining those functions in JavaScript as part of an add-in. Users within Excel can access custom functions just as they would any native function in Excel, such as SUM().
Poznámka
Custom function is a general term that is interchangeable with user-defined function. Both terms apply to VBA, COM, and Office.js add-ins. The Office Add-ins documentation uses the term custom function when referring to custom functions that use Office JavaScript APIs.
Dôležité
Note that Excel custom functions are available on the following platforms.
Excel custom functions aren't currently supported in the following:
Poznámka
The unified manifest for Microsoft 365 doesn't currently support custom functions projects. You must use the add-in only manifest for custom functions projects. For more information, see Office Add-ins manifest.
The following animated image shows your workbook calling a function you've created with JavaScript or TypeScript. In this example, the custom function =MYFUNCTION.SPHEREVOLUME calculates the volume of a sphere.
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;
}
If you use the Yeoman generator for Office Add-ins to create an Excel custom functions add-in project, it creates files which control your functions and task pane. We'll concentrate on the files that are important to custom functions.
| File | File format | Description |
|---|---|---|
| ./src/functions/functions.js or ./src/functions/functions.ts |
JavaScript or TypeScript |
Contains the code that defines custom functions. |
| ./src/functions/functions.html | HTML | Provides a <script> reference to the JavaScript file that defines custom functions. |
| ./manifest.xml | XML | Specifies the location of multiple files that your custom function use, such as the custom functions JavaScript, JSON, and HTML files. It also lists the locations of task pane files, command files, and specifies which runtime your custom functions should use. |
Prepitné
The Yeoman generator for Office Add-ins offers multiple Excel Custom Functions projects. We recommend selecting the project type Excel Custom Functions using a Shared Runtime and the script type JavaScript.
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.
The following code defines the custom function add. The code comments are used to generate a JSON metadata file that describes the custom function to Excel. The required @customfunction comment is declared first, to indicate that this is a custom function. Next, two parameters are declared, first and second, followed by their description properties. Finally, a returns description is given. For more information about what comments are required for your custom function, see Autogenerate 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;
}
Prepitné
In Excel on the web, custom function descriptions and parameter descriptions display inline. This gives users additional information when writing custom functions. Learn how to configure the inline descriptions by exploring any of the custom functions Script Lab samples in Excel on the web. See the following screenshot for an example.
The add-in only manifest file for an add-in that defines custom functions (./manifest.xml in the project that the Yeoman generator for Office Add-ins creates) does several things.
To see a full working manifest from a sample add-in, see the manifest in the one of our Office Add-in samples Github repositories.
Prepitné
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 manifest file for each environment. In each manifest file, you can:
Specify the URLs that correspond to the environment.
Customize metadata values so that end users are able to identify a sideloaded add-in's corresponding environment. For example:
DisplayName element and and labels within the Resources element to indicate the environment.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.
Excel on the web and on Windows connected to a Microsoft 365 subscription allow end users to coauthor in Excel. If an end user's workbook uses a custom function, that end user's coauthoring colleague is prompted to load the corresponding custom functions add-in. Once both users have loaded the add-in, the custom function shares results through coauthoring.
For more information on coauthoring, see About coauthoring in Excel.
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.
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.
Pripomienky k produktu Office Add-ins
Office Add-ins je open-source projekt. Ak chcete poskytnúť pripomienky, vyberte prepojenie:
Školenie
Modul
Build Office Add-ins for Excel - Training
This module walks through development of Office Add-ins for Microsoft Excel.
Certifikácia
Microsoft Office Specialist: Excel Expert (Microsoft 365 Apps) - Certifications
Demonstrate that you have the advanced skills needed to get the most out of Excel (Microsoft 365 Apps) by earning a Microsoft Office Specialist (MOS) Expert certification.