Как записать функцию в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Прежде чем разбирать саму функцию отметим 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.

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

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

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

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

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

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

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

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

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

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

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:

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

Discount = quantity * price * 0.1

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

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

Наконец, следующий оператор округляет значение, назначенное переменной 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. Например, пользовательские функции не могут изменять размер окон, редактировать формулы в ячейках, а также изменять шрифт, цвет и параметры узора для текста в ячейке. Если вы включаете код "Action" этого типа в процедуру Function, функция возвращает #VALUE! ошибка #ЧИСЛО!.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

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

Вам понадобится

Инструкция

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

2. Исполняя расчеты, программа использует формулы, но рассматривать Exсel будет только те, которые начинаются со знака «=». Скажем, напишите в ячейке =2+2 и нажмите на клавиатуре Enter. Итог выражения появится в этой же ячейке. Набранное выражение, при этом, никуда не исчезает – его дозволено посмотреть, щелкнув двукратно левой клавишей мыши по этой же ячейке. Если позже двойного щелчка на клавиатуре нажать F2, выражение появится на панели инструментов в строке формул, там его дозволено изменить. Если в формуле применяется текст, он должен быть заключен в двойные кавычки, скажем: «=”мама”».

3. В Excel не неукоснительно набирать повторные выражения весь раз снова. Примитивно скопируйте предыдущую запись, выделив необходимые ячейки. Всей скопированной записи программа задаст свой цвет, а формула будет выглядеть приблизительно так: = A1+D1. Дабы увидеть выражение двукратно щелкните по выделенной ячейке левой клавишей мыши и нажмите Enter. Обнаружить формулу дозволено, ориентируясь по цвету ячейки – разноцветные ссылки указывают на номера строк и букв столбцов соответствующего выражения.

4. Выражения в Excel могут быть арифметическими либо логическими. Запишите в строке формул: =СТЕПЕНЬ(3;10) и нажмите Enter, получится число 59049. Для решения логических выражений применяются особые коды либо блоки. Записываться они обязаны сурово по правилам. Скажем, записанное в строке формул логическое выражение будет выглядеть приблизительно так:=MИН(СУММ(A22;СТЕПЕНЬ(С10;B22));ПРОИЗВЕД(СУММ(А22;В22);СТЕПЕНЬ(СУММ(А22;С10);1/В22)) Трудные формулы дозволено писать с подмогой мастера функций.

5. Запустите его, нажав на кнопку . расположенную в начале строки функций. В появившемся окне выбирайте надобную формулу и жмите Ок. Дабы упростить ее поиск, пользуйтесь фильтром категорий. Предпочтя функцию , вы попадете в следующее окно. Вставьте в первую еще одну либо нажмите «Отмена». Вложите одну формулу в иную с подмогой кнопки «?»на панели задач. После этого, из выпадающего списка, выберите нужную. Не забывайте переставлять текстовый курсор по ячейкам с данными.

6. Excel распознает простейшие математические формулы: МИН, МАКС, СРЗНАЧ, СТЕПЕНЬ, СУММ, СЧЁТ, ПИ, ПРОИЗВЕД, СУММЕСЛИ, СЧЁТЕСЛИ. Если написать: СУММЕСЛИ(«?5»А1:А5), будет учитываться сумма ячеек, значение которых огромнее 5. Исполнить несколько условий единовременно дозволено при помощи функции «И». Используйте значение «ЛИБО» при проверке одного из множества условий. Если таблица с числами дюже огромная, пользуйтесь функциями ВПР- вертикальное первое равенство, ГПР – горизонтальное первое равенство. Применяя их, вы механически обнаружите необходимый диапазон ячеек и избавите себя от ручного копирования данных.

7. Облегчить работу с функциями дозволено с подмогой кнопки «?». Посчитайте сумму нескольких чисел, обнаружьте их средне арифметическое значение, число используемых в списке чисел, наибольшее и наименьшее число в таблице.