13 функций Excel, которые экономят часы работы — синтаксис, примеры, ошибки
Сколько бы ни писали о смерти Excel, в большинстве компаний именно он остаётся главным инструментом аналитики, бюджетирования и операционного управления. Ниже — 13 базовых функций, без которых не обходится ни один отчёт: для каждой синтаксис на русском и английском, живой пример прямо в карточке (можно подвигать значения и увидеть, как пересчитывается формула), сценарий применения и главная ловушка, на которой спотыкаются новички. В конце — мини-блок про современный набор функций Microsoft 365 (ПРОСМОТРХ, ФИЛЬТР, УНИК, ЕСЛИОШИБКА), хоткеи для работы с формулами и шпаргалка для печати.
Как читать карточки функций
Каждая карточка устроена одинаково: имя (русское и английское), категория, синтаксис с подсветкой обязательных и опциональных аргументов, сценарий применения в одной строке, живой пример в имитации листа Excel (можно нажать на пресеты или поменять значения), и в конце — главная ловушка. Если работаете в англоязычном Excel, имя функции и синтаксис в скобках — для вас.
Разделитель аргументов В русской локализации Excel аргументы в формулах разделяются точкой с запятой ;, в англоязычной — запятой ,. В статье используется точка с запятой; при переключении локализации Excel сам подставляет нужный разделитель.
Карта 13 функций по категориям
Агрегация
СУММ
СРЗНАЧ
ПРОИЗВЕД
СУММПРОИЗВ
Границы и ранги
МИН / МАКС
НАИМЕНЬШИЙ
НАИБОЛЬШИЙ
Логика
ЕСЛИ
Поиск
ВПР
ИНДЕКС
Условная агрегация
СУММЕСЛИ
СУММЕСЛИМН
Этот список — обязательный минимум для работы с любыми табличными данными. Аналитику, маркетологу, бухгалтеру, проджект-менеджеру и финансисту в любой день недели понадобится 3–5 из них одновременно.
1. СУММ
1СУММSUMагрегация
=СУММ(число1; [число2]; …)
Когда: сложить значения в диапазоне или нескольких отдельных ячейках.
D1fx=СУММ(A1:C1)
A
B
C
D
1
10
25
37
72
D1 = 10 + 25 + 37 = 72
Ловушка: текстовые ячейки внутри диапазона СУММ игнорируются молча — формула не выдаст ошибку, но цифра может оказаться меньше, чем вы ждали. Если рядом с числами видите выровненные по левому краю «числа» — это текст; их нужно конвертировать через ЗНАЧЕН() или умножить на 1.
2. ПРОИЗВЕД
2ПРОИЗВЕДPRODUCTагрегация
=ПРОИЗВЕД(число1; [число2]; …)
Когда: умножить несколько чисел или диапазон — типично при расчёте сложного процента, цепочки коэффициентов, накопленной инфляции.
D1fx=ПРОИЗВЕД(A1:C1)
A
B
C
D
1
1,07
1,09
1,12
1,3063
D1 = 1,07 × 1,09 × 1,12 ≈ 1,3063 (накопленная инфляция за 3 года ≈ 30,6%)
Ловушка: пустая ячейка в диапазоне трактуется как 1 (не влияет), а ячейка с нулём обнуляет всю формулу — это часто ловят в моделях, где забыли заполнить коэффициент одного периода.
3. СРЗНАЧ
3СРЗНАЧAVERAGEагрегация
=СРЗНАЧ(число1; [число2]; …)
Когда: средний чек, средняя оценка, средний срок ответа поддержки за период.
F1fx=СРЗНАЧ(A1:E1)
A
B
C
D
E
F
1
8
9
7
10
6
8
F1 = (8 + 9 + 7 + 10 + 6) / 5 = 8,0
Ловушка: СРЗНАЧ игнорирует пустые ячейки, но учитывает нули. Если в выборке часть значений «не заполнены», но стоит ноль — он опустит среднее. Чтобы взять только заполненные положительные, используйте СРЗНАЧЕСЛИ(диапазон;">0").
4. СУММПРОИЗВ
4СУММПРОИЗВSUMPRODUCTскалярное произведение
=СУММПРОИЗВ(массив1; [массив2]; …)
Когда: взвешенная сумма (цена × количество, оценка × вес), подсчёт строк по нескольким условиям без массивных формул.
Ловушка: размеры массивов должны совпадать. СУММПРОИЗВ(A1:A3; B1:B5) вернёт #Н/Д или некорректный результат. Также удобный трюк: СУММПРОИЗВ((A1:A100="Москва")*(B1:B100>1000)) считает строки по двум условиям без перехода на массивные формулы.
5. МИН
5МИНMINграница
=МИН(число1; [число2]; …)
Когда: минимальная цена в прайсе, самая ранняя дата заказа, минимальный остаток на складе за период.
F1fx=МИН(A1:E1)
A
B
C
D
E
F
1
14
3
22
9
18
3
Ловушка: если в диапазоне есть текст или ошибка #Н/Д — МИН их игнорирует или возвращает ошибку. Если нужно учитывать пустые как ноль — оборачивайте в ЕСЛИОШИБКА(МИН(...);0).
6. МАКС
6МАКСMAXграница
=МАКС(число1; [число2]; …)
Когда: пиковая нагрузка на сервер, рекорд продаж за период, самая поздняя дата платежа в графике.
F1fx=МАКС(A1:E1)
A
B
C
D
E
F
1
14
3
22
9
18
22
Ловушка: МАКС возвращает значение, но не говорит, в какой ячейке оно встретилось. Для координаты используйте ПОИСКПОЗ(МАКС(A1:A100); A1:A100; 0) — вернёт номер строки.
7. НАИМЕНЬШИЙ
7НАИМЕНЬШИЙSMALLранг
=НАИМЕНЬШИЙ(массив; k)
Когда: второй по дешевизне поставщик, третий с конца сотрудник по выручке, нижний квартиль через k = 25% от длины массива.
B6fx=НАИМЕНЬШИЙ(A1:A5; 2)
A
B
1
1
2
3
3
6
4
5
5
10
6
k =
3
2
Ловушка:НАИМЕНЬШИЙ(...; 1) возвращает то же, что и МИН — но при дубликатах оба считают одинаковое значение. Чтобы найти второе уникальное значение по возрастанию, нужна формула посложнее или функция УНИК().
8. НАИБОЛЬШИЙ
8НАИБОЛЬШИЙLARGEранг
=НАИБОЛЬШИЙ(массив; k)
Когда: топ-3 клиентов по выручке, второй по чеку заказ месяца, верхний квартиль зарплат.
B6fx=НАИБОЛЬШИЙ(A1:A5; 2)
A
B
1
1
2
3
3
6
4
5
5
10
6
k =
6
2
Ловушка: та же, что у НАИМЕНЬШИЙ — повторы могут заставить «второе место» совпасть с «первым». Для топ-3 уникальных значений сначала УНИК(массив), потом НАИБОЛЬШИЙ.
9. ЕСЛИ
9ЕСЛИIFлогика
=ЕСЛИ(условие; если_истина; [если_ложь])
Когда: категоризация (новый / повторный клиент), флаг «выполнен план», переключение тарифа, любые вилки в финансовых моделях.
Базовый пример простой: =ЕСЛИ(A1>=100; "выполнен"; "недовыполнен"). Сила функции — во вложенности, она позволяет строить деревья решений прямо в ячейке. Поиграйте: введите три числа A22, A23, A24, и формула пройдёт по веткам.
Ловушка: 3+ вложенных ЕСЛИ становятся нечитаемыми. Альтернативы: ЕСЛИМН (IFS) для линейного перечисления вилок, ПЕРЕКЛЮЧ (SWITCH) для категорий, или таблица соответствия + ВПР, если веток больше 4–5.
Когда: подтянуть данные из справочника по ключу — цену по артикулу, должность по фамилии, валютный курс по дате.
Четвёртый аргумент: 0 или ЛОЖЬ — точное совпадение (нужно в 99% случаев), 1 или ИСТИНА — приблизительное (работает только на отсортированных диапазонах).
B6fx=ВПР(A6; A1:C4; 3; 0)
A
B
C
1
Иванов
МГУ
22
2
Петров
ВШЭ
28
3
Сидоров
МФТИ
35
4
Кузнецова
СПбГУ
30
6
Иванов
22
Ловушка: ВПР ищет ключ только в первом столбце таблицы и возвращает значение справа. Если ключ в третьем столбце, а нужно подтянуть из первого — ВПР не справится; используйте связку ИНДЕКС + ПОИСКПОЗ или новую функцию ПРОСМОТРХ (см. ниже).
11. ИНДЕКС
11ИНДЕКСINDEXпоиск
=ИНДЕКС(массив; номер_строки; [номер_столбца])
Когда: точечный доступ к значению по «координатам», особенно вместе с ПОИСКПОЗ (MATCH) — это самая гибкая замена ВПР, работающая «во все стороны».
E1fx=ИНДЕКС(A1:C4; 2; 1)
A
B
C
1
Иванов
МГУ
22
2
Петров
ВШЭ
28
3
Сидоров
МФТИ
35
4
Кузнецова
СПбГУ
30
Результат: Петров (пересечение 2 строки и 1 столбца)
Главная сила — в паре с ПОИСКПОЗ: =ИНДЕКС(B1:B4; ПОИСКПОЗ("Петров"; A1:A4; 0)) вернёт ВУЗ Петрова, и при этом таблица может быть какой угодно ширины, и ключ может быть в любом столбце.
Ловушка: если указать только один аргумент после массива, ИНДЕКС интерпретирует его как номер строки в одномерном массиве. Для двумерных таблиц всегда указывайте оба — строку и столбец.
Ловушка: у СУММЕСЛИМН диапазон суммирования стоит первым аргументом, а у СУММЕСЛИ — последним. Это самая частая опечатка: формула возвращает не то число, и долго не понятно, где ошибка. Лайфхак: всегда вставляйте формулу через помощник (вкладка «Формулы → Вставить функцию»), пока не запомните порядок.
Современный набор: что добавить к классике Microsoft 365
В подписке Microsoft 365 и в Excel 2021+ есть функции, которые радикально сокращают код в типовых задачах. Если они доступны в вашей версии — используйте их вместо ВПР, вложенных ЕСЛИ и массивных формул.
Когда: везде, где раньше был ВПР или связка ИНДЕКС+ПОИСКПОЗ. Ищет в любом направлении, возвращает значение или массив, имеет встроенный аргумент «если не найдено».
Пример: =ПРОСМОТРХ("Петров"; A1:A4; B1:B4; "не найден") — найдёт ВУЗ Петрова или вернёт сообщение, если такого нет. Никаких номеров столбцов и обёртки в ЕСЛИОШИБКА.
+ФИЛЬТРFILTERмассивы
=ФИЛЬТР(массив; условие; [если_пусто])
Когда: выгрузить все строки таблицы, удовлетворяющие условию — без сводных таблиц и без копипасты.
Пример: =ФИЛЬТР(A2:C100; B2:B100="Москва") вернёт все строки с Москвой как динамический массив. Размер результата подстраивается под данные.
+УНИКUNIQUEмассивы
=УНИК(массив; [по_столбцам]; [только_уникальные])
Когда: получить список уникальных значений колонки — городов, артикулов, клиентов. Заменяет «удалить дубликаты» в виде формулы, которая автоматически обновляется.
+ЕСЛИОШИБКАIFERRORлогика
=ЕСЛИОШИБКА(выражение; значение_если_ошибка)
Когда: закрыть #Н/Д от ВПР, #ДЕЛ/0! от деления, #ЗНАЧ! от неверного типа аргумента — чтобы отчёт не пестрел красными ошибками.
Пример: =ЕСЛИОШИБКА(ВПР(A1; справочник; 2; 0); "—") — если в справочнике ключа нет, вернёт прочерк вместо #Н/Д.
Хоткеи для работы с формулами
F2Войти в режим редактирования активной ячейки — курсор в конце формулы.
F4Переключить тип ссылки: A1 → $A$1 → A$1 → $A1. Самый частый хоткей при копировании формул.
F9Вычислить выделенный фрагмент формулы прямо в строке формул — мощный способ отладки сложных выражений.
Ctrl+Shift+EnterВ классическом Excel — ввод массивной формулы (старого образца). В современном Excel чаще не нужно.
Ctrl+EnterПодтвердить ввод, не сходя с активной ячейки — удобно для проверки формулы без потери позиции.
Alt+=Автоматически вставить СУММ для соседнего диапазона — экономит несколько кликов на каждом отчёте.
Ctrl+`Показать формулы вместо значений на всём листе — полезно при проверке чужой модели.
Ctrl+D / Ctrl+RПротянуть формулу вниз / вправо без перетаскивания мышью.
ВПР vs ИНДЕКС vs ПРОСМОТРХ
Три инструмента под одну задачу «найди значение по ключу». Различия — в гибкости и поведении в крайних случаях.
Свойство
ВПР
ИНДЕКС + ПОИСКПОЗ
ПРОСМОТРХ
Поиск только слева направо
да, ключ в первом столбце
в любом направлении
в любом направлении
Чувствителен к перестановке столбцов
да (по номеру)
нет (по диапазону)
нет (по диапазону)
«Если не найдено»
через ЕСЛИОШИБКА
через ЕСЛИОШИБКА
встроенный аргумент
Возврат массива (нескольких значений)
нет
да, отдельной формулой
да, нативно
Доступность
везде
везде
Excel 2021+ и Microsoft 365
Скорость на больших данных
медленнее
быстрее
сопоставимо с ИНДЕКС
Рекомендация: если работаете в современном Excel — ПРОСМОТРХ; если совместимость с Excel 2016 и старее важна — ИНДЕКС+ПОИСКПОЗ; ВПР оставьте для быстрых одноразовых формул, где данные точно не «поплывут».
СУММ vs СУММПРОИЗВ — кого выбрать
СУММ — когда
Просто сложить значения. Все числа уже в нужной форме, веса не нужны. Часть итога в P&L, общий объём заказов, шапка отчёта.
СУММПРОИЗВ — когда
Нужны веса (выручка как Σ цена × количество), либо одной формулой посчитать строки по 2–3 условиям без массивной формулы и без сводной.
Типичные ошибки
Цифры, которые на самом деле текст
Импорт из 1С, выгрузка из веб-сервиса, копипаст из PDF — частая причина «потерянных» строк в СУММ. Признак: выравнивание по левому краю и кавычка слева сверху. Лечение: умножить столбец на 1 через спецвставку или функция ЗНАЧЕН().
Жёсткие ссылки вместо относительных
Формулу с $A$1 протянули — все ячейки ссылаются на одну. Запомните F4: переключатель видов ссылки решает 90% таких проблем за пару секунд.
Не та локализация в критерии
СУММЕСЛИ(...; ">=1 000"; ...) с пробелом-разделителем тысяч не сработает, потому что Excel прочитает критерий как текст. Используйте число без форматирования: ">=1000".
Циклические ссылки
Формула в B5 ссылается на A5, а в A5 — на B5. Excel показывает 0 и предупреждение в строке состояния. В моделях с итерациями это иногда нужно, но по умолчанию — это ошибка логики, ищите цепочку через «Формулы → Влияющие ячейки».
Кириллица и латиница в названиях
Заголовок «Москва» с латинской «о» внутри визуально неотличим от «Москва» с кириллической, но критерий по тексту не сработает. Проверка: =ДЛСТР(A1) и быстрая визуальная проверка через увеличение шрифта.
Слепая вера в автозаполнение
Excel «угадывает» формат при заполнении: «01.02» может стать датой 1 февраля. При работе с артикулами, телефонами, номерами договоров заранее переводите столбец в «Текстовый» формат.
В русской локализации десятичный разделитель — запятая (1,5), в английской — точка (1.5). При импорте файлов из англоязычных систем часто нужно сначала прогнать «найти и заменить» через CSV-параметры, иначе «1.5» интерпретируется как дата 1 мая.
Что делать с зависшим файлом, в котором тысячи формул?
Сначала проверьте «Формулы → Параметры вычислений → Ручной режим» (Manual). Тогда Excel пересчитывает только при F9. На большой модели это спасает работоспособность; перед сдачей переключайтесь обратно в «Автоматически».
Можно ли назвать диапазон, чтобы не писать A1:C4 каждый раз?
Да: «Формулы → Имена → Диспетчер имён», или быстрее — выделить диапазон и ввести имя в адресной строке слева. Дальше можно писать =ВПР(A6; сотрудники; 3; 0) — модель становится самодокументируемой.
Когда переходить с формул на сводные таблицы или Power Query?
Если данных больше 50 тыс. строк и/или одну и ту же агрегацию вы пересобираете каждую неделю — формулы становятся хрупкими. Сводные таблицы решают агрегацию, Power Query — трансформацию и объединение источников; вместе они закрывают 80% задач BI на уровне отчёта.
Почему ВПР возвращает не то значение, хотя ключ есть?
Чаще всего — забыли четвёртый аргумент или поставили 1/ИСТИНА. В этом режиме ВПР ищет «приближённое» совпадение и работает только на отсортированных диапазонах. Для точного поиска ставьте 0 (ЛОЖЬ) явно.
Сколько функций реально нужно знать аналитику?
Эти 13 + ЕСЛИОШИБКА, ПРОСМОТРХ (или ИНДЕКС+ПОИСКПОЗ), УНИК, ФИЛЬТР и базовые текстовые (ЛЕВСИМВ, ПРАВСИМВ, ПСТР, СЦЕП, ТЕКСТ) закрывают примерно 90% задач. Дальше — функции дат, статистики и финансов под конкретную роль.
Дисклеймер: синтаксис функций и хоткеи приведены для актуальных версий Microsoft Excel (Microsoft 365, Excel 2021). В Excel 2016 и старее часть функций (ПРОСМОТРХ, ФИЛЬТР, УНИК, ЕСЛИМН) недоступна — используйте классические альтернативы. Аналоги в Google Таблицах в большинстве случаев работают идентично, но имена функций — английские.