Работа с большими формулами может вызвать затруднения даже у опытных пользователей. Самое сложное — это понять чужой дизайн и понять, как он работает. Недавно в рамках обучения меня попросили разобрать несколько сложных формул и оказалось, что формулы действительно благодарные: я насчитал 7-8 ЕСЛИ и примерно 5-6 других функций в ячейке. В таких ситуациях очень важно определить, каков аргумент каждой функции. Поэтому я решил написать небольшую статью о чем-то важном: аргументе Excel и его роли в вычислениях. И самое главное, я опишу в статье, как удобно находить и выделять каждый из аргументов при написании огромных формул.
Думаю, сначала следует сказать несколько слов банальной теории.
Аргумент Excel. Теория
Аргументы — это величины, используемые при вычислении функций/формул. Аргументами могут быть число, текст и даже формула с другими функциями.
Аргументы могут быть обязательными (без которых формула не будет работать) или необязательными (без которых функция будет работать по умолчанию), выделенными в квадратных скобках.
Пример:
=ДЕНЬНЕД(дата_в_числовом_формате;)
Где «дата_в_числовом_формате» нужно заполнять, а — вводить нельзя, и даже точка с запятой не обязательна.
Важно отметить, что функции могут не содержать аргумент
Пример:
=СЕГОДНЯ()
Или они могут быть с непостоянным количеством аргументов, например:
=СУММ.ЕСЛИ.ЕСЛИ()
Как удобно найти и выделить аргументы функции?
Для понимания больших и тяжелых формул очень важно уметь выделять аргументы вложенных функций. Даже если вы написали эту формулу, я гарантирую, что через полгода вы не сразу ее поймете. Для удобного просмотра аргумента функции нажмите на подсказку ниже, которая появится при входе в ячейку и аргумент будет выделен:
В примере нажмите на «искомое значение», и сама формула будет выделена подсветкой.
Для удобства также прилагаю гифку
Но есть диски и потяжелее. Такой:
Или, может быть
Формулы не мои, надеюсь создатели не обиделись. Я уверен, что многие из нас (включая меня) знали горы символов и других более серьезных символов. Без возможности выбора разобраться в таких конструкциях крайне сложно.
Что еще важно сказать
При таких конструкциях очень важно понимать формулу по частям, чтобы ваш аргумент выделялся. Метод выбора аргумента Excel очень полезен. Я также могу порекомендовать вам расширять каждую функцию на новую строку с помощью Alt + Enter; тоже очень помогает. Подробнее здесь.
Также важно отметить, что выбор аргумента функции можно сделать после выбора любого аргумента формулы.
Например, вам непонятно, как работает функция в примере (четвёртый аргумент ВПР я специально не заполнял).
Вы можете щелкнуть мышкой в любом месте формулы, после чего появится подсказка, какой это аргумент.
Из этой подсказки вы можете перейти к любому из аргументов, щелкнув по нему
А еще можно перейти к справке, нажав на название самой формулы, в данном случае ВПР.
Удачного разбора своих и чужих формул! Пишите свои комментарии.
Поделитесь нашей статьей в своих социальных сетях:
(Посещение 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.
- В открывшемся окне выберите элемент Insert и подэлемент Module, как показано на рисунке:
- Новый модуль будет создан автоматически и в основной части окна редактора появится окно для ввода кода:
- При необходимости вы можете изменить название модуля.
- В отличие от макросов, код которых должен находиться между объявлениями Sub и End Sub, пользовательские функции обозначаются объявлениями Function и End Function соответственно. Пользовательская функция включает имя (произвольное имя, отражающее ее суть), список параметров (аргументов) с объявлением их типов, при необходимости (некоторые могут не принимать аргументы), тип возвращаемого значения, тело функции (код, отражающий логику его работы), а также оператор End Function. На следующем рисунке показан пример простой определяемой пользователем функции, которая возвращает названия дней недели на основе указанного числа:
- После ввода вышеуказанного кода нужно нажать комбинацию клавиш Ctrl+S или специальную иконку в левом верхнем углу редактора кода для сохранения.
- Для использования созданной функции необходимо вернуться в табличный редактор Excel, установить курсор в любую ячейку и после символа «=» ввести имя пользовательской функции «=»:
Встроенные функции Excel содержат пояснения как к возвращаемому ими результату, так и к аргументам, которые они принимают. Это можно увидеть на примере любой функции, нажав комбинацию горячих клавиш SHIFT+F3. Но наша функция по-прежнему не имеет формы.
Чтобы задокументировать пользовательскую функцию, необходимо сделать следующее:
- Создайте новый макрос (нажмите комбинацию клавиш Alt+F8), в появившемся окне введите произвольное имя нового макроса, нажмите кнопку Создать:
- В результате будет создан новый модуль с пустым пространством, ограниченным объявлениями Sub и End Sub.
- Введите код, как показано на рисунке ниже, указав необходимое количество переменных (в зависимости от количества аргументов пользовательской функции):
- В качестве «Макроса» необходимо передать текстовую строку с именем пользовательской функции, в качестве «Описания» — переменную типа String с текстом описания возвращаемого значения, в качестве «Описания аргументов» — массив переменных типа Строка с текстами описания аргументов пользовательской функции.
- Чтобы создать описание пользовательской функции, достаточно один раз запустить ранее созданный модуль. Теперь при вызове пользовательской функции (или SHIFT+F3) отображается описание возвращаемого результата и переменной:
Описание функций является необязательным. Они необходимы в тех случаях, когда пользовательские функции будут часто использоваться другими пользователями.
Примеры использования пользовательских функций, которых нет в Excel
Пример 1. Рассчитайте сумму отпускных для каждого работника, проработавшего на предприятии не менее 12 месяцев, исходя из суммы общей заработной платы и количества выходных дней в году.
Просмотр исходной таблицы данных:
Каждому работнику полагается 24 оплачиваемых выходных дня S=N*24/(365-n), где:
- N – общая заработная плата за год;
- n — количество отпусков в году.
Давайте создадим пользовательскую функцию для расчета на основе этой формулы:
Пример кода:
Общественная функция Отпускные (суммЗп всегда, отпуск всегда) всегда
Если IsNumeric(праздники) = False или IsNumeric(summZp) = False Тогда
Отпускные = «Введены нечисловые данные»
Выходная функция
Отпуск
Функции представляют собой зависимость одного элемента (результата) от других элементов (аргументов, т.е находящихся внутри :-)). Это несколько понятно. Для использования любой функции в Excel ее необходимо ввести в виде формулы (нюансы описаны здесь) или в составе формулы в ячейке электронной таблицы. Последовательность, в которой должны располагаться используемые в формуле символы и аргументы, называется синтаксисом функции. Все функции используют одни и те же правила синтаксиса. Если вы нарушите эти правила, Excel выдаст сообщение о том, что в формуле ошибка и не будет вашим другом. Но поверьте, в функциях 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 или текущую дату в ячейках, вам нужно ввести следующие формулы: =PI() =TODAY() списки, функции даты и времени, финансовые, статистические, текстовые, математические, логические. Текстовые функции используются для обработки текста, а именно: поиска нужных символов, записи символов в строго определенном месте в тексте и т.д. Используя функции даты и времени, вы можете решить практически любую проблему, связанную с календарными датами или временем (например, рассчитать количество рабочих дней для любого периода времени). Булевы функции используются для создания сложных формул, которые в зависимости от выполнения определенных условий будут осуществлять различные виды обработки данных. Они особенно интересны, и о них мы поговорим в отдельной статье. Математические функции широко представлены в Excel, и некоторые из них я уже приводил в примерах. В распоряжении пользователя также имеется библиотека функций статистики, с помощью которой он может искать среднее значение, максимальные и минимальные элементы и т д рассчитать количество рабочих дней за любой период времени). Булевы функции используются для создания сложных формул, которые в зависимости от выполнения определенных условий будут осуществлять различные виды обработки данных. Они особенно интересны, и о них мы поговорим в отдельной статье. Математические функции широко представлены в Excel и некоторые из них я уже приводил в примерах. В распоряжении пользователя также имеется библиотека функций статистики, с помощью которой он может искать среднее значение, максимальные и минимальные элементы и т д рассчитать количество рабочих дней за любой период времени). Булевы функции используются для создания сложных формул, которые в зависимости от выполнения определенных условий будут осуществлять различные виды обработки данных. Они особенно интересны, и о них мы поговорим в отдельной статье. Математические функции широко представлены в Excel и некоторые из них я уже приводил в примерах. Пользователь также имеет библиотеку функций статистики, с помощью которого вы можете искать среднее значение, максимальные и минимальные элементы и т д. Математические функции широко представлены в Excel, и некоторые из них я уже приводил в примерах. У пользователя также есть библиотека функций статистики, с помощью которой он может искать среднее значение, максимальные и минимальные элементы и т д. Математические функции широко представлены в Excel и некоторые из них я уже приводил в примерах. У пользователя также есть библиотека функций статистики, с помощью которой он может искать среднее значение, максимальные и минимальные элементы и т д с помощью которого вы можете искать среднее значение, максимальные и минимальные элементы и т д. Математические функции широко представлены в Excel и некоторые из них я уже приводил в примерах. У пользователя также есть библиотека функций статистики, с помощью которой он может искать среднее значение, максимальные и минимальные элементы и т д с помощью которого вы можете искать среднее значение, максимальные и минимальные элементы и т д. Математические функции широко представлены в Excel и некоторые из них я уже приводил в примерах. У пользователя также есть библиотека функций статистики, с помощью которой он может искать среднее значение, максимальные и минимальные элементы и т д.
