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

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

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

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

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

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

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

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

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

Объяснение формулы:

=СУММ(--(МУМНОЖ(--($A$2:$C$12=300);ТРАНСП(СТОЛБЕЦ($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 столбцов, будет работать как array1 в функции МУМНОЖ.
• ТРАНСП(СТОЛБЕЦ($A$2:$C$12)): Функция СТОЛБЕЦ здесь используется для получения номера столбца диапазона A2:C12, она возвращает массив из 3 столбцов следующего вида: {1,2,3}. А затем функция ТРАНСП меняет этот массив на массив из 3 строк {1;2;3}, функционируя как array2 внутри функции МУМНОЖ.
• МУМНОЖ(--($A$2:$C$12="Joanna"),ТРАНСП(СТОЛБЕЦ($A$2:$C$12))): Эта функция МУМНОЖ возвращает матричное произведение двух вышеуказанных массивов, вы получите результат следующего вида: {0;1;0;5;0;2;0;1;3;0;6}.
• СУММ(--(МУМНОЖ(--($A$2:$C$12="Joanna"),ТРАНСП(СТОЛБЕЦ($A$2:$C$12)))>0))= СУММ(--{0;1;0;5;0;2;0;1;3;0;6}>0): Сначала проверьте значения в массиве больше 0: Если значение больше 0, отображается ИСТИНА; если меньше 0, отображается ЛОЖЬ. А затем двойной отрицательный знак заставляет ИСТИНЫ и ЛОЖИ становиться 1 и 0, так что вы получите это: СУММ({0;1;0;1;0;1;0;1;1;0;1}). Наконец, функция СУММ суммирует значения в массиве, чтобы вернуть результат: 6.

Советы:

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

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

Больше статей: