У меня есть данные в Excel, который выглядит как
10
15
12
3
2
5
3
Я хотел бы вернуть промежуточные итоги, как
10
15
12
37
3
2
5
5
3
8
Есть ли способ добиться этого в Excel?
2
4 ответа
5
Я придумал этого монстра, но он отлично работает, вы можете просто скопировать его:
=IF(ISBLANK(A4),SUBTOTAL(9,OFFSET(B4,IFERROR(MATCH(0,$B$1:B3,-1),1)-ROW(),-1,ROW()-IFERROR(MATCH(0,$B$1:B3,-1),1),1)),"")
Это мой оригинал (немецкий):
=WENN(ISTLEER(A4);TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(B4;WENNFEHLER(VERGLEICH(0;$B$1:B3;-1);1)-ZEILE();-1;ZEILE()-WENNFEHLER(VERGLEICH(0;$B$1:B3;-1);1);1));"")
Таким образом, когда ячейка в столбце A пуста, выполните подсчет, суммируя полученные значения, до последнего значения или 1.
Если форум не ясно, не стесняйтесь спрашивать о специфике.
редактировать
В псевдо / объясненном коде:
IF(ISBLANK(A4),SUBTOTAL(),"")
Если ячейка текущей строки в столбце A пуста, вычислите промежуточный итог, иначе ничего не отобразите.
SUBTOTAL(9,RANGE)
Создайте промежуточный итог, используя функцию суммы (9) для заданного диапазона
RANGE = OFFSET(B4,ROWS,COLS,HEIGHT,WIDTH)
Диапазон для промежуточного итога создается путем смещения / охвата диапазона на основе ячейки B4.
На этом конкретном примере B4 это было бы похоже на запись:
OFFSET(B4,-3,-1,3,1)
Итак, создайте диапазон, который на 3 строки выше B4 (-3) и на 1 столбец слева (-1), а также на 3 строки вниз / максимум и 1 столбец в ширину.
Чтобы вычислить -3 и 3, которые будут различаться в зависимости от строк между двумя пустыми ячейками в столбце А, мы используем их:
IFERROR(MATCH(0,$B$1:B3,-1),1)-ROW()
ROW()-IFERROR(MATCH(0,$B$1:B3,-1)
которые в основном одинаковы, кроме знака.
MATCH(0,$B$1:B3,-1) ищет что-либо выше 0 (-1 означает больше) в столбце b, от первой до предыдущей ячейки текущей ($ B $ 1:B3).
Поскольку это приведет к ошибке для первого промежуточного итога , IFERROR предоставляет 1, чтобы исправить это.
Таким образом, когда вы находитесь в B4, вы ищете, есть ли какое-либо значение выше B4, предполагая, что это промежуточный итог, и если вы не можете найти какое-либо значение, то вы используете 1 в качестве строки. 1-4 = -3; 4-1 = 3 вот так.
3
Альтернатива, которая может быть полезна и, возможно, более универсальна (также позволяет избежать «монстра» в случае, если ее нельзя передать / легко восстановить), состоит в объединении более знакомых операций.
Предполагая: ColumnA является «запасным»; значения находятся в ColumnB ; Row1 содержит метку, а в C1 есть что-то (скажем, «Sum»).
Требуется определение значения «блоков», поэтому отфильтруйте A:C , снимите флажок (Blanks) в B , введите «v» в A2 и скопируйте до последнего синего номера строки.
Выберите A1:C{last blue row number} , удалите фильтр (если требуется группировка), промежуточный итог с каждым изменением в: (столбец A), используйте функцию: «Сумма», добавьте промежуточный итог к: отметьте «Сумма», OK
Удалите пустые строки (например, с помощью фильтрации ColumnA), затем ColumnA .
Выберите ColumnB , найдите что: ', B', Замените на: ', A', нажмите Replace All .
Итого это бонус.
0
Альтернативная более короткая формула, которую вы можете попробовать:
Максимальное количество строк для вычисления можно увеличить, увеличив «$ A $ 1000», используя A2 в качестве 1-й строки:
= ЕСЛИ (А2 = "", СУММ ($ A $ 1:$ A $ 1000)-сумма (А3:$ A $ 1000)-сумма ($ B $ 1:N 2), "")
0
Я придумал другое решение той же проблемы:
решил ее, используя =if(isblank(a2),H1+1,H1+0) в H2 и 1 в H1, а затем использовал простые субтитры для получения решения.