✅ Как использовать несколько критериев в Excel СЧЁТЕСЛИ и СЧЁТЕСЛИМН

Содержание

Введение в функции Excel СЧЁТЕСЛИ и СЧЁТЕСЛИМН
Использование критерия NUMBER в функциях Excel СЧЁТЕСЛИ
Использование критериев ТЕКСТА в функциях Excel
Использование критериев DATE в функциях Excel СЧЁТЕСЛИ и СЧЁТЕСЛИМН
Использование СИМВОЛОВ WILDCARD в критериях функций СЧЁТЕСЛИ и СЧЁТЕСЛИМН

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

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

Хотя в этом руководстве я в первую очередь сосредоточусь на функциях СЧЁТЕСЛИ и СЧЁТЕСЛИМН, все эти примеры также можно использовать в других функциях Excel, которые принимают несколько критериев в качестве входных данных (например, СУММЕСЛИ, СУММЕСЛИ, СРЕДНЕЛИ и СРЕДНЕЕСЛИ).

Введение в функции Excel СЧЁТЕСЛИ и СЧЁТЕСЛИМН

Давайте сначала познакомимся с использованием функций СЧЁТЕСЛИ и СЧЁТЕСЛИ в Excel.

Функция СЧЁТЕСЛИ в Excel (принимает единичные критерии)

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

Синтаксис

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

Входные аргументы

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

Функция СЧЁТЕСЛИМН в Excel (принимает несколько критериев)

Функция Excel СЧЁТЕСЛИМН лучше всего подходит для ситуаций, когда вы хотите подсчитать ячейки на основе нескольких критериев.

Синтаксис

= COUNTIFS (диапазон_ критериев1, критерий1, [диапазон_ критериев2, критерий2]…)

Входные аргументы

диапазон_критериев1 - Диапазон ячеек, для которых вы хотите оценить соответствие критериям1.
критерии1 - критерии, которые вы хотите оценить для диапазона_критерия1, чтобы определить, какие ячейки нужно подсчитать.
[диапазон_критериев2] - Диапазон ячеек, для которых вы хотите оценить критерии 2.
[критерий2] - критерии, которые вы хотите оценить для диапазона_критерия2, чтобы определить, какие ячейки нужно подсчитать.

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

Использование критерия NUMBER в функциях Excel СЧЁТЕСЛИ

# 1 Подсчет ячеек, когда критерии РАВНЫ значению

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

Ниже приведен пример, в котором мы подсчитываем ячейки, содержащие число 9 (что означает, что аргумент критерия равен 9). Вот формула:

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 11; D3)

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

= СЧЁТЕСЛИ (2 млрд долларов США: 11,9 млрд долларов США)

# 2 Подсчет ячеек, когда критерий БОЛЬШЕ, чем значение

Чтобы получить количество ячеек со значением, превышающим указанное значение, мы используем оператор «больше» («>»). Мы могли бы использовать его непосредственно в формуле или использовать ссылку на ячейку с критериями.

Всякий раз, когда мы используем оператор в критериях Excel, нам нужно заключать его в двойные кавычки. Например, если критерий больше 10, тогда нам нужно ввести «> 10» в качестве критерия (см. Рис. Ниже):

Вот формула:

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 11, "> 10")

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

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 11; D3)

Также может быть случай, когда вы хотите, чтобы критерии были в ячейке, но не хотите, чтобы это было с оператором. Например, вы можете захотеть, чтобы в ячейке D3 было число 10, а не> 10.

В этом случае вам необходимо создать аргумент критерия, который представляет собой комбинацию оператора и ссылки на ячейку (см. Рис. Ниже):

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 11, ”>” & D3)

ПРИМЕЧАНИЕ. При объединении оператора и ссылки на ячейку оператор всегда заключен в двойные кавычки. Оператор и ссылка на ячейку соединены амперсандом (&).

# 3 Подсчет ячеек, когда критерий МЕНЬШЕ значения

Чтобы получить количество ячеек со значением меньше указанного, мы используем оператор «меньше» («<«). Мы могли бы использовать его непосредственно в формуле или использовать ссылку на ячейку с критериями.

