Как написать функцию в excel: Примеры как создать пользовательскую функцию в Excel

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

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

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

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

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

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

Чтобы задокументировать пользовательскую функцию, необходимо выполнить следующие действия:

1. Создайте новый макрос (нажмите комбинацию клавиш Alt+F8), в появившемся окне введите произвольное название нового макроса, нажмите кнопку Создать:
2. В результате будет создан новый модуль с заготовкой, ограниченной операторами Sub и End Sub.
3. Введите код, как показано на рисунке ниже, указав требуемое количество переменных (в зависимости от числа аргументов пользовательской функции):
4. В качестве «Macro» должна быть передана текстовая строка с названием пользовательской функции, в качестве «Description» — переменная типа String с текстом описания возвращаемого значения, в качестве «ArgumentDescriptions» — массив переменных типа String с текстами описаний аргументов пользовательской функции.
5. Для создания описания пользовательской функции достаточно один раз выполнить созданный выше модуль. Теперь при вызове пользовательской функции (или 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. 2 — 4 * a * c)) / (2 * a) & «)»
Else:
    SquareEquation = «Решений нет»
End If
End Function

Найдем корни первого уравнения:

=SquareEquation(A3;B3;C3)

Выполним расчеты для остальных уравнений. Полученные результаты:

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

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

Вставляем функцию в Excel — ОфисГуру

Перейти к содержанию

Search for:

Главная » Уроки MS Excel

Автор Антон Андронов На чтение 3 мин Опубликовано 14. 12.2019

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

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

• СУММ – эта функция суммирует значения всех аргументов.
• СРЗНАЧ – определяет среднее арифметическое величин, содержащихся в аргументах. Функция вычисляет сумму значений ячеек, а затем делит результат на их количество.
• СЧЁТ – подсчитывает количество чисел в списке аргументов. Функция полезна для быстрого подсчета числа элементов в диапазоне.
• МАКС – определяет максимальное значение из списка аргументов.
• МИН – определяет минимальное значение из списка аргументов.

Как вставить функцию в Excel

В следующем примере мы создадим простую формулу для расчета средней цены за единицу заказанных товаров, используя функцию СРЗНАЧ.

1. Выделите ячейку, в которую необходимо вставить формулу. В нашем примере мы выделим ячейку C11.
2. Введите знак равенства (=) и нужное имя функции. Вы также можете вставить функцию из списка, который появится при вводе ее названия (автозавершение в Excel). В нашем случае мы введем =СРЗНАЧ.
3. Введите диапазон ячеек в качестве аргумента в круглых скобках. В нашем примере мы введем (C3:C10). Эта формула суммирует значения в диапазоне С3:С10, а затем делит результат на количество ячеек в этом диапазоне, тем самым определяя среднее значение.
4. Нажмите Enter на клавиатуре. Функция будет вычислена, и Вы увидите результат. В данном примере средняя цена за единицу заказанных товаров составила $15,93.

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

Вставка функции с помощью команды Автосумма

Команда Автосумма позволяет автоматически вставлять наиболее распространенные функции в формулы Excel, включая СУММ, СРЗНАЧ, СЧЁТ, МИН и МАКС. В следующем примере мы создадим формулу для расчета полной стоимости недавно заказанных товаров, используя функцию СУММ.

1. Выделите ячейку, в которую необходимо вставить формулу. В нашем примере мы выделим ячейку D12.
2. В группе команд Редактирование на вкладке Главная найдите и нажмите стрелку рядом с командой Автосумма, а затем выберите нужную функцию в раскрывающемся меню. В нашем случае мы выберем Сумма.
3. Выбранная функция появится в ячейке. Команда Автосумма автоматически определяет диапазон ячеек для аргумента. В данном примере диапазон D3:D11 был выбран автоматически, а значения просуммированы, чтобы вычислить полную стоимость. Вы также можете вручную ввести нужный диапазон.
4. Нажмите Enter на клавиатуре. Функция будет вычислена, и Вы увидите результат. В нашем примере сумма значений диапазона D3:D11 составляет $606,05.

В Microsoft Excel команду Автосумма можно также найти на вкладке Формулы.

Оцените качество статьи. Нам важно ваше мнение:

Adblock
detector

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

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

