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

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

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

Пользовательские функции (как и макросы) записываются на языке программирования Visual Basic для приложений (VBA). Они отличаются от макросов двумя вещами. Во-первых, в них используются процедуры Function, а не Sub. Это значит, что они начинаются с оператора Function, а не Sub, и заканчиваются оператором End Function, а не End Sub. Во-вторых, они выполняют различные вычисления, а не действия. Некоторые операторы (например, предназначенные для выбора и форматирования диапазонов) исключаются из пользовательских функций. Из этой статьи вы узнаете, как создавать и применять пользовательские функции. Для создания функций и макросов используется редактор Visual Basic (VBE), который открывается в отдельном окне.

Предположим, что ваша компания предоставляет скидку в размере 10 % клиентам, заказавшим более 100 единиц товара. Ниже мы объясним, как создать функцию для расчета такой скидки.

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

Чтобы создать пользовательскую функцию DISCOUNT в этой книге, сделайте следующее:

1. Нажмите клавиши ALT+F11 (или FN+ALT+F11 на Mac), чтобы открыть редактор Visual Basic, а затем щелкните Insert (Вставка) > Module (Модуль). В правой части редактора Visual Basic появится окно нового модуля.

2. Скопируйте указанный ниже код и вставьте его в новый модуль.

   
   

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

Примечание: Чтобы код было более удобно читать, можно добавлять отступы строк с помощью клавиши TAB. Отступы необязательны и не влияют на выполнение кода. Если добавить отступ, редактор Visual Basic автоматически вставит его и для следующей строки. Чтобы сдвинуть строку на один знак табуляции влево, нажмите SHIFT+TAB.

Теперь вы готовы использовать новую функцию DISCOUNT. Закройте редактор Visual Basic, выделите ячейку G7 и введите следующий код:

=DISCOUNT(D7;E7)

Excel вычислит 10%-ю скидку для 200 единиц по цене 47,50 ₽ и вернет 950,00 ₽.

В первой строке кода VBA функция DISCOUNT(quantity, price) указывает, что функции DISCOUNT требуется два аргумента: quantity (количество) и price (цена). При вызове функции в ячейке листа необходимо указать эти два аргумента. В формуле =DISCOUNT(D7;E7) аргумент quantity имеет значение D7, а аргумент price — значение E7. Если скопировать формулу в ячейки G8:G13, вы получите указанные ниже результаты.

Рассмотрим, как Excel обрабатывает эту функцию. При нажатии клавиши ВВОД Excel ищет имя DISCOUNT в текущей книге и определяет, что это пользовательская функция в модуле VBA. Имена аргументов, заключенные в скобки (quantity и price), представляют собой заполнители для значений, на основе которых вычисляется скидка.

Оператор If в следующем блоке кода проверяет аргумент quantity и сравнивает количество проданных товаров со значением 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Если количество проданных товаров не меньше 100, VBA выполняет следующую инструкцию, которая перемножает значения quantity и price, а затем умножает результат на 0,1:

Discount = quantity * price * 0.1

Результат хранится в виде переменной Discount. Оператор VBA, который хранит значение в переменной, называется оператором назначения, так как он вычисляет выражение справа от знака равенства и назначает результат имени переменной слева от него. Так как переменная Discount называется так же, как и процедура функции, значение, хранящееся в переменной, возвращается в формулу листа, из которой была вызвана функция DISCOUNT.

Если значение quantity меньше 100, VBA выполняет следующий оператор:

Discount = 0

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

Discount = Application.Round(Discount, 2)

В VBA нет функции округления, но она есть в Excel. Чтобы использовать округление в этом операторе, необходимо указать VBA, что метод (функцию) Round следует искать в объекте Application (Excel). Для этого добавьте слово Application перед словом Round. Используйте этот синтаксис каждый раз, когда нужно получить доступ к функции Excel из модуля VBA.

Пользовательские функции должны начинаться с оператора Function и заканчиваться оператором End Function. Помимо названия функции, оператор Function обычно включает один или несколько аргументов. Однако вы можете создать функцию без аргументов. В Excel доступно несколько встроенных функций (например, СЛЧИС и ТДАТА), в которых нет аргументов.

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

