Создать в excel функцию: Создание пользовательских функций в Excel — Office Add-ins

Содержание

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





Twitter




LinkedIn




Facebook




Адрес электронной почты










  • Статья


  • Чтение занимает 4 мин


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

Важно!

Обратите внимание, что настраиваемые функции доступны в Excel на следующих платформах.

  • Office для Windows
    • Подписка на Microsoft 365
    • Розничный бессрочный Office 2016 и более поздних версий
  • Office для Mac
  • Office в Интернете

В настоящее время пользовательские функции Excel не поддерживаются в следующих приложениях:

  • Office для iPad
  • корпоративные бессрочные версии Office 2019 или более ранних версий
  • Розничная версия Office 2013 с бессрочным сроком 2013

Ниже на анимированном изображении показано, как рабочая книга вызывает функцию, созданную вами с помощью JavaScript или TypeScript. В этом примере пользовательская функция =MYFUNCTION.SPHEREVOLUME рассчитывает объем сферы.

Приведенный ниже код определяет пользовательскую функцию =MYFUNCTION. SPHEREVOLUME.

/**
 * Returns the volume of a sphere.
 * @customfunction
 * @param {number} radius
 */
function sphereVolume(radius) {
  return Math.pow(radius, 3) * 4 * Math.PI / 3;
}

Совет

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

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

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

ФайлФормат файлаОписание
./src/functions/functions.js
или
. /src/functions/functions.ts		JavaScript
или
TypeScript		Содержит код, который определяет пользовательские функции.
./src/functions/functions.htmlHTMLПредоставляет <скрипт> со ссылкой на файл JavaScript, который определяет пользовательские функции.
./manifest.xmlXMLУказывает расположение нескольких файлов, которые используются пользовательскими функциями, например JavaScript, JSON и HTML-файлов. А также среду выполнения, которую должны использовать пользовательские функции, расположение файлов области задач и командных файлов.

Файл скрипта

Файл скрипта (./src/functions/functions.js или ./src/functions/functions.ts) содержит код, определяющий пользовательские функции, и комментарии, определяющие функцию.

Приведенный ниже код определяет пользовательскую функцию add. Примечания кода используются для создания файла метаданных JSON с описанием пользовательской функции для Excel. Обязательный комментарий @customfunction объявлен первым, чтобы указать, что это пользовательская функция. Затем объявляются еще два параметра: first и second, за которыми следуют их свойства description. Наконец, дается описание returns. Дополнительные сведения о том, какие комментарии являются обязательными для вашей пользовательской функции, см. в статье Автоматическое создание метаданных JSON для пользовательских функций.

/**
 * Adds two numbers.
 * @customfunction 
 * @param first First number.
 * @param second Second number.
 * @returns The sum of the two numbers.
 */
function add(first, second){
  return first + second;
}

Файл манифеста

Файл манифеста XML для надстройки, определяющий пользовательские функции (./manifest.xml в проекте, созданном генератором Yeoman для надстроек Office), выполняет несколько задач.

  • Определяет пространство имен для пользовательских функций. Пространство имен добавляется к пользовательским функциям, чтобы клиенты могли определить ваши функции в рамках надстройки.
  • Использует <элементы ExtensionPoint> и <Resources> , уникальные для манифеста пользовательских функций. Эти элементы содержат сведения о расположении JavaScript, JSON и HTML-файлов.
  • Указывает, какую среду выполнения использовать для пользовательской функции. Рекомендуется всегда использовать общую среду выполнения, если нет особой потребности в использовании другой среды, так как общая позволяет делиться данными между функциями и областью задач.

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

Полный рабочий манифест из примера надстройки можно просмотреть в одном из репозиториев Github для примеров надстроек Office.

Совет

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

  • Указать URL-адреса, соответствующие среде.
  • Настроить значения метаданных, такие как DisplayName, и метки в Resources для указания среды, чтобы конечные пользователи могли определить соответствующую среду надстройки, загруженной без публикации.
  • Настроить пользовательские функции namespace, чтобы указать среду, если ваша надстройка определяет пользовательские функции.

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

Совместное редактирование

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

Дополнительные сведения о совместном редактировании см. в статье О совместном редактировании в Excel.

Дальнейшие действия

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

Еще одно простое средство ознакомления с пользовательскими функциями — Script Lab, надстройка, в которой можно экспериментировать с пользовательскими функциями прямо в Excel. Вы можете попробовать создать собственные пользовательские функции или поиграть с готовыми примерами.

См. также

  • Сведения о программе для разработчиков Microsoft 365
  • Наборы обязательных элементов пользовательских функций
  • Правила именования пользовательских функций
  • Создание пользовательских функций, совместимых с функциями XLL, определенными пользователями
  • Настройка надстройки Office для использования общей среды выполнения
  • Среды выполнения в надстройках Office






Примеры как создать пользовательскую функцию в 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 для Интернета Дополнительно… Меньше

Вы можете создать формулу для вычисления значений на листе с помощью функции. Например, формулы =СУММ(A1:A2) и СУММ(A1,A2) используют функцию СУММ для сложения значений в ячейках A1 и A2. Формулы всегда начинаются со знака равенства ( = ).

  1. org/ListItem»>

    Щелкните ячейку, в которую вы хотите ввести формулу.

  2. Чтобы начать формулу с функцией, щелкните в строке формул или начните вводить формулу в ячейке.

    Совет: Список доступных функций см. в разделе Функции (в алфавитном порядке) или Функции (по категориям).

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

Совет:  Если вы часто используете функции, вы можете вводить формулы прямо на листе. После того как вы введете знак равенства (=) и имя функции, вы увидите информацию о синтаксисе формулы и аргументах функции.

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

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

Данные

5

4

2

6

3

8

7

1

Формула

Описание

Результат

‘=СУММ(А:А)

Добавляет все числа в столбец A

=СУММ(А:А)

‘=СРЕДНЕЕ(A1:B4)

Усредняет все числа в диапазоне A1:B4

=СРЕДНЕЕ(A1:B4)

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

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

  • Что такое определяемая пользователем функция в 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() .

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

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

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

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

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

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

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

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

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

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

  • Далее аргументы функции обычно указываются в скобках. Это данные, с которыми он будет работать. Аргументов может быть один или несколько. Если у вас есть несколько аргументов, вам нужно перечислить их через запятую.
  • Если функции внутри пользовательской функции не используют аргументы (например, 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 — целочисленная переменная, которая будет содержать количество слов.

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

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

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

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

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

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

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

CountWords = lCount

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Function SheetName() as String

Application.Volatile

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

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

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

SheetName = ActiveSheet.Name

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

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

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

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

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

Функция ReturnLastWord(The_Text As String)
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 as Range)
Dim RngCell как диапазон

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

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

Функция SumEven(NumRange as Variant)

Тип 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 (текстовая ячейка как диапазон, необязательный CaseText = False) как строка
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.0219 ЛОЖЬ .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также: