excel - 过滤单元格作为公式中函数的参数

我是程序员，请多多包涵。我知道 Excel 不一定是我在其他领域所习惯的，但我正在努力解决如何完成一些看似简单的事情。

我有一列数字，它们本身就是公式的基础。我想根据某些标准过滤这些单元格并将它们传递给另一个函数以执行计算。

我知道这可以通过 excel 意义上的“过滤器”来完成。这意味着我必须为每个计算多次单击、过滤结果、复制值并将其粘贴到我需要的位置。如果数据发生变化，我将不得不再次这样做。

我正在寻找的是相当于编程语言中的过滤，这里有一个例子：

let range = [1,2,3,4,0,-1,-2,-3,-4];
let subrange = range.filter(function (cell) { return cell > 0; });
subtotal(1,subrange);

所以我的excel是什么样的。

我有一列 G，其中有 12,000 多个结果，这些列中的每一列都是这样的：

=(En-Bn)/Bn

这些被复制下来，n表示从 5-12,000+ 的行号

现在我想创建一个单元格，M2，它包含：

=SUBTOTAL(1,[ Gn in G5:G12000 where Gn > 0 ])

目标是我不想指向并单击，因为实际上，我需要创建更多具有类似“过滤器”谓词的单元格（大约 20 个）。

如果我也不必指定列的 n...n-1 范围，那将尽可能地好，因为理想情况下可以改变。可能是 10，可能是 20,000，应该没关系。

最好的公式或解决方案如下：

SUBTOTAL(1, [ Gn in G0:GLENGTH where Gn > SOMECELL ])

任何指示或建议在哪里阅读或解决方案都会很棒。我一直在谷歌上搜索，似乎我缺乏正确的理解来在所提供的材料中找到答案。

另外，请原谅我使用程序员说话，我知道Excel公式不一定是1：1，我只是在寻找一种节省时间的方法。欢迎使用 VBA 或使用宏的答案，主要是找到一种方法来做到这一点......

最好的，杰森

更新

我应该指定它需要有点向后兼容，所以我不能使用仅在 >= 365 中可用的 FILTER 函数

标签： excelvba