Мастерство COUNTIF:8 ключевых примеров для Excel, которые стоит знать

В повседневной работе подсчет количества вхождений определённых критериев в большом наборе данных Excel может напоминать поиск иголки в стоге сена. Но не переживайте — функция COUNTIF в Excel придет вам на помощь. COUNTIF — это мощный инструмент, который позволяет быстро и точно подсчитать количество данных, соответствующих заданным условиям. В этой статье мы подробно рассмотрим синтаксис и применение функции COUNTIF, а также приведем восемь примеров, чтобы вы смогли максимально эффективно использовать этот инструмент анализа.

Скачать пример файла бесплатно

Видео: COUNTIF —8 ключевых примеров для Excel

Подпишитесь на наш канал сейчас и получите лучшие советы по Excel!

COUNTIF: синтаксис и аргументы

Функция COUNTIF используется для подсчёта количества ячеек в диапазоне, соответствующих определённому условию.

Синтаксис

Общий синтаксис функции COUNTIF выглядит так:

=COUNTIF(range, criteria)

Аргументы

• Диапазон: Обязательный параметр. Это диапазон, в котором вы хотите подсчитать количество соответствий по заданному критерию.
• Критерий: Обязательный параметр. Определяет, что именно вы ищете в указанном диапазоне.

Простой пример для понимания функции COUNTIF

Чтобы найти имя «Judy» в диапазоне A4:A13 и подсчитать количество вхождений, используйте функцию COUNTIF следующим образом:

=COUNTIF(A4:A13,"Judy")

Затем нажмите клавишу Enter, чтобы получить результат подсчёта.

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

Основные способы использования COUNTIF

Подсчёт, если ячейки содержат определённый текст (точное совпадение)

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

В качестве примера возьмём таблицу A3:C13: в столбце A указаны названия фруктов, в столбце B — числа, а в столбце C — даты. Мы отдельно подсчитаем количество вхождений 'Apple', '100' и '6/10/2023' в этих столбцах.

В таблице ниже приведены3 различных сценария на основе приведённого выше примера, чтобы показать, как записывать критерии в функции COUNTIF:

После ввода функции COUNTIF нажмите клавишу Enter, чтобы получить результат подсчёта.

Подсчёт, если ячейки содержат определённый текст (частичное совпадение)

Если вам нужно подсчитать ячейки, содержащие определённый текст, функция COUNTIF также поможет, используя подстановочные знаки.

Подстановочные знаки:

• Вопросительный знак (?) заменяет любой один символ, например, L??? найдёт «Lisa» и «Lucy».
• Звёздочка (*) заменяет ноль или более любых символов, например, *cy найдёт «Lucy» и «Nancy».
• Тильда (~) — это символ экранирования: если поставить её перед подстановочным знаком (например, ~*, ~?, ~*), то подстановочный знак будет восприниматься как обычный символ. Например, ~* найдёт «*».

В этом разделе рассмотрим пример подсчёта количества вхождений текста, начинающегося с «L», в столбце A3:A13.

Здесь мы приводим список типовых сценариев, с которыми вы можете столкнуться, а также соответствующие критерии для функции COUNTIF и необходимые формулы.

После ввода функции COUNTIF нажмите клавишу Enter, чтобы получить результат подсчёта.

Подсчёт, если ячейки пустые или непустые

Если у вас большой диапазон данных и нужно быстро подсчитать количество пустых или непустых ячеек, функция COUNTIF станет отличным помощником.

В этом примере мы используем диапазон A4:A11 для подсчёта количества пустых и непустых ячеек.

После ввода функции COUNTIF нажмите клавишу Enter, чтобы получить результат.

Подсчёт, если ячейки содержат только текст или только числа

Иногда требуется подсчитать ячейки, содержащие только текстовые строки или только числа. В таких случаях функция COUNTIF будет очень полезна.

В этом примере мы используем диапазон A4:A11 для подсчёта ячеек, содержащих только текст или только числа.

После ввода функции COUNTIF нажмите клавишу Enter, чтобы получить результат.

Подсчёт, если числа больше/меньше/равны

Чтобы подсчитать ячейки с числами в диапазоне A4:A13, которые больше, меньше или равны определённому значению, также можно использовать функцию COUNTIF.

Достаточно добавить соответствующий оператор к критерию. В таблице ниже показаны операторы, которые можно использовать.

