Как подсчитать отфильтрованные строки в Excel (с примером)
Самый простой способ подсчитать количество ячеек в отфильтрованном диапазоне в Excel — использовать следующий синтаксис:
SUBTOTAL( 103 , A1:A10 ) Обратите внимание, что значение 103 — это сокращение для определения количества отфильтрованных строк.
В следующем примере показано, как использовать эту функцию на практике.
Пример: подсчет отфильтрованных строк в Excel
Предположим, у нас есть следующий набор данных, который показывает количество продаж, совершенных компанией в разные дни:
Затем давайте отфильтруем данные, чтобы отображались только даты в январе или апреле.
Для этого выделите диапазон ячеек A1:B13.Затем щелкните вкладку « Данные » на верхней ленте и нажмите кнопку « Фильтр ».
Затем щелкните стрелку раскрывающегося списка рядом с « Дата » и убедитесь, что отмечены только поля рядом с «Январь» и «Апрель», затем нажмите « ОК »:
Данные будут автоматически отфильтрованы, чтобы отображались только строки, в которых даты указаны в январе или апреле:
Если мы попытаемся использовать функцию COUNT() для подсчета количества значений в столбце Date, она фактически вернет количество всех исходных значений:
Вместо этого мы можем использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ() :
Эта функция считает только видимые строки.
Из вывода мы видим, что 5 дней приходятся на январь или апрель.
Обратите внимание, что в этой конкретной формуле мы использовали 103 в функции промежуточного итога, но мы могли бы также использовать 102 :
Вот разница между ними:
- 102 использует функцию COUNT , которая подсчитывает только ячейки, содержащие числа.
- 103 использует функцию COUNTA , которая подсчитывает все непустые ячейки.
Не стесняйтесь использовать значение в формуле, которое имеет смысл для ваших данных.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:
Подсчет Уникальных строк с помощью Расширенного фильтра в EXCEL
Имеется таблица Товары состоящая из 2-х столбцов: Товар и Стоимость . Таблица создана в формате Excel 2007 ( Вставка/ Таблицы/ Таблица ).
Задача
С помощью Расширенного фильтра подсчитаем количество уникальных строк (см. файл примера ).
Решение
Для подсчета числа уникальных строк, в любой ячейке выше таблицы введите формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;Товары[товар]) Число 3 – это номер функции СЧЕТЗ() . Если нет желания создавать таблицу в формате Excel 2007 , то вместо Товары[товар] нужно указать диапазон ячеек столбца Товар .
Применим Расширенный фильтр для отображения только уникальных записей таблицы.
- Выделите любую ячейку в диапазоне таблицы;
- На вкладке Данные в группе Сортировка и фильтр выберите команду Дополнительно ( Расширенный фильтр) .
- Отобразится диалоговое окно Расширенный фильтр .
- Установите флажок Только уникальные записи и нажмите кнопку ОК .
- Будут отображены только Уникальные строки (записи) таблицы. Повторяющиеся записи будут скрыты.
- Для отображения скрытых записей на вкладке Данные в группе Сортировка и фильтр выберите команду Очистить.
После применения Расширенного фильтра будут отображены уникальные записи и функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() вернет количество уникальных строк в таблице.
СОВЕТ: Подсчет уникальных строк можно организовать и с помощью формул (см. статью Подсчет уникальных строк )
Как посчитать количество строк в excel после фильтра
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Суммирование только видимых ячеек
Некоторые из приведенных ниже способов чувствительны к этим факторам, некоторые – нет. Это нужно учитывать при выполнении вычислений:
СУММ (SUM) – тупо суммирует все в выделенном диапазоне без разбора, т.е. и скрытые строки в том числе. Если хотя бы в одной ячейке есть любая ошибка – перестает считать и тоже выдает ошибку на выходе.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 9 в первом аргументе – суммирует все видимые после фильтра ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 109 в первом аргументе – суммирует все видимые после фильтра и группировки (или скрытия) ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.
Если нужно не суммировать, то можно использовать другие значения кода математической операции:
АГРЕГАТ (AGGREGATE) – самая мощная функция, появившаяся в Office 2010. Также как и ПРОМЕЖУТОЧНЫЕ.ИТОГИ может не только суммировать, но и считать среднее, количество, минимум, максимум и т.д. — код операции задается первым аргументом. Плюс к этому имеет множество опций по подсчету, которые можно указать вторым аргументом:
Ссылки по теме
- Выборочные вычисления по одному или нескольким условиям
- Вставка в отфильтрованные строки
- Быстрое скрытие и отображение ненужных строк и столбцов