Суммирование только видимых ячеек
Если у нас имеется таблица, по которой должны считаться итоги, то важную роль играет какой именно функцией они вычисляются, т.к. в таблице могут быть:
- Включены фильтры
- Скрыты некоторые строки
- Свернуты сгруппированные строки
- Промежуточные итоги внутри таблицы
- Ошибки в формулах
Некоторые из приведенных ниже способов чувствительны к этим факторам, некоторые – нет. Это нужно учитывать при выполнении вычислений:
СУММ (SUM) – тупо суммирует все в выделенном диапазоне без разбора, т.е. и скрытые строки в том числе. Если хотя бы в одной ячейке есть любая ошибка – перестает считать и тоже выдает ошибку на выходе.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 9 в первом аргументе – суммирует все видимые после фильтра ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 109 в первом аргументе – суммирует все видимые после фильтра и группировки (или скрытия) ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.
Если нужно не суммировать, то можно использовать другие значения кода математической операции:
АГРЕГАТ (AGGREGATE) – самая мощная функция, появившаяся в Office 2010. Также как и ПРОМЕЖУТОЧНЫЕ.ИТОГИ может не только суммировать, но и считать среднее, количество, минимум, максимум и т.д. - код операции задается первым аргументом. Плюс к этому имеет множество опций по подсчету, которые можно указать вторым аргументом:
Ссылки по теме
- Выборочные вычисления по одному или нескольким условиям
- Вставка в отфильтрованные строки
- Быстрое скрытие и отображение ненужных строк и столбцов
-------
А, заметил разницу, если фильтр не устанавливать, а просто скрывать строки, то появляется разница в значениях. Но тогда промежуточные итоги с 9, просто равняются общей сумме.
Если скрыты строки, все ок. Но условие не распространяется на сгруппированные столбцы. Их значения все равно считает.
Я бы писал пользовательскую функцию на Visual Basic для такой задачи.
Более того в сочетании с работает просто великолепно, например:
=АГРЕГАТ(9;3;Таблица1[Тамож.сбор]) ,где: Таблица1 – название умной таблицы, а [Тамож.сбор] – столбец умной таблицы
Более того эту функцию можно вставлять в автоматическую «строку итогов» умной таблицы:
=АГРЕГАТ(9;3; [Тамож.сбор])
А что если мне необходимо посчитать промежуточные итоги (просуммировать) по какому-либо критерию? Т.е. как с функцией СУМЕСЛИ, но при этом чтобы скрытые фильтром строки не учитывались? Ни у ПРОМЕЖУТОЧНЫЕ.ИТОГИ ни у АГРЕГАТ такой альтернативы я не нашел...
P.S. Речь идёт об "умной" таблице и её фильтре.
P.P.S. Спасибо, проблема решена.
Заранее огромное спасибо!!!
решение
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$7;СТРОКА($B$7:$B$76)-СТРОКА($B$7))*($B$7:$B$76="УСЛОВИЕ")
Всем спасибо! И прекрасных отпусков!