Countifs excel как пользоваться

Обновлено: 04.07.2024

Функции Excel могут сэкономить много времени, независимо от того, с какими данными вы имеете дело. Одним из любимых является COUNTIFS, и изучение того, как использовать функции COUNTIFS с несколькими критериями в Excel, является отличным шагом на пути к доминированию данных. Вот как начать.

Инструкции в этой статье применяются к Excel в Office 365, Excel 2019 и Excel 2016.

Что такое функция COUNTIFS?

Excel может быть невероятно универсальным с ручным вводом и анализом данных, но это еще более полезно, когда вы автоматизируете его части. Вот где приходят функции. От использования SUM для выполнения различных численных расчетов до удаления непечатаемых символов с помощью CLEAN . COUNTIFS работает во многом таким же образом, но, как и функция COUNTIF, COUNTIFS полезен для анализа данных. В то время как COUNTIF просматривает один диапазон данных и критериев, COUNTIFS просматривает кратные каждого из них.

Он берет эти входы и выводит общее количество в зависимости от того, что вы ищете.

Как использовать функцию COUNTIFS в Excel

Функция COUNTIFS может быть введена вручную или с помощью меню формул Excel . В любом случае конечная формула будет выглядеть примерно так:

= COUNTIFS (D4: D17, «Да», Е4: E17, «> = 5»)

В этом примере функция COUNTIFS ищет в ячейках D4-D17 текст « Да» и в ячейках E4-E17 числа, которые равны или превышают пять. В тех случаях, когда он обнаруживает, что оба критерия выполнены, он записывает один экземпляр, а затем суммирует их все, выводя количество экземпляров обоих критериев, встречающихся в данных.

Вот как использовать меню «Формула» для выполнения этой функции.

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

Выберите ячейку, в которой вы хотите отобразить результаты функции COUNTIFS.

Выберите меню функций . Это небольшой логотип FX в левом верхнем углу главного окна, рядом со значками крестика и галочки.

Рядом с Или выберите категорию, используйте раскрывающееся меню, чтобы выбрать Все, а затем в поле поиска введите COUNTIFS . Выберите соответствующий результат (убедитесь, что выбрали COUNTIFS , а не COUNTIF ) и нажмите OK .

В открывшемся окне « Аргументы функции» введите Criteria_Range1 (начало и конец, разделенные двоеточием) или нажмите / коснитесь и перетащите ячейки, которые вы хотите использовать в качестве части вычисления. В нашем тестовом примере это ячейка от D4 до D17, поэтому она вводится как D4: D17 .

Введите или выберите критерий1, который вы хотите, чтобы функция COUNTIFS учитывала. В нашем примере мы хотим, чтобы он учитывал все результаты « Да» в столбце «D», поэтому мы вводим « Да» .

Сделайте то же самое с Criteria_Range2 и Criteria2 , выбрав ячейки и введя критерии, которые вы ищете. В нашем примере мы ищем людей, которые посетили Lifewire пять или более раз, поэтому мы добавили E4: E17 и > = 5.

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

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

В этом примере вышеупомянутые шаги повторяются для людей, которые сказали, что они не любили Lifewire, но все еще посещали пять или более раз. Как и следовало ожидать, это привело к гораздо меньшему количеству, но это все еще интересные данные, полученные из набора данных.

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

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

Описание

Освободи Себя COUNTIFS Функция возвращает количество ячеек, соответствующих одному или нескольким критериям. Функция СЧЁТЕСЛИМН является одной из встроенных статистических функций, и ее можно использовать как функцию рабочего листа (WS) в Excel. Функция СЧЁТЕСЛИМН поддерживает подстановочные знаки (? И *) и логические значения (<,>, <> и =).

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

Синтаксис формулы

COUNTIFS( criteria_range1 , criteria1 , [ criteria_range2 ],[ criteria2 ],…[ criteria_range_n ],[ criteria_n ])

аргументы

Criteria_range1: Required, the first range that you want to evaluate the first criteria.
Criteria1: Required, the first criteria that define which cells will be counted. It can be in the form of number, date, text, cell reference. For example, it can be expressed as <1, A2, “KTE”.
criteria_range2: Optional, the second range that you want to evaluate the second criteria.
Criteria2: Optional, the second criteria that define which cells will be counted.
criteria_range_n: Optional, the nth range that you want to evaluate the nth criteria, and it can be up to 127 ranges.
Criteria_n: Optional, the nth criteria that define which cells will be counted, it can be up to 127 criteria.

Возвращаемое значение

