Подсчитать количество строк, содержащих определенные значения в Excel
Нам может быть легко подсчитать количество ячеек с определенным значением на листе Excel. Однако получить количество строк, содержащих определенные значения, может быть довольно сложно. В этом случае более сложная формула, основанная на функциях СУММ, ММУЛЬТИ, ТРАНСПОРТ и СТОЛБЕЦ, может оказать вам услугу. В этом руководстве будет рассказано о том, как создать эту формулу для решения этой задачи в Excel.
Подсчитать количество строк, содержащих определенные значения
Например, у вас есть диапазон значений на листе, и теперь вам нужно подсчитать количество строк с заданным значением «300», как показано ниже:
Чтобы получить количество строк, содержащих определенные значения, общий синтаксис:
Array formula, should press Ctrl + Shift + Enter keys together.
- data: Диапазон ячеек, которые нужно проверить, содержат ли они определенное значение;
- X: Конкретное значение, которое вы используете для подсчета строк.
1. Введите или скопируйте приведенную ниже формулу в пустую ячейку, в которую вы хотите поместить результат:
2, Затем нажмите Shift + Ctrl + Enter вместе, чтобы получить правильный результат, см. снимок экрана:
Пояснение к формуле:
=SUM(--(MMULT(--($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))
- - 2 австралийских доллара: 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 столбцов, будет работать как arrayXNUMX в функции MMULT.
- ТРАНСПОРТ (КОЛОНКА (2 доллара США: 12 канадских долларов)): Функция COLUMN здесь используется для получения номера столбца диапазона A2: C12, она возвращает массив из 3 столбцов, например: {1,2,3}. Затем функция TRANSPOSE меняет этот массив на трехстрочный массив {3; 1; 2}, который функционирует как array3 в функции MMULT.
- MMULT (- ($ A $ 2: $ C $ 12 = "Джоанна"), TRANSPOSE (COLUMN ($ A $ 2: $ C $ 12))): Эта функция MMULT возвращает матричное произведение двух вышеуказанных массивов, вы получите следующий результат: {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, отображается ИСТИНА; если меньше 0, отображается ЛОЖЬ. Затем двойной отрицательный знак заставляет ИСТИНЫ и ЛОЖЬ равняться 1 и 0, поэтому вы получите следующее: СУММ ({0; 1; 0; 1; 0; 1; 0; 1; 1; 0; 1}). Наконец, функция СУММ суммирует значения в массиве и возвращает результат: 6.
Советы:
Если вам нужно подсчитать количество строк, содержащих определенный текст на листе, примените приведенную ниже формулу и не забудьте нажать кнопку Shift + Ctrl + Enter ключи вместе, чтобы получить общее количество:
Используемая относительная функция:
- СУММА:
- Функция СУММ в Excel возвращает сумму предоставленных значений.
- МУЛЬТИ:
- Функция Excel MMULT возвращает матричное произведение двух массивов.
- ТРАНСПОРТ:
- Функция TRANSPOSE вернет массив в новой ориентации на основе определенного диапазона ячеек.
- КОЛОНКА:
- Функция COLUMN возвращает номер столбца, в котором отображается формула, или номер столбца для данной ссылки.
Другие статьи:
- Подсчитать строки, если они соответствуют внутренним критериям
- Предположим, у вас есть отчет о продажах продукции в этом и прошлом году, и теперь вам может потребоваться подсчитать продукты, продажи в которых в этом году больше, чем в прошлом году, или продажи в этом году меньше, чем в прошлом году, как показано ниже. показан снимок экрана. Обычно вы можете добавить вспомогательный столбец для расчета разницы продаж за два года, а затем использовать COUNTIF для получения результата. Но в этой статье я представлю функцию СУММПРОИЗВ, чтобы получить результат напрямую, без какого-либо вспомогательного столбца.
- Подсчитайте строки, если они соответствуют нескольким критериям
- Подсчитайте количество строк в диапазоне на основе нескольких критериев, некоторые из которых зависят от логических тестов, работающих на уровне строк, функция СУММПРОИЗВ в Excel может оказать вам услугу.
- Подсчитать количество ячеек равно одному из многих значений
- Предположим, у меня есть список продуктов в столбце A, теперь я хочу получить общее количество конкретных продуктов Apple, Grape и Lemon, которые перечислены в диапазоне C4: C6 из столбца A, как показано на скриншоте ниже. Обычно в Excel простые функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН не работают в этом сценарии. В этой статье я расскажу о том, как быстро и легко решить эту задачу с помощью комбинации функций СУММПРОИЗВ и СЧЁТЕСЛИ.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
