Как подсчитать отфильтрованные данные / список с критериями в Excel?
Вы можете заметить, что независимо от того, отфильтровали вы свою таблицу или нет, функция СЧЁТЕСЛИ игнорирует фильтрацию и возвращает фиксированное значение. В некоторых случаях вам необходимо подсчитывать отфильтрованные данные по определенным критериям, так как же это сделать? В этой статье я быстро расскажу о нескольких способах работы с отфильтрованными данными / списками Countif в Excel.
- Countif отфильтровал данные по критериям, добавив вспомогательный столбец в Excel
- Отфильтрованные данные по счетчику с критериями с помощью функций 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 подсчитывает все ячейки в указанном диапазоне независимо от того, скрыты/отфильтрованы ячейки или нет. В то время как функция промежуточного итога может только суммировать/подсчитывать/усреднять, игнорируя скрытые строки. Однако, Kutools for 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 for ExcelАвтора Разделить данные Утилита может помочь пользователям Excel легко разделить диапазон на несколько листов на основе критериев в одном столбце исходного диапазона.
Статьи по теме
Как посчитать, если ячейка не содержит текста в Excel?
Как посчитать, содержат ли ячейки какую-либо дату / данные в Excel?
Лучшие инструменты для офисной работы
Усовершенствуйте свои навыки работы с Excel с помощью Kutools for Excelи испытайте эффективность, как никогда раньше. Kutools for Excel Предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Office Tab Добавляет в Office интерфейс с вкладками и значительно упрощает вашу работу
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
