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

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

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

Для пользовательских функций, например макросов, используется язык программирования Visual Basic для приложений (VBA). Они отличаются от макросов двумя вещами. Во-первых, в них используются процедуры Function, а не Sub. Это значит, что они начинаются с оператора Function, а не Sub, и заканчиваются оператором End Function, а не End Sub. Во-вторых, они выполняют различные вычисления, а не действия. Некоторые операторы (например, предназначенные для выбора и форматирования диапазонов) исключаются из пользовательских функций. Из этой статьи вы узнаете, как создавать и использовать пользовательские функции. Для создания функций и макросов используется редактор Visual Basic (VBE), который открывается в отдельном окне.

Предположим, что ваша компания предоставляет скидку в размере 10 % клиентам, заказавшим более 100 единиц товара. Ниже мы объясним, как создать функцию для расчета такой скидки.

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

Чтобы создать в книге настраиваемую функцию СКИДКА, выполните указанные ниже действия.

1. Нажмите клавиши ALT+F11 , чтобы открыть редактор Visual Basic (на компьютере Mac нажмите клавиши FN+ALT+F11), а затем нажмите кнопку "Вставить>модуль". В правой части редактора Visual Basic появится окно нового модуля.

2. Скопируйте указанный ниже код и вставьте его в новый модуль.

   Function DISCOUNT(quantity, price)
    If quantity >=100 Then
    DISCOUNT = quantity * price * 0.1
    Else
    DISCOUNT = 0
    End If
   
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   
   

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

Теперь вы готовы использовать новую функцию СКИДКА. Закройте редактор Visual Basic, выделите ячейку G7 и введите следующий код:

=DISCOUNT(D7;E7)

Excel вычислит 10%-ю скидку для 200 единиц по цене 47,50 ₽ и вернет 950,00 ₽.

В первой строке кода VBA функция DISCOUNT(quantity, price) указывает, что функции DISCOUNT требуется два аргумента: quantity (количество) и price (цена). При вызове функции в ячейке листа необходимо указать эти два аргумента. В формуле =DISCOUNT(D7;E7) аргумент quantity имеет значение D7, а аргумент price — значение E7. Если скопировать формулу в ячейки G8:G13, вы получите указанные ниже результаты.

Рассмотрим, как Excel интерпретирует эту процедуру. При нажатии клавиши ВВОД Excel ищет имя DISCOUNT в текущей книге и определяет, что это пользовательская функция в модуле VBA. Имена аргументов, заключенные в скобки (quantity и price), представляют собой заполнители для значений, на основе которых вычисляется скидка.

Оператор If в следующем блоке кода проверяет аргумент quantity и определяет, не равно ли количество проданных товаров 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Если количество проданных товаров не меньше 100, VBA выполняет следующую инструкцию, которая перемножает значения quantity и price, а затем умножает результат на 0,1:

Discount = quantity * price * 0.1

Результат хранится в виде переменной Discount. Оператор VBA, который хранит значение в переменной, называется оператором назначения, так как он вычисляет выражение справа от знака равенства и назначает результат имени переменной слева от него. Так как переменная Discount называется так же, как и процедура функции, значение, хранящееся в переменной, возвращается в формулу листа, из которой была вызвана функция DISCOUNT.

Если значение quantity меньше 100, VBA выполняет следующий оператор:

Discount = 0

Наконец, следующий оператор округляет значение, назначенное переменной Discount, до двух дробных разрядов:

Discount = Application.Round(Discount, 2)

В VBA нет функции округления, но она есть в Excel. Чтобы использовать округление в этом операторе, необходимо указать VBA, что метод (функцию) Round следует искать в объекте Application (Excel). Для этого добавьте слово Application перед словом Round. Используйте этот синтаксис каждый раз, когда нужно получить доступ к функции Excel из модуля VBA.

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

Пользовательские функции должны начинаться с оператора Function и заканчиваться оператором End Function. Помимо названия функции, оператор Function обычно включает один или несколько аргументов. Однако вы можете создать функцию без аргументов. В Excel есть несколько встроенных функций (например, СЛЧИС и СЕЙЧАС), которые не используют аргументы.

После оператора Function указывается один или несколько операторов VBA, которые проверят соответствия условиям и выполняют вычисления с использованием аргументов, переданных функции. Наконец, в процедуру функции следует включить оператор, назначающий значение переменной с тем же именем, что у функции. Это значение возвращается в формулу, которая вызывает функцию.

Применение ключевых слов VBA в пользовательских функциях

Количество ключевых слов VBA, которые можно использовать в пользовательских функциях, меньше, чем в макросах. Настраиваемым функциям разрешается только возвращать значение в формулу на листе или выражение, используемое в другом макросе или функции VBA. Например, пользовательские функции не могут изменять размеры окон или формулу в ячейке или параметры шрифта, цвета или узора текста в ячейке. Если включить такой код "действия" в процедуру-функцию, она возвращает #VALUE! .

Единственное действие, которое может выполнять процедура функции (кроме вычислений), — это отображение диалогового окна. Чтобы получить значение от пользователя, выполняющего функцию, можно использовать в ней оператор InputBox. Кроме того, с помощью оператора MsgBox можно выводить сведения для пользователей. Можно также использовать настраиваемые диалоговые окна или формы UserForms, но эта тема выходит за рамки областей данного введения.

Документирование макросов и пользовательских функций

Даже простые макросы и пользовательские функции может быть сложно понять. Чтобы сделать эту задачу проще, добавьте комментарии с пояснениями. Для этого нужно ввести перед текстом апостроф. Например, ниже показана функция DISCOUNT с комментариями. Благодаря подобным комментариями и вам, и другим будет впоследствии проще работать с кодом VBA. Если в будущем вам потребуется внести изменения в код, вам будет проще понять, что вы сделали изначально.

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

Кроме того, рекомендуется присваивать макросам и пользовательским функциям описательные имена. Например, присвойте макросу название MonthLabels вместо Labels, чтобы более точно указать его назначение. Использование описательных имен для макросов и пользовательских функций особенно полезно при создании большого количества процедур, особенно если используются процедуры сходного, но не идентичного назначения.

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

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

Чтобы использовать настраиваемую функцию, необходимо открыть книгу, содержащую модуль, в котором она создана. Если эта книга не открыта, вы получаете #NAME? при попытке использования функции. Если ссылка на функцию находится в другой книге, перед ее именем должно начинаться имя книги, в которой находится функция. Например, если вы создаете функцию СКИДКА в книге с именем Personal.xlsb и вызываете эту функцию из другой книги, необходимо ввести не просто =discount(), а =personal.xlsb!discount().

Чтобы вставить пользовательскую функцию быстрее (и избежать ошибок), ее можно выбрать в диалоговом окне "Вставка функции". Пользовательские функции доступны в категории "Определенные пользователем":

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

После выполнения этих действий ваши пользовательские функции будут доступны при каждом запуске Excel. Если вы хотите добавить что-то в библиотеку функций, вернитесь в редактор Visual Basic. Если вы посмотрите в редактор Visual Basic Обозреватель Project под заголовком VBAProject, вы увидите модуль, названный в честь файла надстройки. Ваша надстройка будет иметь расширение .xlam.

Если дважды щелкнуть этот модуль в обозревателе проекта, редактор Visual Basic отобразит код функции. Чтобы добавить новую функцию, установите точку вставки после оператора End Function, который завершает последнюю функцию в окне кода, и начните ввод. Вы можете создать любое количество функций, и они будут всегда доступны в категории "Определенные пользователем" диалогового окна Вставка функции.

Об авторах

Эта статья основана на главе книги Microsoft Office Excel 2007 Inside Out, написанной Марком Доджем (Mark Dodge) и Крейгом Стинсоном (Craig Stinson). В нее были добавлены сведения, относящиеся к более поздним версиям Excel.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.