К тому моменту, как я начал писать эту статью, Excel уже познакомил вас с более чем 450 различными функциями. С их помощью можно выполнять огромное количество различных операций. Однако не всякую задачу можно решить в Excel, поскольку его разработчики не смогли предусмотреть всех проблем, с которыми мы сталкиваемся. Я думаю, что многие из вас сталкивались хотя бы с одной из этих проблем:

• Не все данные можно обработать стандартными функциями (например, даты до 1900 года).
• Формулы могут быть довольно длинными и сложными. Их невозможно запомнить, трудно понять и трудно изменить, чтобы они соответствовали новым критериям.
• Не все задачи можно решить стандартными функциями Excel (например, нельзя извлечь URL из гиперссылки).
• Невозможно автоматизировать часто повторяющиеся стандартные операции (такие как импорт данных из бухгалтерской программы в таблицу Excel, форматирование дат и чисел, удаление ненужных столбцов).

Как решить эти проблемы?

• Некоторые пользователи создают архив рабочих книг с примерами. Они копируют оттуда нужную формулу и применяют ее к своей электронной таблице.
• Некоторые пользователи предпочитают один раз создать набор макросов VBA и запускать их по мере необходимости.
• Другой путь — создание пользовательских функций (далее — UDF) с помощью редактора VBA.

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

Что такое определяемая пользователем функция (UDF) в Excel?

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

Другими словами, UDF является своего рода обновлением стандартных функций Excel. Вы можете использовать его, когда возможностей обычных функций недостаточно. Его основное назначение — дополнять и расширять возможности Excel и выполнять действия, которые невозможны при использовании стандартных функций.

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

• Использование Visual Basic для приложений (VBA). Этот способ описан в этой статье.
• Использование замечательной функции LAMBDA, представленной в Office 365.
• Использование сценариев Office. В настоящее время сценарии доступны только в Excel в Интернете.

Посмотрите на скриншот ниже, чтобы увидеть разницу между двумя способами извлечения чисел — с использованием формулы и пользовательского ExtractNumber() 9Функция 0046.

Вы можете использовать UDF любым из следующих способов:

• Как формула, которая может брать необработанные данные с вашего рабочего листа и возвращать вычисленное значение или массив значений.
• Как часть макроса VBA или кода другой пользовательской функции.
• Как компонент ваших формул условного форматирования.
• Для хранения констант и списков данных.

Как создать пользовательскую функцию в Excel?

Прежде всего необходимо открыть редактор Visual Basic (VBE). Имейте в виду, что он просто открывается в новом окне и не закрывает электронную таблицу Excel.

Самый простой способ открыть VBE — использовать сочетание клавиш — Alt + F11. Это быстро, просто и не нужно настраивать ленту или панель быстрого доступа.

Совет. Нажмите Alt + F11, когда VBE открыт, чтобы вернуться в окно Excel.

После открытия VBE вам нужно добавить новый модуль, в котором вы будете писать свои функции. Щелкните правой кнопкой мыши панель проекта VBA и выберите Insert -> Module . Появится пустое окно модуля, где вы должны указать свою пользовательскую функцию.

Прежде чем мы начнем, давайте рассмотрим правила, по которым создаются пользовательские функции:

• Пользовательская функция всегда начинается с «Функция» и заканчивается «Конец функции».
• За «Функция» следует название функции. Это название, которое вы создаете и даете своей функции, чтобы вы могли идентифицировать и использовать его позже. Это имя не должно содержать пробелов. Если вы хотите разделить слова, используйте символы подчеркивания. Например, Count_Words.
• Кроме того, имя также не может совпадать с именами стандартных функций Excel. Если вы сделаете это, то всегда будет выполняться стандартная функция.
• Имя определяемой пользователем функции не может совпадать с адресами ячеек на рабочем листе. Например, имя ABC1234 недопустимо.

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

• Далее в скобках обычно указываются аргументы функции. Это данные, с которыми он будет работать. Аргументов может быть один или несколько. Если у вас есть несколько аргументов, вам нужно перечислить их через запятую.
• Если функции внутри UDF не используют аргументы (например, NOW, TODAY или RAND), то вы можете создать функцию без аргументов. Кроме того, аргументы не нужны, если вы используете UDF для хранения констант (таких как pi).
• После этого укажите переменные, которые использует пользовательская функция. Указывается тип этих переменных — число, дата, текст, массив.
• Затем вы помещаете несколько операторов VBA, выполняющих вычисления с использованием аргументов, переданных функции.
• В конце вы должны написать оператор, который присваивает конечное значение переменной с тем же именем, что и у функции. Это значение возвращается в формулу, из которой была вызвана определяемая пользователем функция.
• Код пользовательской функции может включать комментарии. Они помогут вам запомнить назначение функции и ее операторов. Если вы хотите внести какие-либо изменения в будущем, комментарии будут очень полезны.

Примечание. Комментарий всегда начинается с апострофа (‘). Апостроф указывает Excel игнорировать все после него и до конца строки.

Теперь давайте попробуем создать вашу первую пользовательскую формулу. Для начала создадим пользовательскую функцию, которая будет подсчитывать количество слов в диапазоне ячеек. Для этого вставьте в окно модуля этот код:

Функция CountWords (NumRange As Range) As Long
Dim rCell As Range, lCount As Long

Для каждой rCell в NumRange
lСчетчик = lСчетчик + _
Len (Trim(rCell)) — Len (Replace (Trim(rCell), «», «»)) + 1
Следующая ячейка
Количество слов = количество слов
Конечная функция

Я думаю, что здесь могут потребоваться некоторые пояснения. Как вы помните из приведенных выше правил, код UDF всегда начинается со слова «Функция». Затем делаем описание новой функции и закрываем нашу UDF с помощью «End Function».

Поскольку мы указываем в скобках исходные данные, которые он будет использовать, NumRange As Range означает, что аргумент UDF будет диапазоном значений. Эта функция должна вернуть только один аргумент — диапазон ячеек.

Во второй строке кода мы объявляем переменные.

As Long указывает, что результатом функции CountWords будет целое число.

Оператор Dim объявляет две переменные нашей функции:

• rCell — это переменная диапазона ячеек, в которых мы будем считать слова.
• lCount — это целочисленная переменная, которая будет содержать количество слов.

Аргумент For Each предназначен для выполнения вычислений по каждому элементу в группе элементов (диапазоне ячеек). Этот оператор цикла используется, когда количество элементов в группе неизвестно. Мы начинаем с первого элемента, затем берем следующий и так далее до самого последнего значения. Цикл повторяется столько раз, сколько ячеек во входном диапазоне.

Внутри этого цикла операция подсчета количества слов применяется к значению каждой ячейки:

Len (Trim(rCell)) — Len(Replace(Trim(rCell), «», «»)) + 1

Как видите, это обычная формула Excel, в которой используются стандартные текстовые функции: ДЛСТР, ОБРЕЗАТЬ и ЗАМЕНИТЬ. Вместо ссылки на ячейку мы используем переменную диапазона rCell . Значит, для каждой ячейки диапазона мы последовательно подсчитываем количество слов в ней.

Подсчитанные числа суммируются и сохраняются в lCount переменная:

lCount = lCount + Len (Trim(rCell)) - Len(Replace(Trim(rCell), "", "")) + 1

Когда цикл завершен, значение переменной присваивается функции.

Количество слов = lCount

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

Именно эта строка кода гарантирует, что функция вернет lCount значение в ячейку, из которой он был вызван.

Как видите, это не очень сложно. Сохраните свою функцию, нажав кнопку «Сохранить» на ленте VBE. После этого вы можете закрыть окно редактора. Для этого можно использовать сочетание клавиш Alt+Q. Или просто вернуться на лист Excel, нажав Alt+F11.

Совет. Вы можете узнать больше об использовании VBA в Excel в нашем блоге.

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

Как использовать пользовательские функции

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

Чтобы применить пользовательскую функцию, у вас есть два варианта.

• Нажмите кнопку fx в строке формул. Среди категорий функций вы увидите новую группу — User Defined . И в этой категории вы можете увидеть нашу новую пользовательскую функцию Счетчик слов .
• Вы можете просто записать эту функцию в ячейку так же, как и со стандартными функциями. Когда вы начнете вводить имя, Excel покажет вам имя определяемой пользователем функции в списке подходящих функций. В приведенном ниже примере, когда я ввел =cou , Excel показал мне список соответствующих функций, среди которых вы видите CountWords .

Давайте, наконец, применим нашу пользовательскую формулу. Запишите в любую ячейку:

= Количество слов (A1: A4)

Нажмите Enter. Видите, мы только что указали функцию и установили диапазон, и вот результат подсчета: 28 слов.

Различные типы определяемых пользователем функций