После ввода формулы COUNTIF нажмите клавишу Enter.

Подсчёт, если даты больше/меньше/равны

Если нужно подсчитать ячейки с датами, которые больше, меньше или равны определённой дате, используйте формулы, аналогичные рассмотренным ранее. Для дат действуют те же принципы, что и для чисел. Вот несколько примеров:

После ввода формулы COUNTIF нажмите клавишу Enter, чтобы получить результат.

Помимо стандартных способов, вы можете использовать функцию COUNTIF в сочетании с определёнными функциями даты и времени Excel, например, с функцией TODAY, чтобы подсчитывать ячейки на основе текущей даты. Это позволит вам динамически отслеживать и подсчитывать значения, соответствующие сегодняшнему дню. Вот несколько примеров использования COUNTIF с TODAY():

Расширенные способы использования COUNTIF

Действительно, функция COUNTIF в Excel не предназначена специально для подсчёта ячеек по нескольким критериям. В таких случаях рекомендуется использовать функцию COUNTIFS. Однако в некоторых ситуациях можно получить нужный результат, объединив несколько функций COUNTIF в одной формуле.

>> Критерий ИЛИ

Допустим, вы хотите подсчитать ячейки, равные «Peach» или «Apple». Для этого объедините две функции COUNTIF, как показано ниже:

=COUNTIF(A4:A11,"Peach")+COUNTIF(A4:A11,"Apple")

Нажмите клавишу Enter, чтобы получить результат.

Обратите внимание: в этом случае мы используем знак плюс (+) для объединения результатов двух функций COUNTIF. Аналогично можно создать формулу COUNTIF с несколькими условиями. Вот пример формулы COUNTIF с тремя условиями ИЛИ, которая подсчитывает вхождения «Peach», «Apple» и «Lemon»:

=COUNTIF(A2:A9,"Peach")+COUNTIF(A2:A9,"Apple")+COUNTIF(A2:A9,"Lemon")

>> Критерий И

Допустим, вы хотите подсчитать числа, которые больше5 и меньше10 в диапазоне A4:A11. Для этого объедините две функции COUNTIF, как показано на скриншоте ниже:

=COUNTIF(A4:A11,">5")-COUNTIF(A4:A11,">=10")

Нажмите клавишу Enter, чтобы получить результат.

Обратите внимание: в этом случае мы используем знак минус (-) для объединения результатов двух функций COUNTIF.

Подсчёт, если значения уникальны или дублируются

Если вы хотите подсчитать уникальные значения или дубликаты в диапазоне A3:A13, функция COUNTIF также поможет вам.

Шаг1: Определите, является ли каждое значение уникальным или дубликатом

1. В столбце рядом с подсчитываемыми значениями используйте формулу ниже, чтобы определить, уникально ли значение или оно дублируется: TRUE означает, что значение является дубликатом, FALSE означает, что значение уникально.
   

   =COUNTIF($A$4:$A$13,A4)>1

2. Нажмите Enter и дважды щёлкните по маркеру автозаполнения (маленький зелёный квадратик в правом нижнем углу первой ячейки с результатом), чтобы увидеть все результаты.
   

Шаг2: Подсчитайте уникальные или дублирующиеся значения

• Для подсчёта уникальных значений используйте формулу ниже
  

  =COUNTIF(B4:B13,"FALSE")

• Для подсчёта дубликатов используйте формулу ниже
  

  =COUNTIF(B4:B13,"TRUE")

  B4:B13 — это вспомогательный столбец, в котором вы использовали формулу на шаге1.
  Затем нажмите Enter чтобы получить результат.
  

COUNTIF — примечания и важные моменты

• Функция COUNTIF не чувствительна к регистру.

• Функция COUNTIF требует фактический диапазон, массив использовать нельзя.

• Функция COUNTIF не поддерживает несмежные диапазоны, и её синтаксис не позволяет указывать несколько отдельных ячеек в качестве первого параметра. Если нужно подсчитать значения в нескольких диапазонах, используйте комбинацию нескольких функций COUNTIF, например:
  =COUNTIF(A2:A10,">0") + COUNTIF(B3,">0") + COUNTIF(C2:C5,">0").

• Функция COUNTIF возвращает неверные результаты при поиске строк длиннее255 символов.

• Функция COUNTIF возвращает ошибку #VALUE при обращении к другой закрытой книге.