Перейти к содержимому

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

Author: Xiaoyang Last Modified: 2025-06-05

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

doc-count-rows-with-specific-value-1


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

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

doc-count-rows-with-specific-value-2

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

{=SUM(–(MMULT(–(data=X),TRANSPOSE(COLUMN(data)))>0))}
Массивная формула, следует нажать клавиши Ctrl + Shift + Enter вместе.
  • данные: Диапазон ячеек для проверки на наличие определенного значения;
  • X: Конкретное значение, которое вы используете для подсчета строк.

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

=СУММ(--(МУМНОЖ(--($A$2:$C$12=300);ТРАНСП(СТОЛБЕЦ($A$2:$C$12)))>0))

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

doc-count-rows-with-specific-value-3


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

=СУММ(--(МУМНОЖ(--($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 вместе, чтобы получить общее количество:

=СУММ(--(МУМНОЖ(--(ЕЧИСЛО(ПОИСК("Joanna";A2:C12)));ТРАНСП(СТОЛБЕЦ($A$2:$C$12)))>0))

doc-count-rows-with-specific-value-4


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

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

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

  • Подсчет строк при соблюдении внутренних критериев
  • Предположим, у вас есть отчет о продажах продукции за этот год и прошлый год, и теперь вам может понадобиться подсчитать продукты, где продажи в этом году больше, чем в прошлом году, или продажи в этом году меньше, чем в прошлом году, как показано на скриншоте ниже. Обычно вы можете добавить вспомогательный столбец для расчета разницы в продажах между двумя годами, а затем использовать СЧЁТЕСЛИ для получения результата. Но в этой статье я представлю функцию СУММПРОИЗВ, чтобы получить результат напрямую без какого-либо вспомогательного столбца.
  • Подсчет количества ячеек, равных одному из многих значений
  • Предположим, у меня есть список продуктов в столбце A, теперь я хочу получить общее количество конкретных продуктов Apple, Grape и Lemon, которые перечислены в диапазоне C4:C6 из столбца A, как показано на скриншоте ниже. Обычно в Excel простые функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН не будут работать в этом сценарии. В этой статье я расскажу о том, как решить эту задачу быстро и легко с помощью комбинации функций СУММПРОИЗВ и СЧЁТЕСЛИ.

Лучшие инструменты для повышения производительности Office

Kutools для Excel - Помогает вам выделиться из толпы

🤖 KUTOOLS AI Помощник: Революционизируйте анализ данных на основе: Интеллектуального выполнения   |  Генерация кода  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов расширенных функций Kutools
Популярные функции: Найти, Выделить или Отметить дубликаты  |  Удалить пустые строки  |  Объединить столбцы или ячейки без потери данных  |  Округлить без формулы ...
Супер VLookup: Множественные критерии  |  Множественные значения  |  По нескольким листам  |  Распознавание нечетких соответствий...
Расширенный выпадающий список: Простой выпадающий список  |  Зависимый выпадающий список  |  Выпадающий список с множественным выбором...
Управление столбцами: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить видимость скрытых столбцов  Сравнить столбцы для выбора одинаковых и разных ячеек ...
Основные функции: Сетка фокусировки  |  Дизайн листа  |  Улучшенная строка формул  |  Управление книгами и листами | Библиотека автотекста (Auto Text)  |  Выбор даты  |  Объединить данные  |  Шифровать/Расшифровать ячейки  |  Отправить письма по списку  |  Супер фильтр  |  Специальный фильтр (фильтр жирного/курсивного/зачеркнутого текста...) ...
Топ-15 наборов инструментов12 текстовых инструментов (Добавить текст, Удалить определенные символы ...)  |  50+ типов диаграмм (Диаграмма Ганта ...)  |  40+ практических формул (Расчет возраста на основе даты рождения ...)  |  19 инструментов вставки (Вставить QR-код, Вставить изображение по пути ...)  |  12 инструментов преобразования (Преобразовать в слова, Конвертация валюты ...)  |  7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки Excel ...)  |  ... и многое другое

Kutools для Excel имеет более 300 функций, гарантируя, что нужный вам инструмент находится всего в одном клике...


Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)

  • Один щелчок мыши, чтобы переключаться между десятками открытых документов!
  • Сократите сотни кликов мышью ежедневно, попрощайтесь с болью в руке.
  • Увеличивает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.