Мастерство СЧЁТЕСЛИ: 8 Необходимые примеры Excel, которые вы должны знать

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

Бесплатно загрузите образец файла

Видео: COUNTIF — 8 основные примеры в Excel

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

Функция СЧЁТЕСЛИ: синтаксис и аргументы

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

Синтаксис

Общий синтаксис функции COUNTIF выглядит следующим образом:

=COUNTIF(range, criteria)

Аргументы

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

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

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

=COUNTIF(A4:A13,"Judy")

Затем нажмите Enter, чтобы получить результат.

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

Основные применения СЧЁТЕСЛИ

Count if cells with specific text (exact match)

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

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

В приведённой ниже таблице представлено 3 различных сценариев на основе приведённого выше примера, демонстрирующих, как записывать критерии в функции СЧЁТЕСЛИ:

После ввода функции СЧЁТЕСЛИ нажмите клавишу Enter, чтобы получить результат подсчёта.

Count if cells contain specific text (partial match)

Если нужно подсчитать ячейки, содержащие определённый текст, функция СЧЁТЕСЛИ справится с этой задачей легко — просто используйте символы подстановки!

Символы подстановки:

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

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

Здесь представлен список распространённых сценариев, с которыми вы можете столкнуться на основе данного примера, а также соответствующие критерии для функции СЧЁТЕСЛИ и необходимые формулы.

После ввода функции СЧЁТЕСЛИ нажмите клавишу Enter, чтобы получить результат подсчёта.

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

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

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

После ввода функции СЧЁТЕСЛИ нажмите клавишу Enter, чтобы получить результат.

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

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

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

После ввода функции СЧЁТЕСЛИ нажмите клавишу Enter, чтобы получить результат.

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

Для подсчёта ячеек в диапазоне A4:A13, значения которых больше, меньше или равны заданному числу, можно также использовать функцию СЧЁТЕСЛИ.

Для этого достаточно добавить нужный оператор к критерию. В таблице ниже представлены доступные операторы.

После ввода формулы СЧЁТЕСЛИ нажмите клавишу Enter.

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

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

После ввода формулы СЧЁТЕСЛИ нажмите клавишу Enter, чтобы получить результат подсчёта.

Помимо описанных распространённых случаев, вы можете использовать функцию СЧЁТЕСЛИ совместно со специальными функциями Excel для работы с датами и временем, такими как функция СЕГОДНЯ, чтобы подсчитывать ячейки на основе текущей даты. Это позволяет динамически отслеживать и подсчитывать ячейки, соответствующие критерию «текущая дата». Вот несколько примеров использования функции СЧЁТЕСЛИ вместе с СЕГОДНЯ():

Расширенные применения СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ в Excel не предназначена специально для подсчёта ячеек по нескольким критериям. В таких случаях рекомендуется использовать функцию СЧЁТЕСЛИМН. Однако в некоторых ситуациях желаемого результата можно достичь, объединив несколько функций СЧЁТЕСЛИ в одной формуле.

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

Допустим, вам нужно подсчитать ячейки, содержащие «Peach» или «Apple». Для этого просто объедините две функции СЧЁТЕСЛИ, как показано ниже:

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

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

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

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

>> Критерии И

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

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

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

Обратите внимание, что в данном случае мы используем знак минус (-) для объединения результатов двух функций СЧЁТЕСЛИ.

Подсчёт уникальных или повторяющихся ячеек Дублирующиеся значения

Если вы хотите подсчитать количество уникальных или дублирующихся значений в диапазоне A3:A13, функция СЧЁТЕСЛИ также придёт вам на помощь.

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

1. В столбце рядом со значениями, которые нужно подсчитать, используйте приведённую ниже формулу, чтобы определить, является ли соответствующее значение уникальным или дублирующимся: ИСТИНА означает, что значение дублируется, а ЛОЖЬ — что значение уникально.
   

   =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()A2:A10,«>0») + COUNTIF(B3,«>0») + COUNTIF(C2:C5,«>0»).

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

• Функция СЧЁТЕСЛИ возвращает ошибку #ЗНАЧ!, если ссылается на другую закрытую книгу.