动态重新定位与动态范围关联的公式

Question

我正在尝试帮助同事改进电子表格。我们正在使用 Excel 365。

在细胞内C3我们有公式：

=FILTER(O1:O51,O1:O51>E1)

它会产生一个动态的结果列，该结果列将根据E1。 在C1我们有一个公式可以对以下单元格求和：

=SUM(C3#)

这一切都有效。

我的同事想将公式放在动态下溢的底部。他还希望公式随着下溢的增大或缩小而自动上下移动。因此在图示中，公式将进入单元格C20。

我不知道该怎么做，甚至不知道是否有可能。

我的建议是不包含公式。首先设置过滤参数，然后运行这个简短的宏：

Sub dural()
    Dim r As Range, rNext As Range
    Set r = Range("C3#")
    Set rNext = r(r.Count + 1)
    
    rNext.Formula = "=SUM(C3#)"
End Sub

宏会将公式放入正确的单元格中。同事回答说“不够自动化”

欢迎任何建议。

Answer 1

您可以使用公式来实现此目的：

=LET(filt,FILTER(O1:O51,O1:O51>E1),CHOOSE(INT(SEQUENCE(COUNT(filt)+1,,0)/COUNT(filt))+1,filt,SUM(filt)))

INT(SEQUENCE(COUNT(filt)+1,,0)/COUNT(filt))+1返回一个数组，其中1包含 FILTER 返回的数字计数，并在末尾附加 2。然后使用 CHOOSE，我们首先返回 FILTER 中的每个数字，然后返回2SUM。