Что такое пользовательские функции
Пользовательские функции помогают нам расширить функционал вычислений в Excel. Они похожи на встроенные в Excel, однако логику вычислений мы можем задавать сами. Пользовательские функции принимают определенные значения из параметров при вызове, производят с ними определенные вычисления и возвращают результат. Задать эти параметры можно и ссылкой на ячейку, и статичным значением.
Создаются они так же, как и макросы — в окне редактора VBA Excel. Разница в том, что макрос — это алгоритм определенных действий, который сам по себе не возвращает никакого результата и может манипулировать листом, книгой, файлами, практически как реальный пользователь. Функции таких возможностей лишены, и используются только для вычислений или преобразований отправленных в них данных.
Использовать их можно:
- непосредственно на листе Эксель
- как часть макроса или другой функции в редакторе VBA
- как правило условного форматирования
Важно учитывать, что они работают медленнее встроенных, поэтому не стоит злоупотреблять ими. Используйте их только там, где обычных или недостаточно, или придется писать слишком уж мудреную формулу, которую сложно поддерживать и воспринимать.
Как создать
Давайте попробуем составить простую функцию Excel для сложения двух чисел. Откройте редактор кода сочетанием клавиш «Alt+F11». Затем создайте дополнительный модуль к книге и вставьте приведенный ниже код.


Function СЛОЖИТЬ(number1 As Long, number2 As Long) As Long
Dim result As Long
result = number1 + number2
СЛОЖИТЬ = result
End Function
Сохраните книгу сочетанием клавиш Ctrl+S. Теперь функцию СЛОЖИТЬ() можно использовать на листе. Чтобы проверить ее работу, вернитесь на лист и попробуйте написать =СЛОЖИТЬ(1;2)
в ячейку.
=СЛОЖИТЬ(1;2)

Как видите, все работает.
Синтаксис
Давайте разберем из чего синтаксически состоит код.

Вместо команды Sub
у макроса, функция определяется командой Function
.
Далее идет имя. Оно может быть задано цифрами, кириллическими или латинскими буквами, символом подчеркивания. Нельзя использовать пробелы. Также имя пользовательской функции не должно дублировать имя стандартной, поскольку в противном случае она просто не будет работать.
После имени объявляются аргументы. У аргументов при необходимости можно задать тип данных. Типы аргументов соответствуют стандартным типам данных VBA. При этом функция может быть вообще без аргументов. Также аргументы могут быть обязательными и необязательными. Эти примеры мы разберем позже.
После аргументов можно объявить тип данных возвращаемого значения.
Далее идет непосредственно блок вычислений - в нем мы можем объявлять переменные, проводить операции и вычисления с аргументами. Перед завершающей командой End Function
нужно обязательно указать возвращаемое значение:
Function ИМЯФУНКЦИИ()
' тут наши вычисления
ИМЯФУНКЦИИ = 2
End Function
Аргументы
Как правило, задается несколько аргументов, однако их может не быть совсем — Вы наверняка знаете, что в Эксель есть и стандартные функции без аргументов, типа СЕГОДНЯ() или СЛЧИС(). Например, мы можем получить имя текущего листа - для этого не требуется никаких аргументов.
Function ИМЯЛИСТА()
ИМЯЛИСТА = Application.ActiveSheet.Name
End Function
Как писал выше, аргументы могут быть обязательными и необязательными. Это полезно, например, если у функции должна быть определенная возможность, но она редко используется. И чтобы каждый раз не тратить время на прописывание отвечающего за нее аргумента, мы можем задать его как необязательный.
В нашем примере сложения добавим возможность вывести единицы измерения после числа, если аргумент единиц измерения задан.
Function СЛОЖИТЬЕД(number1 As Long, number2 As Long, Optional ed As String)
Dim count As Long
count = number1 + number2 ' Складываем
If Len(ed) > 0 Then
result = count & ed ' Если единицы заданы, добавляем их
Else
result = count ' Иначе возвращаем просто число
End If
СЛОЖИТЬЕД = result
End Function
Обратите внимание, как задан необязательный аргумент — перед именем указано ключевое слово Optional
и расположен он после обязательных, это важно.
Необязательный аргумент также позволяет установить значение по умолчанию, и не указывать его при вызове. Для примера, напишем функцию расчета суммы с НДС, а НДС по умолчанию зададим 20%:
Function СНДС(price As Long, Optional nds As Long = 20) As Long
Dim result As Long
result = price + price * nds / 100
СНДС = result
End Function
Значение по умолчанию задается после типа данных через символ =
. Значение по умолчанию есть смысл указывать только у необязательных аргументов, так как остальные всегда нужно задавать при вызове функции.
Примеры
Вот несколько полезных функций, которых изначально нет в Эксель. Их можно легко реализовать и стандартными функциями, это просто для примера.
Рассчитать процент от числа
Function ПРЦН(num1 As Long, num2 As Long) As Double
Dim result As Double
result = num1 / num2 * 100
ПРЦН = result
End Function
Рассчитать маржу товара
Function МАРЖА(price As Long, sebes As Long, Optional dop As Long = 0, Optional logist As Long = 0, Optional nalog As long = 0) As Double
Dim result As Double
result = price - sebes - dop - logist - (price * nalog / 100)
МАРЖА = result
End Function
Рассчитать маржинальность товара
Function МАРЖИНАЛЬНОСТЬ(price As Long, sebes As Long, Optional dop As Long = 0, Optional logist As Long = 0, Optional nalog As long = 0) As Double
Dim result As Double
result = (price - sebes - dop - logist - (price * nalog / 100)) / price * 100
МАРЖИНАЛЬНОСТЬ = result
End Function
Комментарии (1)