COUNTIF Mastery: 8 основных примеров Excel, которые вам нужно знать

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

Бесплатно скачать образец файла

Видео: СЧЁТЕСЛИ — 8 основных примеров Excel

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

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

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

Синтаксис

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

=COUNTIF(range, criteria)

аргументы

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

Простой пример для понимания функции СЧЁТЕСЛИ

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

=COUNTIF(A4:A13,"Judy")

Затем нажмите Enter ключ, чтобы получить счет.

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

СЧЁТЕСЛИ основные способы использования

Подсчет ячеек с определенным текстом (точное совпадение)

Функцию СЧЕТЕСЛИ можно использовать для подсчета ячеек, которые равны определенному числу, тексту или дате.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Press Enter после ввода формулы СЧЁТЕСЛИ.

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

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

Press Enter ключ, чтобы получить счет после ввода формулы COUNTIF.

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

COUNTIF расширенное использование

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

>> Или критерии

Предположим, вы хотите подсчитать ячейки, которые равны «Персик» или «Яблоко», вы должны объединить две функции СЧЁТЕСЛИ вместе, как показано ниже:

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

Press Enter ключ, чтобы получить счет.

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

=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")

Press Enter ключ, чтобы получить счет.

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

Подсчитайте, являются ли ячейки уникальными или повторяющимися значениями

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

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

1. В столбце рядом со значениями, которые вы хотите подсчитать, используйте приведенную ниже формулу, чтобы определить, является ли связанное значение уникальным или повторяющимся. ИСТИНА указывает, что связанное значение дублируется, НЕПРАВДА означает, что значение уникально.
   

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

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

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

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

  =COUNTIF(B4:B13,"FALSE")

• Для подсчета повторяющихся значений используйте приведенную ниже формулу.
  

  =COUNTIF(B4:B13,"TRUE")

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

СЧЁТЕСЛИ – примечания и некоторые важные вопросы

• Функция СЧЁТЕСЛИ не чувствительна к регистру.

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

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

• Функция СЧЁТЕСЛИ возвращает неправильные результаты при использовании для сопоставления строк длиннее 255 символов.

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