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

Как подсчитать отфильтрованные данные / список с критериями в Excel?

Вы можете заметить, что независимо от того, отфильтровали вы свою таблицу или нет, функция СЧЁТЕСЛИ игнорирует фильтрацию и возвращает фиксированное значение. В некоторых случаях вам необходимо подсчитывать отфильтрованные данные по определенным критериям, так как же это сделать? В этой статье я быстро расскажу о нескольких способах работы с отфильтрованными данными / списками Countif в Excel.


Countif отфильтровал данные по критериям, добавив вспомогательный столбец в Excel

В этой статье я возьму в качестве примера следующую таблицу. Здесь я отфильтровал Джули и Николь в столбце «Продавец».

Исходные данные:

Отфильтрованные данные:

Этот метод поможет вам добавить дополнительный вспомогательный столбец, а затем вы сможете применить функцию СЧЁТЕСЛИМН для подсчета отфильтрованных данных в Excel. (Внимание: этот метод требует, чтобы вы отфильтровали исходную таблицу, прежде чем выполнять следующие шаги.)

1. Найдите пустую ячейку помимо исходной отфильтрованной таблицы, скажем, ячейку G2, введите = ЕСЛИ (B2 = "Груша"; 1; ""), а затем перетащите маркер заливки в нужный диапазон. (Примечание: В формуле = ЕСЛИ (B2 = "Груша"; 1; ""), B2 - это ячейка, которую вы будете считать, а «Груша» - это критерий, по которому вы будете рассчитывать.)

Теперь помимо исходной отфильтрованной таблицы добавлен вспомогательный столбец. «1» означает, что это груша в столбце B, а пробел означает, что это не груша в столбце B.

2. Найдите пустую ячейку и введите формулу =COUNTIFS(B2:B18,"Pear",G2:G18,"1"), и нажмите Enter ключ. (Примечание: В формуле =COUNTIFS(B2:B18,"Pear",G2:G18,"1"), B2: B18 и G2: G18 - это диапазоны, которые вы будете считать, а «Груша» и «1» - критерии, по которым вы будете рассчитывать.)

Теперь вы сразу получите счетное число. Обратите внимание, что номер счетчика не изменится если вы отключите фильтрацию или измените фильтрацию.

Сумма / количество / среднее значение видимых ячеек только в указанном диапазоне с игнорированием скрытых или отфильтрованных ячеек / строк / столбцов

Обычно функция SUM / Count / Average будет подсчитывать все ячейки в указанном диапазоне, если ячейки материи скрыты / отфильтрованы или нет. В то время как функция Subtotal может только суммировать / подсчитывать / усреднять, игнорируя скрытые строки. Однако Kutools для Excel СУЩЕСТВЕННЫЙ/СОВМЕСТНЫЙ/СРЕДНЯЯВИДИМАЯ функции легко вычисляют указанный диапазон, игнорируя любые скрытые ячейки, строки или столбцы.


только количество видимых ячеек

Отфильтрованные данные по счетчику с критериями с помощью функций Excel

Если вы хотите, чтобы число счетчиков изменялось по мере изменения фильтра, вы можете применить функции СУММПРОИЗВ в Excel следующим образом:
В пустую ячейку введите формулу =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)),ISNUMBER(SEARCH("Pear",B2:B18))+0), и нажмите Enter .

лента для заметок Формула слишком сложна для запоминания? Сохраните формулу как запись Auto Text для повторного использования одним щелчком мыши в будущем!
Подробнее ...     Бесплатная пробная версия

Ноты:
(1) В приведенной выше формуле B2: B18 - это диапазон, который вы будете считать, а «Груша» - это критерий, по которому вы будете рассчитывать.
(2) Возвращаемое значение изменится при отключении фильтрации или фильтрации изменений.

Легко разделить диапазон на несколько листов на основе критериев в столбце Excel

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

Kutools для Excel Разделить данные Утилита может помочь пользователям Excel легко разделить диапазон на несколько листов на основе критериев в одном столбце исходного диапазона.


данные рекламного разделения 0


Статьи по теме


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

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (16)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
вау, мощный и простой в использовании. СПАСИБО!
Этот комментарий был сведен к минимуму модератором на сайте
ГДЕ Я МОГУ СКАЧАТЬ ПРОБНУЮ ВЕРСИЮ KUTOOLS ДЛЯ MAC? С УВАЖЕНИЕМ
Этот комментарий был сведен к минимуму модератором на сайте
УДИВИТЕЛЬНО, я использовал формулу, и это было именно то, что мне нужно. спасибо!!
Этот комментарий был сведен к минимуму модератором на сайте
Как мне добавить еще один критерий в формулу отфильтрованных данных?
Этот комментарий был сведен к минимуму модератором на сайте
Привет Кейн,
Какие критерии фильтрации вы хотите добавить? Более подробная информация может помочь нам понять и решить вашу проблему быстрее.
Этот комментарий был сведен к минимуму модератором на сайте
То же самое с моим вопросом. Как считать отфильтрованным, если есть два критерия "Груша" для фруктов и "Юлия" для продавца?

Благодарю.
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,
В этой ситуации я предполагаю, что вспомогательный столбец, который я ввел в первом методе, может быть проще для подсчета.
Этот комментарий был сведен к минимуму модератором на сайте
Превосходно!!! Теперь можно фильтровать и считать на основе creiteria.
Этот комментарий был сведен к минимуму модератором на сайте
Большое спасибо. Это действительно превосходно! Спасибо раз большое.
Этот комментарий был сведен к минимуму модератором на сайте
Как насчет того, если «груша» должна быть числовым значением «<0», что вы используете вместо (поиск?
Этот комментарий был сведен к минимуму модератором на сайте
Привет Сиб,
Вы можете применять функции СЧЁТЕСЛИМН для подсчета элементов с двумя или более критериями. В случае с этой веб-страницей вы можете использовать формулы =СЧЁТЕСЛИМН(B2:B21,"Груша",C2:C21,"<0") для подсчета груш, количество которых меньше 0.
Однако результат подсчета стабилен и не изменится при смене фильтра.
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,
Мне нужна помощь в расчете процента учащихся PP (столбец F) с SEN (столбец E), у которых есть s или b (столбец G)

Вот формула, которую я пытался использовать, но она не работает.

Любая помощь / совет приветствуются.

=SUMPRODUCT(ISNUMBER(MATCH($E$2:$E$30,{"<>"},0))*ISNUMBER(MATCH($F$2:$F$30,{"<>"},0))*ISNUMBER(MATCH($T$2:$T$30,{"s","b"},0)))/SUMPRODUCT(ISNUMBER(MATCH($E$2:$E$30,{"<>"},0))*ISNUMBER(MATCH($F$2:$F$30,{"<>"},0)))

Клэр
Этот комментарий был сведен к минимуму модератором на сайте
эй, я хочу подсчитать значение больше 1, но с отфильтрованными видимыми данными, вы можете помочь?
Этот комментарий был сведен к минимуму модератором на сайте
Сладкий, это работает!
Этот комментарий был сведен к минимуму модератором на сайте
Есть ли способ изменить формулу для отфильтрованных данных, если я хочу собрать информацию, но как для груш, так и для апельсинов?
Этот комментарий был сведен к минимуму модератором на сайте
Всем привет,

С помощью первого метода вы можете ввести следующую формулу во вспомогательный столбец: =ЕСЛИ(B2="Груша",1,ЕСЛИ(B2="Оранжевый",1,"")
А затем используйте следующую формулу, чтобы получить общее количество: =СЧЁТЕСЛИМН(G2:G18,1)

Аманда
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

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

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