Теперь мы проверим различные типы UDF в зависимости от используемых ими аргументов и результатов, которые они возвращают.

Нет аргументов

В Excel есть несколько стандартных функций, не требующих аргументов (СЛУЧАЙ, СЕГОДНЯ, СЕЙЧАС). Например, функция СЛЧИС возвращает случайное число от 0 до 1. Функция СЕГОДНЯ возвращает текущую дату. Никаких значений им указывать не нужно.

Хорошей новостью является то, что вы можете создать такую ​​функцию и в VBA. Ниже приведен код, который запишет имя вашего рабочего листа в ячейку:

.

Функция SheetName() как строка

Application.Volatile

Имя Листа = Application.Caller.Worksheet.Name

Конечная функция

Или вы можете использовать этот код вместо:

имя_листа = ActiveSheet.Name

Обратите внимание, что в скобках после имени функции нет аргументов. Поскольку возвращаемый результат не зависит ни от каких значений в вашем рабочем файле, функция не требует никаких аргументов.

Приведенный выше код определяет результат функции как строковый тип данных (поскольку желаемый результат — это имя файла, то есть текст). Если не указать тип данных, то Excel определит его самостоятельно.

С одним аргументом

Теперь давайте создадим простую функцию, которая работает с одним аргументом, то есть с одной ячейкой.

Наша задача — извлечь последнее слово из текстовой строки. Вот код, который мы будем использовать:

Функция ReturnLastWord (The_Text как строка)
Dim stLastWord как строка

‘Извлекает ПОСЛЕДНЕЕ слово из текстовой строки
stLastWord = StrReverse(The_Text)
stLastWord = Left(stLastWord, InStr(1, stLastWord, » «, vbTextCompare))
ReturnLastWord = StrReverse(Trim(stLastWord))
Конечная функция

The_Text — это значение выбранной ячейки. Мы указываем, что это должно быть текстовое значение ( As String ).

Функция StrReverse возвращает текст в обратном порядке символов. Затем функция InStr определяет положение первого пробела. Используя функцию Left , мы получаем все символы, заканчивающиеся первым пробелом. Затем убираем пробелы с помощью Trim . Снова измените порядок символов, используя StrReverse . Получаем последнее слово из текста.

Поскольку эта функция принимает значение ячейки, нам не нужно использовать здесь Application.Volatile . Как только аргумент изменится, функция автоматически обновится.

Использование массива в качестве аргумента

Многие функции Excel используют в качестве аргументов массивы значений. Вспомните функции СУММ, СУММЕСЛИ, СУММПРОИЗВ. Мы уже рассмотрели эту ситуацию выше, когда узнали, как создать пользовательскую функцию для подсчета количества слов в диапазоне ячеек.

Приведенный ниже код создает функцию, которая суммирует все четные числа в указанном диапазоне ячеек.

Функция SumEven (NumRange как диапазон)
Dim RngCell как диапазон

Для каждой RngCell в NumRange
Если Числовой(RngCell.Value) Тогда
Если RngCell.Value Mod 2 = 0 Тогда
Результат = Результат + RngCell.Value
Конец, если
Конец, если
Следующая RngCell
СуммаЭвен = Результат
Конечная функция

Аргумент NumRange указан как Диапазон . Это означает, что функция будет использовать исходный массив данных. Следует отметить, что также можно использовать тип переменной Variant. Похоже на

Функция SumEven (NumRange как вариант)

Тип Variant предоставляет «безразмерный» контейнер для хранения данных. Такая переменная может хранить любые типы данных, разрешенные в VBA, включая числовые значения, текст, даты и массивы. Более того, одна и та же такая переменная в одной и той же программе в разное время может хранить данные разных типов. Excel самостоятельно определит, какие данные передаются функции.

Код содержит аргумент For Each . Он берет каждую ячейку и проверяет, содержит ли она число. Если это не так, то ничего не происходит и он переходит к следующей ячейке. Если число найдено, оно проверяет, четное оно или нет (используя функцию MOD).

Все четные числа суммируются в переменной Result .

Когда цикл завершен, значение Result присваивается переменной SumEven и передается в функцию.

С несколькими аргументами

Большинство функций Excel имеют несколько аргументов. Пользовательские функции не являются исключением. Вот почему так важно иметь возможность создавать пользовательские функции с несколькими аргументами.

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

