Руководство: создание пользовательских функций в ExcelTutorial: Create custom functions in Excel

Пользовательские функции позволяют добавлять новые функции в Excel путем определения этих функций в JavaScript как части надстройки.Custom functions enable you 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 as they would any native function in Excel, such as SUM(). Вы можете создавать пользовательские функции, которые будут выполнять простые задачи, такие как вычисления, или более сложные задачи, такие как потоковая передача данных в режиме реального времени из Интернета на лист.You can create custom functions that perform simple tasks like calculations or more complex tasks such as streaming real-time data from the web into a worksheet.

В этом руководстве описан порядок выполнения перечисленных ниже задач.In this tutorial, you will:

  • Создание надстройки пользовательской функции с помощью генератора Yeoman для надстроек Office.Create a custom function add-in using the Yeoman generator for Office Add-ins.
  • Использование готовой пользовательской функции для выполнения простых вычисленийUse a prebuilt custom function to perform a simple calculation.
  • Создание пользовательской функции, которая получает данные из сети Интернет.Create a custom function that gets data from the web.
  • Создание пользовательской функции, которая осуществляет потоковую передачу данных в реальном времени из сети ИнтернетCreate a custom function that streams real-time data from the web.

Необходимые компонентыPrerequisites

  • Node.js (версия 8.0.0 или более поздняя)Node.js (version 8.0.0 or later)

  • GitGit

  • Последняя версия Yeoman и генератора Yeoman для надстроек Office. Выполните в командной строке указанную ниже команду, чтобы установить эти инструменты глобально.The latest version of Yeoman and the Yeoman generator for Office Add-ins. To install these tools globally, run the following command via the command prompt:

    npm install -g yo generator-office
    

    Примечание

    Даже если вы ранее установили генератор Yeoman, рекомендуем обновить его до последней версии из NPM.Even if you've previously installed the Yeoman generator, we recommend you update your package to the latest version from npm.

  • Excel в Windows (версия 1904 или более поздняя версия, подключенная к подписке на Office 365) или в ИнтернетеExcel on Windows (version 1904 or later, connected to Office 365 subscription) or on the web

Создание проекта пользовательских функцийCreate a custom functions project

Чтобы начать, вам необходимо создать проект кода для разработки надстройки пользовательской функции.To start, you'll create the code project to build your custom function add-in. Генератор Yeoman для надстроек Office настроит проект с помощью некоторых предварительно созданных настраиваемых функций, которые можно испытать. Если вы уже запустили функцию быстрого запуска пользовательских функций и создали проект, продолжайте использовать этот проект и переходите к этому шагу .The Yeoman generator for Office Add-ins will set up your project with some prebuilt custom functions that you can try out. If you have already run the custom functions quick start and generated a project, continue to use that project and skip to this step instead.

  1. Выполните следующую команду, чтобы создать проект надстройки с помощью генератора Yeoman:Run the following command to create an add-in project using the Yeoman generator:

    yo office
    

    Примечание

    При выполнении yo office команды могут появиться приглашения о политиках сбора данных Yeoman и СРЕДСТВАх CLI для надстройки Office.When you run the yo office command, you may receive prompts about the data collection policies of Yeoman and the Office Add-in CLI tools. Используйте сведения, которые предоставляются для ответа на приглашения, как они отображаются.Use the information that's provided to respond to the prompts as you see fit.

    При появлении соответствующего запроса укажите следующие сведения для создания проекта надстройки:When prompted, provide the following information to create your add-in project:

    • Выберите тип проекта: Excel Custom Functions Add-in projectChoose a project type: Excel Custom Functions Add-in project
    • Выберите тип сценария: JavaScriptChoose a script type: JavaScript
    • Как вы хотите назвать надстройку?What do you want to name your add-in? starcount

    Генератор Yeoman для надстройки Office, приглашающий к созданию пользовательских функций

    Генератор Yeoman создаст файлы проекта и установит вспомогательные компоненты Node.The Yeoman generator will create the project files and install supporting Node components.

    Совет

    Вы можете проигнорировать дальнейшие указания, которые предоставляет генератор Yeoman после создания проекта надстройки.You can ignore the next steps guidance that the Yeoman generator provides after the add-in project's been created. Пошаговые инструкции, приведенные в этой статье, содержат все рекомендации, которые необходимо выполнить в этом руководстве.The step-by-step instructions within this article provide all of the guidance you'll need to complete this tutorial.

  2. Перейдите к корневой папке проекта.Navigate to the root folder of the project.

    cd starcount
    
  3. Выполните построение проекта.Build the project.

    npm run build
    

    Примечание

    Надстройки Office должны использовать HTTPS, а не HTTP, даже в случае разработки.Office Add-ins should use HTTPS, not HTTP, even when you are developing. Если вам будет предложено установить сертификат после того, как вы запустите npm run build, примите предложение установить сертификат от генератора Yeoman.If you are prompted to install a certificate after you run npm run build, accept the prompt to install the certificate that the Yeoman generator provides.

  4. Запустите локальный веб-сервер, работающий на Node.js.Start the local web server, which runs in Node.js. Вы можете испытать надстройку настраиваемой функции в Excel в Интернете или в Windows.You can try out the custom function add-in in Excel on the web or Windows.

Чтобы протестировать надстройку в Excel для Windows или Mac, выполните следующую команду.To test your add-in in Excel on Windows or Mac, run the following command. При выполнении этой команды запустится локальный веб-сервер, и откроется приложение Excel с загруженной надстройкой.When you run this command, the local web server will start and Excel will open with your add-in loaded.

npm run start:desktop

Проверка работы готовой пользовательской функцииTry out a prebuilt custom function

Созданный проект пользовательских функций содержит некоторые предварительно созданные пользовательские функции, определенные в файле ./СРК/функтионс/функтионс.ЖС .The custom functions project that you created contains some prebuilt custom functions, defined within the ./src/functions/functions.js file. Файл ./manifest.xml указывает, что все пользовательские функции принадлежат пространству имен CONTOSO.The ./manifest.xml file specifies that all custom functions belong to the CONTOSO namespace. Вы будете использовать пространство имен CONTOSO для доступа к пользовательским функциям в Excel.You'll use the CONTOSO namespace to access the custom functions in Excel.

Затем вы проверите пользовательскую функцию ADD, выполнив описанные ниже действия:Next you'll try out the ADD custom function by completing the following steps:

  1. В Excel перейдите в любую ячейку и введите =CONTOSO.In Excel, go to any cell and enter =CONTOSO. Обратите внимание на то, что в меню автозаполнения содержится список всех функций в пространстве имен CONTOSO.Notice that the autocomplete menu shows the list of all functions in the CONTOSO namespace.

  2. Выполните запуск функции CONTOSO.ADD с числами 10 и 200 в качестве входных параметров, введя значение =CONTOSO.ADD(10,200) в ячейке и нажав клавишу ВВОД.Run the CONTOSO.ADD function, with numbers 10 and 200 as input parameters, by typing the value =CONTOSO.ADD(10,200) in the cell and pressing enter.

Пользовательская функция ADD вычисляет сумму двух чисел, которые вы указываете и возвращает результат 210.The ADD custom function computes the sum of the two numbers that you provided and returns the result of 210.

Создание пользовательской функции, которая запрашивает данные из сети ИнтернетCreate a custom function that requests data from the web

Интеграция данных из Интернета — отличный способ расширения функционала Excel через пользовательские функции.Integrating data from the Web is a great way to extend Excel through custom functions. Далее вы создадите пользовательскую функцию с именем getStarCount , которая показывает количество звезд, которыми обладает данный репозиторий GitHub.Next you’ll create a custom function named getStarCount that shows how many stars a given Github repository possesses.

  1. В проекте старкаунт найдите файл ./СРК/функтионс/функтионс.ЖС и откройте его в редакторе кода.In the starcount project, find the file ./src/functions/functions.js and open it in your code editor.

  2. В файле Function. jsдобавьте следующий код:In function.js, add the following code:

/**
  * Gets the star count for a given Github repository.
  * @customfunction 
  * @param {string} userName string name of Github user or organization.
  * @param {string} repoName string name of the Github repository.
  * @return {number} number of stars given to a Github repository.
  */
  async function getStarCount(userName, repoName) {
    try {
      //You can change this URL to any web request you want to work with.
      const url = "https://api.github.com/repos/" + userName + "/" + repoName;
      const response = await fetch(url);
      //Expect that status code is in 200-299 range
      if (!response.ok) {
        throw new Error(response.statusText)
      }
        const jsonResponse = await response.json();
        return jsonResponse.watchers_count;
    }
    catch (error) {
      return error;
    }
  }
  1. Выполните указанную ниже команду, чтобы повторно собрать проект.Run the following command to rebuild the project.

    npm run build
    
  2. Выполните следующие действия (для Excel в Интернете, Windows или Mac), чтобы повторно зарегистрировать надстройку в Excel.Complete the following steps (for Excel on the web, Windows, or Mac) to re-register the add-in in Excel. Прежде чем новая функция станет доступна, необходимо выполнить указанные ниже действия.You must complete these steps before the new function will be available.

  1. Закройте Excel, а затем откройте Excel повторно.Close Excel and then reopen Excel.

  2. В Excel перейдите на вкладку Вставка , а затем щелкните стрелку вниз, расположенную справа от моих надстроек. Вставка ленты в Excel в Windows с выделенной стрелкой "Мои надстройки"In Excel, choose the Insert tab and then choose the down-arrow located to the right of My Add-ins. Insert ribbon in Excel on Windows with the My Add-ins arrow highlighted

  3. В списке доступных надстроек найдите раздел надстройки для разработчиков и выберите надстройку старкаунт , чтобы зарегистрировать ее.In the list of available add-ins, find the Developer Add-ins section and select the starcount add-in to register it. Вставка ленты в Excel в Windows с выделенной надстройкой "пользовательские функции Excel" в списке "Мои надстройки"Insert ribbon in Excel on Windows with the Excel Custom Functions add-in highlighted in the My Add-ins list

  1. Теперь давайте оценим, как работает новая функция.Try out the new function. В ячейке B1введите текст = contoso. ЖЕТСТАРКАУНТ ("OfficeDev", "Excel-Custom-functions") и нажмите клавишу ВВОД.In cell B1, type the text =CONTOSO.GETSTARCOUNT("OfficeDev", "Excel-Custom-Functions") and press enter. Вы увидите, что в ячейке B1 получено текущее число звезд, заданное [репозиторием GitHub Excel-Custom-functions](https://github.com/OfficeDev/Excel-Custom-Functions).You should see that the result in cell B1 is the current number of stars given to the [Excel-Custom-Functions Github repository](https://github.com/OfficeDev/Excel-Custom-Functions).

Создание потоковой асинхронной пользовательской функцииCreate a streaming asynchronous custom function

getStarCount Функция возвращает число звезд, которые репозиторий содержит в определенный момент времени.The getStarCount function returns the number of stars a repository has at a specific moment in time. Пользовательские функции также могут возвращать непрерывно изменяемые данные.Custom functions can also return data that is continuously changing. Эти функции называются потоковыми функциями.These functions are called streaming functions. Они должны включать invocation параметр, который ссылается на ячейку, в которой была вызвана функция.They must include an invocation parameter which refers to the cell where the function was called from. invocation Параметр используется для обновления содержимого ячейки в любое время.The invocation parameter is used to update the contents of the cell at any time.

В приведенном ниже примере кода обратите внимание, что существуют две функции currentTime и. clockIn the following code sample, you'll notice that there are two functions, currentTime and clock. currentTime Функция — это статическая функция, которая не использует потоковую передачу.The currentTime function is a static function that does not use streaming. Он возвращает дату в виде строки.It returns the date as a string. clock Функция использует currentTime функцию, чтобы указать новое время каждую секунду для ячейки в Excel.The clock function uses the currentTime function to provide the new time every second to a cell in Excel. Он используется invocation.setResult для доставки времени в ячейку Excel и invocation.onCanceled обработки действий, выполняемых при отмене функции.It uses invocation.setResult to deliver the time to the Excel cell and invocation.onCanceled to handle what occurs when the function is canceled.

  1. В проекте старкаунт добавьте следующий код в файл ./СРК/функтионс/функтионс.ЖС и сохраните его.In the starcount project, add the following code to ./src/functions/functions.js and save the file.
/**
 * Returns the current time
 * @returns {string} String with the current time formatted for the current locale.
 */
function currentTime() {
  return new Date().toLocaleTimeString();
}

 /**
 * Displays the current time once a second
 * @customfunction
 * @param {CustomFunctions.StreamingInvocation<string>} invocation Custom function invocation
 */
function clock(invocation) {
  const timer = setInterval(() => {
    const time = currentTime();
    invocation.setResult(time);
  }, 1000);

  invocation.onCanceled = () => {
    clearInterval(timer);
  };
}
  1. Выполните указанную ниже команду, чтобы повторно собрать проект.Run the following command to rebuild the project.

    npm run build
    
  2. Выполните следующие действия (для Excel в Интернете, Windows или Mac), чтобы повторно зарегистрировать надстройку в Excel.Complete the following steps (for Excel on the web, Windows, or Mac) to re-register the add-in in Excel. Прежде чем новая функция станет доступна, необходимо выполнить указанные ниже действия.You must complete these steps before the new function will be available.

  1. Закройте Excel, а затем откройте Excel повторно.Close Excel and then reopen Excel.

  2. В Excel перейдите на вкладку Вставка , а затем щелкните стрелку вниз, расположенную справа от моих надстроек. Вставка ленты в Excel в Windows с выделенной стрелкой "Мои надстройки"In Excel, choose the Insert tab and then choose the down-arrow located to the right of My Add-ins. Insert ribbon in Excel on Windows with the My Add-ins arrow highlighted

  3. В списке доступных надстроек найдите раздел надстройки для разработчиков и выберите надстройку старкаунт , чтобы зарегистрировать ее.In the list of available add-ins, find the Developer Add-ins section and select the starcount add-in to register it. Вставка ленты в Excel в Windows с выделенной надстройкой "пользовательские функции Excel" в списке "Мои надстройки"Insert ribbon in Excel on Windows with the Excel Custom Functions add-in highlighted in the My Add-ins list

  1. Теперь давайте оценим, как работает новая функция.Try out the new function. В ячейке C1введите текст = contoso. CLOCK ()) и нажмите клавишу ВВОД.In cell C1, type the text =CONTOSO.CLOCK()) and press enter. Должна отобразиться текущая дата, которая пересылает обновление каждую секунду.You should see the current date, which streams an update every second. Несмотря на то, что часы находятся только в цикле, вы можете использовать ту же идею задания таймера для более сложных функций, которые делают веб-запросы для данных в режиме реального времени.While this clock is just a timer on a loop, you can use the same idea of setting a timer on more complex functions that make web requests for real-time data.

Дальнейшие действияNext steps

Поздравляем!Congratulations! Вы создали новый проект пользовательских функций, выполнили предварительно составленную функцию, создал пользовательскую функцию, которая запрашивает данные из веб-сайта, и создала пользовательскую функцию, которая пересылает данные.You've created a new custom functions project, tried out a prebuilt function, created a custom function that requests data from the web, and created a custom function that streams data. Вы также можете попробовать выполнить отладку этой функции , используя инструкции по отладке пользовательских функций.You can also try out debugging this function using the custom function debugging instructions. Чтобы узнать больше о пользовательских функции в Excel, перейдите к следующей статье:To learn more about custom functions in Excel, continue to the following article: