Как сделать функцию в excel
Пользовательские функции позволяют добавлять новые функции в Excel путем определения этих функций в JavaScript как части надстройки. Пользователи в Excel могут получить доступ к пользовательским функциям так же, как и к любой встроенной функции в Excel, например SUM() . Вы можете создавать пользовательские функции, которые будут выполнять простые задачи, такие как вычисления, или более сложные задачи, такие как потоковая передача данных в режиме реального времени из Интернета на лист.
В этом руководстве описан порядок выполнения перечисленных ниже задач.
- Создание надстройки пользовательской функции с помощью генератора Yeoman для надстроек Office.
- Использование готовой пользовательской функции для выполнения простых вычислений
- Создание пользовательской функции, которая получает данные из сети Интернет.
- Создание пользовательской функции, которая осуществляет потоковую передачу данных в реальном времени из сети Интернет
Предварительные требования
Последняя версия Yeoman и генератора Yeoman для надстроек Office. Выполните в командной строке указанную ниже команду, чтобы установить эти инструменты глобально.
Даже если вы уже установили генератор Yeoman, рекомендуем обновить пакет до последней версии из npm.
Пакет Office, подключенный к подписке Microsoft 365 (включая Office в Интернете).
Если у вас еще нет Office, вы можете присоединиться к программе для разработчиков Microsoft 365, чтобы получить бесплатную 90-дневную возобновляемую подписку на Microsoft 365 для использования в процессе разработки.
Создание проекта пользовательских функций
Чтобы начать, создайте проект кода для разработки надстройки пользовательской функции. Генератор Yeoman для надстроек Office настроит в вашем проекте некоторые готовые пользовательские функции, которые можно попробовать. Если вы уже с помощью краткого руководства по пользовательским функциям создали проект, продолжайте работать с ним и пропустите этот шаг.
Если повторно создать проект Yo Office, может возникнуть ошибка, так как в кэше Office уже есть экземпляр функции с таким же именем. Это можно предотвратить, очищая кэш Office перед запуском npm run start .
Выполните следующую команду, чтобы создать проект надстройки с помощью генератора Yeoman.
При выполнении команды yo office может появиться запрос о политиках сбора данных генератора Yeoman и средств CLI надстройки Office. Используйте предоставленные сведения, чтобы ответить на запросы подходящим образом.
При появлении запроса предоставьте следующую информацию для создания проекта надстройки.
- Выберите тип проекта: Excel Custom Functions Add-in project
- Выберите тип сценария: JavaScript
- Как вы хотите назвать надстройку? starcount
Генератор Yeoman создаст файлы проекта и установит вспомогательные компоненты Node.
Вы можете игнорировать инструкции по дальнейшим действиям, предоставляемые генератором Yeoman после создания проекта надстройки. Пошаговые инструкции этой статьи содержат все сведения, необходимые для завершения этого учебного курса.
Перейдите к корневой папке проекта.
Выполните построение проекта.
Запустите локальный веб-сервер, работающий на Node.js. Вы можете попробовать использовать надстройку пользовательской функции в Excel.
Чтобы проверить надстройку в Excel для Windows или Mac, выполните следующую команду. Когда вы выполните эту команду, запустится локальный веб-сервер и откроется приложение Excel, в котором будет загружена ваша надстройка.
Чтобы проверить надстройку в Excel в Интернете, выполните следующую команду. После выполнения этой команды запустится локальный веб-сервер. Замените "" на URL-адрес документа Excel в OneDrive или библиотеке SharePoint, для которой у вас есть разрешения.
Ниже приведены примеры.
Если надстройка не загружена в документе, вручную разгрузите ее вручную, следуя инструкциям в Sideload Officeнадстройки в Office в Интернете вручную .
Проверка работы готовой пользовательской функции
Созданный проект пользовательских функций содержит некоторые готовые пользовательские функции, определенные в файле src/functions/functions.js. Файл ./manifest.xml указывает, что все пользовательские функции принадлежат пространству имен CONTOSO . Вы будете использовать пространство имен CONTOSO для доступа к пользовательским функциям в Excel.
Попробуйте, как работает пользовательская функция ADD , выполнив описанные далее шаги.
В Excel перейдите в любую ячейку и введите =CONTOSO . Обратите внимание на то, что в меню автозаполнения содержится список всех функций в пространстве имен CONTOSO .
Выполните запуск функции CONTOSO.ADD с числами 10 и 200 в качестве входных параметров, введя значение =CONTOSO.ADD(10,200) в ячейке и нажав клавишу ВВОД.
Пользовательская функция ADD вычисляет сумму двух чисел, которые вы указываете и возвращает результат 210.
Если пространство имен CONTOSO недоступно в меню автозаполнения, для регистрации надстройки в Excel выполните следующие действия.
В Excel выберите вкладку Вставка, а затем нажмите стрелку вниз, находящуюся справа от элемента Мои надстройки.
В списке доступных надстроек найдите раздел Надстройки разработчика и выберите вашу надстройку starcount, чтобы ее зарегистрировать.
В Excel на вкладке Вставка выберите пункт Надстройки.
Выберите Обзор. и откройте корневой каталог проекта, созданный генератором Yeoman.
Теперь давайте оценим, как работает новая функция. В ячейке B1 введите текст =CONTOSO.GETSTARCOUNT("OfficeDev", "Excel-Custom-Functions") и нажмите клавишу ВВОД. Результат в ячейке B1 — это текущее количество звезд, отданных репозиторию Excel-Custom-Functions Github.
Создание пользовательской функции, которая запрашивает данные из сети Интернет
Интеграция данных из Интернета — отличный способ расширения функционала Excel через пользовательские функции. Затем вы создадите пользовательскую функцию с именем getStarCount , показывающую, сколько звезд имеет данный репозиторий Github.
В проекте starcount найдите файл ./src/functions/functions.js и откройте его в редакторе кода.
В function.js добавьте следующий код.
Выполните указанную ниже команду, чтобы повторно собрать проект.
Чтобы повторно зарегистрировать надстройку в Excel, выполните указанные ниже действия (для Excel в Интернете, для Windows или для Mac). Выполните описанные ниже действия, чтобы новая функция стала доступной.
Закройте Excel, а затем откройте Excel повторно.
В Excel выберите вкладку Вставка, а затем нажмите стрелку вниз, находящуюся справа от элемента Мои надстройки.
В списке доступных надстроек найдите раздел Надстройки разработчика и выберите вашу надстройку starcount, чтобы ее зарегистрировать.
В Excel на вкладке Вставка выберите пункт Надстройки.
Выберите Обзор. и откройте корневой каталог проекта, созданный генератором Yeoman.
Теперь давайте оценим, как работает новая функция. В ячейке B1 введите текст =CONTOSO.GETSTARCOUNT("OfficeDev", "Excel-Custom-Functions") и нажмите клавишу ВВОД. Результат в ячейке B1 — это текущее количество звезд, отданных репозиторию Excel-Custom-Functions Github.
Создание потоковой асинхронной пользовательской функции
Функция getStarCount возвращает количество звезд, которые есть у репозитория в определенный момент времени. Пользовательские функции также возвращают непрерывно изменяемые данные. Эти функции называются потоковыми передачами функций. Они должны содержать параметр invocation , ссылающийся на ячейку, из которой была вызвана функция. Параметр invocation используется для обновления содержимого ячейки в любое время.
В примере кода ниже вы заметите наличие двух функций, currentTime и clock . Функция currentTime — это статическая функция, которая не использует потоковую передачу функций. Она возвращает дату в виде строки. Функция clock использует функцию currentTime для обеспечения нового времени каждую секунду для ячейки в Excel. В ней используется invocation.setResult для передачи времени в ячейку Excel и invocation.onCanceled для обработки отмены функции.
Проект starcount уже содержит две следующие функции в файле ./src/functions/functions.js.
Чтобы опробовать функции, введите текст =CONTOSO.CLOCK() в ячейку C1 и нажмите ВВОД. Должна отобразиться текущая дата, которая потоком обновляется каждую секунду. Хотя эти часы являются просто таймером в цикле, однако можно использовать аналогичную идею настройки таймера для более сложных функций, которые выполняют веб-запросы в режиме реального времени.
Дальнейшие действия
Поздравляем! Вы создали новый проект пользовательских функций, попробовали, как работает готовая функция, создали пользовательскую функцию, которая запрашивает данные из Интернета, а также создали пользовательскую функцию, которая осуществляет потоковую передачу данных. Затем вы можете изменить свой проект, чтобы использовать общую среду выполнения, упрощая взаимодействие с панелью задач. Выполните инструкции из следующей статьи.
Функции — это заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления. Все функции Excel можно найти на вкладке "формулы" на ленте.
Синтаксис функции Excel
В приведенном ниже примере функции ОКРУГЛ , округленной на число в ячейке A10, показан синтаксис функции.
1. Structure. Структура функции начинается со знака равенства (=), за которым следует имя функции, открывающую круглую скобку, аргументы функции, разделенные запятыми, и закрывающая круглая скобка.
2. имя функции. Чтобы просмотреть список доступных функций, щелкните ячейку и нажмите клавиши SHIFT + F3, чтобы открыть диалоговое окно Вставка функции .
4. всплывающая подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, всплывающая подсказка появляется после ввода выражения =ОКРУГЛ(. Всплывающие подсказки отображаются только для встроенных функций.
Ввод функций Excel
Диалоговое окно Вставить функцию упрощает ввод функций при создании формул, в которых они содержатся. После выбора функции в диалоговом окне Вставка функции Excel запустит мастер функций, который выведет на экран имя функции, каждый из ее аргументов, описание функции и каждый аргумент, текущий результат функции и текущий результат всей формулы.
Для упрощения создания и редактирования формул, а также для минимизации ошибок ввода и синтаксиса используйте Автозаполнение формул. После ввода знака = (знак равенства) и начальных букв функции Excel отобразит динамический раскрывающийся список допустимых функций, аргументов и имен, соответствующих этим буквам. Затем вы можете выбрать один из раскрывающегося списка, и приложение Excel введет его автоматически.
Вложение функций Excel
В некоторых случаях может потребоваться использовать функцию в качестве одного из аргументов другой функции. Например, в следующей формуле используется вложенная функция СРЗНАЧ , а результат сравнивается со значением 50.
1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).
В качестве первого примера для Excel рассмотрим самую популярную функцию F(x)=X^2. График от этой функции в большинстве случаев должен содержать точки, что мы и реализуем при его составлении в будущем, а пока разберем основные составляющие.
-
Создайте строку X, где укажите необходимый диапазон чисел для графика функции.
Если график должен быть точечным, но функция не соответствует указанной, составляйте его точно в таком же порядке, формируя требуемые вычисления в таблице, чтобы оптимизировать их и упростить весь процесс работы с данными.
Вариант 2: График функции y=sin(x)
Функций очень много и разобрать их в рамках этой статьи просто невозможно, поэтому в качестве альтернативы предыдущему варианту предлагаем остановиться на еще одном популярном, но сложном — y=sin(x). То есть изначально есть диапазон значений X, затем нужно посчитать синус, чему и будет равняться Y. В этом тоже поможет созданная таблица, из которой потом и построим график функции.
-
Для удобства укажем всю необходимую информацию на листе в Excel. Это будет сама функция sin(x), интервал значений от -1 до 5 и их шаг весом в 0.25.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Для создания функций в функции Excel можно использовать окно вставки или отдельное поле в главном окне. Пример с двумя условиями проще всего выполнить при помощи первого варианта. Допустим, у нас есть числа в отдельной таблице, при достижении определенного среднего значения которых в отдельной ячейке должна показаться сумма чисел других блоков. Для этого потребуется определиться с первым списком чисел, что видно на прикрепленном ниже скриншоте.
Второе условие — числа, которые будут суммироваться. Их тоже определяем заранее, чтобы было проще создавать функцию.
Подобная реализация пригодится во многих ситуациях, например, при бухгалтерских расчетах или составлении сметы. В любом случае, это всего лишь пример того, как может выглядеть функция в функции, а пользователь уже сам решает, как эту возможность адаптировать под себя.
- Создадим функцию через отдельное окно, нажав по кнопке с тематическим значком.
Еще больше информации об используемом Мастере функций читайте в развернутой инструкции на нашем сайте, где находятся все необходимые сведения, иллюстрирующие работу с инструментом.
Пример 2: Три и более условий
Вариант с использованием инструмента вставки формулы не сработает, если внутри корня находится более двух переменных. Тогда придется заполнять поле вручную, учитывая синтаксис Excel, что нужно делать с особой осторожностью и внимательно проверять каждый символ. В качестве примера возьмем небольшую таблицу, где есть несколько наименований продукции с ценами за текущий и прошлый месяц. Нужно сделать так, что если цена будет оставаться на том же месте, увеличиваться или уменьшаться, рядом будет появляться соответствующее уведомление.
-
Создадим отдельную формулу при использовании все той же корневой функции ЕСЛИ.
Для лучшего понимания оставим полную строку формулы с функцией в функции в правильном ее виде =ЕСЛИ(B2=C2;»Не изменилась»;ЕСЛИ(B2 C2;»Уменьшилась»))). Если требуется, скопируйте ее и используйте в своих целях, изменив условия и значения.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Функции в программе Excel позволяют выполнять различные, довольно сложные действия вычислительного характера буквально в несколько кликов. Ещё больше упрощает работу с ними такой удобный инструмент, как «Мастер функций». Давайте рассмотрим, как он устроен и что с его помощью можно делать.
Работа Мастера функций
Мастер функций представляет собой инструмент в виде небольшого окошка, в котором все имеющиеся функции в Excel упорядочены по категориям, что делает доступ к ним проще. Также, он предоставляет возможность ввести аргументы формулы через интуитивно понятный графический интерфейс.
Переход в Мастер функций
Мастер функций можно запустить сразу несколькими способами. Но прежде, чем активировать этот инструмент, нужно выделить ту ячейку, в которой будет находиться формула и, следовательно, выводиться результат.
Проще всего перейти в него, нажав на кнопку «Вставить функцию», расположенную слева от строки формул. Этот способ хорош тем, что им воспользоваться можно, находясь в любой вкладке программы.
Кроме того, нужный нам инструмент можно запустить, перейдя во вкладку «Формулы». Затем следует нажать на самую крайнюю слева кнопку на ленте «Вставить функцию». Она располагается в блоке инструментов «Библиотека функций». Это способ хуже предыдущего тем, что если вы не находитесь во вкладке «Формулы», то придется выполнять дополнительные действия.
Можно также кликнуть на любую другую кнопку блока инструментов «Библиотека функций». При этом, в выпадающем меню появится список, в самом низу которого есть пункт «Вставить функцию…». Вот по нему и нужно кликнуть. Но, данный способ является ещё запутаннее предыдущего.
Очень простым способом перехода в режим Мастера является нажатие комбинации горячих клавиш Shift+F3. Этот вариант предусматривает быстрый переход без дополнительных «телодвижений». Главный недостаток его состоит в том, что далеко не каждый пользователь способен удержать у себя в голове все комбинации горячих клавиш. Так что для новичков в освоении Excel этот вариант не подходит.
Категории элементов в Мастере
Какой бы способ активации из вышеуказанных вы не выбрали, в любом случае, после данных действий запускается окно Мастера. В верхней части окна расположено поле поиска. Сюда можно ввести наименование функции и нажать кнопку «Найти», чтобы быстрее отыскать нужный элемент и получить доступ к нему.
Средняя часть окна представляет выпадающий список категорий функций, которые представляет Мастер. Чтобы просмотреть данный перечень, жмем на пиктограмму в виде перевернутого треугольника справа от него. Таким образом открывается полный список доступных категорий. Прокручивать вниз его можно с помощью боковой полосы прокрутки.
Все функции разделены на следующие 12 категорий:
- Текстовые;
- Финансовые;
- Дата и время;
- Ссылки и массивы;
- Статистические;
- Аналитические;
- Работа с базой данных;
- Проверка свойств и значений;
- Логические;
- Инженерные;
- Математические;
- Определенные пользователем;
- Совместимость.
В категории «Определенные пользователем» находятся функции, составленные самим юзером или загруженные из внешних источников. В категории «Совместимость» расположены элементы из старых версий Excel, для которых уже существуют более новые аналоги. Они были собраны в эту группу для поддержки совместимости работы с документами, созданными в старых версиях приложения.
Кроме того, в этом же списке находятся две дополнительные категории: «Полный алфавитный перечень» и «10 недавно использовавшихся». В группе «Полный алфавитный перечень» располагается полный список всех функций в независимости от категории. В группе «10 недавно использовавшихся» находится список десяти последних элементов, к которым пользователь прибегал. Этот перечень постоянно обновляется: использовавшиеся раньше элементы убираются, а новые добавляются.
Выбор функции
Для того, чтобы перейти к окну аргументов, прежде всего необходимо выбрать нужную категорию. В поле «Выберите функцию» следует отметить то наименование, которое требуется для выполнения конкретной задачи. В самой нижней части окна находится подсказка в виде комментария к выделенному элементу. После того, как конкретная функция выбрана, требуется нажать на кнопку «OK».
Аргументы функции
После этого, открывается окно аргументов функции. Главным элементом этого окна являются поля аргументов. У различных функций аргументы отличаются, но принцип работы с ними остается одинаковыми. Их может быть несколько, а может быть и один. В качестве аргументов могут выступать числа, ссылки на ячейки или даже ссылки на целые массивы.
-
Если мы работаем с числом, то просто вводим его с клавиатуры в поле, таким же образом, как вбиваем цифры в ячейки листа.
Если же в качестве аргумента выступают ссылки, то их также можно прописать вручную, но намного удобнее поступить иначе.
Выполнение функции
После того, как вы нажали на кнопку «OK» Мастер закрывается и происходит выполнение самой функции. Результат выполнения может быть самый разнообразный. Он зависит от тех задач, которые ставятся перед формулой. Например, функция СУММ, которая была выбрана в качестве примера, производит суммирование всех введенных аргументов и показывает результат в отдельной ячейке. Для других вариантов из списка Мастера результат будет абсолютно иным.
Как видим, Мастер функций является очень удобным инструментом, который значительно упрощает работу с формулами в Excel. С его помощью можно производить поиск нужных элементов из списка, а также вводить аргументы через графический интерфейс. Для начинающих пользователей Мастер особенно незаменим.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Читайте также: