一种在动态运行总计时维护单元格引用的方法 [英] A way to maintain cell references when doing dynamic running total

问题描述

我有另一个动态生成的列 (I7:I) 的运行总数.我使用 mmult() 计算了它.唯一的问题是使用间接意味着如果我通过添加或删除 7 以上的行来移动我的工作表中的数据，引用会中断.如果我使用 I7:I 作为我的引用，它表示结果数组太大.有没有更好的方法来做到这一点?

I have a running total of another dynamically generated column (I7:I). I computed it using mmult(). The only problem with this is using indirect means the references break if I move data around in my sheet say by adding or removing rows above 7. If I use I7:I as my reference, it says the resulting array is too large. Is there a better way of doing this?

=ArrayFormula( MMULT(TRANSPOSE((ROW(indirect("I7:I" & max(ArrayFormula(ROW(I6:I)*(I6:I <> "")))) )<=TRANSPOSE(ROW(indirect("I7:I" & max(ArrayFormula(ROW(I6:I)*(I6:I <> "")))) )))*indirect("I7:I" & max(ArrayFormula(ROW(I6:I)*(I6:I <> "")))) ),query(indirect("I7:I" & max(ArrayFormula(ROW(I6:I)*(I6:I <> "")))),  "select 1 label 1 ''"))) 

推荐答案

试试这个:

=ARRAYFORMULA(MMULT(
 TRANSPOSE((ROW(INDIRECT("I7:I"&MAX(ROW(I:I)*($I:$I<>""))))<=
 TRANSPOSE( ROW(INDIRECT("I7:I"&MAX(ROW(I:I)*($I:$I<>""))))))*
                INDIRECT("I7:I"&MAX(ROW(I:I)*($I:$I<>"")))),
          QUERY(INDIRECT("I7:I"&MAX(ROW(I:I)*($I:$I<>""))),  
 "select 1 label 1''")))

这篇关于一种在动态运行总计时维护单元格引用的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！