Подсчёт количества строк, содержащих определённые значения, в Excel

Подсчитать количество ячеек с определённым значением на листе Excel довольно просто. Однако подсчёт строк, содержащих конкретные значения, может оказаться задачей посложнее. На помощь здесь придёт продвинутая формула, сочетающая функции СУММ, МУМНОЖ, ТРАНСП и СТОЛБЕЦ. В этом руководстве мы покажем, как создать такую формулу и эффективно решить эту задачу в Excel.

• Подсчёт количества строк, содержащих определённые значения

Подсчёт количества строк, содержащих определённые значения

Например, у вас есть диапазон значений на листе, и вам нужно подсчитать количество строк со значением «300», как показано на скриншоте ниже:

Общий синтаксис формулы для подсчёта количества строк, содержащих определённые значения:

• data: диапазон ячеек для проверки на наличие конкретного значения;
• XКонкретное значение, по которому выполняется подсчёт строк.

1. Введите или скопируйте приведённую ниже формулу в пустую ячейку, куда хотите поместить результат:

2. Затем одновременно нажмите клавиши Ctrl + Shift + Enter, чтобы получить корректный результат (см. скриншот):

Пояснение к формуле:

=SUM(--(MMULT(--($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>,0))

• --$A$2:$C$12=300: это выражение проверяет, содержится ли значение «300» в диапазоне A2:C12, и создаёт массив из значений ИСТИНА и ЛОЖЬ. Двойной знак минус преобразует ИСТИНА в 1, а ЛОЖЬ — в 0. В результате вы получите следующий массив: {0,0,0;1,0,0;0,0,0;0,1,1;0,0,0;0,1,0;0,0,0;1,0,0;0,0,1;0,0,0;1,1,1}. Этот массив размером 11 строк на 3 столбца будет использоваться как массив1 в функции МУМНОЖ.
• ТРАНСП(COLUMN($A$2:$C$12)): здесь функция СТОЛБЕЦ возвращает номера столбцов диапазона A2:C12, формируя трёхстолбцовый массив {1;2;3}. Затем функция ТРАНСП преобразует его в трёхстрочный массив {1;2;3}, который используется как массив2 в функции МУМНОЖ.
• МУМНОЖ(--($A$2:$C$12=«Joanna»),TRANSPOSE(COLUMN($A$2:$C$12))): эта функция МУМНОЖ возвращает матричное произведение двух указанных выше массивов, и вы получите следующий результат: {0;1;0;5;0;2;0;1;3;0;6}.
• SUM(--(MMULT(--($A$2:$C$12=«Joanna»),TRANSPOSE(COLUMN($A$2:$C$12)))>0))= SUM(--{0;1;0;5;0;2;0;1;3;0;6}>0): сначала проверяется, какие значения в массиве больше 0: если значение больше 0 — получаем ИСТИНА, если нет — ЛОЖЬ. Затем двойной унарный минус преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно, и вы получаете следующий массив: SUM({0;1;0;1;0;1;0;1;1;0;1}). Наконец, функция СУММ складывает все элементы массива и возвращает результат: 6.

Советы:

Если вам нужно подсчитать количество строк, содержащих определённый текст на листе, используйте приведённую ниже формулу и не забудьте нажать одновременно клавиши Ctrl + Shift + Enter, чтобы получить итоговое количество:

Используемые связанные функции:

Другие статьи: