当表单添加新行时更新 Google 表格公式中的引用

当表单添加新行时更新 Google 表格公式中的引用

借助各种来源的帮助以及从不同的 Google Sheets 列中获取数据的公式部分,我现在有了以下用于计算一些数值总数的公式。当添加新数据行时,我无法让它更新并向下移动。

=SUMIF(D2:D105,C108,E2:E106)

我知道它是类似的东西,我在其他地方也有,但我只是不知道如何将它们结合起来。

=SUM(INDIRECT("E2:E"&ROW()-1))

所以我本质上希望第一个公式能够在新行添加时进行更新。

答案1

原始答案因 OP 声明他们使用的是 Google Sheets 而不是 Excel 而失效。基于此修改后的答案以及他们的工作表共享如下:

D2:D105您需要将顶部公式(和)中的范围引用替换E2:E106为第二个公式中的以下代码片段:

INDIRECT("E2:E"&ROW()-1)

INDIRECT()让您输入一个字符串,它将被转换为一个范围。文本E2:E为您提供了大部分范围,但它仍然需要第二个行号。ROW()返回单元格的行。如果您不告诉它要使用哪个单元格,它将使用公式所在的同一单元格。ROW()-1然后,为您提供单元格上方的行。现在看到您的工作表后,您只需-1根据公式相对于数据末尾的位置调整为合适的值。




最好的选择取决于您的数据如何设置。


引用整个专栏:

=SUMIF(D:D,C108,E:E)

这将始终捕获所有内容,并自动在底部包含任何新数据。您不应将此公式放在任何受影响的列中(即,不要将其放在 D 列或 E 列的任何地方)。

您在评论中说您尝试了此操作并收到#REF!错误。这可能意味着 D 列或 E 列中的某个单元格有#REF!错误。如果您尝试在公式中引用错误,该公式也会返回错误。查找其中有错误的单元格,如果找到一个或多个,请更正它/它们。


从第 2 行转到第一个空白行:

=LET(blankRow,ROW(INDEX(D:D,MATCH(TRUE,INDEX((D:D<>0),0),0))),SUMIF(INDIRECT("D2:D"&blankRow),C108,INDIRECT("E2:E"&blankRow)))

这有点复杂,但让我们先拿出一个大块:

LET(blankRow,ROW(INDEX(D:D,MATCH(TRUE,INDEX((D:D<>0),0),0))),...

LET()函数允许您使用一些大公式并为其指定一个简短的名称。我们的大公式是ROW(INDEX(...)),它会在给定范围内找到第一个空白行。这就是我给它命名为的原因blankRow。此公式查找范围,D:D但您可以将其更改为查找范围E:E。现在我们知道了它是什么,让我们看看第二部分:

...SUMIF(INDIRECT("D2:D"&blankRow),C108,INDIRECT("E2:E"&blankRow)))

这看起来有点熟悉。它引用 D 列和 E 列,但只引用从2到 的行blankRow。该INDIRECT()函数采用范围地址的字符串版本并将其转换为范围。当您将数据添加到底部时,blankRow前半部分将变大,因此后半部分的范围引用也会相应增大。


从第 2 行转到最后一个非空白行

=LET(nonBlankRow,SUMPRODUCT(MAX((D:D<>"")*ROW(D:D))),SUMIF(INDIRECT("D2:D"&nonBlankRow),C108,INDIRECT("E2:E"&nonBlankRow)))

基本思想相同。前半部分定义了要调用的最后一个非空行nonBlankRow

=LET(nonBlankRow,SUMPRODUCT(MAX((D:D<>"")*ROW(D:D))),...

后半部分根据从第 2 行到最后一个非空行的范围计算总和:

...SUMIF(INDIRECT("D2:D"&nonBlankRow),C108,INDIRECT("E2:E"&nonBlankRow)))

相关内容