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

COUNTIF быстро подсчитает числа меньше нуля в диапазоне. В простом случае используйте критерий “<0”; для русской локали формула будет с функцией СЧЁТЕСЛИ и разделителем “;”. Привожу примеры для Excel (включая Excel 365) и альтернативы, если COUNTIF не подходит.

Что делает COUNTIF

COUNTIF — функция Excel для подсчёта ячеек в диапазоне, которые соответствуют условию. Коротко: она перебирает ячейки, проверяет условие и возвращает количество совпадений.

Определение в одну строку: COUNTIF считает ячейки, соответствующие заданному критерию.

Важно: в русской версии Excel имя функции локализовано как СЧЁТЕСЛИ, а разделитель аргументов часто — точка с запятой.

Поддерживаемые логические операторы

• < меньше чем

• больше чем

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

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

Вы можете использовать операторы напрямую в критерии. Для отрицательных чисел нам нужен оператор “<” и значение 0.

Синтаксис

Англоязычная версия Excel:

=COUNTIF(range, criteria)

Русская версия Excel:

=СЧЁТЕСЛИ(диапазон; критерий)

Пример простого критерия (универсально):

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

Локализованный вариант для русской Excel (функция и разделитель):

=СЧЁТЕСЛИ(B2:B8; "<0")

Если критерий берётся из другой ячейки (например A1), используйте конкатенацию:

=COUNTIF(B2:B8, "<" & A1)

В русской Excel:

=СЧЁТЕСЛИ(B2:B8; "<" & A1)

Пример: подсчёт отрицательных температур

У нас есть среднесуточные температуры с B2 по B8. Мы хотим узнать, в скольких днях средняя температура была ниже нуля. Нужная формула в ячейке E2:

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

Или, для русской локали:

=СЧЁТЕСЛИ(B2:B8; "<0")

Шаги:

1. Выделите ячейку E2.
2. Введите формулу из примера.
3. Нажмите Enter. Excel вернёт количество ячеек в диапазоне B2:B8, где значение меньше нуля.

Важно: COUNTIF учитывает только числовые значения — текст и пустые ячейки не будут считаться.

Альтернативные подходы

Если вы используете Excel 365 или хотите другие способы, рассмотрите варианты ниже.

• SUMPRODUCT (работает в большинстве версий):

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

В русском Excel можно писать тот же логический тест, но с локализованной точкой с запятой при необходимости:

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

SUMPRODUCT превращает логические значения TRUE/FALSE в 1/0 и суммирует их.

• FILTER + COUNTA (Excel 365):

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

FILTER вернёт массив значений меньше нуля, а COUNTA посчитает непустые элементы.

• COUNTIFS для многокритериальных задач (например, отрицательные и по диапазону дат):

=COUNTIFS(B2:B8, "<0", A2:A8, ">=" & D1)

• Вспомогательный столбец: создайте столбец с логикой =B2<0 и затем посчитайте сумму единиц. Подходит, если хотите отлаживать или визуализировать причину совпадений.

Когда COUNTIF не сработает

• Ячейки содержат числа как текст (например, “-5”), тогда COUNTIF не найдёт такие значения. Решение: преобразовать через VALUE или исправить источник данных.
• В данных есть пробелы или невидимые символы. Примените TRIM/CLEAN или используйте VALUE.
• Вы используете локализованную Excel: не забудьте о локализованном имени функции (СЧЁТЕСЛИ) и разделителе “;”.
• Если нужен сложный набор условий, COUNTIF может быть недостаточен — используйте COUNTIFS, SUMPRODUCT или FILTER.

Практические советы

• Проверяйте типы данных: выделите диапазон и примените Формат ячейки -> Числовой, чтобы убедиться, что Excel видит данные как числа.
• Для больших таблиц SUMPRODUCT может работать быстрее при сложных логических выражениях.
• Используйте именованные диапазоны (например, Temps) для удобства и читаемости формул: =COUNTIF(Temps; “<0”).
• Если нужно игнорировать ошибки, комбинируйте с IFERROR/ЕСЛИОШИБКА.

Критерии приёмки

• Формула возвращает корректное количество ячеек со значением меньше нуля в указанном диапазоне.
• Результат совпадает с ручной проверкой на небольшом подмножестве данных.
• Учтены локальные настройки Excel (имя функции и разделитель аргументов).

Быстрые роли: чеклисты

Аналитик:

• Убедился, что данные числовые.
• Протестировал формулу на небольшом образце.
• Сохранил формулу в именованном диапазоне или документировании.

Менеджер/руководитель:

• Проверил итоговую метрику на соответствие ожиданиям.
• Запросил краткое пояснение метода у исполнителя.

Контроль качества и тесты

Тестовые случаи:

• Диапазон с числами [-3, -1, 0, 2] → результат 2.
• Диапазон с текстовыми значениями [“-3”, “-1”] → COUNTIF не считает: рекомендовать преобразование.
• Пустые ячейки и ошибки → COUNTIF игнорирует пустые, но ошибки ломают расчёт; оборачивайте в IFERROR.

Итог

COUNTIF/СЧЁТЕСЛИ — простой и мощный инструмент для подсчёта отрицательных чисел в диапазоне. Для продвинутых сценариев используйте SUMPRODUCT, COUNTIFS или FILTER. Не забывайте про локализацию функций и проверку типов данных.

Важно: при работе с локальной версией Excel всегда проверяйте имя функции и разделитель аргументов.