Суммирование только видимых ячеек
Некоторые из приведенных ниже способов чувствительны к этим факторам, некоторые – нет. Это нужно учитывать при выполнении вычислений:
СУММ (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: Как суммировать ОТФИЛЬТРОВАННЫЕ ячейки? Есть более удобный способ кроме =СУММ (D10+. +D50) ?
СУММЕСЛИ / СУММЕСЛИМН. Промежуточные итоги. Сводные таблицы. Запросы PowerQuery. Да макросы, в конце концов.
Дмитрий МитяевУченик (15) 4 года назад
ОК, а как написать макрос для этой ситуации?
Дмитрий МитяевУченик (15) 4 года назад
СУММЕСЛИ () — подошел. Спасибо.
Остальные ответы
Глупость это. Есть же СУММЕСЛИ!
В данном случае просто в ячейку D60 ввести =СУММЕСЛИ (C$5:C$53;C60;D$5:D$53)
И копировать её вверх и вниз. Получим всё и сразу!
для суммирования отфильтрованных диапазон используется
=ПРОМЕЖУТОЧНЫЕ. ИТОГИ (9;Дипазон)
Дмитрий МитяевУченик (15) 4 года назад
Да, но если снять фильтр — все собьется и будет посчитан ВЕСЬ диапазон.
Умрбек Искандарович Мастер (1672) возможно я неправильно понял вопрос, но автор вопроса об этом и спрашивает. ОТФИЛЬТРОВАННЫЕ А если снять фильтр — то фильтра нет получается.
Константин СокольскийЗнаток (405) 3 года назад
Вы очень доступно все объяснили, а можете пояснить вот что:
Отфильтровал ячейки, сделал все как вы показали и получилась итоговая сумма.
При включении скрытых ячеек сумма изменяется на величину раскрытых ячеек. Вопрос: можно ли сделать так, чтоб после автосуммы, при раскрытии скрытых ячеек итоговая сумма не изменялась?
При этом суммировать не отдельно отфильтрованные ячейки, а суммирование диапазоном
Умрбек Искандарович Мастер (1672) нет нельзя, смысл того что я показал вам работает с раскрытыми ячейками. Если вы хотите подобрать сумму по другим условиям почитайте про суммеслимн может это поможет?
Как суммировать отфильтрованные строки в Excel (с примером)
Самый простой способ получить сумму отфильтрованного диапазона в Excel — использовать следующий синтаксис:
SUBTOTAL( 109 , A1:A10 ) Обратите внимание, что значение 109 — это сокращение для получения суммы отфильтрованного диапазона строк.
В следующем примере показано, как использовать эту функцию на практике.
Пример: суммирование отфильтрованных строк в Excel
Предположим, у нас есть следующий набор данных, содержащий информацию о различных баскетбольных командах:
Далее давайте отфильтруем данные, чтобы показывать только игроков на Mavs или Warriors.
Для этого выделите диапазон ячеек A1:B10.Затем щелкните вкладку « Данные » на верхней ленте и нажмите кнопку « Фильтр ».
Затем щелкните стрелку раскрывающегося списка рядом с « Команда» , снимите флажок рядом с «Селтикс» и нажмите « ОК »:
Данные будут автоматически отфильтрованы, чтобы удалить строки с «Селтикс» в качестве команды:
Если мы попытаемся использовать функцию SUM() для суммирования столбца точек отфильтрованных строк, она фактически вернет сумму всех исходных значений:
Вместо этого мы можем использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ() :
Эта функция принимает сумму только видимых строк.
Мы можем вручную проверить это, взяв сумму видимых строк:
Сумма очков в видимых рядах: 99 + 94 + 93 + 104 + 109 + 84 = 583 .
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel: