Note: The other languages of the website are Google-translated. Back to English

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

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


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

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

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

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

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

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

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 ключи вместе, чтобы получить общее количество:

=SUM(--(MMULT(--(ISNUMBER(SEARCH("Joanna",A2:C12))),TRANSPOSE(COLUMN($A$2:$C$12)))>0))


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

  • СУММА:
  • Функция СУММ в Excel возвращает сумму предоставленных значений.
  • МУЛЬТИ:
  • Функция Excel MMULT возвращает матричное произведение двух массивов.
  • ТРАНСПОРТ:
  • Функция TRANSPOSE вернет массив в новой ориентации на основе определенного диапазона ячеек.
  • КОЛОНКА:
  • Функция COLUMN возвращает номер столбца, в котором отображается формула, или номер столбца для данной ссылки.

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

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

Лучшие инструменты для работы в офисе

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

Хотите быстро и качественно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80% времени.

  • Разработан для 1500 рабочих сценариев, помогает решить 80% проблем с Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (0)
Оценок пока нет. Оцените первым!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL