Складывать числа в Excel несложно — с этим справляется функция СУММ за считанные секунды. Но что делать, если нужно суммировать только те значения, которые отвечают определённому условию? Именно здесь незаменимы СУММЕСЛИ и СУММЕСЛИМН. Эти две функции позволяют выборочно складывать числа — по одному или нескольким критериям — и относятся к числу наиболее востребованных формул в повседневной работе с электронными таблицами.
В этом руководстве обе функции рассматриваются с самого начала: синтаксис, реальные примеры, типичные ошибки и практический сценарий, которому вы можете следовать шаг за шагом. Независимо от того, отслеживаете ли вы продажи, управляете бюджетами или анализируете данные по проекту, условное суммирование избавит вас от огромного объёма ручной работы.
СУММЕСЛИ суммирует значения в диапазоне только в том случае, если соответствующая ячейка другого диапазона удовлетворяет заданному условию. Функция идеально подходит для одного критерия — например, «суммировать все продажи по региону Восток» или «сложить расходы, превышающие 500 $».
=SUMIF(range, criteria, [sum_range])Предположим, в столбце A указаны категории товаров, а в столбце B — суммы продаж. Чтобы суммировать все продажи по категории «Электроника»:
=SUMIF(A2:A100, "Electronics", B2:B100)Чтобы суммировать все значения в столбце B, превышающие 1000:
=SUMIF(B2:B100, ">1000")Обратите внимание: если диапазон проверки и диапазон суммирования совпадают, третий аргумент можно не указывать. Также учтите, что операторы сравнения, такие как >, <, >= и <>, необходимо заключать в кавычки.
СУММЕСЛИМН — это версия СУММЕСЛИ с поддержкой нескольких условий. Функция позволяет задать два и более критерия, и Excel суммирует значения только там, где все условия выполняются одновременно. Структура аргументов немного отличается от СУММЕСЛИ: диапазон суммирования указывается первым.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Используя тот же набор данных, суммируем продажи по категории «Электроника» в регионе «Восток» (при условии, что в столбце C указаны названия регионов):
=SUMIFS(B2:B100, A2:A100, "Electronics", C2:C100, "East")Формула проверяет каждую строку: если в столбце A указано «Электроника» И в столбце C — «Восток», соответствующее значение из столбца B включается в итог.
Рассмотрим реалистичный сценарий. Представьте, что вы ведёте отчёт о продажах со следующими столбцами:
| A: Продавец | B: Регион | C: Товар | D: Месяц | E: Выручка |
|---|---|---|---|---|
| Алиса | Восток | Ноутбуки | Январь | $4 200 |
| Боб | Запад | Телефоны | Январь | $3 800 |
| Алиса | Восток | Телефоны | Февраль | $2 900 |
| Кэрол | Восток | Ноутбуки | Февраль | $5 100 |
| Боб | Запад | Ноутбуки | Февраль | $4 400 |
Данные расположены со строки 2 по строку 500. Вот формулы для ответов на типичные бизнес-вопросы:
Общая выручка Алисы:
=SUMIF(A2:A500, "Alice", E2:E500)Общая выручка по региону Восток:
=SUMIF(B2:B500, "East", E2:E500)Общая выручка от продажи ноутбуков в регионе Восток:
=SUMIFS(E2:E500, C2:C500, "Laptops", B2:B500, "East")Общая выручка Алисы от продажи ноутбуков в январе:
=SUMIFS(E2:E500, A2:A500, "Alice", C2:C500, "Laptops", D2:D500, "January")Обратите внимание: каждое дополнительное условие сужает результат. Такой анализ вручную занял бы несколько минут, а с СУММЕСЛИМН выполняется мгновенно. Если вы создаёте полноценный инструмент отчётности, эти приёмы отлично сочетаются с методами из руководства Панель продаж в Excel: отслеживание KPI и результативности.
Указывать критерии прямо в формуле удобно для разовых вычислений, но в дашбордах и отчётах ссылки на ячейки делают формулы динамическими и простыми в обновлении.
Введите «Alice» в ячейку H2, а «Laptops» — в ячейку H3. Формула примет вид:
=SUMIFS(E2:E500, A2:A500, H2, C2:C500, H3)Измените H2 на «Bob» — и формула мгновенно пересчитается для продаж ноутбуков Боба. Такой подход необходим для интерактивных дашбордов. Понимание ссылок на ячейки в Excel — относительных и абсолютных поможет вам избежать неожиданного смещения ссылок при копировании формул.
Обе функции поддерживают подстановочные знаки, особенно полезные при неоднородных данных:
"Ноут*" соответствует «Ноутбук», «Ноутбучная сумка» и т. д."Бо?" соответствует «Боб», «Бог», «Бор».~*, чтобы найти буквальную звёздочку.Пример — суммируем выручку по всем товарам, название которых начинается с «Lap»:
=SUMIF(C2:C500, "Lap*", E2:E500)СУММЕСЛИМН без труда работает с датами, поскольку Excel хранит их в виде порядковых чисел. Операторы сравнения позволяют суммировать значения в пределах диапазона дат.
При условии, что столбец D содержит реальные значения дат (не текст), для суммирования выручки с 1 января по 31 марта 2024 года:
=SUMIFS(E2:E500, D2:D500, ">="&DATE(2024,1,1), D2:D500, "<="&DATE(2024,3,31))Оператор & объединяет оператор сравнения (как текст) с результатом функции DATE. Это очень распространённый приём, который стоит запомнить.
Все диапазоны в СУММЕСЛИМН должны быть одинакового размера. Если диапазон суммирования содержит 500 строк, а диапазон условия — 499, Excel вернёт ошибку. Всегда проверяйте согласованность диапазонов.
Запись вида =SUMIF(B2:B100, >500, B2:B100) приведёт к ошибке. Операторы и текстовые критерии необходимо заключать в кавычки: ">500" или "Электроника".
В СУММЕСЛИ диапазон суммирования является третьим аргументом. В СУММЕСЛИМН — первым. Путаница в порядке аргументов — частая причина неверных результатов: проверяйте его каждый раз.
Если столбец диапазона условий содержит числа, сохранённые в текстовом формате, числовой критерий не совпадёт с ними. Возможно, потребуется предварительно очистить данные. В статье Power Query: импорт и преобразование данных как профессионал описано, как эффективно решать подобные проблемы качества данных.
В Excel есть и другие способы условного суммирования, и полезно знать, когда применять каждый из них:
Для большинства задач бизнес-отчётности СУММЕСЛИМН — оптимальный инструмент: быстрый, читаемый и покрывающий подавляющее большинство сценариев условного суммирования. При создании комплексного финансового обзора объединение СУММЕСЛИМН с приёмами из статьи Шаблон бюджета в Excel: учёт личных и корпоративных финансов формирует мощную и гибкую систему отчётности.
СУММЕСЛИМН становится ещё мощнее в сочетании с другими формулами:
Вычисление доли от итога:
=SUMIFS(E2:E500, B2:B500, "East") / SUM(E2:E500)Сравнение двух условных сумм:
=SUMIFS(E2:E500, B2:B500, "East") - SUMIFS(E2:E500, B2:B500, "West")Использование с ЕСЛИ для корректной обработки пустого критерия:
=IF(H2="", SUM(E2:E500), SUMIFS(E2:E500, A2:A500, H2))Если вы хотите углубить навыки работы с логическими формулами, следующим шагом станет статья Функция ЕСЛИ: логические проверки и вложенные ЕСЛИ.
Если вы смотрите на сложную формулу СУММЕСЛИМН с четырьмя-пятью критериями и никак не можете понять, почему она возвращает ноль, попробуйте описать задачу простыми словами — инструменты вроде GPTExcel сгенерируют нужную формулу по описанию наподобие «суммировать выручку, где регион — Восток, товар — ноутбуки, а дата — в первом квартале 2024 года», мгновенно предоставив точный синтаксис для проверки и использования.
Напрямую — нет. СУММЕСЛИ рассчитана на одно условие. Для двух и более условий используйте СУММЕСЛИМН. Однако можно обойти это ограничение, складывая результаты нескольких СУММЕСЛИ, когда критерии применяются к одному диапазону и вам нужно условие ИЛИ (например, суммировать строки, где регион — «Восток» или «Запад»).
Наиболее распространённые причины: числа, сохранённые как текст в диапазоне суммирования или диапазоне условий; лишние пробелы в значениях ячеек; несовпадение размеров диапазонов. Регистр символов не важен — СУММЕСЛИМН нечувствительна к нему. Используйте функцию СЖПРОБЕЛЫ или шаги по очистке данных для устранения проблем с пробелами.
Да, при условии, что даты хранятся в виде реальных значений дат Excel (не текста). Используйте операторы сравнения вместе с функцией DATE или прямыми ссылками на даты: =SUMIFS(E2:E500, D2:D500, ">="&H1, D2:D500, "<="&H2), где H1 и H2 содержат начальную и конечную даты.
Excel допускает до 127 пар «диапазон условий/условие» в одной формуле СУММЕСЛИМН — значительно больше, чем когда-либо понадобится на практике. При очень больших наборах данных и многочисленных критериях производительность может снизиться, однако для типичных бизнес-данных (десятки тысяч строк) СУММЕСЛИМН остаётся быстрой и надёжной.
Узнайте, как функция ТЕКСТ в Excel преобразует числа, даты и время в форматированные текстовые строки с помощью кодов формата — с реальными примерами и практическими сценариями использования.
Узнайте, как работает функция ЕСЛИ в Excel, как создавать вложенные ЕСЛИ и когда использовать современные альтернативы — IFS и SWITCH — для более чистой и читаемой логики.
Освойте СУММЕСЛИ и СУММЕСЛИМН в Excel для суммирования данных по одному или нескольким условиям: синтаксис, практические примеры и пошаговое руководство.