Аргументы функции в excel как открыть. Основные встроенные функции Excel. Пример использования функции если в excel
В MS Excel содержится большое количество стандартных формул, называемых функциями. Встроенные функции Excel делятся на следующие категории: математические, логические, тригонометрические, статистические, финансовые, информационные, текстовые, функции даты и времени, инженерные, функции для работы с базой данных, функции просмотра и ссылок.
Встроенные функции Excel – это специальные, заранее созданные формулы, которые позволяют легко и быстро выполнять сложные вычисления. Они подобны специальным клавишам на некоторых калькуляторах, предназначенным для вычисления квадратных корней, логарифмов и статистических характеристик.
Некоторые функции, такие как СУММ (SUM), SIN (SIN) и ФАКТР (FACT), являются эквивалентами длинных математических формул, которые можно создать самим. Другие функции, такие как ЕСЛИ (IF) и ВПР (VLOOKUP), в виде формул реализовать невозможно.
В тех случаях, когда нужна информация о функциях, следует обращаться к справочной системе Excel, где находится полное описание каждой встроенной функции.
Быстро получить информацию о функциях можно также с помощью кнопки Вставка функции .
Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции, например, СУММ (SUM) или СРЗНАЧ (AVERAGE) описывает операцию, которую эта функция выполняет. Аргументы функции Excel задают значения или ячейки, используемые функцией. Например, в следующей формуле СУММ – это имя функции, а С3:С5 – ее единственный аргумент. Эта формула суммирует числа в ячейках С3, С4 и С5:
СУММ(С3:С5).
Аргумент функции заключен в круглые скобки. Открывающая скобка отмечает начало аргумента и ставится сразу после имени функции. В случае ввода пробела или другого символа между именем и открывающей скобкой в ячейке будет отображено ошибочное значение #ИМЯ? (#NAME?).
Некоторые функции, такие как ПИ (PI) и ИСТИНА (TRUE), не имеют аргументов. Даже если функция не имеет аргументов, она все равно должна содержать круглые скобки:
При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. Например, следующая формула указывает Excel, что необходимо перемножить числа в ячейках С1, С2 и С5:
ПРОИЗВЕД(С1;С2;С5).
В функции можно использовать до 30 аргументов, если при этом общая длина формулы не превосходит 1024 символов. Однако любой аргумент может быть диапазоном, содержащим произвольное число ячеек листа. Например, следующая функция имеет три аргумента, но суммирует числа в 29 ячейках (первый аргумент, А1:А5, ссылается на диапазон пяти ячеек от А1 до А5 и т.д.):
CУMM(A1:A5;C2:C10;D3:D17).
Комбинацию функций можно использовать для создания выражения, которое Excel сводит к единственному значению и интерпретирует его как аргумент. Например, в следующей формуле: SIN(A1*ПИ()) и 2*COS(A2*ПИ()) – это выражения, которые вычисляются и используются в качестве аргументов функции СУММ:
CУMM(SIN(A1*ПИ()); 2*COS(A2*ПИ())).
Типы аргументов
Аргумент – выражение, задающее значение при обращении к процедуре или функции, от которого зависит результат ее выполнения.
В качестве аргументов используются числовые, текстовые и логические значения, имена диапазонов, массивы и ошибочные значения. Некоторые функции возвращают значения этих типов и их в дальнейшем можно использовать в качестве аргументов в других функциях.
Аргументы функции могут быть числовыми. Например, функция СУММ в следующей формуле суммирует числа 327, 209 и 176:
СУММ(327;209;176).
Обычно числа вводятся в ячейки листа, которые будут использоваться, а затем применяются ссылки на эти ячейки в качестве аргументов в функциях.
В качестве аргумента функции могут использоваться текстовые значения. Например:
ТЕКСТ(ТДАТА();«Д МММ ГГГГ»).
В этой формуле второй аргумент функции ТЕКСТ «Д МММ ГГГГ», является текстовым и задает шаблон для преобразования десятичного значения даты, возвращаемого функцией ТДАТА(), в строку символов. Текстовый аргумент может быть строкой символов, заключенной в двойные кавычки, или ссылкой на ячейку, которая содержит текст.
Аргументы ряда функций могут принимать только логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE). Логическое выражение возвращает значение ИСТИНА или ЛОЖЬ в ячейку или формулу, содержащую это выражение. Например, первый аргумент функции ЕСЛИ (IF) в следующей формуле является логическим выражением, которое использует значение:
ЕСЛИ(А1=ИСТИНА, «Новая», «Старая»)& «цена».
Если значение в ячейке А1 равно ИСТИНА, то выражение А1=ИСТИНА возвращает значение ИСТИНА, и функция ЕСЛИ возвращает строку Новая, а формула в целом возвращает текстовое значение Новая цена.
В качестве аргумента функции можно указать имя диапазона. Например, если выбрать команду Присвоить подменю Имя меню Вставка и назначить диапазону С3:С6 имя Получено, то для вычисления суммы чисел в ячейках С3, С4, С5 и С6 можно использовать формулу:
СУММ(Получено).
Аргументом функции может быть массив. Некоторые функции, такие как ТЕНДЕНЦИЯ (TREND) и ТРАНСП (TRANSPOSE) требуют задания массива аргументов. Другие функции не требуют задания массива, но могут использовать такие аргументы. Массивы могут содержать числовые, текстовые или логические значения.
В одной функции можно использовать аргументы различных типов. Например, в следующей формуле аргументами являются имя диапазона (Группа 1), ссылка на ячейку (A3) и числовое выражение (5*3), а сама формула возвращает единственное числовое значение:
СРЗНАЧ(Группа1;А3;5*3).
Ввод функций в рабочем листе
Вводить функции в рабочем листе можно прямо с клавиатуры или с помощью команды Функция меню Вставка . При вводе функции с клавиатуры лучше использовать строчные буквы. Когда закончится ввод функции, необходимо нажать клавишу Enter или выделить другую ячейку. Excel изменит буквы в имени функции на прописные, если оно было введено правильно. Если буквы не изменяются, это означает, что имя функции введено неверно.
Если выделить ячейку и выбрать в меню Вставка команду Функция , Excel выведет окно диалога Мастер функций – шаг 1 из 2 , показанное на рис. 2.2. Открыть это окно можно также с помощью кнопки Вставка функции на стандартной панели инструментов.
В этом окне сначала выбирают категорию (или Полный алфавитный перечень) в списке Категория и затем в алфавитном списке Функция указывают нужную функцию. В качестве альтернативы после выбора категории можно щелкнуть на имени любой функции в списке Функция и нажать клавишу, соответствующую первой букве нужного имени. Чтобы ввести функцию, необходимо нажать кнопку ОK или клавишу Enter.
Excel введет знак равенства, имя функции и пару круглых скобок. Затем Excel откроет второе окно диалога Мастера функций (без строки заголовка).
Второе окно диалога Мастера функций содержит по одному полю для каждого аргумента выбранной функции. Если функция имеет переменное число аргументов, это окно диалога при вводе дополнительных аргументов расширяется. Описание аргумента, поле которого содержит точку вставки (курсор), выводится в нижней части окна диалога.
Рис. 2.2. Окно диалога Мастер функций – шаг 1 из 2
Справа от каждого поля аргумента отображается его текущее значение. Это очень удобно, когда используются ссылки или имена. Текущее значение функции отображается внизу окна диалога.
После нажатия кнопки ОК или клавиши Enter созданная функция появится в строке формул.
Некоторые функции, такие как ИНДЕКС (INDEX) имеют несколько форм (вариантов задания аргументов). Если выбрать такую функцию в списке Функция , Excel откроет дополнительное окно диалога Мастера функций , как на рис. 2.2, в котором можно выбрать нужную форму функции.
Перечень основных функций, расположенных по категориям с примерами выполнения приведен в табл. 2.1.
Таблица 2.1
Основные функции и их назначение
| Результат | Назначение |
||||
| Дата и время (всего 14) |
|||||
| СЕГОДНЯ() | Читает текущую дату из системных часов ПК |
||||
| ДАТА(2006;5;12) | Возвращает дату в числовом формате |
||||
| ВРЕМЯ(18;32;15) | Возвращает время в числовом формате |
||||
| Математические (всего 50) |
|||||
| Модуль числа |
|||||
| Синус числа (в радианах) |
|||||
| РАДИАНЫ(170) | Преобразует радианы в градусы |
||||
| ГРАДУСЫ(30) | Преобразует градусы в радианы |
||||
| Экспонента (е =2,71828182845904) |
|||||
| Натуральный логарифм |
|||||
| Логарифм числа по заданному основанию |
|||||
| КОРЕНЬ(256) | Квадратный корень |
||||
| Факториал |
|||||
| ОКРУГЛ(45,827;2) | Округляет до заданного числа десятичных разрядов |
||||
| 3,141592 65358979 | Число пи, округленное до 15 разрядов |
||||
| РИМСКОЕ(454) | Преобразует число в римский текстовый формат |
||||
| МОПРЕД(А1:СЗ) | Определитель матрицы (здесь матрица = -) |
||||
| Статистические (80 функций) | Для статистического анализа диапазонов данных |
||||
| Финансовые (53 функции) | Для типичных финансовых расчетов |
||||
| Инженерные | Устанавливаются дополнительно через пункт меню «Сервис-> Надстройки ->Пакет анализа» |
||||
| Обработка индексов и массивов |
|||||
| Работа с базой данных (всего 12) | Извлечение и обработка записей в базах данных |
||||
| Текстовые (всего 23) |
|||||
| ДЛСТР("Бабочка") | Длина текста |
||||
| ЗАМЕНИТЬ("Лампочка";3;2; "ст") | Ласточка | Замена символов внутри текста |
|||
| ПРАВСИМВ("Лампочка";5) | Правые символы слова |
||||
| НАЙТИ("ана";"Банан и ананас") | Ищет текст и возвращает найденную позицию |
||||
| ПОДСТАВИТЬ("ананас";"ан";"с") | Заменяет один текст другим |
||||
| СЦЕПИТЬ("Само";"лет") | Сцепляет слова |
||||
| Логические (всего 6) |
|||||
| ИЛИ(ИСТИНА;ЛОЖЬ; ЛОЖЬ) | Логическое ИЛИ |
||||
| И(ИСТИНА;ЛОЖЬ) | Логическое И |
||||
| НЕ(ИСТИНА) | Логическое НЕ |
||||
| EСЛИ(F1>5;10;5) | 10 (здесь F1=7); | Проверяет условие и возвращает одно из двух значений |
|||
| ЕНЕТЕКСТ(155) | Если не текст, возвращает логическое значение ИСТИНА |
||||
| ЕЧИСЛО("Текст") | Если число, возвращает логическое значение ИСТИНА |
||||
Функции можно набирать с клавиатуры, но легче создавать с помощью Мастера функций . Он запускается кнопкой fx на Панели инструментов. Используя Мастер функций , можно избежать ошибок в набираемой формуле и получить подсказку по назначению и формату функции и ее аргументов.
Пример . В последовательных ячейках Al, A2, A3 записаны прилагательные в единственном числе. В столбце В требуется получить по два символа окончания слов из столбца А, а в столбце С получить прилагательные во множественном числе, используя текстовые функции Excel (рис. 2.3).
Рис. 2.3. Текстовые функции Excel
Заполнение столбца В.
1. Встаньте на ячейку В1 и нажмите кнопку fx.
2. На первом шаге в окне Мастера функций выберите группу Текстовые, функцию ПРАВСИМВ и нажмите ОК (рис. 2.4).
Рис. 2.4. Окно Мастер функций
3. На втором шаге в окне Мастера функций введите адрес обрабатываемой ячейки (поле Текст ) и число извлекаемых символов (рис. 2.5).
Рис. 2.5. Ввод адреса обрабатываемой ячейки
После нажатия ОК в ячейку В1 автоматически будет записана функция =ПРАВСИМВ(А1;2), которая извлекает (вычисляет) из ячейки А1 два правых символа. Обратите внимание – в правой части каждого поля показан результат ввода.
4. Размножьте формулу из ячейки В1 в ячейки В2 и ВЗ, потянув за угол ячейки В1.
Заполнение столбца С:
1. Встаньте на ячейку С1 и нажмите кнопку fx.
2. На первом шаге в окне Мастера функций выберите Текстовые, функцию ПОДСТАВИТЬ и нажмите ОК.
3. В следующем окне мастера функций введите адрес ячейки с обрабатываемым текстом (А1), заменяемый текст (В1) и новый текст, как показано на рисунке на следующей странице. После нажатия ОК в ячейке С1 автоматически сформируется формула =ПОДСТАВИТЬ(А1; В1; «ые»), которая подставляет окончание «ые» в слово «Красный» вместо букв «ый».
4. Размножьте формулу из ячейки С1 в ячейки С2 и СЗ, потянув за угол ячейки С1.
Запись прилагательных с окончанием «ый» в единственном числе в столбце А, в столбце С они будут преобразовываться во множественное число (рис. 2.6).
Рис. 2.6. Окно преобразования во множественное число
Функция в Excel – это предустановленная формула, которая выполняет вычисления, используя заданные значения в определенном порядке. В Excel используется множество различных функций на все случаи жизни. С помощью функций можно ускорять выполнение задач, упрощать формулы и реализовывать вычисления, которые невозможно было бы выполнить без их использования. В данном уроке Вы узнаете, что представляет собой функция, какой у нее синтаксис и аргументы.
Excel содержит ряд простых функций, которые могут пригодиться для быстрого нахождения суммы, средних величин, максимальных и минимальных значений, а также для подсчета данных. Чтобы правильно использовать функции, вам необходимо понять их синтаксис, т.е. правило записи.
Синтаксис функций в Excel
Для корректной работы, функция должна быть написана в определенной последовательности, которая называется синтаксис. К базовому синтаксису функции относятся знак равенства (=), имя функции (например, СУММ) и один или более аргументов. Аргументы содержат информацию, которую необходимо вычислить. В следующем примере функция суммирует значения в диапазоне A1:A20.
В Excel существуют функции, которые не содержат ни одного аргумента. К примеру, функция СЕГОДНЯ() возвращает текущую дату из системного времени вашего компьютера.
Работа с аргументами
Аргументы могут ссылаться как на отдельные ячейки, так и на диапазоны ячеек и должны быть заключены в круглые скобки. В функциях Excel можно использовать один аргумент или несколько, в зависимости от синтаксиса.
Например, функция =СРЗНАЧ(B1:B9) будет вычислять среднее значение в диапазоне ячеек B1:B9. Эта функция содержит только один аргумент.
Несколько аргументов должны быть разделены точкой с запятой. Например, функция =СУММ(A1:A3; C1:C2; E2) суммирует значения всех ячеек в трех аргументах.
На занятии №4 мы уже делали первое знакомство с функциями Excel. Теперь пришло время более тщательно изучить этот мощный инструментарий электронных таблиц.
Функции Excel - это специальные, заранее созданные формулы, которые позволяют легко и быстро выполнять сложные вычисления. Их можно сравнить со специальными клавишами на калькуляторах, предназначенных для вычисления квадратных корней, логарифмов и проч.
Excel имеет несколько сотен встроенных функций, которые выполняют широкий спектр различных вычислений. Некоторые функции являются эквивалентами длинных математических формул, которые можно сделать самому. А некоторые функции в виде формул реализовать невозможно.
Синтаксис функций
Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции, например СУММ, - описывает операцию, которую эта функция выполняет. Аргументы задают значения или ячейки, используемые функцией. В формуле, приведенной ниже: СУММ - имя функции; В1:В5 - аргумент. Данная формула суммирует числа в ячейках В1, В2, В3, В4, В5.
=СУММ(В1:В5)
Знак равенства в начале формулы означает, что введена именно формула, а не текст. Если знак равенства будет отсутствовать, то Excel воспримет ввод просто как текст.
Аргумент функции заключен в круглые скобки. Открывающая скобка отмечает начало аргумента и ставится сразу после имени функции. В случае ввода пробела или другого символа между именем и открывающей скобкой в ячейке будет отображено ошибочное значение #ИМЯ? Некоторые функции не имеют аргументов. Даже в этом случае функция должна содержать круглые скобки:
=С5*ПИ()
Использование аргументов
При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. Например, следующая формула указывает, что необходимо перемножить числа в ячейках А1, А3, А6:
=ПРОИЗВЕД(А1;А3;А6)
В функции можно использовать до 30 аргументов, если при этом общая длина формулы не превосходит 1024 символов. Однако любой аргумент может быть диапазоном, содержащим произвольное число ячеек листа. Например:
=СУММ(А2:А5;В4:В8)
Типы аргументов
В приведенных ранее примерах все аргументы были ссылками на ячейки или диапазоны. Но в качестве аргументов можно также использовать числовые, текстовые и логические значения, имена диапазонов, массивы и ошибочные значения. Некоторые функции возвращают значения этих типов, и их в дальнейшем можно использовать в качестве аргументов в других функциях.
Числовые значения
Аргументы функции могут быть числовыми. Например, функция СУММ в следующей формуле суммирует числа 24, 987, 49:
=СУММ(24;987;49)
Текстовые значения
В качестве аргумента функции могут использоваться текстовые значения. Например:
=ТЕКСТ(ТДАТА();"Д МММ ГГГГ")
В этой формуле второй аргумент функции ТЕКСТ является текстовым и задает шаблон для преобразования десятичного значения даты, возвращаемого функцией ТДАТА(NOW), в строку символов. Текстовый аргумент может быть строкой символов, заключенной в двойные кавычки, или ссылкой на ячейку, которая содержит текст.
Логические значения
Аргументы ряда функций могут принимать только логические значения ИСТИНА или ЛОЖЬ. Логическое выражение возвращает значение ИСТИНА или ЛОЖЬ в ячейку или формулу, содержащую это выражение. Например:
=ЕСЛИ(А1=ИСТИНА;"Повышение";"Понижение")&" цены"
В качестве аргумента функции можно указать имя диапазона. Например, если диапазону ячеек А1:А5 присвоено имя "Дебет" (Вставка-Имя-Присвоить), то для вычисления суммы чисел в ячейках с А1 по А5 можно использовать формулу
=СУММ(Дебет)
Использование различных типов аргументов
В одной функции можно использовать аргументы различных типов. Например:
=СРЗНАЧ(Дебет;С5;2*8)
Функции представляют собой зависимость одного элемента (результата) от других элементов (аргументов,.. тех, что внутри:-)). Это как бы понятно. Для того чтобы использовать какую-либо функцию в , следует ввести ее как формулу (нюансы описаны ) или как часть формулы в ячейку рабочего листа.
Последовательность, в которой должны располагаться применяемые в формуле символы и аргументы, называется синтаксисом функции . Все функции используют одинаковые правила синтаксиса. Если нарушить эти правила, то Excel выдаст сообщение о том, что в формуле имеется ошибка и не будет с вами дружить. Но поверьте, в функциях Excel все достаточно однотипно и разобравшись один раз, на одной-двух функциях, в остальных случаях все будет достаточно просто.
Правила синтаксиса при записи функций
Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства, как это имеет место в начале любой формулы. Я об этом уже говорил в предыдущих статьях , но не грех ещё повторить.
После этого вводится имя функции и сразу за ним – список аргументов в круглых скобках. Аргументы отделяются друг от друга точкой с запятой «;». Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов.
Заметьте, в записи функции обязательно должны присутствовать открывающая и закрывающая скобки, при этом нельзя вставлять пробелы между названием функции и скобками. В противном случае Excel выдаст сообщение об ошибке.
В качестве аргументов можно использовать числа, текст, логические значения, массивы, значения ошибок или ссылки. При этом параметры, задаваемые пользователем, должны иметь допустимые для данного аргумента значения.
Например, в приведенной ниже формуле осуществляется суммирование значений в ячейках В2, В3, В4, В5 и Е7, причем часть ячеек — от В2 до В5, представлены как непрерывный диапазон.
Рассмотрим работу функции ОКРУГЛ(арг1;арг2), которая возвращает число, округленное до заданного количества знаков после запятой, и имеет два аргумента:
арг1 – адрес ячейки с числом (или само число), которое нужно округлить;
арг2 – количество цифр после запятой у числа после округления.
Чтобы округлить число 2,71828, находящееся в ячейке A1, с точностью до одного, двух или трех знаков после запятой и записать результаты вычислений соответственно в ячейки B1, C1 и D1, необходимо действовать следующим образом.
- Ввести число 2,71828 в ячейку A1.
- Ввести в ячейки B1, C1 и D1 формулы следующего вида:
ОКРУГЛ(A1;1)
ОКРУГЛ(A1;2)
ОКРУГЛ(A1;3)
Аргументы могут быть как константами, так и функциями. Функции, которые являются аргументами другой функции, называются вложенными .
Например, просуммируем значения ячеек А1 и А2, предварительно округлив эти значения до двух десятичных знаков:
СУММ(ОКРУГЛ(A1;2);ОКРУГЛ(A2;2))
Здесь функция ОКРУГЛ является вложенной аж два раза, но это не страшно, в формулах Excel можно использовать до семи уровней вложенности функций.
Стоит отметить, что в Excel существуют функции, которые не имеют аргументов. Примерами таких функций являются ПИ (возвращает значение числа π, округленное до 15 знаков) или СЕГОДНЯ (возвращает текущую дату). При использовании подобных функций следует в строке формул сразу после названия функции ставить пустые круглые скобки без аргументов . Другими словами, чтобы получить в ячейках значение числа p или текущую дату, следует ввести формулы такого вида:
СЕГОДНЯ()
Типы функций Excel
Для удобства работы пользователя при построении формул функции в Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, финансовые, статистические, текстовые, математические, логические.
Текстовые функции используются для обработки текста, а именно: поиска нужных символов, записи символов в строго определенное место текста и т.д.
С помощью функций Даты и времени можно решить практически любые задачи, связанные с учетом календарных дат или времени (например, рассчитать число рабочих дней для любого промежутка времени).
Логические функции используются при создании сложных формул, которые в зависимости от выполнения тех или иных условий будут реализовывать различные виды обработки данных. Они особо интересны, и о них поговорим в отдельной статье.
В Excel широко представлены Математические функции и некоторые я уже привел в примерах.
В распоряжении пользователя также находится библиотека Статистических функций , при помощи которой можно осуществлять поиск среднего значения, максимального и минимального элементов и пр.