Всякий раз, когда мы используем оператор в критериях в Excel, нам нужно заключать его в двойные кавычки. Например, если критерием является то, что число должно быть меньше 5, тогда нам нужно ввести «<5» в качестве критерия (см. Рис. Ниже):

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 11, "<5")

Вы также можете иметь критерии в ячейке и использовать ссылку на ячейку в качестве критерия. В этом случае НЕ нужно заключать критерии в двойные кавычки (см. Рис. Ниже):

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 11; D3)

Кроме того, может быть случай, когда вы хотите, чтобы критерии были в ячейке, но не хотите, чтобы это было с оператором. Например, вы можете захотеть, чтобы в ячейке D3 было число 5, а не <5.

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

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 11, "<" & D3)

# 4 Подсчет ячеек с несколькими критериями - между двумя значениями

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

Вот два способа сделать это:

МЕТОД 1. Использование функции СЧЁТЕСЛИМН

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

= COUNTIFS ($ B $ 2: $ B $ 11, ”> 5 ″, $ B $ 2: $ B $ 11,” <10 ″)

ПРИМЕЧАНИЕ. Приведенная выше формула не учитывает ячейки, содержащие 5 или 10. Если вы хотите включить эти ячейки, используйте операторы «больше, чем равно» (> =) и «меньше, чем равно» (<=). Вот формула:

= COUNTIFS ($ B $ 2: $ B $ 11, ”> = 5 ″, $ B $ 2: $ B $ 11,” <= 10 ″)

Вы также можете поместить эти критерии в ячейки и использовать ссылку на ячейку в качестве критерия. В этом случае НЕ нужно заключать критерии в двойные кавычки (см. Рис. Ниже):

Вы также можете использовать комбинацию ссылок на ячейки и операторов (где оператор вводится непосредственно в формуле). Когда вы комбинируете оператор и ссылку на ячейку, оператор всегда находится в двойных кавычках. Оператор и ссылка на ячейку соединены амперсандом (&).

МЕТОД 2: Использование двух функций СЧЁТЕСЛИ

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

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 11, ”> 5 ″) - СЧЁТЕСЛИ ($ B $ 2: $ B $ 11,”> 10 ″)

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

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

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 11, ”> = 5 ″) - СЧЁТЕСЛИ ($ B $ 2: $ B $ 11,”> 10 ″)

Если вы хотите, чтобы формула исключала из подсчета как «5», так и «10», используйте следующую формулу:

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 11, "> = 5 ″) - СЧЁТЕСЛИ ($ B $ 2: $ B $ 11,"> 10 ″) - СЧЁТЕСЛИ ($ B $ 2: $ B $ 11,10)

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

Использование критериев ТЕКСТА в функциях Excel

# 1 Подсчет ячеек, когда критерии РАВНЫ указанному тексту

Чтобы подсчитать ячейки, содержащие точное совпадение с указанным текстом, мы можем просто использовать этот текст в качестве критерия. Например, в наборе данных (показанном ниже на рисунке), если я хочу подсчитать все ячейки с именем Джо в нем, я могу использовать следующую формулу:

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 11, «Джо»)

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

Вы также можете иметь критерии в ячейке, а затем использовать эту ссылку на ячейку (как показано ниже):

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 11; E3)

ПРИМЕЧАНИЕ. Вы можете получить неверные результаты, если в критерии или диапазоне критериев есть начальные / конечные пробелы. Убедитесь, что вы очистили данные перед использованием этих формул.

# 2 Подсчет ячеек, когда критерии НЕ РАВНЫ указанному тексту

Подобно тому, что мы видели в приведенном выше примере, вы также можете подсчитывать ячейки, не содержащие указанного текста. Для этого нам нужно использовать оператор not equal to ().

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

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 11, «Джо»)

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 11; E3)

Также может быть случай, когда вы хотите, чтобы критерии были в ячейке, но не хотите, чтобы это было с оператором. Например, вы можете захотеть, чтобы в ячейке D3 было имя Джо, а не Джо.

= СЧЁТЕСЛИ ($ B $ 2: $ B $ 11, ”” & E3)

Когда вы комбинируете оператор и ссылку на ячейку, оператор всегда находится в двойных кавычках. Оператор и ссылка на ячейку соединены амперсандом (&).

