Как использовать функцию СЧЁТЕСЛИМН в Excel -

Содержание

Related videos

Резюме

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

Цель

Подсчет ячеек, соответствующих нескольким критериям

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

Сколько раз выполнялись критерии

Синтаксис

= СЧЁТЕСЛИМН (диапазон1; критерий1; (диапазон2); (критерий2);…)

Аргументы

диапазон1 - первый диапазон для оценки.
критерий1 - критерий для использования в диапазоне1.
диапазон2 - (необязательно) Второй диапазон для оценки.
критерий2 - (необязательно) критерий для использования в диапазоне2.

Версия

Excel 2007

Примечания по использованию

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

Критерии могут включать логические операторы (>, <,, =) и подстановочные знаки (*,?) Для частичного соответствия. Критерии также могут быть основаны на значении из другой ячейки, как описано ниже.

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

Базовый пример

В показанном примере можно использовать СЧЁТЕСЛИМН для подсчета записей с использованием 2 следующих критериев:

=COUNTIFS(C5:C14,"red",D5:D14,"tx") // red and TX =COUNTIFS(C5:C14,"red",F5:F14,">20") // red and>20

Обратите внимание, что функция СЧЁТЕСЛИМН не чувствительна к регистру.

Двойные кавычки ("") в критериях

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

=COUNTIFS(A1:A10,100) // count equal to 100 =COUNTIFS(A1:A10,">50") // count greater than 50 =COUNTIFS(A1:A10,"jim") // count equal to "jim"

Примечание: показано одно условие только для простоты. Дополнительные условия должны соответствовать тем же правилам.

Значение из другой ячейки

При использовании значения из другой ячейки в условии ссылка на ячейку должна быть объединена с оператором при использовании. В приведенном ниже примере СЧЁТЕСЛИМН будет подсчитывать значения в ячейке A1: A10, которые меньше значения в ячейке B1. Обратите внимание, что оператор «меньше» (текст) заключен в кавычки, а ссылка на ячейку - нет:

=COUNTIFS(A1:A10,"<"&B1) // count cells less than B1

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

Не равно

Чтобы создать критерий «не равно», используйте оператор «», заключенный в двойные кавычки («»). Например, приведенная ниже формула будет подсчитывать ячейки, не равные «красным» в диапазоне A1: A10:

=COUNTIFS(A1:A10,"red") // not "red"

Пустые ячейки

COUNTIFS can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:

=COUNTIFS(A1:A10,"") // not blank =COUNTIFS(A1:A10,"") // blank

Dates

The easiest way to use COUNTIFS with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than a date in B1, you can use a formula like this:

=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1

Notice we concatenate the ">" operator to the date in B1, but and are no quotes around the cell reference.

The safest way hardcode a date into COUNTIFS is with the DATE function. This guarantees Excel will understand the date. To count cells in A1:A10 that contain a date less than September 1, 2020, you can use:

=COUNTIFS(A1:A10,"<"&DATE(2020,9,1)) // dates less than 1-Sep-2020

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character, and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in a A1:A5 that contain the text "apple" anywhere, you can use a formula like this:

=COUNTIFS(A1:A5,"*apple*") // count cells that contain "apple"

The tilde (~) is an escape character to allow you to find literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

Notes

Multiple conditions are applied with AND logic, i.e. condition 1 AND condition 2, etc.
Each additional range must have the same number of rows and columns as range1, but ranges do not need to be adjacent. If you supply ranges that don't match, you'll get a #VALUE error.
Non-numeric criteria needs to be enclosed in double quotes but numeric criteria does not. For example: 100, "100", ">32", "jim", or A1 (where A1 contains a number).
The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters.
To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).