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

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

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

Пользовательские функции, такие как макросы, используют язык программирования Visual Basic для приложений (VBA) . Они отличаются от макросов двумя существенными способами. Во-первых, они используют процедуры Function вместо процедур Sub . То есть они начинаются с оператора Function вместо оператора Sub и заканчиваются End Function вместо Конец подписки . Во-вторых, они выполняют вычисления, а не предпринимают действия. Определенные виды операторов, например операторы выбора и форматирования диапазонов, исключаются из пользовательских функций. В этой статье вы узнаете, как создавать и использовать собственные функции. Для создания функций и макросов вы работаете с редактором 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 и введите следующее:

= DISCOUNT (D7, E7)

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

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

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

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

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

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

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

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

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

Discount = 0

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

Discount = Application.Round (Discount, 2)

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

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

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

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

Использование ключевых слов VBA в пользовательских функциях

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

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

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

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

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

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

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

Делаем ваши пользовательские функции доступными где угодно

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

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

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

Windows macOS
  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 под заголовком VBAProject, вы увидите модуль, названный в честь вашего файла надстройки. Ваша надстройка будет иметь расширение .xlam.

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

Об авторах

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

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

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

Оцените статью
Botgadget.ru
Добавить комментарий