借助各种来源的帮助以及从不同的 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)))