Преимущества и недостатки пользовательских функций в Excel
Мы продолжаем серию руководств по пользовательским функциям. В наших предыдущих статьях мы познакомились с пользовательскими функциями и научились их создавать и использовать. В этом руководстве мы рассмотрим особенности использования этих функций и увидим различия между UDF и макросами VBA.
В этом уроке мы узнаем следующее:
Мы надеемся, что эта статья расширит ваши знания о UDF и поможет вам еще более эффективно использовать их в своих книгах Excel.
Является ли UDF и макрос одним и тем же?
И пользовательские функции, и макросы VBA создаются с помощью редактора VBA. В чем между ними разница и чему отдать предпочтение?
Самое главное отличие состоит в том, что функция выполняет расчет, а макрос выполняет какое-то действие. Пользовательская функция, как и обычная функция Excel, должна быть записана в ячейке. В результате своего выполнения ячейка возвращает некоторое значение. При этом невозможно изменить значения других ячеек, а также некоторые свойства текущей ячейки (в частности, форматирование). Однако вы можете использовать пользовательскую функцию в формулах условного форматирования.
Макросы UDF и VBA работают по-разному. Например, когда вы создаете пользовательскую функцию в редакторе Visual Basic, вы начинаете с оператора Функция и закончить с Конечная функция. Когда вы записываете макрос, вы начинаете с утверждения Саб и закончить с Конец суб.
Не все операторы Visual Basic можно использовать для создания пользовательских функций. По этой причине макрос является более универсальным решением.
Макрос не требует от пользователя передачи каких-либо аргументов (и не может принимать какие-либо аргументы), в отличие от определяемой пользователем функции.
Дело в том, что некоторые команды макросов могут использовать адреса ячеек или элементы форматирования (например, цвет). Если вы перемещаете ячейки, добавляете или удаляете строки и столбцы, меняете формат ячеек, то вы легко можете «сломать» свои макросы. Это особенно возможно, если вы делитесь своим файлом с коллегами, которые не знают, как работают ваши макросы.
Например, у вас есть файл с отлично работающим макросом. Эта формула вычисляет процент ячеек от A1 до A4. Макрос изменяет цвет этих ячеек на желтый. В активной ячейке задается процентный формат.
Если вы или кто-то другой решите вставить новую строку, макрос продолжит поиск значения в ячейке A4 (параметр 4,1 в вашей пользовательской функции), завершится ошибкой и вернет ошибку:
В данном случае ошибка произошла из-за деления на ноль (нет значения в новой добавленной строке). Если макрос выполняет, скажем, суммирование, то вы просто получите неправильный результат. Но ты об этом не узнаешь.
В отличие от макросов, пользовательские функции не могут вызвать такую неприятную ситуацию.
Ниже вы видите производительность тех же вычислений с использованием UDF. Здесь вы можете указать входные ячейки в любом месте рабочего листа, и вы не столкнетесь с какими-либо непредвиденными проблемами при его изменении.
Я написал следующую формулу в C3:
=UDF_vs_Macro(A1,A4)
Затем я вставил пустую строку, и формула изменилась, как вы можете видеть на скриншоте выше.
Теперь мы можем перемещать входную ячейку или ячейку с функцией куда угодно. Результат всегда будет правильным.
Дополнительным преимуществом использования пользовательских функций является то, что они автоматически обновляются при изменении значения во входной ячейке. При использовании макросов вы всегда должны следить за тем, чтобы все данные были актуальными.
Имея в виду этот пример, я бы предпочел использовать пользовательские функции везде, где это возможно, и использовать макросы только для других операций, не связанных с вычислениями.
Ограничения и недостатки использования UDF
О преимуществах UDF я уже упоминал выше. Короче говоря, он может выполнять вычисления, которые невозможны со стандартными функциями Excel. Кроме того, он может сохранять и использовать длинные и сложные формулы, превращая их в одну функцию. И вам не придется снова и снова писать сложные формулы.
Теперь поговорим подробнее о недостатках UDF:
- Создание UDF требует использования VBA. Нет никакого способа обойти это. Это означает, что пользователь не может записывать UDF так же, как макрос Excel. Вы должны создать UDF самостоятельно. Однако вы можете копировать и вставлять части ранее записанного кода макроса в свою функцию. Просто нужно быть в курсе ограничения пользовательских функций.
- Еще одним недостатком UDF является то, что, как и любая другая функция Excel, она может возвращать в ячейку только одно значение или массив значений. Он просто выполняет расчеты, не более того.
- Если вы хотите поделиться своей книгой с коллегами, обязательно сохраните свои пользовательские функции в том же файле. В противном случае ваши пользовательские функции не будут работать для них.
- Пользовательские функции, созданные с помощью редактора VBA, работают медленнее, чем обычные функции. Особенно это заметно на больших таблицах. К сожалению, VBA пока очень медленный язык программирования. Поэтому, если у вас много данных, по возможности старайтесь использовать стандартные функции или создавайте пользовательские функции с помощью функции LAMBDA.
Ограничения пользовательских функций:
- Пользовательские функции предназначены для выполнения вычислений и возврата значения. Их нельзя использовать вместо макросов.
- Они не могут изменять содержимое любых других ячеек (только активной ячейки).
- Имена функций должны соответствовать определенным правилам. Например, нельзя использовать имя, совпадающее с собственным именем функции Excel или адресом ячейки, например AB123.
- Ваша пользовательская функция не может содержать пробелы в имени, но может включать символ подчеркивания. Однако предпочтительнее использовать заглавные буквы в начале каждого нового слова (например, GetMaxBetween).
- Пользовательская функция не может копировать и вставлять ячейки в другие области рабочего листа.
- Они не могут изменить активный рабочий лист.
- Пользовательские функции не могут изменить форматирование в активной ячейке. Если вы хотите изменить форматирование ячейки при отображении разных значений, вам следует использовать условное форматирование.
- Они не могут открывать дополнительные книги.
- Их нельзя использовать для запуска макросов с помощью Application.OnTime.
- Пользовательская функция не может быть создана с помощью средства записи макросов.
- Функции не отображаются в Разработчик > Макросы диалог.
- Ваши функции появятся в диалоговом окне (Вставка > Функция) и в списке функций, только если они объявлены как Общественный (это значение по умолчанию, если не указано иное).
- Любые функции, объявленные как Частный не будет отображаться в списке функций.
Довольно медленная работа, а также некоторые ограничения в использовании могут заставить задуматься: «А что толку от этих пользовательских функций?»
Они могут пригодиться и пригодятся, если мы будем помнить об ограничениях, накладываемых на них. Если вы научитесь правильно создавать и использовать пользовательские функции, вы сможете написать свою библиотеку функций. Это значительно расширит ваши возможности работы с данными в Excel.
Что касается меня, пользовательские функции отлично экономят время. А что насчет тебя? Вы уже пробовали создать свою собственную UDF? Вам понравилось больше, чем основные функции Excel? Давайте обсудим это в комментариях 🙂
