Excel - сводка итогов, а затем промежуточный итог?

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

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

работает отлично. Однако,

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

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

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


excel excel-formula subtotal roundup
person Statsanalyst    schedule 08.11.2017    source источник
comment
Я посмотрю в ближайшее время, но я считаю, что это может быть невозможно с чистыми формулами (если вы не хотите использовать вспомогательный столбец, в этом случае это, вероятно, будет довольно просто). Я вспоминаю личный опыт разочарования из-за ограничений SUBTOTAL в прошлом. Однако это возможно с VBA.   -  person ImaginaryHuman072889    schedule 08.11.2017

Ответы (2)


arrow_upward
1
arrow_downward

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

=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))

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

person barry houdini    schedule 08.11.2017
comment
Умное решение. К сожалению, ему нужно использовать изменчивые функции, поэтому я согласен, что вспомогательный столбец может быть лучше, просто чтобы он был простым. Тем не менее, отличный ответ. +1 - person ImaginaryHuman072889; 08.11.2017
comment
При условии, что это изменчивая функция, как уже упоминалось, это очень умный ответ, помеченный как ответ. Спасибо. Удивительно, что эта функциональность не включена в ПРОМЕЖУТОЧНЫЕ ИТОГО - неужели это не может быть такой необычной ситуацией? - person Statsanalyst; 08.11.2017
comment
@Statsanalyst Я согласен, что функции SUBTOTAL очень не хватает функций. Обычно, если я хочу выполнить какую-либо сложную функцию с отфильтрованными данными (выполняя вычисления только с нескрытыми данными), я просто уступаю и использую вспомогательные столбцы в сочетании с SUBTOTAL, потому что, как показано, иногда можно обойтись без вспомогательных столбцы, но может быть неэффективным или слишком сложным, если ваша электронная таблица содержит большой объем данных. - person ImaginaryHuman072889; 08.11.2017

arrow_upward
0
arrow_downward

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

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

= ROUNDUP(X4,0)

И тогда вместо

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

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

= SUBTOTAL(9,Y4:Y38)

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

person ImaginaryHuman072889    schedule 08.11.2017