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

Содержание

VBA-Урок 14.2. Создание пользовательской функции

ВВЕРХ

❮
❯

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

Чтобы отобразить значение (B2) если ячейка (C2) имеет значение («YES»), мы могли бы использовать функцию IF в формуле = IF (C2 = «YES», B2,0) :

Нашей целью в этом случае является создание функции, которая могла бы делать это = IF (C2 has a green background, B2,0) , и которую мы бы записали, типа: =IF_GREEN(C2,B2):

Создание собственной функции

Начнем создавать функцию:

Function IF_GREEN(paid As Range, amount)
     
End Function

Аргументы:

  • paid As Range : ячейка, которую мы будем проверять;
  • amount : значение, которое мы будем вставлять, если TRUE .

В данном случае, если проверка выдает FALSE , значение всегда будет 0, поэтому нет причин прописывать аргумент для этого.

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

Function IF_GREEN(paid As Range, amount)
     green_color = Sheets("Sheet1").Range("K1").Interior.color
End Function

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

Function IF_GREEN(paid As Range, amount)
     green_color = 5296274 'Зеленый
End Function

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

Sub test_color()
     MsgBox ActiveCell.Interior.color
End Sub

Теперь, все что нам необходимо сделать, это проверить цвет ячейки используя IF:

Function IF_GREEN(paid As Range, amount)
     
     Application. Volatile
     
     green_color = 5296274 'Зеленый
    
     If paid.Interior.color = green_color Then 'ЕСЛИ ПРАВДА
        IF_GREEN = amount
     Else 'ЕСЛИ ЛОЖЬ
        IF_GREEN = 0
     End If
     
End Function

Теперь наша функция готова к использованию.

Application.Volatile говорит нам, что приложение является меняющимся (как это имеет место для функции IF ). Это означает, что она должна быть перечислена каждый раз, когда значение изменяется. Например, если вы измените любую из сумм (или, на самом деле, любую другую ячейку), эта функция будет перечислена и правильная сумма будет отражена.

Однако, изменение заливки ячейки не является событием, которое запускает перерасчет. Чтобы заставить эксель перечислить значения, вы можете выбрать пустую ячейку и нажать «Удалить» или добавить кнопку «Обновить», которая все будет обновлять, когда вы ее будете нажимать:

Sub refresh_macro()
     Application.Calculate
End Sub

Дополнительно

Ниже есть другой способ написания такой же функции:

Function IF_GREEN(paid As Range, amount)
     Application. Volatile
     IF_GREEN = 0 'ЕСЛИ ЛОЖЬ
     If paid.Interior.color = 5296274 Then IF_GREEN = amount 'ЕСЛИ ПРАВДА
End Function

Вы можете скачать Excel файл, содержащий этот пример, нажав здесь.

Статьи по теме:

  • VBA-Урок 14.1. Использование Excel функций

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

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

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

    Что такое пользовательская функция в Excel?

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

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

    • Комбинирование с макросами Visual Basic for Applications (VBA)

    • Определение значения исходных данных или массива значений

    • Применение в формулах условного форматирования

    • Хранение констант или списков данных

    Создание пользовательских функций в Excel требует использования языка программирования VBA. Хотя содержание этих функций варьируется, каждая из них начинается с оператора функции и завершается функцией завершения. Вы можете записать их в окне редактора Visual Basic Editor (VBE).

    Типы пользовательских функций в Excel

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

    Без аргументов

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

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

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

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

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

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

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

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

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

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

    Массив — это выборка ячеек со значениями, например, ячейки с B3 по D3 для горизонтального массива или ячейки с A2 по A5 для вертикального массива. Существует несколько функций, позволяющих использовать массивы для вычислений, например функции SUM и SUMIF, которые могут быть полезны при создании пользовательской функции. Например, вы можете использовать функцию SUMEVEN для определения суммы только четных значений данных в массиве с помощью вашей пользовательской функции.

    С массивом в качестве возвращаемого значения

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

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

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

    1. Откройте VBE, нажав Alt+F11 на ПК или FN+ALT+F11 на Mac.

    2. Найти Вставить.

    3. Выбрать Модуль.

    4. Тип Функция, затем укажите, какую функцию вы хотите использовать.

    5. Подтвердите автоматическое включение Excel Конечная функция.

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

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

    8. Вернитесь к своей электронной таблице.

    9. Перейдите к разделу Формулы вкладка.

    10. Нажмите на Функция вставки.

    11. Добавьте свою пользовательскую функцию в электронную таблицу.

    Пожалуйста, обратите внимание, что ни одна из компаний или продуктов, упомянутых в этой статье, не связана с Indeed.

    Создайте свои собственные функции Excel с помощью LAMBDA

    1. Столбец
    2. ТЕХНОЛОГИЯ Вопросы и ответы

    Келли Л. Уильямс, CPA, Ph.D.

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

    A. Я люблю все, что связано с Excel, но больше всего мне нравится функция LAMBDA. Это позволяет вам создавать свои собственные пользовательские функции. LAMBDA — это относительно новая функция для пользователей Excel 365 и Excel для Интернета.

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

    При создании пользовательских функций с помощью LAMBDA необходимо выполнить несколько шагов:

    1. Проверьте свою формулу.
    2. Создайте лямбду в электронной таблице.
    3. Определите LAMBDA в диспетчере имен .

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

    Обратите внимание, что последующее содержимое основано на Microsoft Excel 365 для ПК. Другие версии Excel могут работать иначе.

    Давайте рассмотрим пример. Мы создадим LAMBDA для расчета стоимости за единицу. Способ расчета состоит в том, чтобы разделить общую стоимость на количество. (См. скриншот таблицы с этой информацией ниже.)

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

    Теперь давайте выполним шаги, перечисленные выше.

    1. Проверьте формулу: в ячейке D2 введите =B2/C2. Поскольку это таблица, стоимость за единицу заполняется в каждой строке, и формула работает.

    2. Создайте LAMBDA в электронной таблице: В ячейке F2 (вы можете использовать любую ячейку за пределами вашей таблицы) я создаю LAMBDA. Я ввожу =LAMBDA(Общая стоимость,Количество,Общая стоимость/количество)(B2,C2). TotalCost — это первый параметр, Quantity — второй параметр, а TotalCost/Quantity — расчет. Затем мы определяем первый параметр как B2 (только для этого расчета, чтобы убедиться, что он работает), а второй параметр определяем как C2. Лямбда работает.

    3. Определите LAMBDA в диспетчере имен : Скопируйте только LAMBDA без ссылок на ячейки для этой конкретной формулы, чтобы создать пользовательскую функцию, которую можно использовать повторно, = LAMBDA (общая стоимость, количество, общая стоимость/количество). Перейдите на вкладку Formulas и выберите Define Name из группы Defined Names . Вставьте скопированную LAMBDA в поле Refers to: . Введите имя, которое вы хотите назвать своей новой пользовательской функцией, в поле 9. 0018 Имя : коробка. Я назвал свой CostPerUnit. Если вы хотите добавить какие-либо комментарии, вы можете добавить их в поле Комментарий : . Это особенно полезно, если ваши параметры нуждаются в дополнительных пояснениях.

    На приведенном ниже снимке экрана показано заполненное окно New Name для CostPerUnit.

    Теперь у нас есть новая встроенная функция в нашей книге Excel под названием CostPerUnit, которую мы можем вызывать в любое время. Мы можем использовать его так же, как и любую другую встроенную функцию Excel. Пользовательская функция будет доступна только в книге, в которой она была создана, а не во всех ваших книгах Excel. Давайте поместим наш курсор обратно в ячейку D2, первую строку, где мы хотим рассчитать нашу стоимость за единицу (см. снимок экрана ниже). Тип =CostPerUnit. Вы видите, что теперь она указана среди других встроенных функций. Выберите его и задайте параметры. Первый параметр — ячейка B2, а второй параметр — ячейка C2. Окончательная формула должна выглядеть так: =CostPerUnit(B2,C2) или =CostPerUnit([@[Total Cost]],[@Quantity]), если ваши данные содержатся в таблице. На снимке экрана ниже показаны результирующие значения созданной нами новой функции.

    Я создал книгу с примером использования LAMBDA и сопроводительным видео.

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

    Об авторе

    Келли Л. Уильямс, дипломированный бухгалтер, доктор философии, магистр делового администрирования, адъюнкт-профессор бухгалтерского учета в Колледже бизнеса Джонса в Государственном университете Среднего Теннесси.

    Задать вопрос

    У вас есть вопросы по технологии для этой колонки? Или, прочитав ответ, у вас есть лучшее решение? Отправьте их по адресу [email protected]. Мы сожалеем, что не можем индивидуально ответить на все заданные вопросы.

    Как написать определяемые пользователем функции (UDF) в Excel с помощью VBA

    Итог: Узнайте, как создать свою собственную определяемую пользователем функцию (UDF), если в Excel не существует нужной вам функции.

    Уровень мастерства: Средний

    Видеоруководство

    Смотрите на YouTube и подписывайтесь на наш канал

    Загрузите файл Excel

    Я прикрепил свои примеры UDF из видео в файле ниже. Вы можете просмотреть их, открыв редактор VB на вкладке «Разработчик».

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

    Excel — фантастический инструмент с ОЧЕНЬ большим количеством возможностей и функций. Но что делать, если нужная вам функция еще не встроена?

    Вы сами пишете. Когда вы создаете свою собственную пользовательскую функцию для Excel, она называется UDF, Пользовательская функция .

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

     =ЕСЛИ(МЕСЯЦ(E2)>=10,1,0)+ГОД(E2)

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

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

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

    Как и любую другую функцию, UDF можно вызвать из формулы ячейки . На этом изображении вы можете видеть, что я вызвал функцию с именем FiscalYear , чтобы рассчитать финансовый год на основе даты, найденной в ячейке E2. Если эта дата выпадет до октября, будет возвращено одно значение, а если она выпадет в октябре или позже, мы получим другое значение.

    Написание функции в VBA

    Чтобы написать функцию, перейдите в редактор VB и начните со слова Функция . За ним будет следовать имя функции, которое вы назначите. В моем примере я назвал его FiscalYear . Это может быть любое имя функции, которое еще не используется.

    Далее откройте скобки и укажите аргументы. В моем примере я использую dDate , а затем тип данных Variant , потому что мы можем передавать текст или другие данные, которые не имеют формата даты. Затем закройте скобки.

    Затем введите «как» и укажите тип возвращаемых данных. В этом случае я использую Long в качестве типа данных.

    В следующей строке мы установим имя нашей функции. Имя функции всегда должно быть установлено где-то внутри функции, обычно в конце. Для моего примера я установил такую ​​функцию: IIf (Month(dDate) >=10, 1, 0) + Year(dDate) . Этот код в основном сообщает Excel, что если месяц даты приходится на октябрь или позже, используйте следующий год в качестве финансового года, а если нет, оставьте его как год, найденный в дате.

    Таким образом, вся функция выглядит следующим образом:

    Вот код, который вы можете скопировать и вставить:

     
     Функция ФинансовыйГод(dDate As Variant) As Long 
     
       FiscalYear = IIf(Month(dDate) >= 10, 1, 0) + Year (dDate) 
     
     End Function

    Руководство по синтаксису

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

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

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

    Например, если мы хотим добавить немного обработки ошибок в наш код, чтобы мы получали слова «Дата не указана» каждый раз, когда функция передает данные, которые не имеют формат даты, код будет выглядеть примерно так: 3   If IsDate(dDate) Then
        vTemp = IIf(Month(dDate) >= 10, 1, 0) + Year(dDate)
      Else
        vTemp = «Дата не указана»
      End If
      
      FiscalYear2 = vTemp
      
    Конечная функция

    Несколько и необязательные параметры

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

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

    Я также добавил оператор If для обработки сценария, когда аргумент iStartMo не указан. В этом случае значение переменной будет равно нулю, если оно не указано, поскольку это значение по умолчанию для типа данных Long integer.

    Оператор If изменит значение на 13, если оно не указано, и будет рассчитан финансовый год, начинающийся в январе.

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

     Функция FiscalYear4(dDate As Variant, необязательный iStartMo As Long) As Long 
       If iStartMo = 0 Then 
         iStartMo = 13 
       End If 
          
       FiscalYear4 = IIf(Month(dD) съел) >= iStartMo, 1, 0) + Год(dDate) 
     
     End Function

    Private Functions

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

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

    Пошаговое выполнение кода

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

    После добавления точки останова (сочетание клавиш: F9 ) перейдите к любой ячейке в Excel, которая вызывает функцию, отредактируйте ячейку и нажмите Enter. Это запустит код VBA и сделает паузу в строке, где вы разместили точку останова.

    Затем вы можете выполнить код построчно с помощью клавиши F8 , чтобы оценить его.

    Плюсы и минусы UDF

    Плюсы

    1. Пользовательские функции в VBA помогают упростить сложные формулы . Гораздо проще вызвать функцию, чем писать, переписывать или копировать и вставлять целые формулы, особенно когда они часто используются.
    2. Пользовательские функции можно легко копировать в другие рабочие книги.
    3. Мы можем выполнять некоторую расширенную обработку ошибок в наших функциях, поскольку мы настраиваем их так, чтобы они были именно такими, как мы хотим.
    4. Пользовательские функции могут быть вызваны из других макросов , если это необходимо.

    Минусы

    1. Код функции должен быть сохранен в рабочей книге, в которой он используется в . Если вы хотите использовать код в другой книге, вам придется скопировать код в эту книгу и сохранить его как файл с поддержкой макросов. Если вы не знаете, как копировать или импортировать код в другие книги, вы можете обратиться к этому руководству: Как скопировать или импортировать код VBA в другую книгу.
    2. Несмотря на то, что пользовательскую функцию можно сохранить в личной книге макросов или в надстройке Excel, любые другие пользователи, пытающиеся запустить эти пользовательские функции, должны будут иметь тот же код в своей личной книге макросов или установить надстройку.

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