Excel - сводка итогов?

Question

У коллеги есть массив значений в "X4:X38". Поскольку они находятся в таблице, которую можно отфильтровать, она хочет использовать функцию промежуточных итогов для суммирования их - но хочет, чтобы все значения были округлены в первую очередь.

={SUM(ROUNDUP(X4:X38,0))}

работает на отлично Тем не мение,

{SUBTOTAL(9,ROUNDUP(X4:X38,0))}

Создает универсальное сообщение "Введенная вами формула содержит сообщение об ошибке". Я пробовал разные очевидные вещи, такие как добавление дополнительных скобок в раздел "Сводка новостей" и т. Д.

Любая помощь будет оценена.

Answer 1

Вы можете сделать это без вспомогательного столбца, используя следующую формулу:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(X4:X38,ROW(X4:X38)-MIN(ROW(X4:X38)),0,1)),ROUNDUP(X4:X38,0))

OFFSET эффективно разбивает диапазон на отдельные ячейки, которые передаются SUBTOTAL функция, которая возвращает массив значений 1 или 0 в зависимости от того, видима ли каждая ячейка после фильтра или нет - этот массив умножается на округленные значения, чтобы получить общую сумму округленных видимых значений.

Другой способ заключается в использовании AGGREGATE функционировать так

=SUMPRODUCT(ROUNDUP(AGGREGATE(15,7,X4:X38,ROW(INDIRECT("1:"&SUBTOTAL(2,X4:X38)))),0))

Учитывая сложность, вспомогательный столбец может быть предпочтительным подходом

Answer 2

После расследования похоже, что это невозможно без вспомогательной колонки.

Добавьте вспомогательный столбец, который округляет отдельные значения в столбце. XНапример, введите в ячейку следующую формулу Y4 и перетащите вниз к Y38:

= ROUNDUP(X4,0)

А потом вместо

= SUBTOTAL(9,ROUNDUP(X4:X38,0))

использовать:

= SUBTOTAL(9,Y4:Y38)

Тогда при необходимости вы можете просто скрыть вспомогательный столбец. Конечно, вспомогательный столбец не обязательно должен быть столбцом Y, это может быть любой столбец, например столбец, расположенный справа от места окончания данных.