☰

Excel：创建动态求和公式

tag-icon • tag-icon microsoft-excel worksheet-function
Excel：创建动态求和公式

我正在尝试在 Excel 中创建动态求和公式，我在屏幕截图 (1) 中已指出。如您所见，除 H2、H3、H4 和 H5 外，其他行均相同。我不想重复添加更多行，而是想有一个单元格来引用其中应该有多少行。

我知道这本质上是 i-->n 的总和，这里适合使用 for 循环，但我很好奇是否可以不使用 VBA 来实现。有什么想法吗？

公式示例

数据结构示例

答案1

不依赖 VBA 解决方案实现此目的的一种方法是使用函数BYCOL()（适用于 Microsoft 365 版 Excel）：

=BYCOL(array, [function])

数组指定要应用函数的范围，函数本身在 lambda 语句中指定。最后，您想要获取 x 个连续天数之和的最小值。假设您的数据存储在范围内E2:AI2，连续天数存储在单元格中A1，则函数如下所示：

=MIN(BYCOL(E2:AI2,LAMBDA(col,SUM(OFFSET(col,,,,A1)))))

该MIN()部分可确保您仅获得返回的数组（所有 x 个连续值的总和）的最小和。数组只是存储数据的范围；它在 lambda 参数中命名col，因此按其名称使用。在您的例子中，您想要将 sum 函数应用于x = 4连续的几天（其中 4 存储在单元格中A1）。

但是，使用这种简单的规范，您会遇到偏移超出数据右端单元格的问题。这意味着您得到的最后总和将是 81.8（1 月 31 日的值）+ 3 乘以 0，因为单元格是空的。为了避免这种情况，您可以将函数与一个IF()语句组合起来，如果空单元格的数量大于 0，则用空单元格替换结果。调整后的公式如下所示：

=MIN(BYCOL(E2:AI2,
LAMBDA(col,IF(COUNTIF(OFFSET(col,,,,A1),"")>0,"",SUM(OFFSET(col,,,,A1))))))

如果您没有 Microsoft 365 版本，有两种方法也可以使用。但是，这两种方法有点繁琐，尤其是对于有多天的情况（因为天数实际上无法自动设置；除非可能使用 和 的组合来构建范围ADDRESS()）INDIRECT()，但我仍然认为比您当前的规范更简洁一些：

=MIN(INDEX(E2:AF2+F2:AG2+G2:AH2+H2:AI2,0))

=SUMPRODUCT(MIN(E2:AF2+F2:AG2+G2:AH2+H2:AI2))

在两种情况下，关于范围的想法是相同的，每增加一天，范围的起点和终点就会移动 1。

相关内容