Освободи Себя COUNTIFS функция возвращает количество ячеек, соответствующих критериям.

Обычаи

Пример 1: подсчет ячеек на основе одного критерия

Предположим, вы хотите подсчитать ячейки, которые равны яблоку в столбце B3: B7, вы можете использовать следующую формулу

Нажмите Enter клавиша для подсчета количества ячеек, равных яблоку в диапазоне B2: B7.

Пример 2: подсчет строк по нескольким критериям

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

Нажмите Enter клавиша для подсчета количества ячеек, равных яблоку в диапазоне B2: B7, а также цены больше 10.

Дополнительные сведения о подсчете строк, отвечающих нескольким критериям, см. В Подсчитайте ячейки равными x и y.

Замечания

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

Если аргумент критерия является ссылкой на пустую ячейку, функция СЧЁТЕСЛИМН принимает пустую ячейку как нулевое значение.

Если вы хотите найти буквальный вопросительный знак или звездочку, добавьте тильду (

) перед вопросительным знаком (

Относительные функции:

Excel COUNTA функция
Освободи Себя COUNTA функция возвращает количество ячеек, содержащих число, текст, значение ошибки или пустой текст «», за исключением реальных пустых ячеек.

Excel COUNTBLANK функция
Освободи Себя COUNTBLANK функция возвращает количество пустых ячеек. Функция СЧИТАТЬПУСТОТЫ - это встроенная статистическая функция, которую можно использовать как функцию рабочего листа (WS) в Excel.

Excel COUNTIF функция
Освободи Себя COUNTIF Функция - это статистическая функция в Excel, которая используется для подсчета количества ячеек, соответствующих критерию.

Excel COUNT функция
Освободи Себя COUNT Функция используется для подсчета количества ячеек, содержащих числа, или подсчета чисел в списке аргументов. Возьмите пример, COUNT(1,2,3,4,11,2,1,0) возвращает 8.

Относительные статьи:

Считайте ячейки равными
Если вы хотите подсчитать ячейки, точно соответствующие заданному критерию, вы можете выбрать COUNTIF функция или SUMPRODUCT (в сочетании с EXACT function) функция для точного подсчета ячеек без учета регистра или с учетом регистра.

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

Считайте ячейки равными x или y
Если вы хотите подсчитывать ячейки, если они соответствуют хотя бы одному из заданных условий (логика ИЛИ) в Excel, т.е. подсчитывать ячейки, равные либо x, либо y, вы можете использовать несколько COUNTIF формулы или объедините COUNTIF формула и SUM формула для выполнения этой работы.

Подсчитайте ячейки равными x и y
Здесь, в этом руководстве, будут представлены некоторые формулы для подсчета ячеек, соответствующих всем заданным условиям, которые мы называем AND логика с COUNTIFS функции.

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

Описание

Освободи Себя COUNTIF Функция - это статистическая функция в Excel, которая используется для подсчета количества ячеек, соответствующих критерию. Он поддерживает логические операторы (<>, =,> и <), а также символы подстановки (? И *) для частичного сопоставления.

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

Синтаксис формулы

COUNTIF( range , criteria )

аргументы

Возвращаемое значение

Освободи Себя COUNTIF функция возвращает количество ячеек, удовлетворяющих указанным критериям.

Основное использование

Пример1 Подсчитать ячейки равными

Предположим, вы хотите подсчитать количество ячеек, равное 2, вы можете использовать формулу, как показано ниже:

Нажмите Enter Клавиша возвращает количество ячеек, равное 2.

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

Пример 2 Подсчитайте ячейки больше или меньше

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

Предположим, что существует диапазон (B2: D5) чисел, вы хотите подсчитать количество ячеек, превышающих 65, используя следующую формулу:

Нажмите Enter ключ для получения результата.

Если вы хотите подсчитать ячейки меньше числа, например, подсчитать ячейки меньше 50, просто используйте формулу, подобную этой =COUNTIF(B2:D5,"<50 ") нажмите здесь, чтобы узнать больше о подсчете ячеек, которые больше или меньше заданного значения.

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

Пример1 Количество ячеек не равно

нажмите на здесь чтобы узнать больше о формуле

Пример2 Подсчет ячеек, равных x или y

нажмите на здесь чтобы узнать больше о формуле

Относительные функции:

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

Относительные статьи:

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Подсчет пустых ячеек с функцией СЧЁТЕСЛИ
Подсчет непустых ячеек с функцией СЧЁТЕСЛИ
Подсчитайте непустые ячейки двумя щелчками мыши

Синтаксис функции СЧЁТЕСЛИ в Excel

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

Синтаксис содержит два аргумента - ассортимент и Критерии:

ассортимент: относится к диапазону ячеек, которые вы хотите подсчитать.
Критерии: относится к условию, которое вы хотите использовать для подсчета ячеек. Например, вы можете использовать «> = 80» в качестве критерия для поиска ячеек со значениями, которые больше или равны 80 в выбранном вами диапазоне.

Чтобы применить функцию в Excel, вы должны ввести = СЧЁТЕСЛИ (диапазон; критерии) в ячейке назначения. Например, = СЧЁТЕСЛИ (A1: B8; "> = 80").

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

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

Функция СЧЁТЕСЛИ для пустых или непустых ячеек

Например, у меня есть список ячеек, содержащий разные типы данных, такие как текст, логические значения (ИСТИНА и ЛОЖЬ), числа, даты и ошибки. Существует ли универсальная формула СЧЁТЕСЛИ для подсчета только пустых или непустых ячеек независимо от того, какие типы данных существуют в указанном вами диапазоне?

Подсчет пустых ячеек с функцией СЧЁТЕСЛИ

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

=COUNTIF(range,"")
√ Примечание. В кавычках нет текста, что означает, что ячейки, которые вы хотите подсчитать, пусты.

Чтобы использовать функцию СЧЁТЕСЛИ для подсчета пустых ячеек, введите формулу = СЧЁТЕСЛИ (A1: A9; "") в ячейке назначения, затем нажмите ENTER:

>>>

☞ В excel также есть функция под названием СЧИТАТЬПУСТОТЫ, который возвращает количество пустых ячеек в указанном диапазоне. Синтаксис этого = СЧИТАТЬПУСТОТЫ (диапазон). В приведенном выше примере вы можете использовать формулу: = СЧИТАТЬ ПУСТОЙ (A1: A9).

Подсчет непустых ячеек с функцией СЧЁТЕСЛИ

Чтобы подсчитать непустые ячейки, позвольте мне показать вам формулу:

=COUNTIF(range,"<>")
√ Примечание: <> в Excel означает не равный . Итак, в приведенной выше формуле подсчитываются все ячейки, которые не равны пустым, или, можно сказать, не пустые.

Чтобы использовать функцию СЧЁТЕСЛИ для подсчета непустых ячеек, введите формулу = СЧЁТЕСЛИ (A1: A9; «<>») в ячейке назначения, затем нажмите ENTER:

>>>

☞ В excel также есть функция под названием COUNTA, который возвращает количество ячеек, содержащих любые значения в указанном диапазоне. Синтаксис этого = COUNTA (диапазон). В приведенном выше примере вы можете использовать формулу: = СЧЕТЗ (А1: А9).

Однако, если вы хотите подсчитать только ячейки с текстом и исключить логические значения (ИСТИНА и ЛОЖЬ), числа, даты или ошибки, используйте формулу ниже:

=COUNTIF(A1:A9,"*")
√ Note: A single asterisk (*) matches only the value in a text form .

>>>

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

Если ты ха ve Kutools for Excel установлен в вашем Excel, с его Выбрать непустые ячейки вы можете выбрать все непустые ячейки сразу и получить нужное количество.

1. Вы можете перейти на Kutools вкладка в Excel, найдите Выберитеи нажмите Выбрать непустые ячейки в раскрывающемся списке. Смотрите скриншот:

2. Будут выбраны все непустые ячейки, и появится диалоговое окно с указанием количества непустых ячеек, как показано на скриншоте ниже:

Функция СЧЁТЕСЛИ для ячеек меньше, больше или равных определенному значению

Здесь мы перечислили два основных метода для подсчета ячеек со значениями, которые меньше, больше, равны или не равны определенному значению в Excel.

СЧЁТЕСЛИ меньше, больше, равно или не равно с формулами

Чтобы использовать формулу для подсчета ячеек со значениями, которые меньше, больше, равны или не равны определенному значению, вы должны хорошо использовать Операторы сравнения (<, >, =, ≠).

√ Примечание. Этот метод также можно использовать для подсчета ячеек с датами до (меньше <), после (больше>) или равных (=) указанной вами дате.

Теперь давайте посмотрим на несколько реальных примеров в соответствии с таблицей ниже:

☞ Функция СЧЁТЕСЛИ работает только при одном условии, для ситуации с большим количеством условий следует использовать функцию СЧЁТЕСЛИ. Например, чтобы подсчитать количество студентов, у которых оценка по английскому составляет от 60 до 90 (> 60, <90), используйте формулу: =COUNTIFS(B2:B8,">60",B2:B8,"<90").
Нажмите, чтобы узнать больше о функции СЧЁТЕСЛИМН…

СЧЁТЕСЛИ меньше, больше, равно или не равно за несколько кликов

Чтобы подсчитать ячейки со значениями, которые меньше, больше, равны или не равны определенному значению более удобным способом, вы можете применить Выбрать определенные ячейки особенность о f Kutools for Excel.

После установки Kutools for Excel выполните следующие действия:

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

2. Теперь вы увидите всплывающее окно, сделайте следующее:

Ниже приведен пример подсчета количества студентов, получивших оценку по математике ниже чем (<) счет Коко с Kutools.

√ Примечание. Программа вернет результат подсчета и выберет ячейку (я), которая соответствует вашим критериям. Вы можете скопировать их, если хотите.

Функция СЧЁТЕСЛИ для ячеек, содержащих определенное значение

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

Есть три символа подстановки - звездочка (*), вопрос отметка (?), И тильда (

), доступный в Excel для функции СЧЁТЕСЛИ:

) - соответствует фактическому подстановочному знаку. Например,

