В Excel есть тысячи встроенных функций. Если знать хотя бы часть из них, можно здорово экономить время при обработке данных и построении отчетов.
В этой статье собрали топ-10 базовых функций, которые чаще всего используются в Эксель.
#1. SUM
Синтаксис: =SUM(число1; [число 2]; ...)
число1 — обязательный аргумент.
Функция дает возможность найти сумму отдельных числовых значений, диапазонов, ссылок на ячейки с числовыми значениями или сумму всех этих 3-х видов. Часто используется в подбивании итоговых значений строк или столбцов при формировании отчетов.
Примеры использования
Допустим, у вас есть массив числовых значений и вам нужно посчитать сумму некоторых из них. Используя функцию SUM, в области аргументов функции мы подставляем нужные ссылки на ячейки и получаем ответ — 322.
В кое-каких случаях в массивах не находятся значения, которые нужно так же просуммировать, и вместо ссылок можем добавить свои числа. Ответ в этом случае — 293.
Так как функция работает не только с числовыми значениями, а и с целыми диапазонами, можно найти сумму всего диапазона.
Можно не ограничивать себя в количестве значений, которые нужно просуммировать, а посчитать все значения в столбцах или строках. К примеру, просуммируем все значения в первых двух столбцах.
Если в одной или нескольких ячейках диапазона окажется не числовое выражение, а текстовое, Excel будет приравнивать эти значения к нулю.
#2. COUNT
Синтаксис: =COUNT(значение1; [значение2]; ...
значение1 — обязательный аргумент.
С помощью этой функции можно подсчитать ячейки, которые содержат только числовые значения в списке аргументов. Зачастую используется в расчетах средних значений, когда использование функции AVERAGE в Excel нецелесообразно.
Следующая формула возвращает количество ячеек в диапазоне A1:E1, которые содержат числа.
Как мы видим, в нашем списке аргументов диапазон из пяти значений, но функция вернет три, ведь числовые значения содержатся только в столбцах A, B, C. В столбце D — текстовое выражение, а Е — незаполненная ячейка.
Близкие по применению функции:
=COUNTA(значение1;[значение2];…) — считает количество непустых значений в перечне аргументов.
=COUNTBLANK(диапазон) — считает количество пустых значений в указанном диапазоне.
#3. MIN
Синтаксис: =MIN(число 1; [число 2]; ...)
число1 — обязательный аргумент.
Функция позволяет найти минимальное числовое значение в указанном списке аргументов. Часто используется при построении финансовой отчетности, когда нужно определить дату начала периода отчета, минимальный чек покупки и другие параметры.
Пример
Допустим, у нас есть диапазон чисел и текстовых выражений, и нужно найти минимальное значение.
Например, минимальное значение среди двух выбранных диапазонов A1:D2, A4:D4 и числа 54 будет 2. Пустые поля и текстовые выражения функцией исключаются и в расчетах не используются.
Близкие по назначению функции
=MINA(значение1;[значение2];…) — находит минимальное значение в списке аргументов, при этом текстовые и ложные логические выражения равняются к нулю, а логическое выражение "TRUE" в ячейке равняется 1.
=MAX(число1;[число2];…) — находит максимальное значение в списке аргументов, при этом текстовые и пустые выражения игнорируются.
=MAXA(значение1;[значение2];…) — находит максимальное значение в списке аргументов, при этом текстовые и ложные логические выражения приравниваются к нулю, а логическое выражение "TRUE" в ячейке равняется 1.
#4. AVERAGE
Синтаксис: =AVERAGE(число1;[число2];…)
число1 — обязательный аргумент.
С помощью этой функции можно найти среднее арифметическое отдельных числовых значений, диапазонов, ссылок на ячейки с числовыми значениями или же среднее этих 3-х видов. Вычисляется путем суммирования всех чисел и делением суммы на количество этих же чисел. Текстовые и логические значения в диапазоне игнорируются.
Допустим, у нас есть диапазон из 6 ячеек: 4 из них заполнены числами, включая 0, одно значение — текстовое и еще одно — пустое. Функция просуммирует только числовые и поделит сумму на общее количество числовых — 4.
В результате мы получим среднее, равное 4. Давайте проверим формулой:
(4 + 5 + ТЕКСТ + 7 + 0 + ПУСТОЕ ЗНАЧЕНИЕ) / 4 = 16 / 4 = 4
ТЕКСТ и ПУСТОЕ ЗНАЧЕНИЕ игнорируются.
#5. ROUND
Синтаксис: =ROUND (число; число_розрядов)
число — аргумент.
число_разрядов — до какого разряда округляется число.
Функция ROUND применяется для округления действительных чисел до требуемого количества знаков после запятой и возвращает округленное значение согласно математическому правилу округления.
К примеру, для округления числа 2,57525 до 2-х символов после запятой можно ввести формулу =ROUND(2,57525;2), которая вернет значение 2,58. Эта функция часто используется при построении балансовых и других видов отчетности.
#6. IF
Синтаксис: =IF(логическое_выражение;значение_если_истина;значение_если_ложь)
логическое_выражение — условие, выполнение которого проверяет оператор.
значение_если_истина — в случае, если условие окажется правдивым, будет возвращено это значение.
значение_если_ложь — в случае, если условие окажется неправдивым, будет возвращено это значение.
Эта функция одна из самых известных в работе с Эксель. В ней проверяются числа и/или текст, функции, формулы. Когда значения отвечают заданному условию, появляется запись из поля "значение_если_истина", не отвечают — "значение_если_ложь". Часто используется для распределения выражений на категории, группы.
Функция поддерживает использование операторов сравнения:
= (равно),
< (меньше),
<= (меньше или равно),
> (больше),
>= (больше или равно),
<> (не равно).
Также часто используют эту функцию в связке с логическими операторами AND, OR. Рассмотрим несколько примеров.
Пример 1
Предположим, что у нас есть таблица стоимости продуктов питания за 1 кг (л), и задача заключается в том, чтобы разбить товары на две группы в зависимости от цены:
– до 500
– больше 500
Для решения этой задачи используется функция IF.
В ячейке C2 следующая формула: =IF(B2<=500;"до 500";"больше 500"). Она расшифровывается так: если в указанной ячейке число будет меньше или равно 500 (1 на скриншоте), тогда функция должна вернуть текстовое выражение "до 500" (2 на скриншоте), если оно окажется другим — "больше 500" (3 на скриншоте).
Протянем эту формулу вниз и проверим работоспособность.
Пример 2
Предположим, что в таблице выше нам необходимо каждому товару присвоить категорию по цене: "высокая стоимость" и "низкая стоимость".
В ячейке D2 была прописана следующая формула: =IF(С2="до 500";"низкая стоимость";"высокая стоимость"). Расшифровка: если в указанной ячейке текстовое значение равняется "до 500", тогда функция должна вернуть текстовое выражение "низкая стоимость", если оно окажется другим — "высокая стоимость".
Обратите внимание: когда проверяете условия на текстовое значение, это значение в кавычках необходимо брать в еще одни кавычки. Посмотрим на результат, протянув формулу до конца таблицы.
#7. VLOOKUP
Синтаксис: =VLOOKUP(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
искомое_значение — значение, которое необходимо найти в столбце с данными. Аргументы бывают числовыми и текстовыми. Искомое значение должно находиться в крайнем левом столбце диапазона ячеек указанной таблицы.
таблица — ссылка на диапазон ячеек. В левом столбце выполняется поиск искомого значения, а из столбцов правее выводится соответствующее значение. Левый столбец еще называют ключевым. Если в таблице не окажется искомого значения, вернется ошибка #N/A.
номер_столбца — номер столбца таблицы, согласно которому нужно вывести результат.
[интервальный_просмотр] — необязательный аргумент. Принимает два значения: TRUE и FALSE. TRUE устанавливается по умолчанию, и функция предполагает, что левый столбец таблицы отсортирован по возрастанию в алфавитном порядке. Если в этом аргументе — TRUE, функция ищет ближайшее к искомому или совпадающее с ним значение, FALSE — ищет стопроцентное совпадение с искомым значением.
Это функция, которая упростит работу с большими массивами данных и несколькими таблицами. Будет полезной, если нужно подтянуть соответствующий критерию столбец из другой таблицы (например, группа, категория).
Пример
Вернемся к нашей таблице из пункта 6 (IF). Предположим, что каждому предмету покупки присвоили группу, к которой он относится (овощи, фрукты, кондитерские изделия и др.), и у нас есть справочник соответствий, но в другой таблице. В массивах данных, состоящих из тысяч строк, делать это вручную очень долго, а VLOOKUP справится за долю секунды.
В ячейке E2 пишем формулу: =VLOOKUP(A2;$G$2:$H$20;2;FALSE). Обязательно ставим знаки фиксации диапазона $ — в случае их отсутствия при протягивании формулы вниз диапазон ячеек таблицы также будет смещаться вниз вместе с искомым значением.
Как прочитать формулу простым языком: найти значение из ячейки A2 в левом столбце таблицы G2:H20 и вывести соответствующее найденному искомому_значению выражение из столбца 2 этой таблицы, при этом искомое значение должно совпадать с данными в левом столбце таблицы (аргумент FALSE).
Протягиваем формулу вниз — и выводится результат.
#8. IFERROR
Синтаксис: =IFERROR(значение;значение_если_ошибка)
значение — аргумент, который проверяется на наличие ошибки
значение_если_ошибка — значение, которое возвращается, если произошла ошибка.
Эта функция проверяет аргумент на равенство значениям ошибок #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? или #NULL!. Если выражение в ячейке, которая проверяется, содержит ошибку, функция вернет значение, которое определено в этом случае. Если ошибки нет — результат расчетов или данные ячейки. Часто используется при делении на ноль.
Пример
Возьмем таблицу со значениями выручки и количеством проданных единиц. Нужно найти выручку за одну единицу продукции. Для решения этой задачи выручку делим на количество единиц.
Excel ругается, потому что в одном выражении выполняется деление на ноль. Поэтому проверяем на ошибку в соседнем столбце.
#9. TRIM
Синтаксис: =TRIM(текст)
текст — текстовое значение, из которого необходимо удалить лишние пробелы.
Эту функцию используют для обработки текстов из разных источников. Когда в этих текстах есть лишние пробелы, они удаляются.
Пример
Формула =TRIM(" Выручка с начала года ") вернет "Выручка с начала года", убрав из выражения лишние пробелы.
С помощью функции можно удалить из текста лишние знаки пробела (код символа — 32). В некоторых случаях в тексте может присутствовать знак неразрывного пробела (код — 160). Чтобы удалить эти лишние знаки, нужно использовать следующую формулу: =TRIM(SUBSTITUTE(A1;CHAR(160);CHAR(32))). В таком случае функция сначала заменяет все знаки неразрывного пробела на пробел, а после этого удаляет лишние пробелы.
#10. CONCATENATE
Синтаксис: =CONCATENATE(текст1;[текст2];[текст3];…)
текст1 — обязательный аргумент.
Для объединения значений из разных ячеек в одну используется функция CONCATENATE. Также можно применять аналог — & (амперсанд). Функция часто используется для объединения данных с нескольких столбцов.
Пример
Допустим, у нас есть таблица сотрудников с разделенными ФИО, должностью и возрастом, а в отчете нужно отобразить все эти значения в одной ячейке. Для выполнения этой задачи используем функцию так:
Хотите получать дайджест статей?
