Как добавить функцию в эксель

Все, кто работает с Excel, сталкивались со встроенными функциями, например ВПР , ЕСЛИ и т.д. Из этих функций в Excel строятся различные формулы позволяющие, посчитать, обработать или принять решение. Эти функции находятся в мастере функций и разделены по группам. Мастер же позволяет упростить ввод аргументов функции. Набор функций в Excel достаточно обширный и для большинства задач можно найти нужную функцию или составить формулу из нескольких вложенных функций. Но что, если для решения задачи требуются особые вычисления!? В этом нам поможет встроенный язык VB, который позволяет написать собственные процедуры и функции обработки данных, при этом функции могут быть добавлены в мастер функций и использоваться как обычные встроенные функции (пользовательские функции).Итак, что такое функция в Excel и VBA?Функция — это набор команд, которые обрабатывают данные заданным образом и возвращают результат. Функция имеет входные данные, используемые при расчетах (аргументы функции). По сути, функция это та же процедура, с которыми мы уже сталкивались неоднократно при написании макросов, только функция еще и возвращает результат. Функции могут использоваться в следующих ситуациях:

Область видимости функций аналогична области действия переменных т.е. Public, Private, Static. Описание функции начинается с ключевого слова Function и заканчивается End Function.Требования к именам функций такие же, как и к именам переменных в VBA .Рассмотрим простейший пример функции:Function Test (a as integer, b as integer) as integer Test = a*bend functionМы создали функцию Test, которая имеет два аргумента a и b целого типа. Функция Test возвращает только целый результат. Наша функция находит произведение a и b. Обратите внимание, что результат мы присваиваем в Test т.е. это завершающая стадия работы функции. Если этого не сделать, то функция не вернет никакого результата. end function — указывает, что вычисления функции закончились.Эту функцию мы можем использовать в других процедурах и функциях vba как вспомогательную или в формулах листа Excel. Хочу обратить ваше внимание, если Вы пишите функции для использования в формулах, то описание функции должно располагаться в модуле (Module) и начинаться только со слова Function. Описание типа Private сделает функцию невидимой в мастере. Поэтому, если использование функции не планируется в формулах, то Вы можете ограничить ее область видимости: Private Function Test (a as integer, b as integer) as integer …..Остановимся теперь более подробно на создании пользовательских функций, которые будут использоваться в формулах для вычислений.Мы уже создали функцию Test. Добавьте ее в созданный Module.

Переходим теперь на лист и вводим в ячейки A1 и B1 целые числа, выделяем ячейку C1 и жмем вставка функции. В мастере функций необходимо выбрать категорию «Определенные пользователем» и в списке функций найдите «Test»:

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

По умолчанию функции находятся в категории «Определенные пользователем». Каким образом можно пользовательской функции переназначить категорию!?С помощью команды: Application.MacroOptions Macro:=»Test», Category:=10где Macro:=»Test» это название нашего макроса, Category:=10 номер категории, куда разместить функцию. Всего категорий в Excel — 15. Ниже приведен список:

Читайте также:  Электромагнитный клапан печки газель

Sub InstallFunc() Application.MacroOptions Macro:=»Test», Category:=10End Sub

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

Собственно описание находится в Description. Выполните процедуру InstallFunc. Готово. Смотрим результат:

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

Прикрепленный файл: Видеопример создания функции в Excel.zip

Что такое функция в VBA?

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

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

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

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

В чем отличие процедуры (Sub) от функции (Function)?

Основное отличие в том, что процедура (sub) используется для выполнения набора команд, и не призвана в отличие от функции (function) возвращать значение (или массив значений).

Для демонстрации приведем пример. Например есть ряд чисел от 1 до 100 и необходимо отделить четные от нечетных.

С помощью процедуры (sub) вы можете, к примеру, пройтись по ячейкам и выделить нечетные с помощью заливки. А функцию можно использовать в соседнем столбце и она вернет ИСТИНА или ЛОЖЬ в зависимости от того четное значение или нет. Т.е. вы не сможете изменить цвет заливки с помощью функции на листе.

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

Давайте создадим простую пользовательскую функцию на VBA и посмотрим как там все работает.

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

Чтобы у вас все заработало, необходимо вставить данный код в модуль книги. Если вы не знаете как это сделать, то начните со статьи Как записать макрос в Excel.

Теперь посмотрим как функция работает, попробуем использовать ее на листе:

Читайте также:  1С распровести документ программно

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

  • Она стала доступна, как и любая другая встроенная функция (как создать скрытую функцию, расскажем далее).
  • Когда вы ввели знак "=" и начинаете вводить имя функции, то Excel выводит все совпадения и показывает не только встроенные функции, но и пользовательские.

Разбираем функцию пошагово

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

Слово Function говорит о начале функции, далее идет ее название, в нашем случае Цифры.

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

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

Последняя часть первой строки As Long задает тип данных, которая возвращает функция. В нашем случае функция будет возвращать целые значения. Это также не обязательно.

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

Переменную i мы буем использовать для перебора символов. А переменную result для хранения промежуточного результата функции.

Задача функции — пройти по всем символам переменной Текст и сохранить только те, что являются цифрами. Поэтому начнем цикл с 1 и до последнего символа.

Len — функция, которая определяет количество символов.

Основная строка функции — это проверка является ли очередной символ текста цифрой и если да — то сохранение его в переменной result

Для этого нам потребуется функция IsNumeric — она возвращает True если текст — число и False в противном случае.

Функция Mid берет из аргумента Текст i-ый символ (значение 1, указывает что функция Mid берет только 1 символ)/

Функция Next — закрывает цикл For тут все понятно.

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

Последняя строка кода — End Function. Это обязательная строка кода, которая сообщает VBA, что код функции заканчивается здесь.

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

Читайте также:  Installpack downloads что это

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

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

  • СУММ – эта функция суммирует значения всех аргументов.
  • СРЗНАЧ – определяет среднее арифметическое величин, содержащихся в аргументах. Функция вычисляет сумму значений ячеек, а затем делит результат на их количество.
  • СЧЁТ – подсчитывает количество чисел в списке аргументов. Функция полезна для быстрого подсчета числа элементов в диапазоне.
  • МАКС – определяет максимальное значение из списка аргументов.
  • МИН – определяет минимальное значение из списка аргументов.

Как вставить функцию в Excel

В следующем примере мы создадим простую формулу для расчета средней цены за единицу заказанных товаров, используя функцию СРЗНАЧ.

  1. Выделите ячейку, в которую необходимо вставить формулу. В нашем примере мы выделим ячейку C11.
  2. Введите знак равенства (=) и нужное имя функции. Вы также можете вставить функцию из списка, который появится при вводе ее названия (автозавершение в Excel). В нашем случае мы введем =СРЗНАЧ.
  3. Введите диапазон ячеек в качестве аргумента в круглых скобках. В нашем примере мы введем (C3:C10). Эта формула суммирует значения в диапазоне С3:С10, а затем делит результат на количество ячеек в этом диапазоне, тем самым определяя среднее значение.
  4. Нажмите Enter на клавиатуре. Функция будет вычислена, и Вы увидите результат. В данном примере средняя цена за единицу заказанных товаров составила $15,93.

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

Вставка функции с помощью команды Автосумма

Команда Автосумма позволяет автоматически вставлять наиболее распространенные функции в формулы Excel, включая СУММ, СРЗНАЧ, СЧЁТ, МИН и МАКС. В следующем примере мы создадим формулу для расчета полной стоимости недавно заказанных товаров, используя функцию СУММ.

  1. Выделите ячейку, в которую необходимо вставить формулу. В нашем примере мы выделим ячейку D12.
  2. В группе команд Редактирование на вкладке Главная найдите и нажмите стрелку рядом с командой Автосумма, а затем выберите нужную функцию в раскрывающемся меню. В нашем случае мы выберем Сумма.
  3. Выбранная функция появится в ячейке. Команда Автосумма автоматически определяет диапазон ячеек для аргумента. В данном примере диапазон D3:D11 был выбран автоматически, а значения просуммированы, чтобы вычислить полную стоимость. Вы также можете вручную ввести нужный диапазон.
  4. Нажмите Enter на клавиатуре. Функция будет вычислена, и Вы увидите результат. В нашем примере сумма значений диапазона D3:D11 составляет $606,05.

В Microsoft Excel команду Автосумма можно также найти на вкладке Формулы.

Ссылка на основную публикацию
Adblock
detector