Пользовательские функции Excel

Как создавать собственные функции Excel? (с примерами)

Чтобы создать настраиваемую функцию, нам нужно написать код для работы наших собственных функций, который называется «UDF». Пользовательские функции — это определяемые пользователем функции в Excel, поэтому для создания этих функций вам необходимо хорошо знать кодирование VBA.

Пользовательские функции Excel

Пример # 1 — Добавить любые два числа

Например, если вы хотите сложить любые два числа, мы покажем вам простую функцию, определяемую пользователем (UDF).

  • Нажмите Alt + F11 и вставьте модуль.

Пример 1 настраиваемой функции Excel

  • Напишите код в модуле для создания пользовательской функции.

Любая настраиваемая функция должна начинаться со слова «Функция», за которым следует имя формулы.

Пример настраиваемой функции Excel 1-1

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

Пример пользовательской функции Excel 1-2

После того как параметры определены с типом данных, нам также необходимо определить типы данных результатов. Давайте определим результат как значение «Целое число».

Пример пользовательской функции Excel 1-3

Внутри этой «функциональной процедуры» мы напишем код формулы.

Код:

Function Addition(Num1 As Integer, Num2 As Integer) As Integer

  Additiona = Num1 + Num2

End Function

Это говорит о том, что результат имени функции «Добавление» будет суммой значений Num1 и Num2.

  • Теперь вернитесь к рабочему листу и введите любые два целых числа.

Пример настраиваемой функции Excel 1-4

  • Теперь мы сложим эти два числа. Откройте знак равенства и введите имя пользовательской функции «Добавление».

Пример настраиваемой функции Excel 1-5

Выберите первое и второе числа, введя разделитель в виде запятой (,).

Пример пользовательской функции Excel 1-6

  • Нажмите клавишу ввода, чтобы получить результат.

Пример настраиваемой функции Excel 1-7

Вау!!! Как и функция СУММ, мы получили результат суммы двух чисел.

А теперь посмотрите на эти числа.

Пример пользовательской функции Excel 1-8

Попробуйте сложить эти два числа сейчас.

Пример настраиваемой функции Excel 1-9

Мы получили значение ошибки, потому что тип данных аргументов «Num1 & Num2» — «Целое число», т.е. эти два аргумента могут содержать значения от -32767 до 32767, поэтому все, что больше этого, вызовет эти ошибки.

Теперь попробуйте сложить эти два числа.

Пример настраиваемой функции Excel 1-10

Даже это приведет к снижению значения ошибки.

Пример настраиваемой функции Excel 1-11

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

Поскольку мы объявили тип результата также как «целое число», результатом сложения двух чисел также должно быть ограничение на целое число.

Пример # 2 — Добавить все нечетные числа

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

Добавление нечетных чисел, пример 2

Откройте окно редактора VBA и дайте имя процедуре функции.

Пример сложения нечетных чисел 2-1

Задайте параметр для этой функции как «Диапазон».

Пример сложения нечетных чисел 2-2

Это означает, что для этой функции мы предоставляем входное значение для этой функции как «Диапазон» значений ячеек.

Поскольку нам нужно перебрать более одной ячейки, нам нужно использовать цикл «For Each» в VBA, поэтому откройте цикл «For Each».

Пример 2-3 сложения нечетных чисел

Внутри этого цикла добавьте приведенный ниже код.

Код:

Function AddOdd(Rng As Range)

 For Each cell In Rng
  If cell.Value Mod 2 <> 0 Then AddOdd = AddOdd + cell.Value
 Next cell

End Function

Мы должны использовать функцию «MOD», чтобы проверить число. Когда каждое значение ячейки делится на число 2, а оставшееся значение не равно нулю, тогда наш код должен сложить все значения нечетных чисел.

Теперь вернитесь к рабочему листу и откройте пользовательскую функцию Excel.

Пример сложения нечетных чисел 2-4

Выберите диапазон номеров от A1 до D8.

Пример сложения нечетных чисел 2-5

Нажмите клавишу ввода, чтобы получить «нечетное» число ».

Пример сложения нечетных чисел 2-6

Итак, в диапазоне от A1 до D8 у нас есть сумма нечетных чисел 84.

Пример # 3 — Сложение всех четных чисел

Точно так же функция ниже добавит все четные числа.

Код:

Function AddEven(Rng As Range)

 For Each cell In Rng
  If cell.Value Mod 2 = 0 Then AddEven = AddEven + cell.Value
 Next cell

End Function

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

  Сложение четных чисел, пример 3

Таким образом, используя кодирование VBA, мы можем создавать собственные пользовательские функции.

Вы можете скачать этот шаблон Excel с настраиваемыми функциями здесь — Шаблон Excel с настраиваемыми функциями

То, что нужно запомнить

  • Создание собственных функций — это не что иное, как функции, определяемые пользователем.
  • Чтобы создавать собственные функции, необходимо обладать продвинутыми навыками программирования на VBA.
  • При создании пользовательских функций Excel важно следить за аргументами и их типами данных.

УЗНАТЬ БОЛЬШЕ >>

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.