Подсчитайте количество строк, содержащих определенные значения в Excel
Нам может быть легко подсчитать количество ячеек с определенным значением на листе Excel. Однако, чтобы получить количество строк, содержащих определенные значения, может быть довольно сложно. В этом случае более сложная формула, основанная на функциях СУММ, МУМНОЖ, ТРАНСП и СТОЛБЕЦ, может помочь вам. В этом руководстве мы расскажем о том, как создать эту формулу для выполнения этой задачи в Excel.
Подсчет количества строк, содержащих определенные значения
Например, у вас есть диапазон значений на листе, и теперь вам нужно подсчитать количество строк с заданным значением «300», как показано на скриншоте ниже:
Чтобы получить количество строк, содержащих определенные значения, общий синтаксис следующий:
Массивная формула, следует нажать клавиши Ctrl + Shift + Enter вместе.
- данные: Диапазон ячеек для проверки на наличие определенного значения;
- 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 вместе, чтобы получить общее количество:
Используемые связанные функции:
- СУММ:
- Функция Excel СУММ возвращает сумму предоставленных значений.
- МУМНОЖ:
- Функция Excel МУМНОЖ возвращает матричное произведение двух массивов.
- ТРАНСП:
- Функция ТРАНСП вернет массив в новой ориентации на основе определенного диапазона ячеек.
- СТОЛБЕЦ:
- Функция СТОЛБЕЦ возвращает номер столбца, в котором появляется формула, или возвращает номер столбца указанной ссылки.
Больше статей:
- Подсчет строк при соблюдении внутренних критериев
- Предположим, у вас есть отчет о продажах продукции за этот год и прошлый год, и теперь вам может понадобиться подсчитать продукты, где продажи в этом году больше, чем в прошлом году, или продажи в этом году меньше, чем в прошлом году, как показано на скриншоте ниже. Обычно вы можете добавить вспомогательный столбец для расчета разницы в продажах между двумя годами, а затем использовать СЧЁТЕСЛИ для получения результата. Но в этой статье я представлю функцию СУММПРОИЗВ, чтобы получить результат напрямую без какого-либо вспомогательного столбца.
- Подсчет строк при соблюдении нескольких критериев
- Подсчет количества строк в диапазоне на основе нескольких критериев, некоторые из которых зависят от логических тестов, работающих на уровне строки, функция СУММПРОИЗВ в Excel может вам помочь.
- Подсчет количества ячеек, равных одному из многих значений
- Предположим, у меня есть список продуктов в столбце A, теперь я хочу получить общее количество конкретных продуктов Apple, Grape и Lemon, которые перечислены в диапазоне C4:C6 из столбца A, как показано на скриншоте ниже. Обычно в Excel простые функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН не будут работать в этом сценарии. В этой статье я расскажу о том, как решить эту задачу быстро и легко с помощью комбинации функций СУММПРОИЗВ и СЧЁТЕСЛИ.
Лучшие инструменты для повышения производительности Office
Kutools для Excel - Помогает вам выделиться из толпы
Kutools для Excel имеет более 300 функций, гарантируя, что нужный вам инструмент находится всего в одном клике...
Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)
- Один щелчок мыши, чтобы переключаться между десятками открытых документов!
- Сократите сотни кликов мышью ежедневно, попрощайтесь с болью в руке.
- Увеличивает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.