Функция GetMaxBetween (rngCells As Range, MinNum, MaxNum)
Dim NumRange как диапазон
Дим. вмакс.
Dim arrNums()
Dim i как целое число

ReDim arrNums (rngCells. Count)
Для каждого NumRange в rngCells
vMax = числовой диапазон
Выберите случай vMax
Case MinNum + 0,01 To MaxNum — 0,01
arrNums(i) = vMax
я = я + 1
Дело еще
GetMaxBetween = 0
Конец выбора
Следующий диапазон номеров

GetMaxBetween = WorksheetFunction.Max(arrNums)

Конечная функция

У него есть 3 аргумента: диапазон значений, нижняя граница числового диапазона и верхняя граница диапазона. Первый — rngCells As Range . Это диапазон ячеек для поиска максимального значения. Второй и третий аргументы ( MinNum , MaxNum ) указываются без объявления типа. Это означает, что по умолчанию к ним будет применяться тип данных Variant .

VBA использует 6 различных типов числовых данных. Указание только одного из них означает ограничение использования функции. Поэтому было бы лучше, если бы Excel сам определял тип числовых данных.

For Each последовательно перебирает все значения в выбранном диапазоне. Числа, находящиеся в диапазоне от максимального до минимального значения, записываются в специальный массив — arrNums . Используя стандартную функцию MAX, найдите наибольшее число в этом массиве.

С обязательными и необязательными аргументами

Чтобы понять, что такое необязательный аргумент, вспомните функцию ВПР. Его четвертый аргумент [range_lookup] является необязательным. Если вы опустите один из обязательных аргументов, ваша функция выдаст ошибку. Но если вы опустите необязательный аргумент, ваша функция будет работать.

Однако необязательные аргументы не бесполезны. Они позволяют выбрать вариант расчета.

Например, в функции ВПР, если опустить четвертый аргумент, будет выполнен приблизительный поиск. Если вы укажете аргумент [range_lookup] как FALSE (или 0), будет найдено точное совпадение.

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

Чтобы сделать аргумент необязательным, вам просто нужно добавить » Необязательный » перед ним. Давайте посмотрим на пример функции с необязательными аргументами в VBA:

Функция GetText (textCell As Range, необязательно CaseText = False) As String
Dim StringLength как целое число
Затемнить результат как строку

StringLength = Длина (текстовая ячейка)

Для i = 1 до StringLength
Если Not (IsNumeric(Mid(textCell, i, 1))) Then Result = Result & Середина (текстовая ячейка, я, 1)
Далее я

Если CaseText = Истина Тогда
Результат = UCase(Результат)

ПолучитьТекст = Результат
Конечная функция

Эта пользовательская функция извлекает текст из ячейки. Необязательный CaseText = False означает, что аргумент CaseText является необязательным. По умолчанию его значение равно FALSE .

Если необязательный аргумент CaseText равен TRUE , то результат возвращается в верхнем регистре. Если необязательный аргумент равен FALSE или опущен, результат остается без изменения регистра.

Вы можете задаться вопросом: «Могут ли в определяемой пользователем функции быть только необязательные аргументы?». У нас есть ответ для вас внизу 🙂

Только с необязательными аргументами

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

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

Имя пользователя функции (опционально в верхнем регистре как вариант)

Если IsMissing (верхний регистр), то верхний регистр = False

ИмяПользователя = Приложение.ИмяПользователя

Если в верхнем регистре, то имя_пользователя = UCase(имя_пользователя)

Конечная функция

Как видите, есть только один аргумент Верхний регистр , и он необязателен.

Если аргумент равен FALSE или опущен, то имя пользователя возвращается без изменений. Если аргумент функции равен TRUE , имя возвращается в верхнем регистре (с использованием функции Ucase VBA). Обратите внимание на первый оператор функции. Он содержит функцию IsMissing VBA, которая обнаруживает наличие аргумента. Если аргумент отсутствует, оператор устанавливает для переменной Uppercase значение 9.0045 ЛОЖЬ .

Посмотрите на другую версию этой функции:

Имя пользователя функции (необязательный верхний регистр = ложь)

ИмяПользователя = Приложение.ИмяПользователя

Если Верхний регистр = Верно Тогда
Имя пользователя = UCase (имя пользователя)

Конечная функция

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

Возвращаемое значение представляет собой массив

VBA имеет очень полезную функцию под названием Array . Он возвращает вариантный тип данных, который представляет собой массив (проще говоря, несколько значений). Если вы не знакомы с формулами массива в Excel, мы предлагаем начать с руководства для начинающих по функциям массива в нашем блоге.

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

Месяцы функции () как вариант
Месяцы = массив («январь», «февраль», «март», «апрель», «май», «июнь», «июль», «август», «сентябрь», «октябрь», «ноябрь», «декабрь» «)
Конечная функция

Примечание. UDF выводит данные горизонтально (в ряд).

А если нужен вертикальный массив значений? Ранее упоминалось, что пользовательские функции можно использовать в формулах Excel наряду со стандартными функциями. Сказав это, давайте использовать Months() в качестве аргумента в функции ТРАНСП:

= ТРАНСП(Месяцы())

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

Более того, вы можете узнать название месяца по его номеру. Например, ячейка A1 содержит номер месяца. Тогда название месяца можно получить по формуле

= ИНДЕКС (Месяцы(), 1, A1)

Альтернативный вариант этой формулы:

=ИНДЕКС({"Январь", "Февраль", "Март", "Апрель", "Май", "Июнь", "Июль", "Август", "Сентябрь", "Октябрь", "Ноябрь", "Декабрьская"}, 1, А1)

Согласитесь, UDF с массивом значительно упрощает формулу Excel.

Эта статья открывает серию постов о пользовательских функциях.

Если мне удалось вас убедить, что эту функцию стоит попробовать, то рекомендую обратить внимание на эти статьи:

• Преимущества и недостатки пользовательских функций в Excel
• Решение проблем с неработающими пользовательскими функциями
• Узнайте, как отлаживать пользовательские функции в Excel
• Сохранение пользовательских функций в надстройке Excel

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

Авторы: Ken Bluttman и

Обновлено: 07. 01.2022

Обзор Купить книгу на Amazon

Несмотря на все функции, предоставляемые Excel, вам может понадобиться та, которую вы просто не видите в предложении. Excel позволяет создавать собственные функции с помощью кода программирования VBA; ваши функции отображаются в диалоговом окне «Вставить функцию».

Написание кода VBA доступно не всем. Но тем не менее, вот короткий и милый пример. Если вы можете победить это, вы можете узнать больше о программировании VBA. Кто знает — может быть, однажды вы будете штамповать собственные сложные функции! Убедитесь, что вы работаете в рабочей книге с поддержкой макросов (один из типов файлов Excel).

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

1. Нажмите Alt + F11.

   Вы попадете в редактор Visual Basic, где написан VBA.

   Вы также можете нажать кнопку Visual Basic на вкладке «Разработчик» на ленте. Вкладка «Разработчик» отображается, только если установлен флажок «Разработчик» на вкладке «Настроить ленту» диалогового окна «Параметры Excel».

2. Выберите Вставка→Модуль в редакторе.

   Перед вами лежит пустой кодовый модуль. Теперь пришло время создать свою собственную функцию!

3. Введите этот программный код, показанный на следующем рисунке:

   Написание собственной функции

    Добавление общедоступной функции (число 1 как двойное, число 2 как двойное)
   Добавить = число1 + число2
   Завершить функцию 

4. Сохраните функцию.

   Макросы и программы VBA можно сохранять только в рабочей книге с поддержкой макросов.

   После того, как вы наберете первую строку и нажмете Enter, автоматически появится последняя. В этом примере функция добавляет два числа, а слово Public перечисляет функцию в диалоговом окне «Вставить функцию». Возможно, вам придется найти книгу Excel на панели задач Windows, потому что редактор Visual Basic работает как отдельная программа. Или нажмите Alt + F11, чтобы вернуться к рабочей книге.

5. Вернуться в Excel.

6. Нажмите кнопку «Вставить функцию» на вкладке «Формулы», чтобы открыть диалоговое окно «Вставить функцию».

   Поиск функции в категории User Defined

7. Нажмите OK.

   Откроется диалоговое окно «Аргументы функции», готовое к приему аргументов. Разве это не невероятно? Это как если бы вы создавали расширение для Excel, и, по сути, так оно и есть.

   Использование пользовательской функции добавления

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

Книги с поддержкой макросов имеют расширение .xlsm.

Эта статья взята из книги:

• Формулы и функции Excel для чайников,

Об авторе книги:

Кен Блуттман — опытный программист и веб-разработчик, специализирующийся на Excel/VBA и веб-ориентированных базах данных.