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

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

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

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

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

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

Чтобы создать пользовательскую функцию DISCOUNT в этой книге, выполните следующие действия.

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
   
   

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

Теперь вы можете использовать новую функцию DISCOUNT. Закройте редактор 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 включает несколько встроенных функций( например, RAND и NOW), которые не используют аргументы.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Об авторах

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

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

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