Использование функций и вложенных функций в формулах Excel
Функции — это заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления. Все функции Excel можно найти на вкладке Формулы на ленте:
-
Синтаксис функции Excel Следующий пример функции ROUND , округляющей число в ячейке A10, иллюстрирует синтаксис функции.
1. Структура. Структура функции начинается со знака равенства (=), за которым следует имя функции, открывающая скобка, аргументы для функции, разделенные запятыми, и закрывающая скобка. 2. Имя функции. Чтобы получить список доступных функций, щелкните ячейку и нажмите клавиши SHIFT+F3, чтобы открыть диалоговое окно Вставка функции .
3. Аргументы. Аргументами могут быть числа, текст, логические значения, такие как TRUE или FALSE, массивы, значения ошибок, такие как #N/A, или ссылки на ячейки. Используемый аргумент должен возвращать значение, допустимое для данного аргумента. В качестве аргументов также используются константы, формулы и другие функции. 4. Подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, всплывающая подсказка появляется после ввода выражения =ОКРУГЛ(. Всплывающие подсказки отображаются только для встроенных функций.
Примечание: Вам не нужно вводить функции со всеми caps, например =ROUND, так как Excel автоматически будет вводить имя функции заглавную букву после нажатия клавиши ВВОД. Если вы неправильно введете имя функции, например =SUME(A1:A10) вместо =SUM(A1:A10), excel вернет #NAME? ошибку #ЗНАЧ!.
Ввод функций Excel Диалоговое окно Вставить функцию упрощает ввод функций при создании формул, в которых они содержатся. После выбора функции в диалоговом окне Вставка функции Excel запустит мастер функций, который отображает имя функции, каждый из ее аргументов, описание функции и каждого аргумента, текущий результат функции и текущий результат всей формулы.
Чтобы упростить создание и изменение формул, а также свести к минимуму ошибки ввода и синтаксиса, используйте автозавершение формул. После ввода символа = (знак равенства) и начальных букв функции Excel отображает динамический раскрывающийся список допустимых функций, аргументов и имен, соответствующих этим буквам. Затем вы можете выбрать один из них в раскрывающемся списке, и Excel введет его за вас.
Использование аргумента «таблица» в функции просмотра
Важно: Попробуйте использовать новую функцию ПРОСМОТРX, улучшенную версию функции ВПР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что делает ее проще и удобнее в использовании, чем предшественницу.
При создании функции ВПР или HLOOKUP введите диапазон ячеек, например D2:F39. Этот диапазон называется аргументом table_array, а аргумент — это просто фрагмент данных, необходимый функции для выполнения. В этом случае функция выполняет поиск в этих ячейках данных, которые вы пытаетесь найти.
Аргумент table_array всегда является вторым аргументом в функции ВПР или HLOOKUP (первый — это значение, которое вы пытаетесь найти), и функции не будут работать без него.
Первым аргументом, значением, которое требуется найти, может быть определенное значение, например «41» или «smith», или ссылка на ячейку, например F2. Поэтому первый аргумент может выглядеть следующим образом:
=ВПР(F2, .
Аргумент table_array всегда следует за значением подстановки, например:
=ВПР(F2;B4:D39, .
Диапазон ячеек, указанный в аргументе table_array, может использовать относительные или абсолютные ссылки на ячейки. Если вы собираетесь скопировать функцию, необходимо использовать абсолютные ссылки, например:
=ВПР(F2,$B$2:BD$39, .
Кроме того, ячейки в аргументе table_array могут жить на другом листе в книге. Если это так, аргумент включает и имя листа, а синтаксис выглядит следующим образом:
=ВПР(F2,Лист2!$C$14:E$42, .
Обязательно поместите восклицательный знак после имени листа.
Наконец (whew) введите третий аргумент, столбец, содержащий значения, которые вы пытаетесь найти. Это называется столбцом подстановки. В первом примере мы использовали диапазон ячеек от B4 до D39, который проходит по трем столбцам. Давайте представим, что значения, которые вы хотите видеть, в реальном времени в столбце D, третьем столбце в этом диапазоне ячеек, поэтому последним аргументом является 3.
Можно использовать необязательный четвертый аргумент— True или False. Большую часть времени вы хотите использовать False.
Если использовать значение True или оставить четвертый аргумент пустым, функция возвращает приблизительное совпадение со значением в первом аргументе. Поэтому, чтобы продолжить пример, если первым аргументом является «smith» и используется значение True, функция вернет «Smith», «Smithberg» и т. д. Но если вы используете False, функция возвращает только «Smith», точное совпадение, и это то, что большинство людей хотят.
Чтобы сделать использование True еще сложнее, если столбец подстановки (столбец, указанный в третьем аргументе), не отсортирован по возрастанию (от A до Z или от наименьшего до самого большого числа), функция может вернуть неправильный результат. Дополнительные сведения об этом см. в разделе Поиск значений с помощью ВПР и других функций.
Дополнительные сведения о функциях ВПР и HLOOKUP см. в следующих разделах:
- Краткий справочник: обзор функции ВПР
- Функции ссылки и поиска (справка)
Как работать с аргументами функции в таблицах Excel
В Excel есть большое количество полезных функций, и каждая из них имеет свой собственный, уникальный набор аргументов. Вы, вероятно, уже запомнили аргументы тех функций, которые часто используете, но что насчет других?
Лучший способ вставки функции — использование диалогового окна Мастер функций. Это окно вызывается любым из следующих способов.
- Нажмите кнопку Вставить функцию, расположенную в группе Формулы ► Библиотека функций.
- Нажмите кнопку Вставить функцию слева от строки формул.
- Нажмите Shift+F3.
Если вы не знаете имя функции, которая вам нужна, то можете найти ее, набрав текст в поле Поиск функции и нажав кнопку Найти (рис. 80.1). После того как вы определите функцию, нажмите кнопку ОК, и вы увидите окно Аргументы функции, которое ознакомит вас с аргументами данной функции, как показано на рис. 80.2.
Рис. 80.1. Используйте окно Мастер функций, чтобы найти нужную вам функцию
Между прочим, если вы вводите функцию вручную (без помощи диалогового окна Мастер функций), то можете нажать Ctrl+A для открытия диалогового окна Аргументы функции. Это сочетание клавиш работает, только если вы еще не вводили какие-либо аргументы для функции.
Рис. 80.2. Окно Аргументы функции помогает ввести аргументы функции
В некоторых случаях вы можете включить «фиктивные» аргументы для функции — заполнители, которые позже будут заменены. Этот подход окажется полезным, если вы еще не знаете, ссылки на какие ячейки будут использованы. Для вставки названий аргументов в качестве заполнителей нажмите Ctrl+Shift+A после ввода имени функции. Excel использует названия аргумента в качестве самих аргументов.
Например, если нажать Ctrl+Shift+A при вводе функции ВПР, то Excel вставит следующие названия: =ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр) . Формула, конечно, возвращает ошибку, так что вы должны заменить фиктивные аргументы фактическими значениями или ссылками на ячейки.
Аргументы функции на VBA
В этой статье речь пойдет о том, как создавать функции, которые не принимают никаких аргументов, а также про функции, которые принимают несколько аргументов. Расскажем также о необязательных аргументах пользовательской функции.
Создание функции без каких-либо аргументов
Начнем с функций, которые не имеют аргументов. В качестве примера, можете вспомнить такие стандартные функции как СЕГОДНЯ() или СЛЧИС(). Эти функции не зависят от каких либо входных параметров. Давайте тоже создадим аналогичную функцию. Например функцию, которая будет возвращать нам название текущего листа (нам ведь не нужны никакие параметры в данном случае. Код функции приведен ниже:
Function НазваниеЛиста() As String НазваниеЛиста = ActiveSheet.Name End Function
Как видите скобки мы оставили пустые, указали наименование функции НазваниеЛиста и задали тип возвращаемых значение As String (Текстовое значение).
Единственный нюанс данной функции состоит в том, что она автоматически не изменит значение, если Вы переименуете лист. К сожалению Excel запускает процесс пересчета когда меняется какой либо аргумент, но тут их нет и функций «не знает» когда пересчитывать значение. Принудительно можно запустить пересчет книги с помощью сочетания клавиш Ctrl + Alt + F9. Чтобы заставить формулу пересчитываться всякий раз, когда происходит изменение на листе, нужно указать еще одно строку кода.
Function НазваниеЛиста() As String Application.Volatile True НазваниеЛиста = ActiveSheet.Name End Function
Теперь, если вы измените имя листа, функция будет пересчитываться. Функция также будет пересчитываться при любом редактировании листа.
Создание функции с одним аргументом
В предыдущей статье Создаем свою первую функцию в Excel мы достаточно подробно рассказали о создании функции с одним аргументом. Поэтому приведем еще один пример простой но полезной функции и перейдем к следующему пункту.
Наверняка вы сталкивались с тем, что необходимо написать огромную формулу, проверяющую несколько условий с помощью функций ЕСЛИ. Получалось что-то вроде нескольких вложенных функций ЕСЛИ. Давайте попробуем решить эту задачу путем создания пользовательской функции. Например нам необходимо рассчитать значение премии в зависти от процента выполненного плана. Если план выполнен менее чем 50% нам ничего не платят. Если от 50 до 75 процентов, то дают премию 5 000 рублей. Если от 75 до 90% — 15 000 рублей. От 90 до 100% — 25 000 рублей. И если более 100%, то 50 000 тысяч. Давайте писать код:
Function Премия(Процент As Double) As Double Dim res As Double Select Case Процент Case Is < 0.5 res = 0 Case Is < 0.75 res = 5000 Case Is < 0.9 res = 15000 Case Is < 1 res = 25000 Case Else res = 50000 End Select Премия = res End Function
Смысл кода думаю ясен, конструкция Select Case проверяет условие и выдает результат если условие истинно.
А теперь применим функцию и посмотрим как из сложной функции мы сделали короткую, а самое главное понятную для других пользователей функцию.
Создание функции с несколькими аргументами
Сейчас научимся создавать функции с несколькими переменными и заодно создадим достаточно полезную для работы функцию. Наверняка у всех была похожая задача. Нужно из ячейки, которая содержит ФИО извлечь, к примеру только имя или отчество. Можно обойтись набором стандартных функций, но сложновато. Подобно функциям листа, вы можете создавать функции в VBA, которые принимают несколько аргументов.
В приведенном ниже коде будет создана функция, которая будет извлекать текст перед указанным разделителем. Он принимает два аргумента - ссылку на ячейку с текстовой строкой и разделитель.
Function Слово(Текст As String, Номер As Long) As String Dim splt As Variant splt = Split(Текст, " ") Слово = splt(Номер - 1) End Function
Когда вам необходимо использовать более одного аргумента в функции, то нужно их все перечислить в скобках через запятую. Обратите внимание, что для каждого аргумента вы можете указать тип данных. В приведенном выше примере переменная Текст была объявлена As String (как текст), а переменная Номер объявлена как Long (целое число). Если вы не укажете какой-либо тип данных, VBA считает, что тип данных Variant - этот тип переменных сам изменяет свой тип в зависимости от значения, но потребляет больше ресурсов.
При использовании функций на листе вы можете в качестве аргументов указывать как статичное значение, так и делать ссылки на ячейки.
На картинке ниже, которая демонстрирует работу нашей функции можно увидеть, что в качестве первого параметра (Текст) мы берем ссылку на ячейку, а в качестве второго аргумента вводим число вручную, хотя также могли бы ссылаться на ячейку.
Создание функции с необязательными аргументами
В Excel есть много функций, в которых некоторые аргументы являются необязательными.
Например, всем известная функция ВПР. У нее 3 обязательных аргумента и один необязательный. Он обозначается в квадратных скобках - [интервальный_просмотр].
Если вы не укажете один из обязательных аргументов, ваша функция выдаст ошибку, а если не указать необязательный аргумент функция продолжит работу.
Но это не означает, что необязательные аргументы бесполезны. Они призваны облегчить функцию и использовать значения по умолчанию.
Чтобы создать функцию с необязательными аргументами, необходимо перед названием аргумента указать инструкцию Optional. Существует правило - все необязательные переменные должны идти после обязательных. Нельзя создать функцию, где сначала будет необязательный аргумент, а после обязательные.
Приведем пример функции с необязательными аргументами
Function ТолькоТекст(Ячейка As Range, Optional Прописные As Boolean = False) As String Dim i As Long Dim result As String For i = 1 To Len(Ячейка) If Not IsNumeric(Mid(Ячейка, i, 1)) Then result = result & Mid(Ячейка, i, 1) Next If Прописные Then result = UCase(result) ТолькоТекст = result End Function
Тут хочу обратить внимание на первую строку, а именно на:
Optional Прописные As Boolean = False
Optional обозначает, что переменная необязательная, далее идет наименование аргумента - Прописные, далее при необходимости указывается тип данных. В нашем случае это логическая переменная Boolean. А далее? после знака равно, указывается значение по умолчанию. Если при использовании функции второй аргумент не будет указан, то переменной присвоется значение по умолчанию, в нашем случае False.
Далее в коде вы увидите строку:
If Прописные Then result = UCase(result)
Она как раз и проверяет значение необязательного аргумента. Если в качестве аргумента указано значение ИСТИНА (или 1), то функция вернет текст прописными буквами.
Все функции вы можете скачать в файле к этой статье. В следующих статьях поговорим о функциях, которые возвращают массив значений.