Как посчитать отрицательные числа в Excel с помощью COUNTIF

Краткое объяснение

Функция COUNTIF считает количество ячеек в указанном диапазоне, которые соответствуют условию. Условие может быть числовым выражением, логическим оператором или шаблоном с подстановочными знаками.

Определение в одну строку: COUNTIF(range, criteria) — возвращает число ячеек из range, удовлетворяющих criteria.

Операторы, поддерживаемые COUNTIF

• < — меньше

• — больше

• = — равно
• <> — не равно
• <= — меньше или равно

• = — больше или равно

Вы используете логический оператор вместе с числом или ссылкой на ячейку. Чтобы посчитать отрицательные числа, применяют критерий “<0”.

=COUNTIF(диапазон, критерий)

Пример: дни с отрицательной средней температурой

В примере средние температуры за неделю расположены в ячейках B2:B8. В ячейке E2 мы хотим увидеть количество дней со средним значением ниже нуля.

Шаги:

1. Выберите ячейку E2.
2. В строке формул введите:

=COUNTIF(B2:B8, "<0")

3. Нажмите Enter. В ячейке E2 отобразится число дней со средним значением ниже 0.

Важно: критерий “<0” должен быть в кавычках.

Примечание: если отрицательные числа в диапазоне записаны как текст (например, “-5”), COUNTIF может не считать их корректно. См. раздел «Когда COUNTIF не сработает».

Альтернативные способы и когда они полезны

1. SUMPRODUCT — универсальный способ, работает со сложной логикой и не требует кавычек:

=SUMPRODUCT(--(B2:B8<0))

2. FILTER + ROWS (только Excel 365/2021):

=ROWS(FILTER(B2:B8, B2:B8<0))

3. COUNTIFS — если нужно сочетать несколько условий (например, отрицательные и другой столбец соответствует условию):

=COUNTIFS(B2:B8, "<0", C2:C8, ">=10")

4. SUBTOTAL после фильтра — если вы фильтруете таблицу и хотите считать видимые строки:

• Примените автофильтр и отфильтруйте только отрицательные значения.
• Используйте формулу SUBTOTAL для видимого счёта (код 3 для COUNT или 103 для COUNTA в новых версиях):

=SUBTOTAL(3, B2:B8)

5. Пивотная таблица — удобна для больших наборов данных и агрегаций по группам.

6. 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 — простая проверка: диапазон {1,-2,3,-4} → =COUNTIF(range,”<0”) ожидает 2.
2. Тест 2 — числа как текст: диапазон {“-1”,”2”} → после конвертации COUNTIF должен вернуть 1.
3. Тест 3 — смешанные данные и пустые ячейки: COUNTIF игнорирует пустые и считает только отрицательные числа.
4. Тест 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.