Как посчитать отрицательные числа в Excel с помощью COUNTIF
Краткое объяснение
Функция COUNTIF считает количество ячеек в указанном диапазоне, которые соответствуют условию. Условие может быть числовым выражением, логическим оператором или шаблоном с подстановочными знаками.
Определение в одну строку: COUNTIF(range, criteria) — возвращает число ячеек из range, удовлетворяющих criteria.
Операторы, поддерживаемые COUNTIF
- < — меньше
— больше
- = — равно
- <> — не равно
- <= — меньше или равно
= — больше или равно
Вы используете логический оператор вместе с числом или ссылкой на ячейку. Чтобы посчитать отрицательные числа, применяют критерий “<0”.
=COUNTIF(диапазон, критерий)Пример: дни с отрицательной средней температурой
В примере средние температуры за неделю расположены в ячейках B2:B8. В ячейке E2 мы хотим увидеть количество дней со средним значением ниже нуля.
Шаги:
- Выберите ячейку E2.
- В строке формул введите:
=COUNTIF(B2:B8, "<0")- Нажмите Enter. В ячейке E2 отобразится число дней со средним значением ниже 0.
Важно: критерий “<0” должен быть в кавычках.
Примечание: если отрицательные числа в диапазоне записаны как текст (например, “-5”), COUNTIF может не считать их корректно. См. раздел «Когда COUNTIF не сработает».
Альтернативные способы и когда они полезны
- SUMPRODUCT — универсальный способ, работает со сложной логикой и не требует кавычек:
=SUMPRODUCT(--(B2:B8<0))- FILTER + ROWS (только Excel 365/2021):
=ROWS(FILTER(B2:B8, B2:B8<0))- COUNTIFS — если нужно сочетать несколько условий (например, отрицательные и другой столбец соответствует условию):
=COUNTIFS(B2:B8, "<0", C2:C8, ">=10")- SUBTOTAL после фильтра — если вы фильтруете таблицу и хотите считать видимые строки:
- Примените автофильтр и отфильтруйте только отрицательные значения.
- Используйте формулу SUBTOTAL для видимого счёта (код 3 для COUNT или 103 для COUNTA в новых версиях):
=SUBTOTAL(3, B2:B8)Пивотная таблица — удобна для больших наборов данных и агрегаций по группам.
VBA/макрос — когда нужно автоматизировать проверку и отчётность по расписанию.
Когда COUNTIF не сработает (контрпримеры и решения)
- Числа записаны как текст. Решение: привести к числу с помощью VALUE или умножения на 1, или использовать
=COUNTIF(B2:B8, "<" & 0)и предварительно конвертировать текст в числа через:
=VALUE(B2)или выделить столбец и выбрать «Текст по столбцам» → Завершить.
- Нужны сочетания условий по разным столбцам — используйте COUNTIFS или SUMPRODUCT.
- Данные содержат ошибки #N/A или пустые строки — перед подсчётом отфильтруйте или добавьте обработку ошибок.
Шпаргалка по формулам (быстрый выбор)
- Простая проверка на отрицательное:
=COUNTIF(B2:B8, "<0")- То же самое через SUMPRODUCT (работает при текстовых числах после приведения):
=SUMPRODUCT(--(N(B2:B8)<0))- Фильтрация и подсчёт в Excel 365:
=ROWS(FILTER(B2:B8, B2:B8<0))- Множественные условия:
=COUNTIFS(B2:B8, "<0", D2:D8, "=Yes")- Игнорировать заголовки и пустые строки: указывайте диапазон без заголовка и используйте проверку на непустоту.
Критерии приёмки и тесты
- Тест 1 — простая проверка: диапазон {1,-2,3,-4} → =COUNTIF(range,”<0”) ожидает 2.
- Тест 2 — числа как текст: диапазон {“-1”,”2”} → после конвертации COUNTIF должен вернуть 1.
- Тест 3 — смешанные данные и пустые ячейки: COUNTIF игнорирует пустые и считает только отрицательные числа.
- Тест 4 — множественные условия: COUNTIFS с дополнительным столбцом даёт ожидаемый результат по пересечению.
Если любой тест не проходит, проверьте формат ячеек и наличие скрытых символов (неразрывные пробелы и т. п.).
Чек-листы по ролям
Аналитик:
- Убедиться, что столбец в числовом формате.
- Применить COUNTIF с “<0” и проверить на тестовом поддиапазоне.
- Сохранить формулу и документировать диапазон.
Менеджер/руководитель:
- Проверить отчётность: отображаются корректные суммарные значения и смысловые метрики.
- Запросить тестовые кейсы у аналитика.
Аудитор/контролёр качества:
- Проверить преобразования данных и операции приведения типов.
- Сопоставить результат COUNTIF с альтернативной формулой (SUMPRODUCT или Pivot).
Короткий глоссарий
- COUNTIF — функция подсчёта ячеек по одному условию.
- Диапазон — набор ячеек, например B2:B8.
- Критерий — условие, например “<0”.
Советы по совместимости и переносимости
- COUNTIF работает во всех версиях Excel; FILTER доступен только в Excel 365/2021 и выше.
- Формулы с массивами ведут себя по-разному в старых версиях — в старых нужно подтверждать Ctrl+Shift+Enter для некоторых массивных формул.
Резюме
COUNTIF с критерием “<0” — самый быстрый и понятный способ посчитать отрицательные числа в диапазоне. Если данные имеют сложные условия или нередки текстовые представления чисел, рассмотрите SUMPRODUCT, COUNTIFS или FILTER (в Excel 365). Всегда проверяйте формат ячеек и прогоняйте простые тесты.
Ключевые формулы: =COUNTIF(B2:B8, “<0”), =SUMPRODUCT(–(B2:B8<0)), =ROWS(FILTER(B2:B8, B2:B8<0)).
Сообщение для публикации: Используйте COUNTIF, чтобы быстро посчитать отрицательные значения в столбце, а если нужны сложные условия — примените SUMPRODUCT или COUNTIFS.