Использование критериев DATE в функциях Excel СЧЁТЕСЛИ и СЧЁТЕСЛИМН

Дата и время в Excel сохраняются в виде чисел. Таким образом, мы можем использовать его так же, как и числа.

# 1 Подсчет ячеек, когда критерии РАВНЫ указанной дате

Чтобы получить количество ячеек, содержащих указанную дату, мы будем использовать оператор равенства (=) вместе с датой.

Чтобы использовать дату, я рекомендую использовать функцию ДАТА, так как она избавляет от любой возможности ошибки в значении даты. Так, например, если я хочу использовать дату 1 сентября 2015 года, я могу использовать функцию ДАТА, как показано ниже:

= ДАТА (2015,9,1)

Эта формула вернет ту же дату, несмотря на региональные различия. Например, 01-09-2015 будет 1 сентября 2015 года в соответствии с синтаксисом даты в США и 9 января 2015 года в соответствии с синтаксисом даты в Великобритании. Однако эта формула всегда возвращает 1 сентября 2105 года.

Вот формула для подсчета количества ячеек, содержащих дату 02-09-2015:

= СЧЁТЕСЛИ ($ A $ 2: $ A $ 11, DATE (2015,9,2))

# 2 Подсчет ячеек, когда критерий ДО или ПОСЛЕ указанной даты

Для подсчета ячеек, содержащих дату до или после указанной даты, мы можем использовать операторы «меньше / больше».

Например, если я хочу подсчитать все ячейки, содержащие дату после 2 сентября 2015 года, я могу использовать формулу:

= СЧЁТЕСЛИ ($ A $ 2: $ A $ 11, ”>” & ДАТА (2015,9,2))

Точно так же вы также можете подсчитать количество ячеек до указанной даты. Если вы хотите включить дату в подсчет, используйте оператор «равно» вместе с оператором «больше / меньше».

Вы также можете использовать ссылку на ячейку, содержащую дату. В этом случае вам нужно объединить оператор (в двойных кавычках) с датой с помощью амперсанда (&).

См. Пример ниже:

= СЧЁТЕСЛИ ($ A $ 2: $ A $ 11, ”>” & F3)

# 3 Подсчет ячеек с несколькими критериями - между двумя датами

Мы можем сделать это двумя способами - одной функцией СЧЁТЕСЛИ или двумя функциями СЧЁТЕСЛИ.

МЕТОД 1. Использование функции СЧЁТЕСЛИМН

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

= СЧЁТЕСЛИМН ($ A $ 2: $ A $ 11, ">" & ДАТА (2015,9,2), $ A $ 2: $ A $ 11, "<" & ДАТА (2015,9,7))

В приведенной выше формуле не учитываются ячейки, содержащие указанные даты. Если вы хотите включить и эти даты, используйте операторы «больше, чем равно» (> =) и «меньше, чем равно» (<=). Вот формула:

= СЧЁТЕСЛИМН ($ A $ 2: $ A $ 11, ”> =” & ДАТА (2015,9,2), $ A $ 2: $ A $ 11, ”<=” & ДАТА (2015,9,7))

Вы также можете указать даты в ячейке и использовать ссылку на ячейку в качестве критерия. В этом случае нельзя иметь оператора с датой в ячейках. Вам необходимо вручную добавить операторы в формулу (в двойных кавычках) и добавить ссылку на ячейку с помощью амперсанда (&). Смотрите картинку ниже:

= COUNTIFS ($ A $ 2: $ A $ 11, ”>” & F3, $ A $ 2: $ A $ 11, ”<“ & G3)

МЕТОД 2: Использование функций СЧЁТЕСЛИ

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

= СЧЁТЕСЛИ ($ A $ 2: $ A $ 11, ”>” & ДАТА (2015,9,2)) - СЧЁТЕСЛИ ($ A $ 2: $ A $ 11, ”>” & ДАТА (2015,9,7))

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

Если вы не хотите, чтобы формула учитывала одновременно 2 и 7 сентября, используйте следующую формулу:

= СЧЁТЕСЛИ ($ A $ 2: $ A $ 11, ”> =” & ДАТА (2015,9,2)) - СЧЁТЕСЛИ ($ A $ 2: $ A $ 11, ”>” & ДАТА (2015,9,7))

