Примеры как создать пользовательскую функцию в Excel
В Excel содержится множество встроенных функций, которые могут быть использованы для инженерных, статистических, финансовых, аналитических и прочих расчетов. Иногда условия поставленных задач требуют более гибкого инструмента для поиска решения, тогда на помощь приходят макросы и пользовательские функции.
Пример создания своей пользовательской функции в Excel
Подобно макросам, пользовательские функции могут быть созданы с использованием языка VBA. Для реализации данной задачи необходимо выполнить следующие действия:
Встроенные функции Excel содержат пояснения как возвращаемого результата, так и аргументов, которые они принимают. Это можно увидеть на примере любой функции нажав комбинацию горячих клавиш SHIFT+F3. Но наша функция пока еще не имеет формы.
Чтобы задокументировать пользовательскую функцию, необходимо выполнить следующие действия:
Описания функций создавать не обязательно. Они необходимы в случаях, если пользовательские функции будут часто использоваться другими пользователями.
Примеры использования пользовательских функций, которых нет в Excel
Пример 1. Рассчитать сумму отпускных для каждого работника, проработавшего на предприятии не менее 12 месяцев, на основе суммы общей заработной платы и числа выходных дней в году.
Каждому работнику полагается 24 выходных дня с выплатой S=N*24/(365-n), где:
Создадим пользовательскую функцию для расчета на основе данной формулы:
Сохраним функцию и выполним расчет с ее использованием:
Растянем формулу на остальные ячейки с целью получения результатов для остальных работников:
Калькулятор расчета калорий в Excel
Пример 2. Рассчитать суточную норму калорий для участников программы похудения, среди которых есть как женщины, так и мужчины определенного возраста с известными показателями роста и веса.
Для расчета используем формулу Миффлина — Сан Жеора, которую запишем в коде пользовательской функции с учетом пола участника. Код примера:
Проверки корректности введенных данных упущены для упрощения кода. Если пол не определен, функция вернет результат 0 (нуль).
В результате использования автозаполнения получим следующие результаты:
Пользовательская функция для решения квадратных уравнений в Excel
Пример 3. Создать функцию, которая возвращает результаты решения квадратных уравнений для указанных в ячейках коэффициентах a, b и c уравнения типа ax2+bx+c=0.
Для решения создадим следующую пользовательскую функцию:
Выполним расчеты для остальных уравнений. Полученные результаты:
Мы создали свою пользовательскую функцию для расчета квадратных уравнений, которой раньше не было в Excel по умолчанию.
[expert_bq id=»1570″]Иногда бывает сложно найти такой список, например, когда электронная таблица очень длинная и содержит много различной информации. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] В случае с Excel количество доступных функций настолько велико, что большинство наиболее важных из них невозможно уместить в одном тексте. Выбранные ниже функции позволяют вам изучить основы и немного более сложные возможности, которые можно использовать в этой программе.
Как пользоваться Microsoft Excel (Эксель)
В Excel содержится множество встроенных функций, которые могут быть использованы для инженерных, статистических, финансовых, аналитических и прочих расчетов. Иногда условия поставленных задач требуют более гибкого инструмента для поиска решения, тогда на помощь приходят макросы и пользовательские функции.
Работа с мастером функций в Microsoft Excel
Возможности Эксель позволяют не только создавать и редактировать таблицы, но и производить всевозможные расчеты: математические, финансовые, статистические и т.д. Делается это с помощью формул или функций (операторов), для выбора и настройки которых предусмотрен специальный инструмент под названием “Мастер функций”. Алгоритм его использования мы и разберем в данной статье.
Шаг 1: вызов Мастера функций
Для начала выбираем ячейку, в которую планируется вставить функцию.
Затем у нас есть несколько способов открытия Мастера функций:
Шаг 2: выбор функции
Итак, независимо от того, какой из описанных выше способов был выбран, перед нами появится окно Мастера функций (Вставка функции). Оно состоит из следующих элементов:
Шаг 3: заполнение аргументов функции
В следующем окне предстоит заполнить аргументы (один или несколько), перечень и тип которых зависит от выбранной функции.
Рассмотрим на примере “СРЗНАЧ” (для вычисления среднего арифметического значения), работающего с числовыми данными.
Поле напротив аргумента можно заполнить вручную, введя конкретное число (или несколько числовых значений, разделенных точкой с запятой) с помощью клавиш на клавиатуре.
Либо можно указать ссылку на ячейку или диапазон ячеек, содержащих числа.
Здесь возможны два варианта – это можно сделать либо вручную (т.е. с помощью клавиатуры), либо с помощью мыши. Последний вариант удобнее – просто щелкаем по нужному элементу в самой таблице, находясь в поле напротив нужного аргумента.
Возможна комбинация способов заполнения значений аргументов, а переключаться между ними можно с помощью щелчков мыши внутри нужного поля или клавиши Tab.
- В нижней части окна представлено описание функции, а также комментарии/рекомендации касательно того, как именно следует заполнить тот или иной аргумент.
- Иногда количество аргументов может увеличиться. Например, как это произошло в нашем случае с функцией “СРЗНАЧ”. По умолчанию предусмотрено всего два аргумента, но если мы перейдем к заполнению второго, добавится третий и т.д.
- Принцип заполнения текстовых данных в других функциях, где это предполагается, аналогичен рассмотренному выше – либо мы указываем конкретные значения, либо ссылки на ячейки или диапазоны ячеек.
Шаг 4: выполнение функции
Окно Мастера функций закроется. И если все сделано правильно, мы увидим в выбранной ячейке результат согласно заданным значениям аргументов.
А в строке формул будет отображаться автоматически заполненная формула функции.
Заключение
Таким образом, Мастер функций позволяет максимально упростить работу с функциями, что делает его одним из самых незаменимых инструментов в Excel. Благодаря нему не нужно запоминать сложные формулы и правила их написания, т.к. все предельно просто реализовано через заполнение специальных полей.
Полезные функции Microsoft Excel – руководство для новичков
- В нижней части окна представлено описание функции, а также комментарии/рекомендации касательно того, как именно следует заполнить тот или иной аргумент.
- Иногда количество аргументов может увеличиться. Например, как это произошло в нашем случае с функцией “СРЗНАЧ”. По умолчанию предусмотрено всего два аргумента, но если мы перейдем к заполнению второго, добавится третий и т.д.
- Принцип заполнения текстовых данных в других функциях, где это предполагается, аналогичен рассмотренному выше – либо мы указываем конкретные значения, либо ссылки на ячейки или диапазоны ячеек.
Можно вводить текст, числа или формулы в любую ячейку рабочего листа. Для этого выделяю ячейку и набираю с клавиатуры то, что нужно. Например, заголовки могут быть такими: «Составил», «Дата» и так далее. (См. рис. 1)
