Примеры как создать пользовательскую функцию в Excel
В Excel содержится множество встроенных функций, которые могут быть использованы для инженерных, статистических, финансовых, аналитических и прочих расчетов. Иногда условия поставленных задач требуют более гибкого инструмента для поиска решения, тогда на помощь приходят макросы и пользовательские функции.
Пример создания своей пользовательской функции в Excel
Подобно макросам, пользовательские функции могут быть созданы с использованием языка VBA. Для реализации данной задачи необходимо выполнить следующие действия:
- Открыть редактор языка VBA с помощью комбинации клавиш ALT+F11.
- В открывшемся окне выбрать пункт Insert и подпункт Module, как показано на рисунке:
- Новый модуль будет создан автоматически, при этом в основной части окна редактора появится окно для ввода кода:
- При необходимости можно изменить название модуля.
- В отличие от макросов, код которых должен находиться между операторами Sub и End Sub, пользовательские функции обозначают операторами Function и End Function соответственно. В состав пользовательской функции входят название (произвольное имя, отражающее ее суть), список параметров (аргументов) с объявлением их типов, если они требуются (некоторые могут не принимать аргументов), тип возвращаемого значения, тело функции (код, отражающий логику ее работы), а также оператор End Function. Пример простой пользовательской функции, возвращающей названия дня недели в зависимости от указанного номера, представлен на рисунке ниже:
- После ввода представленного выше кода необходимо нажать комбинацию клавиш Ctrl+S или специальный значок в левом верхнем углу редактора кода для сохранения.
- Чтобы воспользоваться созданной функцией, необходимо вернуться к табличному редактору Excel, установить курсор в любую ячейку и ввести название пользовательской функции после символа «=»:
Встроенные функции Excel содержат пояснения как возвращаемого результата, так и аргументов, которые они принимают. Это можно увидеть на примере любой функции нажав комбинацию горячих клавиш SHIFT+F3. Но наша функция пока еще не имеет формы.
Чтобы задокументировать пользовательскую функцию, необходимо выполнить следующие действия:
- Создайте новый макрос (нажмите комбинацию клавиш Alt+F8), в появившемся окне введите произвольное название нового макроса, нажмите кнопку Создать:
- В результате будет создан новый модуль с заготовкой, ограниченной операторами Sub и End Sub.
- Введите код, как показано на рисунке ниже, указав требуемое количество переменных (в зависимости от числа аргументов пользовательской функции):
- В качестве «Macro» должна быть передана текстовая строка с названием пользовательской функции, в качестве «Description» — переменная типа String с текстом описания возвращаемого значения, в качестве «ArgumentDescriptions» — массив переменных типа String с текстами описаний аргументов пользовательской функции.
- Для создания описания пользовательской функции достаточно один раз выполнить созданный выше модуль. Теперь при вызове пользовательской функции (или SHIFT+F3) отображается описание возвращаемого результата и переменной:
Описания функций создавать не обязательно. Они необходимы в случаях, если пользовательские функции будут часто использоваться другими пользователями.
Примеры использования пользовательских функций, которых нет в Excel
Пример 1. Рассчитать сумму отпускных для каждого работника, проработавшего на предприятии не менее 12 месяцев, на основе суммы общей заработной платы и числа выходных дней в году.
Вид исходной таблицы данных:
Каждому работнику полагается 24 выходных дня с выплатой S=N*24/(365-n), где:
- N – суммарная зарплата за год;
- n – число праздничных дней в году.
Создадим пользовательскую функцию для расчета на основе данной формулы:
Сохраним функцию и выполним расчет с ее использованием:
Растянем формулу на остальные ячейки с целью получения результатов для остальных работников:
Калькулятор расчета калорий в Excel
Пример 2. Рассчитать суточную норму калорий для участников программы похудения, среди которых есть как женщины, так и мужчины определенного возраста с известными показателями роста и веса.
Вид исходной таблицы данных:
Для расчета используем формулу Миффлина — Сан Жеора, которую запишем в коде пользовательской функции с учетом пола участника. Код примера:
Проверки корректности введенных данных упущены для упрощения кода. Если пол не определен, функция вернет результат 0 (нуль).
Пример расчета для первого участника:
В результате использования автозаполнения получим следующие результаты:
Пользовательская функция для решения квадратных уравнений в Excel
Пример 3. Создать функцию, которая возвращает результаты решения квадратных уравнений для указанных в ячейках коэффициентах a, b и c уравнения типа ax2+bx+c=0.
Вид исходной таблицы:
Для решения создадим следующую пользовательскую функцию:
Найдем корни первого уравнения:
Выполним расчеты для остальных уравнений. Полученные результаты:
Мы создали свою пользовательскую функцию для расчета квадратных уравнений, которой раньше не было в Excel по умолчанию.
Создание макросов и пользовательских функций на VBA
Всем нам приходится — кому реже, кому чаще — повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую "рутинную составляющую" — одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.
Макрос — это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.
В принципе, существует великое множество языков программирования (Pascal, Fortran, C++, C#, Java, ASP, PHP. ), но для всех программ пакета Microsoft Office стандартом является именно встроенный язык VBA. Команды этого языка понимает любое офисное приложение, будь то Excel, Word, Outlook или Access.
Способ 1. Создание макросов в редакторе Visual Basic
Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно — редактор программ на VBA, встроенный в Microsoft Excel.
- В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис — Макрос — Редактор Visual Basic(Toos — Macro — Visual Basic Editor).
- В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)
:
К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:
Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:
-
Обычные модули — используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert — Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:
Обычный макрос, введенный в стандартный модуль выглядит примерно так:
Давайте разберем приведенный выше в качестве примера макрос Zamena:
- Любой макрос должен начинаться с оператора Sub, за которым идет имя макроса и список аргументов (входных значений) в скобках. Если аргументов нет, то скобки надо оставить пустыми.
- Любой макрос должен заканчиваться оператором End Sub.
- Все, что находится между Sub и End Sub — тело макроса, т.е. команды, которые будут выполняться при запуске макроса. В данном случае макрос выделяет ячейку заливает выделенных диапазон (Selection) желтым цветом (код = 6) и затем проходит в цикле по всем ячейкам, заменяя формулы на значения. В конце выводится окно сообщения (MsgBox).
С ходу ясно, что вот так сразу, без предварительной подготовки и опыта в программировании вообще и на VBA в частности, сложновато будет сообразить какие именно команды и как надо вводить, чтобы макрос автоматически выполнял все действия, которые, например, Вы делаете для создания еженедельного отчета для руководства компании. Поэтому мы переходим ко второму способу создания макросов, а именно.
Способ 2. Запись макросов макрорекордером
Макрорекордер — это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает получившуюся команду в программный модуль. Если мы включим макрорекордер на запись, а затем начнем создавать свой еженедельный отчет, то макрорекордер начнет записывать команды вслед за каждым нашим действием и, в итоге, мы получим макрос создающий отчет как если бы он был написан программистом. Такой способ создания макросов не требует знаний пользователя о программировании и VBA и позволяет пользоваться макросами как неким аналогом видеозаписи: включил запись, выполнил операци, перемотал пленку и запустил выполнение тех же действий еще раз. Естественно у такого способа есть свои плюсы и минусы:
- Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу — запись останавливается.
- Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
- Если во время записи макроса макрорекордером вы ошиблись — ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) — во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.
Чтобы включить запись необходимо:
- в Excel 2003 и старше — выбрать в меню Сервис — Макрос — Начать запись(Tools — Macro — Record New Macro)
- в Excel 2007 и новее — нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)
Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:
- Имя макроса — подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
- Сочетание клавиш — будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис — Макрос — Макросы — Выполнить(Tools — Macro — Macros — Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
- Сохранить в. — здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
- Эта книга — макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
- Новая книга — макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
- Личная книга макросов — это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .
Запуск и редактирование макросов
Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или — в старых версиях Excel — через меню Сервис — Макрос — Макросы (Tools — Macro — Macros) :
- Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
- Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
- Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.
Создание кнопки для запуска макросов
Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:
Кнопка на панели инструментов в Excel 2003 и старше
Откройте меню Сервис — Настройка (Tools — Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый "колобок" — Настраиваемую кнопку (Custom button) :
Перетащите ее к себе на панель инструментов и затем щелкните по ней правой кнопкой мыши. В контекстом меню можно назначить кнопке макрос, выбрать другой значок и имя:
Кнопка на панели быстрого доступа в Excel 2007 и новее
Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :
Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:
Кнопка на листе
Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:
- В Excel 2003 и старше — откройте панель инструментов Формы через меню Вид — Панели инструментов — Формы (View — Toolbars — Forms)
- В Excel 2007 и новее — откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)
Выберите объект Кнопка (Button) :
Затем нарисуйте кнопку на листе, удерживая левую кнопку мыши. Автоматически появится окно, где нужно выбрать макрос, который должен запускаться при щелчке по нарисованной кнопке.
Создание пользовательских функций на VBA
Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция — только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).
Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert — Module и введем туда текст нашей функции:
Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка — Функция) в категории Определенные пользователем (User Defined) :
После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:
Создание функций в функции в Microsoft Excel
Для создания функций в функции Excel можно использовать окно вставки или отдельное поле в главном окне. Пример с двумя условиями проще всего выполнить при помощи первого варианта. Допустим, у нас есть числа в отдельной таблице, при достижении определенного среднего значения которых в отдельной ячейке должна показаться сумма чисел других блоков. Для этого потребуется определиться с первым списком чисел, что видно на прикрепленном ниже скриншоте.
Подобная реализация пригодится во многих ситуациях, например, при бухгалтерских расчетах или составлении сметы. В любом случае, это всего лишь пример того, как может выглядеть функция в функции, а пользователь уже сам решает, как эту возможность адаптировать под себя.
- Создадим функцию через отдельное окно, нажав по кнопке с тематическим значком.
- Пример базируется на корневой функции «ЕСЛИ», поэтому в окне «Вставка функции» выберите именно ее.
Еще больше информации об используемом Мастере функций читайте в развернутой инструкции на нашем сайте, где находятся все необходимые сведения, иллюстрирующие работу с инструментом.
Пример 2: Три и более условий
Вариант с использованием инструмента вставки формулы не сработает, если внутри корня находится более двух переменных. Тогда придется заполнять поле вручную, учитывая синтаксис Excel, что нужно делать с особой осторожностью и внимательно проверять каждый символ. В качестве примера возьмем небольшую таблицу, где есть несколько наименований продукции с ценами за текущий и прошлый месяц. Нужно сделать так, что если цена будет оставаться на том же месте, увеличиваться или уменьшаться, рядом будет появляться соответствующее уведомление.
- Создадим отдельную формулу при использовании все той же корневой функции ЕСЛИ.
- Начните создавать формулу, нажав по нужной пустой ячейке и написав =ЕСЛИ(B2=C2;»Не изменилась»). Как можно понять, после открывающихся скобок идет первое условие, а при его истине выводится сообщение «Не изменилась».
- Поставьте знак ; и сразу же впишите новую функцию ЕСЛИ без знака =, где внутри содержится второе условие.
- Точно так же поступите с третьим условием и расставьте все закрывающиеся скобки.
- При сохранении результата вы увидите, что сообщение показывается верно, а также сможете скопировать саму формулу для других ячеек, поменяв только сравнение целевых цен.
Для лучшего понимания оставим полную строку формулы с функцией в функции в правильном ее виде =ЕСЛИ(B2=C2;»Не изменилась»;ЕСЛИ(B2 C2;»Уменьшилась»))). Если требуется, скопируйте ее и используйте в своих целях, изменив условия и значения.
Создание и примеры формул Excel
Формулы это то, что сделало таблицы Excel настолько популярными. Создание формул дает возможность мгновенно видеть результат расчетов при изменении любой информации в ячейках.
Основы создания формул в Microsoft Excel
- Все формулы начинаются со знака равенства «=».
- После знака равенства вводится сама формула, с числами или номерами ячеек.
- Использование двоеточия «:» позволяет получить диапазон ячеек для формулы.
- Данные функций (числа или ячейки) заключаются в скобки «()».
Примеры формул Excel
И так начнем рассматривать формулы Microsoft Excel. Думаю, что сразу же со второго нашего примера будет ясно, что формулы показаны на примере нормальной русифицированной программы, то бишь, официального «офиса». Если его кого-то такого еще нет, то можете купить MS Office 2010, установить на компьютер и сразу же приступить к работе и обучению.
Простой пример формулы. Формула добавляет 10 к произведению 3 и 5.
Формула отображает среднее значение чисел в ячейках D4 и D5. Например, если вам нужно узнать среднее значение чисел в ячейках от А1 до А30, то вам нужно ввести: =СРЗНАЧ(A1:A30).
Эта функция возвращает сегодняшнюю дату.
Функция, которая покажет вам максимальное число в диапазоне ячеек от M15 до M18. Также можно узнать минимальное число диапазона ячеек, для этого введите: =МИН(M15:M18).
Функция суммирует числа в ячейках А1 и А2.
Функция суммирует все числа в диапазоне ячеек от А1 до А27.
Добавляет ячейки А1, А2 и А5.
Использование Мастера функций
Другие функции можно найти и использовать с помощью «Мастера функций». Чтобы открыть «Мастер функций» нажмите на «fx» в строке формул:
Или выберите вкладку «Формулы» и нажмите «Вставить функцию».
У вас откроется окно Мастера функций, в нем выберите категорию функции, затем саму функцию и нажмите «ОК».
Далее выбираете нужные ячейки или вводите числа в поле «Число».