Суммирование только видимых ячеек

120454 08.04.2015 Скачать пример

Если у нас имеется таблица, по которой должны считаться итоги, то важную роль играет какой именно функцией они вычисляются, т.к. в таблице могут быть:

  • Включены фильтры
  • Скрыты некоторые строки
  • Свернуты сгруппированные строки
  • Промежуточные итоги внутри таблицы
  • Ошибки в формулах

Некоторые из приведенных ниже способов чувствительны к этим факторам, некоторые – нет. Это нужно учитывать при выполнении вычислений:

суммирование только видимых ячеек после фильтра, итогов

СУММ (SUM) – тупо суммирует все в выделенном диапазоне без разбора, т.е. и скрытые строки в том числе. Если хотя бы в одной ячейке есть любая ошибка – перестает считать и тоже выдает ошибку на выходе.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 9 в первом аргументе – суммирует все видимые после фильтра ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 109 в первом аргументе – суммирует все видимые после фильтра и группировки (или скрытия) ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.

Если нужно не суммировать, то можно использовать другие значения кода математической операции:

коды операций функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ

АГРЕГАТ (AGGREGATE) – самая мощная функция, появившаяся в Office 2010. Также как и ПРОМЕЖУТОЧНЫЕ.ИТОГИ может не только суммировать, но и считать среднее, количество, минимум, максимум и т.д. - код операции задается первым аргументом. Плюс к этому имеет множество опций по подсчету, которые можно указать вторым аргументом:

функция АГРЕГАТ в Excel

Ссылки по теме




10.04.2015 15:49:29
Что-то у меня вне зависимости от установленного фильтра, промежуточные итоги что с 9, что с 109, выводят одно и то же значение. Поскольку у меня 2007, то АГРЕГАТ не работает, ничего не могу сказать.
-------
А, заметил разницу, если фильтр не устанавливать, а просто скрывать строки, то появляется разница в значениях. Но тогда промежуточные итоги с 9, просто равняются общей сумме.
26.06.2015 14:59:18
Промежуточные.итоги с кодом 109.
Если скрыты строки, все ок. Но условие не распространяется на сгруппированные столбцы. Их значения все равно считает.
14.04.2016 07:54:01
А подскажите как сделать ВПР, чтобы он работал с таблицей, где выставлен фильтр, и возвращал только видимые строки?
10.11.2016 11:20:38
Не уверен, что это можно нормально реализовать формулой.
Я бы писал пользовательскую функцию на Visual Basic для такой задачи.
08.11.2016 11:10:03
Сергей Пепеляев, если вы нашли ответ на свой вопрос, то просьба поделиться - тоже интересует этот вопрос.
27.12.2016 23:33:50
Подскажите, как написать формулу средневзвешенного значения без скрытых ячеек. Например, у меня формула такая:=СУММПРОИЗ(A1:A20;D1:D20)/СУММ(D1:D20). Скрываю ячейки, но эта формула считает и скрытые ячейки, а мне они не нужны. Как сделать, чтобы они не учитывались, у меня не хватает ,,тяму,,.
04.01.2017 09:36:47
Юлия, я бы сделал еще один столбец с простым умножением A на D. Затем, после фильтра, считал бы по нему сумму только видимых с помощью функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ или АГРЕГАТ и делил на такую же сумму по D.
04.01.2017 09:45:57
я так уже сделала. Хотела сделать в одной ячейке чтобы не загромождать файл, т.к. таблица с большим количеством столбцов. Всеравно большое спасибо за отве т. С Новым годом Вас!
17.04.2017 11:35:40
Николай, согласен с Вами функция АГРЕГАТ – мощная и полезная штука!

Более того в сочетании с «Умными таблицами» работает просто великолепно, например:

=АГРЕГАТ(9;3;Таблица1[Тамож.сбор]) ,где: Таблица1 – название умной таблицы, а [Тамож.сбор] – столбец умной таблицы


Более того эту функцию можно вставлять в автоматическую «строку итогов» умной таблицы:
=АГРЕГАТ(9;3; [Тамож.сбор])
16.06.2017 11:24:02
Добрый день, разрешите вопрос?
А что если мне необходимо посчитать промежуточные итоги (просуммировать) по какому-либо критерию? Т.е. как с функцией СУМЕСЛИ, но при этом чтобы скрытые фильтром строки не учитывались? Ни у ПРОМЕЖУТОЧНЫЕ.ИТОГИ ни у АГРЕГАТ такой альтернативы я не нашел...
P.S. Речь идёт об "умной" таблице и её фильтре.

P.P.S. Спасибо, проблема решена.
11.07.2017 19:57:18
А можете поделиться ответом?!
Заранее огромное спасибо!!!
11.07.2017 20:22:38
См. тему ''суммирование по критерию в умной таблице. Как получить результат без учета скрытых строк''. Сорри, я уже в отпуске, с телефона не очень удобно отвечать.
11.07.2017 22:24:21
https://fex.net/#!699647596340
решение
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$7;СТРОКА($B$7:$B$76)-СТРОКА($B$7))*($B$7:$B$76="УСЛОВИЕ")

Всем спасибо! И прекрасных отпусков!
25.08.2017 15:23:04
Применил Промежуточные.итоги с кодом 9. все прекрасно отобразилось. Но как теперь прикрутить нумерацию отображенных строк?
Наверх