Полный справочник по формулам Excel: от базы до продвинутых функций

Чтобы эффективно работать в Excel, достаточно освоить около 20 ключевых функций, которые покрывают 90% повседневных задач: от простых сумм (SUM) и условий (IF) до мощных инструментов работы с массивами (FILTER, XLOOKUP). Знание этих формул позволяет автоматизировать рутину, избегать ошибок ручного ввода и быстро анализировать большие объемы данных без использования макросов.

Базовые функции для ежедневных расчетов

Эти инструменты составляют фундамент любой таблицы. Они используются для агрегации данных и простейшей статистики.

• =SUM(диапазон) — суммирует числа. Идеально для итогов бюджета или продаж.
• =AVERAGE(диапазон) — вычисляет среднее арифметическое. Полезно для анализа средней цены или времени выполнения задач.
• =MIN(диапазон) / =MAX(диапазон) — находят минимальное и максимальное значения. Помогают быстро выявить выбросы в данных.
• =COUNT(диапазон) — считает только ячейки с числами.
• =COUNTA(диапазон) — считает все заполненные ячейки (текст, числа, даты). Используется для подсчета количества сотрудников, заказов или позиций.
• =TEXTJOIN("разделитель"; ИСТИНА; диапазон) — современная замена CONCATENATE. Объединяет текст из множества ячеек, игнорируя пустые, если указан второй аргумент ИСТИНА.

Математические функции и условная агрегация

Когда простых сумм недостаточно и требуется выборка по критериям, на помощь приходят условные функции.

• =SUMIF(диапазон_условия; условие; [диапазон_суммы]) — суммирует значения, если они соответствуют одному критерию.
  • Пример: Сумма продаж только по менеджеру "Иванов".
• =SUMIFS(диапазон_суммы; диапазон1; условие1; диапазон2; условие2; ...) — суммирует по нескольким условиям одновременно. Аргументы здесь переставлены: сначала что суммируем, потом условия.
  • Пример: Сумма продаж "Иванова" за "Январь" по товару "Ноутбук".
• =ROUND(число; знаков), =ROUNDUP, =ROUNDDOWN — округление до заданного количества знаков, всегда вверх или всегда вниз. Критично для финансовых отчетов, чтобы избежать расхождений в копейках.
• =MOD(число; делитель) — возвращает остаток от деления. Часто используется для создания циклических паттернов (например, подсветка каждой второй строки) или проверки четности.
• =POWER(число; степень) или оператор ^ — возведение в степень.

Логические функции: автоматизация принятия решений

Логика позволяет таблице «думать» и выдавать разные результаты в зависимости от входящих данных.

• =IF(условие; значение_если_истина; значение_если_ложь) — базовое ветвление.
  • Пример: =IF(A2>100; "Бонус"; "Нет").
• =IFS(условие1; результат1; условие2; результат2; ...) — замена громоздким вложенным IF. Проверяет условия по порядку и возвращает результат первого истинного.
  • Пример: Оценка результата: =IFS(A2>=90;"Отлично"; A2>=70;"Хорошо"; A2<70;"Плохо").
• =AND(условие1; условие2) / =OR(условие1; условие2) — комбинируют условия внутри IF.
• =SWITCH(выражение; значение1; результат1; значение2; результат2; ...) — удобна, когда нужно сопоставить одно значение со списком вариантов (например, код региона -> название города).
• =IFERROR(формула; значение_при_ошибке) — скрывает технические ошибки (#Н/Д, #ДЕЛ/0!) и заменяет их на понятный текст или ноль.

Новые динамические функции (Excel 365 / 2021+)

Эти функции совершили революцию в Excel, позволив одной формуле возвращать сразу массив значений, который автоматически «разливается» (spill) по соседним ячейкам.

Поиск и фильтрация

• =XLOOKUP(искомое; массив_поиска; массив_результата; [если_не_найдено]) — универсальная замена VLOOKUP и HLOOKUP. Ищет в любом направлении, не ломается при вставке столбцов и умеет сообщать, если значение не найдено.
• =FILTER(массив; условие; [если_пусто]) — выгружает список данных, соответствующих условию, в отдельную область.
  • Пример: Показать список всех товаров из категории "Электроника".
• =UNIQUE(массив) — мгновенно извлекает список уникальных значений из столбца с повторами.
• =SORT(массив; [номер_столбца]; [порядок]) — сортирует данные динамически. Если исходная таблица изменится, отсортированный список обновится сам.

Работа с текстом и переменными

• =TEXTSPLIT(текст; разделитель_столбцов; [разделитель_строк]) — разбивает текст на части (аналог «Текст по столбцам», но формулой).
• =TEXTBEFORE(текст; маркер) / =TEXTAFTER — извлекают часть строки до или после указанного символа.
• =LET(имя1; значение1; имя2; значение2; ...; расчет) — позволяет задавать переменные внутри формулы. Делает сложные расчеты читаемыми и ускоряет их, так как промежуточный результат вычисляется один раз.
• =LAMBDA(параметры; расчет) — создание собственных функций без программирования на VBA.

Сравнение подходов к поиску данных

Частые ошибки при работе с формулами

1. #Н/Д (#N/A): Возникает, когда функция поиска (например, XLOOKUP или VLOOKUP) не находит значение. Решается оборачиванием в IFERROR или указанием аргумента «если не найдено».
2. #ССЫЛКА! (#REF!): Появляется при удалении ячеек, на которые ссылалась формула. Восстановить такую ссылку автоматически нельзя, нужно переписать формулу.
3. #ЗНАЧ! (#VALUE!): Ошибка типа данных. Часто возникает, когда математическая операция пытается сложить число и текст (например, "100 руб." + 50).
4. Круговая ссылка: Формула ссылается сама на себя (прямо или косвенно). Excel обычно предупреждает об этом, но расчет останавливается.

FAQ: Вопросы по формулам Excel

В чем разница между точной ссылкой ($A$1) и относительной (A1)? Знак доллара $ фиксирует ссылку. При копировании формулы $A$1 всегда будет указывать на ячейку A1, а A1 сместится относительно новой позиции (например, станет A2). Используйте $ для закрепления шапки таблицы или констант.

Как заменить старые формулы массива (Ctrl+Shift+Enter)? В современных версиях Excel большинство операций с массивами работают автоматически. Вам больше не нужно нажимать Ctrl+Shift+Enter. Просто введите формулу, использующую функции FILTER, SORT, UNIQUE или операции с диапазонами, и нажмите Enter.

Можно ли использовать русские названия функций в английской версии Excel? Нет. В англоязычной версии интерфейса функции называются на английском (СУММ → SUM, ЕСЛИ → IF). Однако файлы, созданные в русской версии, обычно корректно открываются в английской, так как Excel internally переводит имена функций при сохранении.

Как ускорить работу файла с тысячами формул? Избегайте ссылок на целые столбцы (A:A) в условных функциях. Используйте функцию LET для исключения повторных вычислений одних и тех же выражений. По возможности замените летучие функции (СЕГОДНЯ, СЛЧИС, НЕПРОМ) на статические значения, если они не требуют ежеминутного обновления.