* означает буквальную звездочку,

означает буквальную тильду.

√ Примечание. Логические значения (ИСТИНА и ЛОЖЬ), числа, даты и ошибки не считаются символами. Поэтому, если в выбранном диапазоне ячеек есть элементы, указанные выше, вам придется преобразовать их в текст.

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

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

Подсчет ячеек, содержащих определенные слова или символы, с помощью нескольких щелчков мышью

С o ur Надстройка Excel инст alled, сделайте следующее:

1. К Kutools вкладка, найти Выберитеи нажмите Выбрать определенные ячейки в раскрывающемся списке.

2. в Выбрать определенные ячейки диалоговом окне выберите диапазон ячеек, который вы хотите подсчитать, а затем щелкните Ячейка под Тип выбора, выберите одно условие в раскрывающемся списке Конкретный тип в соответствии с вашими потребностями.

Здесь я приведу два примера использования Kutools считать t ячейки, содержащие определенные слова или символы.

• Чтобы подсчитать количество мальчиков в классе, нажмите Равно в раскрывающемся списке и введите M в правом поле ввода, или вы можете выбрать ячейку со значением «Мужской» на листе, нажав кнопку.

Программа вернет результат подсчета и выберет ячейку (я), которая соответствует вашим критериям. Вы можете скопировать их, если хотите.

