Суммирование только видимых ячеек
Некоторые из приведенных ниже способов чувствительны к этим факторам, некоторые – нет. Это нужно учитывать при выполнении вычислений:
СУММ (SUM) – тупо суммирует все в выделенном диапазоне без разбора, т.е. и скрытые строки в том числе. Если хотя бы в одной ячейке есть любая ошибка – перестает считать и тоже выдает ошибку на выходе.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 9 в первом аргументе – суммирует все видимые после фильтра ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 109 в первом аргументе – суммирует все видимые после фильтра и группировки (или скрытия) ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.
Если нужно не суммировать, то можно использовать другие значения кода математической операции:
АГРЕГАТ (AGGREGATE) – самая мощная функция, появившаяся в Office 2010. Также как и ПРОМЕЖУТОЧНЫЕ.ИТОГИ может не только суммировать, но и считать среднее, количество, минимум, максимум и т.д. — код операции задается первым аргументом. Плюс к этому имеет множество опций по подсчету, которые можно указать вторым аргументом:
Ссылки по теме
- Выборочные вычисления по одному или нескольким условиям
- Вставка в отфильтрованные строки
- Быстрое скрытие и отображение ненужных строк и столбцов
Как посчитать количество строк в excel после фильтра
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Подсчет количества строк или столбцов в Excel
Если вам нужен быстрый способ подсчета строк, содержащих данные, вы можете выбрать все ячейки в первом столбце этих данных (возможно, это не столбец A). Просто щелкните заглавную кнопку столбца. В строке состояния в правом нижнем углу окна Excel вы можете подсчитать количество строк.
Сделайте то же самое, чтобы подсчитать количество столбцов, но в этот раз щелкните селектор строки в левой части строки.
Затем в области состояния отображается количество, что-то вроде этого:
Если вы выберете всю строку или столбец, Excel подсчитываются только ячейки, содержащие данные. Если вы выберете блок ячеек, будет подсчитано количество выбранных ячеек. Если выбранная строка или столбец содержит только одну ячейку с данными, строка состояния останется пустой.
- Если вам нужно подсчитать символы в ячейках, см. подсчет символов в ячейках.
- Если вы хотите узнать, сколько ячеек имеет данные, см. подсчет непустых ячеек с помощью счёта СЧЁТ.
- Вы можете управлять сообщениями, которые отображаются в панели состояния, щелкнув правой кнопкой мыши ее и щелкнув элемент, который вы хотите увидеть или удалить. Дополнительные сведения см. в Excel состояния.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как посчитать количество видимых строк в Excel
Если вы хотите подсчитать количество видимых элементов в отфильтрованном списке, вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая автоматически игнорирует строки, которые скрыты с помощью фильтра.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ может выполнять вычисления, как СЧЁТ, СУММ, МАКС, МИН, и многие другие.
Что делает ПРОМЕЖУТОЧНЫЕ.ИТОГИ: особенно интересным и полезным является то, что она автоматически игнорирует элементы, которые не видны в отфильтрованном списке или таблице. Это делает ее идеальной для показа того, сколько элементов видно в списке, промежуточных итогов видимых строк и т.д.
Следуя примеру, чтобы подсчитать количество не пустых строк, видимых, когда фильтр активен, используйте:
Если вы скрываете строки вручную (т.е. правой кнопкой мыши, Скрыть), а не с помощью автоматического фильтра используйте эту версию вместо той:
Только с критериями
=СУММПРОИЗВ((диапазон=критерий)*( ПРОМЕЖУТОЧНЫЕ.ИТОГИ (3; СМЕЩ (диапазон; ЧСТРОК;0;1))))
Для подсчета видимых строк только с критериями, вы можете использовать довольно сложную формулу, основанную на СУММПРОИЗВ, ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СМЕЩ.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ может легко генерировать суммы и счетчики для скрытых и не скрытых строк. Тем не менее, она не в состоянии справиться с критериями (т.е. как СЧЁТЕСЛИ или СУММЕСЛИ).
Решение состоит в том, чтобы использовать СУММПРОИЗВ, применив с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ (через СМЕЩ) и критерии. В показанном примере формула в С12:
=СУММПРОИЗВ ((C5:C8 = С10) * (ПРОМЕЖУТОЧНЫЕ.ИТОГИ (103;СМЕЩ(C5;СТРОКА(C5:C8) — МИН(СТРОКА(C5:C8));0))))
Суть этой формулы вычисление массива внутри СУММПРОИЗВ. Первый массив применяет критерии, а второй массив обрабатывает «проблему видимости».
Критерии применяется с частью формулы:
Который генерирует массив следующим образом:
Где ИСТИНА означает «отвечает критериям». Обратите внимание, что поскольку мы используем умножение (*) внутри первого (и только) массива, значения ИСТИНА/ЛОЖЬ будут автоматически преобразованы:
Для учета видимости применяется фильтр с использованием ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ может исключить скрытые строки в различных вычислениях, поэтому мы можем использовать ее в этом случае, создав «фильтр», чтобы исключить скрытые строки внутри СУММПРОИЗВ. Проблема, однако, в том, что ПРОМЕЖУТОЧНЫЕ.ИТОГИ рассчитывает единственное число, в то время как нам нужен массив, чтобы использовать его успешно внутри СУММПРОИЗВ.
Хитрость заключается в том, чтобы использовать СМЕЩ, подающую ПРОМЕЖУТОЧНЫЕ.ИТОГИ одну ссылку на строку, так что смещение будет рассчитывать один результат для каждой строки.
Конечно, для этого требуется еще один трюк, который должен дать СМЕЩ массив, содержащий один номер для каждой строки, начиная с нуля. Мы делаем это с помощью:
Что будет генерировать массив вроде этого:
Таким образом, второй массив, который обрабатывает видимость с помощью ПРОМЕЖУТОЧНЫЕ.ИТОГИ, генерируется следующим образом:
= ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ (C5;СТРОКА(C5: C8) — МИН(СТРОКА(C5: C8)); 0))