Мастерство работы с несколькими критериями в Excel

Чтобы использовать два условия в формуле Excel, применяйте функции СУММЕСЛИМН (для сложения значений) или СЧЁТЕСЛИМН (для подсчета записей), либо комбинируйте функцию ЕСЛИ с логическими операторами И (AND) и ИЛИ (OR). Эти инструменты позволяют фильтровать данные сразу по нескольким параметрам: например, суммировать продажи только по конкретному городу и за определенный месяц.

Когда требуется использование нескольких условий

Работа с одним критерием часто недостаточна для реального анализа данных. Два и более условия необходимы в следующих сценариях:

• Фильтрация по диапазонам: Выборка данных, попадающих в промежуток (например, продажи от 1000 до 5000 рублей).
• Пересечение категорий: Поиск записей, соответствующих одновременно тексту и дате (например, заказы «В пути» за текущую неделю).
• Сложная логика выплат: Расчет бонусов, зависящих от выполнения плана И отсутствия опозданий.

Функция СУММЕСЛИМН: сложение по нескольким критериям

Функция СУММЕСЛИМН (англ. SUMIFS) — основной инструмент для агрегации числовых данных при наличии фильтров. В отличие от старой функции СУММЕСЛИ, здесь первым аргументом всегда идет диапазон суммирования.

Синтаксис

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; ...)

Практические примеры

Пример 1: Продажи в конкретном городе за определенный год Необходимо просуммировать выручку (столбец B), если город (столбец C) — «Москва», а год (столбец D) — 2025.

=СУММЕСЛИМН(B2:B100; C2:C100; "Москва"; D2:D100; 2025)

Пример 2: Сумма в диапазоне дат Частая задача — посчитать сумму за период. Для этого одно поле с датами используется дважды: для указания начала и конца периода.

Задача: Сумма продаж товара «Ноутбук» (столбец A) с 1 января по 31 марта 2025 года (столбец E).

=СУММЕСЛИМН(B2:B100; A2:A100; "Ноутбук"; E2:E100; ">=01.01.2025"; E2:E100; "<=31.03.2025")

Функция СЧЁТЕСЛИМН: подсчет записей

Если ваша цель — не сложить числа, а узнать количество строк, удовлетворяющих условиям, используйте СЧЁТЕСЛИМН (англ. COUNTIFS). Здесь не указывается диапазон суммирования, только пары «диапазон — условие».

Синтаксис

=СЧЁТЕСЛИМН(диапазон_условия1; условие1; [диапазон_условия2; условие2]; ...)

Примеры использования

Подсчет выполненных заказов с высокой суммой Сколько заказов имеют статус «Выполнен» (столбец B) и сумму свыше 10 000 руб. (столбец C)?

=СЧЁТЕСЛИМН(B2:B100; "Выполнен"; C2:C100; ">10000")

Анализ активности за месяц Количество обращений из города «Казань» (столбец C) в июле 2024 года (столбец D):

=СЧЁТЕСЛИМН(C2:C100; "Казань"; D2:D100; ">=01.07.2024"; D2:D100; "<=31.07.2024")

Логические связки И и ИЛИ внутри функции ЕСЛИ

Когда нужно вернуть текстовый результат или выполнить действие на основе проверки условий, используется функция ЕСЛИ в связке с И (AND) или ИЛИ (OR).

Оператор И (Все условия должны быть истинны)

Используется, когда результат положителен только при соблюдении всех требований.

Задача: Присвоить статус «Премия», если план выполнен (ячейка A2 >= 100%) И нет дисциплинарных взысканий (ячейка B2 = 0).

=ЕСЛИ(И(A2>=1; B2=0); "Премия"; "Нет премии")

Оператор ИЛИ (Достаточно одного условия)

Используется, когда достаточно выполнения хотя бы одного из условий.

Задача: Пометить заказ как «Приоритетный», если клиент «VIP» (столбец A) ИЛИ сумма заказа больше 50 000 (столбец B).

=ЕСЛИ(ИЛИ(A2="VIP"; B2>50000); "Приоритетный"; "Стандарт")

Альтернативный метод: СУММПРОИЗВ

Для продвинутых пользователей функция СУММПРОИЗВ (англ. SUMPRODUCT) дает максимальную гибкость, позволяя реализовывать сложную логику «И» и «ИЛИ» без вложенности функций.

Пример: Сумма продаж (столбец B), где Регион (C) = «Север» И Дата (D) в 2025 году.

=СУММПРОИЗВ((C2:C100="Север")*(ГОД(D2:D100)=2025)*B2:B100)

Здесь умножение логических выражений (условие1)*(условие2) работает как оператор «И», а сложение (условие1)+(условие2) — как «ИЛИ».

Сравнение подходов к решению задач

Частые ошибки и способы их устранения

1. Несоответствие размеров диапазонов.
   • Ошибка: #ЗНАЧ!. Все диапазоны в формуле должны иметь одинаковое количество строк и столбцов.
   • Решение: Проверьте, что B2:B100 и C2:C105 не используются вместе. Выровняйте диапазоны.
2. Числа, записанные как текст.
   • Ошибка: Формула возвращает 0, хотя данные есть. Часто случается при выгрузке из 1С или банков.
   • Решение: Преобразуйте текстовые числа в настоящие числа (через «Текст по столбцам» или умножение на 1).
3. Лишние пробелы в текстовых условиях.
   • Ошибка: «Москва » не равно «Москва».
   • Решение: Используйте функцию СЖПРОБЕЛЫ (TRIM) для очистки данных или проверяйте исходные ячейки.
4. Неверный порядок аргументов в СУММЕСЛИМН.
   • Ошибка: Суммируется диапазон условий.
   • Решение: Помните: в СУММЕСЛИМН первый аргумент — это то, что мы складываем. В СЧЁТЕСЛИМН такого аргумента нет.

FAQ

Можно ли использовать подстановочные знаки в условиях? Да. В текстовых условиях поддерживаются * (любая последовательность символов) и ? (один любой символ). Например, "*москва*" найдет «Новая Москва» и «Москва-Сити».

Как сослаться на ячейку в условии? Просто укажите адрес ячейки вместо текста в кавычках. Если используете оператор сравнения, соединяйте его через &. Пример: ">="&E1, где в E1 лежит минимальная дата.

Сколько условий максимум можно задать? В современных версиях Excel (2007 и новее) функции СУММЕСЛИМН и СЧЁТЕСЛИМН поддерживают до 127 пар «диапазон-условие».