• Чтобы подсчитать количество имен, содержащих символы «джефф», щелкните Комплект в раскрывающемся списке и введите Джеффили вы можете выбрать ячейку A6 на листе, нажав кнопку.

Инструкции в этой статье применяются к Excel в Office 365, Excel 2019 и Excel 2016.

Что такое функция COUNTIF?

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

Он принимает входные данные и выплевывает общее количество в зависимости от выбранных вами критериев.

Как использовать функцию COUNTIF в Excel

Функция COUNTIF может быть введена вручную или с помощью меню формул Excel. В любом случае конечная формула будет выглядеть примерно так:

= COUNTIF (D4: D10, «Да»)

В этом примере COUNTIF функция будет выполнять поиск в ячейках с D4 по D10 в поисках слова «Да». Затем будет выведено количество раз, сколько раз он найдет его в ячейке, в которую вводится формула.

Вы можете написать это вручную, если хотите, но более простой способ — использовать меню функций Excel.

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

Выберите ячейку, в которой вы хотите отобразить результаты функции COUNTIF.

Выберите логотип Function fx в верхнем левом углу главного окна, рядом с крестиком и отметками.

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

Выбирая эту функцию, убедитесь, что вы выбрали COUNTIF , а не COUNTIFS. COUNTIF использует один критерий, в котором COUNTIFS использует несколько критериев.

В открывшемся окне «Аргументы функции» введите «Диапазон» (начало и конец, разделенные двоеточием) или нажмите / коснитесь и перетащите ячейки, которые необходимо учитывать при расчете. В нашем примере COUNTIF это ячейка с D4 по D10, поэтому она вводится как D4: D10 .

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

Введите или выберите критерии, которые вы хотите, чтобы COUNTIF считал. В нашем примере мы хотим знать, сколько результатов Да в столбце D, поэтому мы вводим Да .

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

Если вы хотите искать результаты в нескольких диапазонах одновременно, вы можете сделать это, но вместо этого вам нужно будет использовать функцию COUNTIFS .

Читайте также: