Как выполнить COUNTIF для отфильтрованных данных/списка с критериями в Excel?
Вы можете заметить, что независимо от того, отфильтрована ваша таблица или нет, функция COUNTIF будет игнорировать фильтрацию и возвращать фиксированное значение. В некоторых случаях вам нужно подсчитать отфильтрованные данные по определенному критерию, так как это сделать? В этой статье я представлю несколько способов быстро подсчитать отфильтрованные данные/список в Excel.
- Подсчет отфильтрованных данных с критериями путем добавления вспомогательного столбца в Excel
- Подсчет отфильтрованных данных с критериями с помощью функций Excel
- Подсчет отфильтрованных данных с критериями путем разделения диапазона данных на несколько листов, а затем подсчета
Подсчет отфильтрованных данных с критериями путем добавления вспомогательного столбца в Excel
В этой статье я возьму следующую таблицу в качестве примера. Здесь я отфильтровал Julie и Nicole в столбце Продавец.
Исходные данные:
Отфильтрованные данные:
Этот метод покажет вам, как добавить дополнительный вспомогательный столбец, а затем вы сможете применить функцию COUNTIFS для подсчета отфильтрованных данных в Excel. (Примечание: Этот метод требует, чтобы вы отфильтровали исходную таблицу перед выполнением следующих шагов.)
1Найдите пустую ячейку рядом с исходной отфильтрованной таблицей, например ячейку G2, и введите =ЕСЛИ(B2="Груша";1;"")и затем перетащите маркер заполнения до нужного диапазона. (Примечание: В формуле =ЕСЛИ(B2="Груша";1;"")B2 — это ячейка, которую вы будете считать, а "Груша" — это критерий, по которому вы будете считать.)
Теперь добавлен вспомогательный столбец рядом с исходной отфильтрованной таблицей. "1" указывает, что это груша в столбце B, а пустая ячейка означает, что это не груша в столбце B.
2Найдите пустую ячейку и введите формулу =COUNTIFS(B2:B18;"Груша";G2:G18;"1")и нажмите клавишу Enter (Примечание:Примечание: В формуле =COUNTIFS(B2:B18;"Груша";G2:G18;"1")B2:B18 и G2:G18 — это диапазоны, которые вы будете считать, а "Груша" и "1" — это критерии, по которым вы будете считать.)
Теперь вы сразу получите число подсчета. Обратите внимание, что число подсчета не изменится, если вы отключите фильтрацию или измените фильтрацию.
Сумма/Подсчет/Среднее только видимых ячеек в указанном диапазоне с игнорированием скрытых или отфильтрованных ячеек/строк/столбцов
Обычно функции СУММ/Подсчет/Среднее будут учитывать все ячейки в указанном диапазоне, независимо от того, скрыты они или отфильтрованы. В то время как функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ может суммировать/подсчитывать/усреднять, игнорируя только скрытые строки. Однако функции SUMVISIBLE, COUNTVISIBLE, AVERAGEVISIBLE из Kutools для Excel легко рассчитают указанный диапазон, игнорируя любые скрытые ячейки, строки или столбцы.
Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас
Подсчет отфильтрованных данных с критериями с помощью функций Excel
Если вы хотите, чтобы число подсчета менялось вместе с изменением фильтра, вы можете применить функцию СУММПРОИЗВ в Excel следующим образом:
В пустой ячейке введите формулу =СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ(B2:B18;СТРОКА(B2:B18)-МИН(СТРОКА(B2:B18));;1));ЕЧИСЛО(ПОИСК("Груша";B2:B18))+0)и нажмите клавишу Enter .
Примечания:
(1) В приведенной выше формуле B2:B18 — это диапазон, который вы будете считать, а "Груша" — это критерий, по которому вы будете считать.
(2) Возвращаемое значение изменится когда вы отключите фильтрацию или измените фильтр.
Легко разделить диапазон на несколько листов на основе критериев в столбце в Excel
По сравнению со сложными формулами массива, может быть намного проще сохранить все отфильтрованные записи на новый лист, а затем применить функцию Подсчета для подсчета диапазона или списка отфильтрованных данных.
Утилита Разделение данных из Kutools для Excel поможет пользователям Excel легко разделить диапазон на несколько листов на основе критериев в одном столбце исходного диапазона.
Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас
Связанные статьи
Как подсчитать, если ячейка не содержит текст в Excel?
Как подсчитать, если ячейки содержат любую дату/данные в Excel?
Как подсчитать ячейки, если они содержат X или Y в Excel?
Как использовать COUNTIF по дате/месяцу/году и диапазону дат в Excel?
Лучшие инструменты для повышения продуктивности работы с Office
Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
- Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!