Как использовать и хранить пользовательские функции в Excel
Сегодня мы продолжим изучение пользовательских функций Excel. Поскольку вы уже знаете, как создавать пользовательские функции (и, надеюсь, пробовали применять их в Excel), давайте копнем немного глубже и узнаем, как использовать и хранить пользовательские функции в Excel.
Кроме того, мы покажем вам, как легко сохранить ваши функции в файле надстройки Excel, чтобы использовать их позже в несколько кликов.
Итак, вот о чем мы будем говорить:
Различные способы использования UDF в Excel
Использование пользовательских функций на листах
Убедившись, что ваши пользовательские функции работают правильно, вы можете использовать их в формулах Excel или в коде VBA.
Пользовательские функции можно применять в книге Excel так же, как и обычные функции. Например, просто напишите в ячейке приведенную ниже формулу:
= ПолучитьМаксМежду(A1:A6,10,50)
UDF можно использовать в сочетании с обычными функциями. Например, добавьте текст к рассчитанному максимальному значению:
= СЦЕПИТЬ(“Максимальное значение между 10 и 50 равно “, GetMaxBetween(A1: A6,10,50))
Результат вы можете увидеть на скриншоте ниже:
Вы можете найти число, которое является как максимальным, так и находится в диапазоне от 10 до 50.
Проверим другую формулу:
= ИНДЕКС(A2:A9, ПОИСКПОЗ(GetMaxBetween(B2:B9, F1, F2), B2:B9,0)),
Пользовательская функция GetMaxBetween проверяет диапазон B2:B9 и находит максимальное число от 10 до 50. Затем, используя ИНДЕКС + ПОИСКПОЗ, мы получаем название продукта, соответствующее этому максимальному значению:
Как видите, использование пользовательских функций не слишком отличается от обычных функций Excel.
При этом помните, что пользовательская функция может только возвращать значение, но не может выполнять никаких других действий. Узнайте больше об ограничениях пользовательских функций.
Использование UDF в процедурах и функциях VBA
Пользовательские функции также можно использовать в макросах VBA. Ниже вы можете увидеть код макроса, который ищет максимальное значение в диапазоне от 10 до 50 в столбце, содержащем активную ячейку.
Sub MacroWithUDF() Dim Rng As Range, maxcase, i As Long With ActiveSheet.Range(Cells(ActiveCell.CurrentRegion.Row, ActiveCell.Column), Cells(ActiveCell.CurrentRegion.Rows.Count _ + ActiveCell.CurrentRegion.Row – 1 , ActiveCell.Column)) maxcase = GetMaxBetween(.Cells, 10, 50) i = Application.Match(maxcase, .Cells, 0) .Cells(i).Interior.Color = vbRed End With End Sub
Код макроса содержит пользовательскую функцию
GetMaxBetween(.Cells, 10, 50)
Он находит максимальное значение в активном столбце. Тогда это значение будет выделено. Результат работы макроса вы можете увидеть на скриншоте ниже.
Пользовательскую функцию также можно использовать внутри другой пользовательской функции. Ранее в нашем блоге мы рассмотрели проблему преобразования числа в текст с помощью пользовательской функции с именем Число заклинаний.
С его помощью мы можем получить максимальное значение из диапазона и тут же записать его в виде текста.
Для этого мы создадим новую пользовательскую функцию, в которой будем использовать функции GetMaxBetween а также Число заклинаний которые нам уже знакомы.
Функция SpellGetMaxBetween(rngCells As Range, MinNum, MaxNum) SpellGetMaxBetween = SpellNumber(GetMaxBetween (rngCells, MinNum, MaxNum)) Конечная функция
Как видите, GetMaxBetween функция является аргументом другой пользовательской функции, Число заклинаний. Он определяет максимальное значение, как мы уже делали много раз раньше. Затем это число преобразуется в текст.
На скриншоте выше вы можете видеть, как ЗаклинаниеGetMaxBetween Функция находит максимальное число от 100 до 500, а затем преобразует его в текст.
Вызов UDF из других книг
Если вы создали UDF в своей рабочей книге, это, к сожалению, не означает, что вы вообще не столкнетесь с какими-либо проблемами.
По моему опыту, большинство пользователей рано или поздно создают свою личную коллекцию макросов и пользовательских функций для автоматизации отдельных процессов и расчетов. И тут возникает проблема — код пользовательских функций в Visual Basic нужно где-то хранить, чтобы потом использовать в работе.
Чтобы применить пользовательскую функцию, рабочая книга, в которой вы ее сохранили, должна быть открыта в Excel. Если это не так, вы получите #ИМЯ! ошибка при попытке его использования. Эта ошибка указывает на то, что Excel не знает имя функции, которую вы хотите использовать в формуле.
Давайте посмотрим, как вы можете использовать созданные вами пользовательские функции.
Способ 1. Добавьте имя книги в функцию
Вы можете указать имя рабочей книги, в которой она находится перед названием функции. Например, если вы сохранили пользовательскую функцию ПолучитьМаксМежду() в рабочей тетради под названием My_Functions.xlsmто необходимо ввести следующую формулу:
= My_Functions.xlsm!GetMaxBetween(A1:A6,10,50)
Способ 2. Хранить все UDF в одном общем файле
Сохраните все пользовательские функции в одной специальной книге (например, My_Functions.xlsm) и скопировать из него нужную функцию в текущую книгу, если это необходимо.
Каждый раз, когда вы создаете новую пользовательскую функцию, вам необходимо дублировать ее код в рабочей книге, в которой вы будете ее использовать. При таком способе может возникнуть несколько неудобств:
- Если рабочих файлов много, а функция нужна везде, то код придется копировать в каждую книгу.
- Не забудьте сохранить книгу в формате с поддержкой макросов (.xlsm или .xlsb).
- При открытии такого файла защита от макросов каждый раз будет выводить предупреждение, которое необходимо подтвердить. Многие пользователи пугаются, когда видят желтую полосу с предупреждением о включении макросов. Чтобы не видеть это сообщение, вам нужно полностью отключить защиту Excel. Однако это не всегда может быть правильным и безопасным.
Думаю, вы согласитесь со мной, что все время открывать файл и копировать из него код пользовательских функций или писать имя этого файла в формуле — не лучшее решение. таким образом, мы подошли к третьему пути.
Способ 3. Создайте файл надстройки Excel
Я думаю, что лучший способ — хранить часто используемые пользовательские функции в файле надстройки Excel. Преимущества использования надстройки:
- Вам нужно подключить надстройку к Excel только один раз. После этого вы можете использовать его процедуры и функции в любом файле на этом компьютере. Вам не нужно сохранять рабочие книги в форматах .xlsm и .xlsb, так как исходный код будет храниться не в них, а в файле надстройки.
- Вас больше не будет беспокоить защита макросов, так как надстройки всегда ссылаются на доверенные источники.
- Надстройка представляет собой отдельный файл. Его легко переносить с компьютера на компьютер, чтобы поделиться им с коллегами.
Подробнее о создании и использовании надстройки мы поговорим позже.
Использование надстроек для хранения пользовательских функций
Как создать собственную надстройку? Давайте рассмотрим этот процесс шаг за шагом.
Шаг 1. Создайте файл надстройки
Откройте Microsoft Excel, создайте новую книгу и сохраните ее под любым подходящим именем (например, My_Functions) в формате надстройки. Для этого воспользуйтесь меню Файл – Сохранить как или клавиша F12. Обязательно укажите тип файла Надстройка Excel:
Ваша надстройка будет иметь расширение .xlam.
Кончик. Обратите внимание, что по умолчанию Excel хранит надстройки в C:\Пользователи\[Your_Name]\AppData\Roaming\Microsoft\AddIns папка. Я рекомендую вам принять местоположение по умолчанию. При желании вы можете указать любую другую папку. Но тогда при подключении надстройки вам нужно будет найти и указать ее новое расположение вручную. Если вы сохраните его в папке по умолчанию, вам не придется искать надстройку на своем компьютере. Excel автоматически перечислит его.
Шаг 2. Подключите файл надстройки
Теперь созданную нами надстройку необходимо подключить к Excel. Затем он будет загружаться автоматически при запуске программы. Для этого воспользуйтесь меню Файл – Параметры – Надстройки. Убедиться Надстройки Excel выбирается в Управлять поле. Нажми на Идти кнопку в нижней части окна. В появившемся окне отмечаем нашу надстройку My_Functions. Если вы не видите его в списке, нажмите кнопку Просматривать и перейдите к местоположению вашего файла надстройки.
Если вы используете надстройку для хранения пользовательских функций, нужно следовать одному простому правилу. Если вы передаете книгу другим людям, не забудьте также передать копию надстройки, которая содержит нужные вам функции. Они должны подключить его так же, как вы сделали сейчас.
Шаг 3. Добавьте пользовательские функции и макросы в надстройку
Наша надстройка подключена к Excel, но пока не имеет никакого функционала. Чтобы добавить в него новые пользовательские функции, откройте редактор Visual Basic, нажав клавиши Alt + F11. Затем вы можете добавить новые модули с кодом VBA, как описано в моем руководстве по созданию пользовательских функций.
Выберите файл надстройки (My_Finctions.xlam) в окне VBAProject. Использовать Вставка – модуль меню для добавления пользовательского модуля. Вам нужно написать в него пользовательские функции.
Вы можете либо набрать код пользовательской функции вручную, либо скопировать его откуда-то.
Это все. Теперь вы создали свою надстройку, добавили ее в Excel и можете использовать в ней UDF. Если вы хотите использовать больше пользовательских функций, просто напишите код в модуле надстройки в редакторе VBA и сохраните его.
Это все на сегодня. Мы узнали, как использовать пользовательские функции в вашей книге. Мы очень надеемся, что вы найдете эти рекомендации полезными. Если у вас остались вопросы, пишите в комментариях к этой статье.