Количество ключевых слов VBA, которые можно использовать в пользовательских функциях, меньше числа, используемого в макросах. Настраиваемые функции не могут выполнять другие задачи, кроме возврата значения в формулу на этом или в выражение, используемом в другом макросе или функции VBA. Например, пользовательские функции не могут изменять размер окна, редактировать формулу в ячейке, а также изменять шрифт, цвет или узор текста в ячейке. Если в процедуру функции включить такой код действия, функция возвращает #VALUE! ошибку «#ВЫЧИС!».

Единственное действие, которое может выполнять процедура функции (кроме вычислений), — это отображение диалогового окна. Чтобы получить значение от пользователя, выполняющего функцию, можно использовать в ней оператор InputBox. Кроме того, с помощью оператора MsgBox можно выводить сведения для пользователей. Вы также можете использовать настраиваемые диалоговые окна (UserForms), но эта тема выходит за рамки данной статьи.

Даже простые макросы и пользовательские функции может быть сложно понять. Чтобы сделать эту задачу проще, добавьте комментарии с пояснениями. Для этого нужно ввести перед текстом апостроф. Например, ниже показана функция DISCOUNT с комментариями. Благодаря подобным комментариями и вам, и другим будет впоследствии проще работать с кодом VBA. Так, код будет легче понять, если потребуется внести в него изменения.

Апостроф указывает приложению Excel на то, что следует игнорировать всю строку справа от него, поэтому вы можете добавлять комментарии в отдельных строках или в правой части строк, содержащих код VBA. Советуем начинать длинный блок кода с комментария, в котором объясняется его назначение, а затем использовать встроенные комментарии для документирования отдельных операторов.

Кроме того, рекомендуется присваивать макросам и пользовательским функциям описательные имена. Например, присвойте макросу название MonthLabels вместо Labels, чтобы более точно указать его назначение. Описательные имена макросов и пользовательских функций особенно полезны, если существует множество процедур с похожим назначением.

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

Для использования настраиваемой функции должна быть открыта книга, содержащая модуль, в котором она была создана. Если книга не открыта, вы получите #NAME? при попытке использования функции. Если вы ссылались на функцию в другой книге, ее имя должно предшествовать названию книги, в которой она находится. Например, при создании функции DISCOUNT в книге Personal.xlsb и вызове ее из другой книги необходимо ввести =personal.xlsb!discount(),а не просто =discount().

Чтобы вставить пользовательскую функцию быстрее (и избежать ошибок), ее можно выбрать в диалоговом окне «Вставка функции». Пользовательские функции доступны в категории «Определенные пользователем»:

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

1. Создав нужные функции, выберите Файл > Сохранить как.

   В Excel 2007 нажмите кнопку Microsoft Office, а затем щелкните Сохранить как.

2. В диалоговом окне Сохранить как откройте раскрывающийся список Тип файла и выберите значение Надстройка Excel. Сохраните книгу с запоминающимся именем, таким как MyFunctions, в папке AddIns. Она будет автоматически предложена в диалоговом окне Сохранить как, поэтому вам потребуется только принять расположение, используемое по умолчанию.

3. Сохранив книгу, выберите Файл > Параметры Excel.

   В Excel 2007 нажмите кнопку Microsoft Office и щелкните Параметры Excel.

4. В диалоговом окне Параметры Excel выберите категорию Надстройки.

5. В раскрывающемся списке Управление выберите Надстройки Excel. Затем нажмите кнопку Перейти.

6. В диалоговом окне Надстройки установите флажок рядом с именем книги, как показано ниже.

1. Создав нужные функции, выберите Файл > Сохранить как.

2. В диалоговом окне Сохранить как откройте раскрывающийся список Тип файла и выберите значение Надстройка Excel. Сохраните книгу с запоминающимся именем, таким как MyFunctions.

3. Сохранив книгу, выберите Сервис > Надстройки Excel.

4. В диалоговом окне Надстройки нажмите кнопку «Обзор», найдите свою надстройку, нажмите кнопку Открыть, а затем установите флажок рядом с надстройкой в поле Доступные надстройки.

После этого пользовательские функции будут доступны при каждом запуске Excel. Если вы хотите добавить его в библиотеку функций, вернимся в Visual Basic редактора. Если вы заглянуть в Visual Basic редактора Project проводника под заголовком VBAProject, вы увидите модуль с именем файла надстройки. У надстройки будет расширение XLAM.

Дважды щелкните модуль в Project Explorer, чтобы вывести код функций. Чтобы добавить новую функцию, установите точку вставки после оператора End Function, который завершает последнюю функцию в окне кода, и начните ввод. Вы можете создать любое количество функций, и они будут всегда доступны в категории «Определенные пользователем» диалогового окна Вставка функции.

Эта статья основана на главе книги Microsoft Office Excel 2007 Inside Out, написанной Марком Доджем (Mark Dodge) и Крейгом Стинсоном (Craig Stinson). В нее были добавлены сведения, относящиеся к более поздним версиям Excel.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Использование вложенных функций в формуле

Использование функции в качестве одного из аргументов в формуле, использующей функцию, называется вложенным, и мы будем называть ее вложенной функцией. Например, при вложении функций СНВП и СУММ в аргументы функции ЕСЛИ следующая формула суммирует набор чисел (G2:G5), только если среднее значение другого набора чисел (F2:F5) больше 50. В противном случае она возвращает значение 0.

Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

В формулу можно вложить до 64 уровней функций.

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

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

   Знак равенства (=) будет вставлен автоматически.

3. В поле Категория выберите пункт Все.

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

   Если вы не знаете, какую функцию использовать, можно ввести вопрос, описывающий необходимые действия, в поле Поиск функции (например, при вводе «добавить числа» возвращается функция СУММ).

4. Чтобы ввести другую функцию в качестве аргумента, введите функцию в поле этого аргумента.

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

   Если щелкнуть элемент ЕСЛИ, в диалоговом окне Аргументы функции отображаются аргументы для функции ЕСЛИ. Чтобы вложить другую функцию, можно ввести ее в поле аргумента. Например, можно ввести СУММ(G2:G5) в поле Значение_если_истина функции ЕСЛИ.

5. org/ListItem»>

   Введите дополнительные аргументы, необходимые для завершения формулы.

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

   Совет: Для получения дополнительных сведений о функции и ее аргументах щелкните ссылку Справка по этой функции.

6. После ввода всех аргументов формулы нажмите кнопку ОК.

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

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

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

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

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

5. Введите дополнительные аргументы, необходимые для завершения формулы.

6. Завершив ввод аргументов формулы, нажмите ввод.

Примеры

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

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Советы: 

• Дополнительные сведения о формулах см. в общих сведениях о формулах.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Видео: вложенные функции ЕСЛИ

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Дополнительно. ..Меньше

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

Пользовательские функции, такие как макросы, используют язык программирования Visual Basic для приложений (VBA) . Они отличаются от макросов двумя важными способами. Во-первых, они используют процедуры Function вместо процедур Sub . То есть они начинаются с оператора Function вместо оператора Sub и заканчиваются End Function вместо End Sub . Во-вторых, они выполняют расчеты, а не действия. Определенные виды операторов, например операторы, которые выбирают и форматируют диапазоны, исключаются из пользовательских функций. В этой статье вы узнаете, как создавать и использовать пользовательские функции. Для создания функций и макросов вы работаете с Редактор Visual Basic (VBE) , который открывается в новом окне отдельно от Excel.

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

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

Чтобы создать пользовательскую функцию СКИДКИ в этой книге, выполните следующие действия:

1. Нажмите Alt+F11 , чтобы открыть редактор Visual Basic (на Mac нажмите FN+ALT+F11 ), а затем нажмите Вставить > Модуль . Окно нового модуля появится в правой части редактора Visual Basic.

2. Скопируйте и вставьте следующий код в новый модуль.

   Функция СКИДКА(количество, цена)
      Если количество >=100 Тогда
        СКИДКА = количество * цена * 0,1
      Еще
        СКИДКА = 0
      Конец, если
    
    СКИДКА = Заявка.Раунд(Скидка, 2)
   Конечная функция
    

Примечание. Чтобы сделать код более читаемым, вы можете использовать клавишу Tab для отступа строк. Отступы нужны только вам и не являются обязательными, так как код будет работать как с ними, так и без них. После ввода строки с отступом редактор Visual Basic предполагает, что следующая строка будет иметь такой же отступ. Чтобы сдвинуться (то есть влево) на один символ табуляции, нажмите Shift+Tab .

Теперь вы готовы использовать новую функцию СКИДКИ. Закройте редактор Visual Basic, выберите ячейку G7 и введите следующее:

=СКИДКА(D7,E7)

Excel вычисляет 10-процентную скидку на 200 единиц по цене 47,50 долларов США за единицу и возвращает 950,00 долларов США.

В первой строке вашего кода VBA, функция СКИДКА (количество, цена), вы указали, что функция СКИДКА требует двух аргументов: количество и цена . Когда вы вызываете функцию в ячейке рабочего листа, вы должны включить эти два аргумента. В формуле =СКИДКА(D7,E7) D7 — это количество аргумент, а E7 — цена аргумент. Теперь вы можете скопировать формулу СКИДКИ в G8:G13, чтобы получить результаты, показанные ниже.

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

Оператор If в следующем блоке кода проверяет аргумент количество и определяет, больше или равно ли количество проданных товаров 100:

Если количество >= 100 Тогда
 СКИДКА = количество * цена * 0,1
Еще
 СКИДКА = 0
Конец, если
 

Если количество проданных товаров больше или равно 100, VBA выполняет следующую инструкцию, которая умножает значение количество на цена значение, а затем результат умножается на 0,1:

Скидка = количество * цена * 0,1

Результат сохраняется как переменная Скидка . Оператор VBA, который сохраняет значение в переменной, называется оператором присваивания , потому что он оценивает выражение справа от знака равенства и присваивает результат имени переменной слева. Поскольку переменная Скидка имеет то же имя, что и процедура функции, значение, хранящееся в переменной, возвращается в формулу листа, вызвавшую функцию СКИДКА.

Если количество меньше 100, VBA выполняет следующую инструкцию:

Скидка = 0

Наконец, следующий оператор округляет значение, присвоенное переменной Discount , до двух знаков после запятой:

Скидка = Заявка.Раунд(Скидка, 2)

В VBA нет функции ОКРУГЛ, но в Excel она есть. Поэтому, чтобы использовать ROUND в этом операторе, вы указываете VBA искать метод (функцию) Round в объекте Application (Excel). Вы делаете это, добавляя слово Приложение перед словом Раунд. Используйте этот синтаксис всякий раз, когда вам нужно получить доступ к функции Excel из модуля VBA.

Пользовательская функция должна начинаться с оператора Function и заканчиваться оператором End Function. В дополнение к имени функции оператор Function обычно указывает один или несколько аргументов. Однако вы можете создать функцию без аргументов. В Excel есть несколько встроенных функций — например, СЛУЧАЙ и СЕЙЧАС, — которые не используют аргументы.

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

Количество ключевых слов VBA, которые вы можете использовать в пользовательских функциях, меньше, чем количество, которое вы можете использовать в макросах. Пользовательским функциям не разрешено делать ничего, кроме возврата значения в формулу на листе или в выражение, используемое в другом макросе или функции VBA. Например, пользовательские функции не могут изменять размер окон, редактировать формулу в ячейке или изменять параметры шрифта, цвета или узора для текста в ячейке. Если вы включаете код «действия» такого типа в процедуру функции, функция возвращает ошибку #ЗНАЧ! ошибка.

Единственное действие, которое может выполнять функциональная процедура (помимо выполнения вычислений), — это отображение диалогового окна. Вы можете использовать оператор InputBox в пользовательской функции как средство получения ввода от пользователя, выполняющего функцию. Вы можете использовать оператор MsgBox как средство передачи информации пользователю. Вы также можете использовать настраиваемые диалоговые окна или UserForms , но это выходит за рамки данного введения.

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

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

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

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

Чтобы использовать пользовательскую функцию, должна быть открыта рабочая книга, содержащая модуль, в котором вы создали эту функцию. Если эта рабочая книга не открыта, вы получите #NAME? ошибка при попытке использовать функцию. Если вы ссылаетесь на функцию в другой рабочей книге, перед именем функции необходимо указать имя рабочей книги, в которой находится функция. Например, если вы создаете функцию с именем СКИДКА в книге с именем Personal.xlsb и вызываете эту функцию из другой книги, вы должны ввести =personal.xlsb!discount() , а не просто =discount() .

Вы можете сэкономить несколько нажатий клавиш (и возможных ошибок при наборе текста), выбрав пользовательские функции в диалоговом окне «Вставить функцию». Ваши пользовательские функции отображаются в категории «Определено пользователем»:

.

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

1. После создания необходимых функций нажмите Файл > Сохранить как .

   В Excel 2007 нажмите кнопку Microsoft Office и выберите Сохранить как

2. В диалоговом окне Сохранить как откройте раскрывающийся список Сохранить как тип и выберите Надстройка Excel . Сохраните книгу под узнаваемым именем, например MyFunctions , в папке AddIns . В диалоговом окне «Сохранить как » будет предложена эта папка, поэтому все, что вам нужно сделать, это принять расположение по умолчанию.

3. После сохранения книги щелкните Файл > Параметры Excel .

   В Excel 2007 щелкните значок Кнопка Microsoft Office и щелкните Параметры Excel .

4. В диалоговом окне Параметры Excel выберите категорию Надстройки .

5. В раскрывающемся списке Управление выберите Надстройки Excel . Затем нажмите кнопку Перейти .

6. В диалоговом окне Add-Ins установите флажок рядом с именем, которое вы использовали для сохранения книги, как показано ниже.

1. После создания необходимых функций нажмите Файл > Сохранить как .

2. В диалоговом окне Сохранить как откройте раскрывающийся список Сохранить как тип и выберите Надстройка Excel . Сохраните книгу под узнаваемым именем, например MyFunctions .

3. После сохранения книги щелкните Инструменты > Надстройки Excel .

4. В диалоговом окне Надстройки нажмите кнопку Обзор, чтобы найти надстройку, нажмите Открыть , затем установите флажок рядом с надстройкой в ​​поле Доступные надстройки .

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

Двойной щелчок по этому модулю в Project Explorer приводит к тому, что редактор Visual Basic отображает код вашей функции. Чтобы добавить новую функцию, поместите точку ввода после оператора End Function, завершающего последнюю функцию в окне кода, и начните вводить текст. Таким образом вы можете создать столько функций, сколько вам нужно, и они всегда будут доступны в категории «Определяемые пользователем» в диалоговом окне «Вставить функцию».

Этот контент был первоначально создан Марком Доджем и Крейгом Стинсоном как часть их книги Microsoft Office Excel 2007 наизнанку . С тех пор он был обновлен, чтобы применяться и к более новым версиям Excel.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

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

Тепинг Крокер

Категории: Excel®, Функции
Теги: пользовательская функция, определяемая пользователем функция

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

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

Подготовьте рабочую тетрадь

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

Выполните описанные ниже действия в новой чистой книге с поддержкой макросов или загрузите файл 06-Custom Functions. xlsm , чтобы просмотреть завершенную пользовательскую функцию.

Определите свои переменные

Функция создает расчет на основе переменных, введенных во время использования функции. Чтобы создать пользовательскую функцию, вам сначала нужно определить аргументы, необходимые для вашей функции. Мы собираемся создать функцию для быстрого вычисления площади треугольника: площадь = основание, умноженное на высоту, деленное на 2, или A=BH/2. Нашими аргументами будут основание и высота.

Открыть редактор Visual Basic

Нажмите Alt + F11 или нажмите кнопку Visual Basic на вкладке «Разработчик».

Выберите Вставка > Модуль в редакторе VBA:

Типовой код

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

.

Функция имя_функции (аргументы) возвращаемый тип
Myfunctionname = расчет
Завершить функцию

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

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

Общая функция TRIAREA(число1, число2)
TRIAREA = (число1 * число2) / 2
Завершить функцию

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

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

1. Щелкните Вставить функцию на вкладке Формулы .
2. В диалоговом окне Вставить функцию выберите User Defined из раскрывающегося списка «выберите категорию». Вы должны увидеть имя, которое вы дали своей функции. В этом примере мы видим TRIAREA.
3. Выберите функцию, затем нажмите ОК.
4. Заполните диалоговое окно Аргументы функции и нажмите OK.

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