Как выполнить подсчёт по условию (COUNTIF) для отфильтрованных данных или списка в Excel?
Вы, вероятно, замечали, что функция СЧЁТЕСЛИ игнорирует фильтрацию и всегда возвращает одно и то же значение — независимо от того, применён ли фильтр к таблице. Однако иногда возникает необходимость подсчитать только видимые (отфильтрованные) строки по заданному критерию. Как этого добиться? В этой статье я покажу несколько эффективных способов быстро подсчитать отфильтрованные данные или список в Excel с использованием функции СЧЁТЕСЛИ.
- Подсчёт отфильтрованных данных по критерию с помощью вспомогательного столбца в Excel
- Подсчёт отфильтрованных данных по критерию с использованием функций Excel
- Подсчёт отфильтрованных данных по критерию путём разделения Диапазон данных на несколько листов с последующим подсчётом
Подсчёт отфильтрованных данных по критерию с помощью вспомогательного столбца в Excel
В этой статье я возьму за основу следующую таблицу, в которой уже отфильтрованы записи для Джули и Николь в столбце «Продавец».
Исходные данные:
Отфильтрованные данные:
Этот метод поможет вам добавить вспомогательный столбец, после чего можно будет использовать функцию СЧЁТЕСЛИМН для подсчёта отфильтрованных данных в Excel. ()Примечание: перед выполнением приведённых ниже шагов исходную таблицу необходимо предварительно отфильтровать.)
1. Найдите пустую ячейку рядом с исходной отфильтрованной таблицей — например, G2 — введите формулу =IF(B2=«Pear»,1,"")и протяните маркер заполнения до конца нужного диапазона. ()Примечание: В формуле =IF(B2=«Pear»,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 — это диапазоны, по которым выполняется подсчёт, а «Pear» и «1» — критерии подсчёта.)
Теперь вы сразу увидите итоговое количество. Обратите внимание: это число не изменится, даже если вы отключите фильтрацию или измените её параметры.
Суммирование/подсчёт/усреднение только видимых ячеек в Ограниченный диапазон с игнорированием скрытых или отфильтрованных ячеек/строк/столбцов
Обычные функции СУММ, СЧЁТ и СРЗНАЧ учитывают все ячейки в ограниченном диапазоне, независимо от того, скрыты они или отфильтрованы. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ позволяет суммировать, подсчитывать и усреднять значения, игнорируя только скрытые строки. Однако функции Kutools для Excel СУММВИДИМ, СЧЁТВИДИМ и СРЗНАЧВИДИМ легко выполняют расчёты для ограниченного диапазона, игнорируя любые скрытые ячейки, строки или столбцы.
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Подсчёт отфильтрованных данных по критерию с использованием функций Excel
Чтобы итоговое количество автоматически обновлялось при изменении фильтра, используйте функцию СУММПРОИЗВ в Excel следующим образом:
В пустой ячейке введите формулу =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)),ISNUMBER(SEARCH(«Pear»,B2:B18))+0)и нажмите клавишу Enter.
Примечания:
(1) В приведённой выше формуле B2:B18 — диапазон, по которому выполняется подсчёт, а «Груша» — критерий подсчёта.
(2) Возвращаемое значение будет изменяться при отключении фильтрации или изменении её условий.
Легкое разделение диапазона на несколько листов по критерию из одного столбца в Excel
По сравнению со сложными формулами массива гораздо проще сохранить все отфильтрованные записи на отдельном новом листе, а затем применить функцию подсчёта к отфильтрованному диапазону данных или списку.
Функция Разделить данные в Kutools для Excel позволяет легко разделять диапазон на несколько рабочих листов по критерию из одного столбца исходного диапазона.
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Связанные статьи
Как подсчитать в Excel ячейки, не содержащие текст?
Как подсчитать ячейки, содержащие любую дату или данные, в Excel?
Как подсчитать в Excel ячейки, содержащие X или Y?
Как выполнить подсчёт по дате, месяцу, году и диапазону дат в Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек