Создание пользовательских функций в Excel

  • Статья

Пользовательские функции позволяют разработчикам добавлять новые функции в Excel путем определения этих функций в JavaScript как части надстройки. Пользователи в Excel могут получить доступ к пользовательским функциям так же, как и к любой встроенной функции в Excel, например SUM().

Примечание.

Пользовательская функция — это общий термин, который является взаимозаменяемым с определяемой пользователем функцией. Оба условия применяются к надстройкам VBA, COM и Office.js. В документации по надстройкам Office термин "настраиваемая функция " используется при обращении к пользовательским функциям, использующим API JavaScript для Office.

Важно!

Обратите внимание, что настраиваемые функции доступны в Excel на следующих платформах.

  • Office в Интернете
  • Office для Windows
    • Подписка на Microsoft 365
    • Розничный бессрочный Office 2016 и более поздних версий
    • Корпоративный бессрочный Office 2021 и более поздних версий
  • Office для Mac

Пользовательские функции Excel в настоящее время не поддерживаются в следующих приложениях:

  • Office для iPad
  • корпоративные бессрочные версии Office 2019 или более ранних версий в Windows

Ниже на анимированном изображении показано, как рабочая книга вызывает функцию, созданную вами с помощью JavaScript или TypeScript. В этом примере пользовательская функция =MYFUNCTION.SPHEREVOLUME рассчитывает объем сферы.

Анимированное изображение, на котором показано, как конечный пользователь вводит пользовательскую функцию MYFUNCTION.SPHEREVOLUME в ячейку листа Excel.

Приведенный ниже код определяет пользовательскую функцию =MYFUNCTION.SPHEREVOLUME.

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

Как определена пользовательская функция в коде

Если использовать генератор Yeoman для надстроек Office для создания в Excel проекта надстройки с пользовательскими функциями, он создаст файлы, управляющие вашими функциями и областью задач. Мы сосредоточимся на файлах, которые важны для пользовательских функций.

Файл Формат файла Описание
./src/functions/functions.js
или
./src/functions/functions.ts		 JavaScript
или
TypeScript		 Содержит код, который определяет пользовательские функции.
./src/functions/functions.html HTML Предоставляет <скрипт> со ссылкой на файл JavaScript, который определяет пользовательские функции.
./manifest.xml XML Указывает расположение нескольких файлов, которые используются пользовательскими функциями, например JavaScript, JSON и HTML-файлов. А также среду выполнения, которую должны использовать пользовательские функции, расположение файлов области задач и командных файлов.

Совет

Генератор Yeoman для надстроек Office предлагает несколько проектов пользовательских функций Excel . Рекомендуется выбрать тип проекта Excel Custom Functions с помощью общей среды выполнения и типа скрипта JavaScript.

Файл скрипта

Файл скрипта (./src/functions/functions.js или ./src/functions/functions.ts) содержит код, определяющий пользовательские функции, и комментарии, определяющие функцию.

Приведенный ниже код определяет пользовательскую функцию add. Примечания кода используются для создания файла метаданных JSON с описанием пользовательской функции для Excel. Обязательный комментарий @customfunction объявлен первым, чтобы указать, что это пользовательская функция. Затем объявляются еще два параметра: first и second, за которыми следуют их свойства description. Наконец, дается описание returns. Дополнительные сведения о том, какие комментарии являются обязательными для вашей пользовательской функции, см. в статье Автоматическое создание метаданных JSON для пользовательских функций.

/**
 * 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;
}

Файл манифеста

Файл манифеста только надстройки для надстройки, который определяет пользовательские функции (./manifest.xml в проекте, создаваемом генератором Yeoman для надстроек Office ), выполняет несколько действий.

  • Определяет пространство имен для пользовательских функций. Пространство имен добавляется к пользовательским функциям, чтобы клиенты могли определить ваши функции в рамках надстройки.
  • Использует <элементы ExtensionPoint> и <Resources> , уникальные для манифеста пользовательских функций. Эти элементы содержат сведения о расположении JavaScript, JSON и HTML-файлов.
  • Указывает, какую среду выполнения использовать для пользовательской функции. Рекомендуется всегда использовать общую среду выполнения, если нет особой потребности в использовании другой среды, так как общая позволяет делиться данными между функциями и областью задач.

Полный рабочий манифест из примера надстройки можно просмотреть в одном из репозиториев Github для примеров надстроек Office.

Совет

Если вы будете тестировать надстройку в нескольких средах (например, в среде разработки, промежуточной подготовки, демонстрации и т. д.), рекомендуется поддерживать разные файлы манифеста для каждой среды. В каждом файле манифеста можно:

  • Указать URL-адреса, соответствующие среде.
  • Настроить значения метаданных, такие как DisplayName, и метки в Resources для указания среды, чтобы конечные пользователи могли определить соответствующую среду надстройки, загруженной без публикации.
  • Настроить пользовательские функции namespace, чтобы указать среду, если ваша надстройка определяет пользовательские функции.

Соблюдение этих рекомендаций поможет вам упорядочить процесс тестирования и избежать проблем, которые могли бы возникнуть при одновременной загрузке вашей надстройки для нескольких сред.

Совместное редактирование

Excel для Интернета и Windows, подключенный к подписке Microsoft 365, дает конечным пользователям возможность совместно редактировать данные в Excel. Если книга пользователя использует пользовательскую функцию, то коллеге этого пользователя по совместному редактированию будет предложено загрузить надстройку с соответствующими пользовательскими функциями. После загрузки этой надстройки обоими пользователями эта пользовательская функция будет предоставлять результаты обоим пользователям с помощью совместного редактирования.

Дополнительные сведения о совместном редактировании см. в статье О совместном редактировании в Excel.

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

Хотите попробовать пользовательские функции? Ознакомьтесь с простым кратким руководством по началу работы с пользовательскими функциями или с более глубоким руководством по пользовательским функциям, если вы этого еще не сделали.

Еще одно простое средство ознакомления с пользовательскими функциями — Script Lab, надстройка, в которой можно экспериментировать с пользовательскими функциями прямо в Excel. Вы можете попробовать создать собственные пользовательские функции или поиграть с готовыми примерами.

См. также