Примеры как создать пользовательскую функцию в Excel
В Excel содержится множество встроенных функций, которые могут быть использованы для инженерных, статистических, финансовых, аналитических и прочих расчетов. Иногда условия поставленных задач требуют более гибкого инструмента для поиска решения, тогда на помощь приходят макросы и пользовательские функции.
Пример создания своей пользовательской функции в Excel
Подобно макросам, пользовательские функции могут быть созданы с использованием языка VBA. Для реализации данной задачи необходимо выполнить следующие действия:
- Открыть редактор языка VBA с помощью комбинации клавиш ALT+F11.
- В открывшемся окне выбрать пункт Insert и подпункт Module, как показано на рисунке:
- Новый модуль будет создан автоматически, при этом в основной части окна редактора появится окно для ввода кода:
- При необходимости можно изменить название модуля.
- В отличие от макросов, код которых должен находиться между операторами Sub и End Sub, пользовательские функции обозначают операторами Function и End Function соответственно. В состав пользовательской функции входят название (произвольное имя, отражающее ее суть), список параметров (аргументов) с объявлением их типов, если они требуются (некоторые могут не принимать аргументов), тип возвращаемого значения, тело функции (код, отражающий логику ее работы), а также оператор End Function. Пример простой пользовательской функции, возвращающей названия дня недели в зависимости от указанного номера, представлен на рисунке ниже:
- После ввода представленного выше кода необходимо нажать комбинацию клавиш Ctrl+S или специальный значок в левом верхнем углу редактора кода для сохранения.
- Чтобы воспользоваться созданной функцией, необходимо вернуться к табличному редактору Excel, установить курсор в любую ячейку и ввести название пользовательской функции после символа «=»:




Встроенные функции Excel содержат пояснения как возвращаемого результата, так и аргументов, которые они принимают. Это можно увидеть на примере любой функции нажав комбинацию горячих клавиш SHIFT+F3. Но наша функция пока еще не имеет формы.
Чтобы задокументировать пользовательскую функцию, необходимо выполнить следующие действия:
- Создайте новый макрос (нажмите комбинацию клавиш Alt+F8), в появившемся окне введите произвольное название нового макроса, нажмите кнопку Создать:
- В результате будет создан новый модуль с заготовкой, ограниченной операторами Sub и End Sub.
- Введите код, как показано на рисунке ниже, указав требуемое количество переменных (в зависимости от числа аргументов пользовательской функции):
- В качестве «Macro» должна быть передана текстовая строка с названием пользовательской функции, в качестве «Description» - переменная типа String с текстом описания возвращаемого значения, в качестве «ArgumentDescriptions» - массив переменных типа String с текстами описаний аргументов пользовательской функции.
- Для создания описания пользовательской функции достаточно один раз выполнить созданный выше модуль. Теперь при вызове пользовательской функции (или SHIFT+F3) отображается описание возвращаемого результата и переменной:




Описания функций создавать не обязательно. Они необходимы в случаях, если пользовательские функции будут часто использоваться другими пользователями.
Примеры использования пользовательских функций, которых нет в Excel
Пример 1. Рассчитать сумму отпускных для каждого работника, проработавшего на предприятии не менее 12 месяцев, на основе суммы общей заработной платы и числа выходных дней в году.
Вид исходной таблицы данных:

Каждому работнику полагается 24 выходных дня с выплатой S=N*24/(365-n), где:
- N – суммарная зарплата за год;
- n – число праздничных дней в году.
Создадим пользовательскую функцию для расчета на основе данной формулы:

Код примера:
Public Function Otpusknye(summZp As Long, holidays As Long) As Long
If IsNumeric(holidays) = False Or IsNumeric(summZp) = False Then
Otpusknye = "Введены нечисловые данные"
Exit Function
ElseIf holidays <= 0 Or summZp <= 0 Then
Otpusknye = "Отрицательное число или 0"
Exit Function
Else
Otpusknye = summZp * 24 / (365 - holidays)
End If
End Function
Сохраним функцию и выполним расчет с ее использованием:
=Otpusknye(B3;C3)
Растянем формулу на остальные ячейки с целью получения результатов для остальных работников:

Калькулятор расчета калорий в Excel
Пример 2. Рассчитать суточную норму калорий для участников программы похудения, среди которых есть как женщины, так и мужчины определенного возраста с известными показателями роста и веса.
Вид исходной таблицы данных:

Для расчета используем формулу Миффлина - Сан Жеора, которую запишем в коде пользовательской функции с учетом пола участника. Код примера:
Public Function CaloriesPerDay(sex As String, age As Integer, weight As Integer, height As Integer) As Integer
If sex = "женский" Then
CaloriesPerDay = 10 * weight + 6.25 * height - 5 * age - 161
ElseIf sex = "мужской" Then
CaloriesPerDay = 10 * weight + 6.25 * height - 5 * age + 5
Else: CaloriesPerDay = 0
End If
End Function
Проверки корректности введенных данных упущены для упрощения кода. Если пол не определен, функция вернет результат 0 (нуль).
Пример расчета для первого участника:
=CaloriesPerDay(B3;C3;D3;E3)
В результате использования автозаполнения получим следующие результаты:

Пользовательская функция для решения квадратных уравнений в Excel
Пример 3. Создать функцию, которая возвращает результаты решения квадратных уравнений для указанных в ячейках коэффициентах a, b и c уравнения типа ax2+bx+c=0.
Вид исходной таблицы:

Для решения создадим следующую пользовательскую функцию:

Код примера:
Public Function SquareEquation(a As Integer, b As Integer, c As Integer) As String
Dim answer1 As String
Dim answer2 As String
If a = 0 Then
answer1 = "Единственный корень - "
SquareEquation = answer1 & "(" & -c / b & ")"
ElseIf c = 0 Then
answer1 = "Единственный корень - "
SquareEquation = answer1 & "(" & -b / a & ")"
ElseIf b = 0 And c < 0 Then
answer1 = "Единственный корень - "
SquareEquation = answer1 & "(" & Sqr(a / c) & ")"
ElseIf b ^ 2 - 4 * a * c >= 0 Then
answer1 = "Первый корень - "
answer2 = "Второй корень - "
SquareEquation = answer1 & "(" & (-b + Sqr(b ^ 2 - 4 * a * c)) / (2 * a) & ")" & "; " & _
answer2 & "(" & (-b - Sqr(b ^ 2 - 4 * a * c)) / (2 * a) & ")"
Else:
SquareEquation = "Решений нет"
End If
End Function
Найдем корни первого уравнения:
=SquareEquation(A3;B3;C3)
Выполним расчеты для остальных уравнений. Полученные результаты:

Скачать примеры создания пользовательский функций в Excel
Мы создали свою пользовательскую функцию для расчета квадратных уравнений, которой раньше не было в Excel по умолчанию.