Если вы хотите исключить из подсчета обе даты, используйте следующую формулу:

= COUNTIF ($ A $ 2: $ A $ 11, ”>” & ДАТА (2015,9,2)) - COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7) -COUNTIF ($ A 2 доллара: 11 австралийских долларов, DATE (2015,9,7)))

Кроме того, вы можете иметь даты критериев в ячейках и использовать ссылки на ячейки (вместе с операторами в двойных кавычках, соединенными с помощью амперсанда).

Использование СИМВОЛОВ WILDCARD в критериях функций СЧЁТЕСЛИ и СЧЁТЕСЛИМН

В Excel есть три подстановочных символа:

* (звездочка) - Он представляет собой любое количество символов. Например, ex * может означать excel, excels, example, expert и т. Д.
? (вопросительный знак) - Он представляет собой один-единственный персонаж. Например, Tr? Mp может означать Трампа или Бродяги.
~ (тильда) - Он используется для обозначения подстановочного знака (~, *,?) В тексте.

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

Теперь давайте рассмотрим различные примеры:

# 1 Подсчет ячеек, содержащих текст

Чтобы подсчитать ячейки с текстом в нем, мы можем использовать подстановочный знак * (звездочка). Поскольку звездочка представляет любое количество символов, она будет считать все ячейки, в которых есть какой-либо текст. Вот формула:

= СЧЁТЕСЛИМН ($ C $ 2: $ C $ 11, ”*”)

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

Вот подробное руководство по обработке случаев, когда есть пустая строка или апостроф.

Вот подробное руководство по обработке случаев, когда есть пустые строки или апострофы.

Ниже представлено видео, в котором объясняются различные сценарии подсчета ячеек с текстом.

# 2 Подсчет непустых ячеек

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

Попробуйте, и он может потерпеть неудачу. COUNTA также будет считать ячейку, содержащую пустую строку (часто возвращаемую формулами как = ”” или когда люди вводят в ячейку только апостроф). Ячейки, содержащие пустые строки, выглядят пустыми, но это не так, и поэтому они подсчитываются функцией COUNTA.

COUNTA также будет считать ячейку, содержащую пустую строку (часто возвращаемую формулами как = ”” или когда люди вводят в ячейку только апостроф). Ячейки, содержащие пустые строки, выглядят пустыми, но это не так, и поэтому они подсчитываются функцией COUNTA.

Поэтому, если вы используете формулу = COUNTA (A1: A11), она вернет 11, а должна вернуть 10.

Вот исправление:

= СЧЁТЕСЛИ ($ A $ 1: $ A $ 11, ”? *”) + СЧЁТ ($ A $ 1: $ A $ 11) + СУММПРОИЗВ (-ISLOGICAL ($ A $ 1: $ A $ 11))

Давайте разберемся в этой формуле:

СЧЁТЕСЛИ ($ N $ 8: $ N $ 18, ”? *”) - эта часть формулы возвращает 5. Сюда входит любая ячейка, содержащая текстовый символ. А? представляет один символ, а * представляет любое количество символов. Следовательно, комбинация? * В критериях заставляет Excel подсчитывать ячейки, в которых есть хотя бы один текстовый символ.
COUNT ($ A $ 1: $ A $ 11) - подсчитывает все ячейки, содержащие числа. В приведенном выше примере он возвращает 3.
СУММПРОИЗВ (-ISLOGICAL ($ A $ 1: $ A $ 11) - подсчитывает все ячейки, содержащие логические значения. В приведенном выше примере возвращается 2.

# 3 Подсчет ячеек, содержащих определенный текст

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

= СЧЁТЕСЛИМН ($ C $ 2: $ C $ 11, ”J *”)

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

Если вы хотите подсчитать ячейки, содержащие алфавит в любом месте текста, отметьте его звездочкой с обеих сторон. Например, если вы хотите подсчитать ячейки, содержащие в себе алфавит «а», используйте в качестве критерия * а *.

Эта статья необычно длинная по сравнению с другими моими статьями. Надеюсь, вам понравилось. Сообщите мне свои мысли, оставив комментарий.

Вы также можете найти следующие полезные руководства по Excel: