Справочник по функциям табличных процессоров

Встроенные функции табличных процессоров (например, Microsoft Excel или Google Таблиц) — это готовые алгоритмы для автоматизации вычислений, обработки текста и анализа данных. Они делятся на категории: математические, текстовые, логические, даты и времени, статистические и функции поиска. Использование правильных функций ускоряет работу с таблицами в разы и исключает ручные ошибки.

Ниже приведено подробное руководство по основным категориям с примерами формул, которые можно скопировать и адаптировать под свои задачи.

Математические и статистические функции

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

Базовые вычисления

• СУММ (SUM) — складывает числа в диапазоне.
  • =СУММ(A1:A10)
• СРЗНАЧ (AVERAGE) — вычисляет среднее арифметическое.
  • =СРЗНАЧ(B1:B20)
• ОКРУГЛ (ROUND) — округляет число до указанного количества знаков.
  • =ОКРУГЛ(A1; 2) (до двух знаков после запятой).

Статистика

• СЧЁТ (COUNT) — считает количество ячеек с числами.
• СЧЁТЗ (COUNTA) — считает количество непустых ячеек (текст + числа).
• МАКС / МИН (MAX / MIN) — находят наибольшее и наименьшее значение в диапазоне.

Текстовые функции

Незаменимы для очистки данных, объединения колонок и извлечения частей строк.

Практический кейс: Извлечение домена из email Если в ячейке A1 адрес [email protected], формула для получения example.com: =ПРАВСИМВ(A1; ДЛСТР(A1) - ПОИСК("@"; A1))

Логические функции

Позволяют строить ветвления и проверять условия.

• ЕСЛИ (IF) — базовая функция условия.
  • Синтаксис: =ЕСЛИ(условие; значение_если_истина; значение_если_ложь)
  • Пример: =ЕСЛИ(C2>1000; "Премия"; "Нет")
• И / ИЛИ (AND / OR) — используются внутри ЕСЛИ для проверки нескольких условий.
  • Пример: =ЕСЛИ(И(A2>10; B2<5); "Подходит"; "Не подходит")
• ЕСЛИОШИБКА (IFERROR) — маскирует ошибки в формулах.
  • Пример: =ЕСЛИОШИБКА(A1/B1; 0) (если деление на ноль, вернет 0 вместо #ДЕЛ/0!).

Функции даты и времени

Работа с календарными данными требует особых функций, так как даты в таблицах хранятся как числа.

• СЕГОДНЯ() — возвращает текущую дату (обновляется при каждом пересчете).
• ТДАТА() — возвращает текущие дату и время.
• ДНИ (DAYS) — вычисляет количество дней между двумя датами.
  • =ДНИ(Дата_конца; Дата_начала)
• ДАТАМЕС (EDATE) — сдвигает дату на указанное количество месяцев вперед или назад.
  • Пример: =ДАТАМЕС(A1; 3) (дата через 3 месяца от значения в A1).

Функции поиска и ссылок

Самая важная категория для связывания разных таблиц между собой.

ВПР (VLOOKUP)

Классический вертикальный поиск. Ищет значение в первом столбце диапазона и возвращает значение из указанного столбца той же строки.

• =ВПР(Искомое_значение; Таблица; Номер_столбца; 0)
• Важно: Последний аргумент 0 (или ЛОЖЬ) означает точное совпадение.

ИНДЕКС и ПОИСКПОЗ (INDEX + MATCH)

Более гибкая альтернатива ВПР. Позволяет искать слева направо и справа налево, а также не ломается при добавлении столбцов.

• =ИНДЕКС(Столбец_результата; ПОИСКПОЗ(Искомое_значение; Столбец_поиска; 0))

ПРОСМОТРX (XLOOKUP)

Современная функция (доступна в новых версиях Excel и Google Таблицах), заменяющая ВПР и ГПР.

• =ПРОСМОТРX(Что_ищем; Где_ищем; Что_возвращаем; "Не найдено")

Работа с массивами и динамические функции

В современных табличных процессорах появились функции, которые возвращают не одно значение, а целый диапазон (динамические массивы).

• УНИК (UNIQUE) — выводит список уникальных значений из диапазона, удаляя дубликаты.
  • =УНИК(A2:A100)
• СОРТ (SORT) — сортирует диапазон данных прямо в формуле.
  • =СОРТ(A2:B10; 2; -1) (сортировка по второму столбцу по убыванию).
• ФИЛЬТР (FILTER) — отбирает строки по условию.
  • =ФИЛЬТР(A2:C100; B2:B100 > 1000) (покажет только строки, где во втором столбце значение больше 1000).

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

1. #Н/Д (#N/A) — возникает в ВПР/ПРОСМОТРX, если искомое значение не найдено. Проверьте наличие лишних пробелов или различия типов данных (число против текста).
2. #ЗНАЧ! (#VALUE!) — ошибка типа данных. Часто случается, когда вы пытаетесь сложить текст с числом или используете неверный разделитель аргументов.
3. #ССЫЛКА! (#REF!) — ссылка на ячейку стала недействительной (например, вы удалили столбец, на который ссылалась формула).
4. Циклические ссылки — когда формула в ячейке A1 ссылается саму на себя (прямо или косвенно). Таблица не может рассчитать такое значение.

FAQ

В чем разница между СЧЁТ и СЧЁТЗ? СЧЁТ учитывает только ячейки с числами. СЧЁТЗ считает любые непустые ячейки, включая текст, даты и логические значения.

Как сделать так, чтобы формула не менялась при копировании? Используйте абсолютные ссылки со знаком доллара $. Например, $A$1 всегда будет ссылаться на ячейку A1, даже если вы скопируете формулу в другую часть таблицы.

Почему ВПР не находит данные, которые визуально есть в таблице? Чаще всего причина в том, что в одной таблице числа хранятся как «Число», а в другой — как «Текст» (часто бывает при выгрузке из 1С или банковских приложений). Используйте функцию ЗНАЧЕН или «Текст по столбцам» для приведения к одному формату.