动态总计运行时维护单元格引用的方法 [英] 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屋！