Работа с большими формулами может быть проблематичной даже для опытных пользователей. Самое сложное — понять дизайн других людей и понять, как он работает. Недавно в рамках туториала меня попросили проанализировать несколько сложных формул, и оказалось, что формулы действительно окупаются — я насчитал 7-8 ЕСЛИ и примерно 5-6 других функций в ячейке. В таких ситуациях очень важно выяснить, что является аргументом каждой функции. Поэтому я решил написать небольшую статью о важной вещи — аргументе Excel и его роли в вычислениях. И самое главное, я опишу в статье, как удобно находить и выделять каждый из аргументов при написании огромных формул.
Думаю, сначала нужно сказать несколько слов о банальной теории.
Аргумент Excel. Теория
Аргументы — это величины, используемые при вычислении функций/формул. Аргументами могут быть число, текст и даже формула с другими функциями.
Аргументы могут быть как обязательными (без которых формула не будет работать), так и необязательными (без которых функция будет работать по умолчанию), такие выделяются в квадратных скобках.
Пример:
=DAYWEEK(date_in_numberformat;)Где "дата_в_числовом_формате" надо заполнять, а - вводить нельзя, и даже точки с запятой необязательны.
Важно отметить, что функции могут не содержать аргумент
Пример:
=СЕГОДНЯ()Или они могут быть с непостоянным числом аргументов, например:
=СУММЕСЛИМН()Как удобно найти и выделить аргументы функции?
Чтобы понимать большие и тяжелые формулы, очень важно уметь выделять аргументы вложенных функций. Даже если вы написали эту формулу, я гарантирую вам, что вы не поймете ее сразу через полгода. Чтобы легко увидеть аргумент функции, щелкните подсказку ниже, которая появится при входе в ячейку, и аргумент будет выделен:
В примере вы нажимаете на «искомое значение», и в самой формуле оно будет выделено подсветкой.
Для удобства также прилагаю гифку
Но есть записи и потяжелее. Такой:
Или что-то вроде того
Формулы не мои, надеюсь создатели не обиделись. Я уверен, что многие (включая меня) сталкивались с горами символов и более серьезных. Разобраться в таких конструкциях без возможности выбора крайне сложно.
Что еще важно сказать
При таких конструкциях очень важно понимать формулу по частям, чтобы подчеркнуть аргумент. Метод выбора аргумента Excel очень полезен. Еще могу посоветовать выкладывать каждую функцию на новую строку с помощью Alt+Enter — тоже очень помогает — подробнее здесь.
Также важно отметить, что выбор аргумента функции может производиться после выбора любого аргумента в формуле.
Например, вам непонятно, как работает функция в примере (я специально не заполнил аргумент 4 для ВПР).
Вы можете щелкнуть мышью в любом месте формулы, после чего для вас появится подсказка, какой это аргумент.
Из этой подсказки вы можете перейти к одному из аргументов, щелкнув по нему
А еще можно перейти к справке, нажав на название самой формулы, в данном случае ВПР.
Удачного разбора своих и чужих формул! Пишите свои комментарии.
Поделитесь нашей статьей в своих социальных сетях:
(Посетили 523 раза, 1 раз сегодня)
Дата: 27 декабря 2015 г. Категория: Excel Поделиться, закладка или статья
Привет, друзья! Это вводная статья о функциях Excel, где я объясню, что такое функции, аргументы функций, как вставить функцию в формулу. А в следующих постах мы разберемся, что такое функции Excel и как их правильно использовать.
Функции в Excel — это инструкции, которые выполняют более сложные вычисления, чем математические операторы. Некоторые расчеты невозможно выполнить без функций, поэтому их идеальное знание является ключом к успеху.
Вы можете «вкладывать» одну функцию в другую, копировать формулы с функциями (не забывайте о разных типах ссылок). Самое главное — четко понимать, как работает функция, иначе она может выдать неправильный результат или ошибку. Вы можете даже не заметить этого. А чтобы понять, как работают функции, прочитайте мои сообщения о различных функциях и справке Microsoft.
С использованием каждой новой версии Excel список функций обновляется. Разработчики добавляют новые, популярные, убирают функции, которые уже не актуальны. В этом блоге я опишу возможности Microsoft Excel 2013, а также отвечу на любые вопросы о возможностях других версий программы. Как всегда, спрашивайте их в комментариях.
Аргументы функции Excel
Аргументы функции — это первые данные для вычисления функции. Например, для функции СУММ это список чисел, ячеек или диапазонов ячеек для суммирования. Аргументы указываются в скобках после имени функции и разделяются точкой с запятой (запятая в английской версии). По количеству аргументов функции могут быть:
- Без аргументов - не требует аргументов для вычисления. Например, =PI() возвращает число 3,1428.
- С одним аргументом - вам нужно указать только один аргумент. Например, =LOWER(A1) — все символы в ячейке A1 будут преобразованы в нижний регистр.
- С несколькими аргументами - необходимо указать определенное количество аргументов, более одного. Например, функция =MID(A1;1;10) вернет первые 10 символов строки в ячейке A1.
- С необязательными аргументами — у функции есть необязательные аргументы. Например, =ВПР("Иванов", A1:B30,2,0) будет искать фамилию "Иванов" в диапазоне A1:B30 и возвращать информацию о нем. Последний аргумент здесь — «Интервальный поиск» — указывает метод поиска, указывать его необязательно.
- При переменном количестве аргументов - количество аргументов можно изменить. Например, =СРЗНАЧ(A1;B3:B15;C2:F2) - будет вычислено среднее значение цифр в указанных диапазонах. Перечисляя ячейки через точку с запятой, вы можете указать другое количество аргументов.
Как вставить функцию в Excel
Чтобы вставить функцию в формулу, вы можете использовать один из следующих способов:
- Если вы знаете название функции, начните вводить его с клавиатуры, инструмент Автозаполнение предложит варианты функций, выберите из списка нужный, нажмите TAB и введите аргументы;
Автодополнение при вводе функции вручную - Другой способ, если вы знакомы со списком функций, это выбрать на ленте: Формулы - Библиотека функций. В этой группе находятся функции по типу: логические, текстовые, математические и т.д. Открыв нужную группу, выберите функцию и приступайте к заполнению аргументов.
Выберите функцию на ленте
Если вы не знаете имя функции, используйте окно «Вставить функцию». Чтобы вызвать его, попробуйте один из следующих способов:
- Нажмите комбинацию клавиш SHIFT+F3
- Выполнить на ленте Формулы - Библиотека функций - Вставить функцию
- Щелкните значок fx слева от строки формул
Вставить функциональное окно
После выполнения любой из этих операций открывается окно Вставить функцию. В поле функции поиска кратко опишите, что вам нужно сделать, и нажмите «Поиск». Результаты поиска отображаются в поле Выберите функцию. Нажмите на функции в списке, прочитайте их описания. Если подходящая функция не найдена, переформулируйте поиск и повторите поиск.
Когда вы нашли функцию, дважды щелкните по ней, откроется окно аргументов. После их заполнения нажмите ОК, программа рассчитает результат.
Окно «Аргументы функции»
Еще раз констатирую, что мы изучили важную и простую тему. Еще просто! Потренируйтесь самостоятельно вставлять функции в электронную таблицу и убедитесь, что это легко. Однако это очень важные навыки. Их успешное применение, доведенное до автоматизма, составляет основу фундамента. Далее я опишу работу на основе практики в этой статье.
Речь идет о вставке функций на лист, и в следующей статье мы начнем рассматривать текстовые функции. До встречи на страницах блога OfficeEASY.com!
Поделиться, добавить в закладки или статью
Excel содержит множество встроенных функций, которые можно использовать для технических, статистических, финансовых, аналитических и других расчетов. Иногда условия поставленной задачи требуют более гибкого инструмента для поиска решения, тогда на помощь приходят макросы и пользовательские функции.
Пример создания своей пользовательской функции в Excel
Как и макросы, пользовательские функции можно создавать с помощью языка VBA. Для реализации этой задачи необходимо выполнить следующие шаги:
- откройте редактор языка VBA с помощью комбинации клавиш ALT+F11.
- В открывшемся окне выберите Вставить элемент и подэлемент Модуль, как показано на рисунке:
- Новый модуль будет создан автоматически и в основной части окна редактора появится окно для ввода кода:
- При необходимости вы можете изменить название модуля.
- В отличие от макросов, код которых должен находиться между операторами Sub и End Sub, пользовательские функции задаются операторами Function и End Function соответственно. В состав пользовательской функции входит имя (произвольное имя, отражающее ее суть), список параметров (аргументов) с объявлением их типов, если это необходимо (некоторые могут не принимать аргументы), тип возвращаемого значения, тело функции (код, отражающий логику работы), а также оператор End Function. Пример простой пользовательской функции, возвращающей названия дней недели в зависимости от заданного числа, показан на рисунке ниже:
- После ввода кода выше нужно нажать комбинацию клавиш Ctrl+S или специальный значок в левом верхнем углу редактора кода для сохранения.
- Чтобы использовать созданную функцию, вернитесь в табличный редактор Excel, установите курсор в любую ячейку и введите имя пользовательской функции после символа «=»:
Встроенные функции Excel содержат пояснения как к результату, который они возвращают, так и к аргументам, которые они принимают. Это можно увидеть на примере любой функции, нажав комбинацию горячих клавиш SHIFT+F3. Но у нашей функции пока нет схемы.
Чтобы задокументировать пользовательскую функцию, необходимо сделать следующее:
- Создайте новый макрос (нажмите комбинацию клавиш Alt+F8), в появившемся окне введите произвольное имя нового макроса, нажмите кнопку Создать:
- В результате будет создан новый модуль с пробелом, разделенным операторами Sub и End Sub.
- Введите код, как показано на рисунке ниже, указав необходимое количество переменных (в зависимости от количества аргументов пользовательской функции):
- Текстовая строка с именем пользовательской функции должна быть передана как «Макрос», переменная типа строка с текстом описания возвращаемого значения как «Описание», а массив переменных типа строка с тексты описаний аргументов пользовательской функции в виде «ArgumentDescriptions.
- Для создания описания пользовательской функции достаточно один раз выполнить созданный выше модуль. Теперь при вызове пользовательской функции (или SHIFT+F3) отображается описание возвращаемого результата и переменной:
Функциональные описания необязательны. Они необходимы в тех случаях, когда пользовательские функции будут часто использоваться другими пользователями.
Примеры использования пользовательских функций, которых нет в Excel
Пример 1. Рассчитайте размер отпускных для каждого работника, проработавшего в компании не менее 12 месяцев, исходя из суммы общей заработной платы и количества выходных дней в году.
Просмотр исходной таблицы данных:
Каждому работнику полагается 24 выходных дня с оплатой S=N*24/(365-n), где:
- N – общая заработная плата за год;
- n – количество государственных праздников в году.
Давайте создадим пользовательскую функцию для расчета на основе этой формулы:
Пример кода:
Общественная функция Отпускные(суммЗп до длительности, отпуска до длительности) до
Если IsNumeric(праздники) = False или IsNumeric(summZp) = False Тогда
Отпускные = "Введены нечисловые данные"
Выходная функция
ElseIf праздничные дни
Функции представляют собой зависимость одного элемента (результата) от других элементов (аргументов, т.е находящихся внутри :-)). Это несколько понятно. Чтобы использовать какую-либо функцию в Excel, ее следует ввести в виде формулы (нюансы описаны здесь) или в составе формулы в ячейке листа. Порядок, в котором должны располагаться используемые в формуле символы и аргументы, называется синтаксисом функции. Все функции используют одни и те же правила синтаксиса. Если вы нарушите эти правила, Excel выдаст сообщение о том, что в формуле ошибка и что он с вами не дружит. Но поверьте, в функциях Excel все довольно похоже, и разобравшись один раз, на одной-двух функциях, в остальных случаях все будет достаточно просто. Синтаксические правила написания функций Ниже приведены правила, которых необходимо придерживаться для грамотного и оптимального построения формулы с использованием одной или нескольких функций. Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства, как и в начале любой формулы. Я уже говорил об этом в предыдущих статьях, но повторить не помешает. После этого вводится имя функции, за которым сразу следует список аргументов в скобках. Аргументы отделяются друг от друга точкой с запятой ";". Круглые скобки позволяют Excel определить, где начинается и заканчивается список аргументов.
Функция Excel Обратите внимание, что запись функции должна содержать открывающие и закрывающие круглые скобки, и вы не можете вставлять пробелы между именем функции и круглыми скобками. В противном случае Excel выдаст сообщение об ошибке. В качестве аргументов вы можете использовать числа, текст, логические значения, массивы, значения ошибок или ссылки. При этом параметры, указанные пользователем, должны иметь допустимые значения для этого аргумента. Например, приведенная ниже формула суммирует значения в ячейках B2, B3, B4, B5 и E7, причем некоторые ячейки с B2 по B5 представлены в виде непрерывного диапазона.
Аргументы функции Excel Рассмотрим работу функции ОКРУГЛ(arg1;arg2), которая возвращает число, округленное до заданного количества знаков после запятой, и имеет два аргумента: arg1 - адрес ячейки с числом (или само число)) округлить; arg2 - количество знаков после запятой в числе после округления.
Чтобы округлить число 2,71828 в ячейке A1 до одного, двух или трех знаков после запятой и записать результаты расчета в ячейки B1, C1 и D1 соответственно, выполните следующие действия. Введите число 2,71828 в ячейку A1. Введите следующие формулы в ячейки B1, C1 и D1: =ОКРУГЛ(A1;1) =ОКРУГЛ(A1;2) =ОКРУГЛ(A1;3) Аргументами могут быть как константы, так и функции. Функции, являющиеся аргументами другой функции, называются вложенными функциями. Например, просуммируем значения ячеек A1 и A2, предварительно округлив эти значения до двух знаков после запятой: =СУММ(ОКРУГЛ(A1;2);ОКРУГЛ(A2;2)) Можно использовать до семи уровни вложенности функций в Excel - формулы. Стоит отметить, что в Excel есть функции, не принимающие аргументов. Примерами таких функций являются PI (возвращает значение π, округленное до 15 цифр) или TODAY (возвращает текущую дату). При использовании таких функций следует ставить пустые круглые скобки без аргументов в строке формулы сразу после имени функции. Другими словами, для получения значения числа p или текущей даты в ячейки следует вводить формулы следующего вида: =ПИ() =СЕГОДНЯ() Типы функций Excel Для удобства пользователя при построении формул , функции в Excel разделены на категории: списки, функции даты и времени, финансовые, статистические, текстовые, математические, логические. Текстовые функции используются для обработки текста, а именно: поиска нужных символов, записи символов в строго определенное место в тексте и т д. С помощью функций даты и времени можно решить практически любую задачу, связанную с календарными датами или временем. (например, рассчитать количество рабочих дней за какой-либо период времени). Булевы функции используются для создания сложных формул, которые в зависимости от выполнения определенных условий будут осуществлять различные виды обработки данных. Они особенно интересны, и о них мы поговорим в отдельной статье. Математические функции широко представлены в Excel и некоторые из них я уже приводил в примерах. Также в распоряжении пользователя имеется библиотека статистических функций, с помощью которых можно осуществлять поиск среднего значения, максимального и минимального элементов и т д.
