对可变范围内的数据点求和

Question

就我个人而言，我也面临着这个问题，但还有一个额外的方面：

	2019	2020	2021	2022
一月	1,600	1,780	1,860	1,890
二月	1,600	1,780	1,860	1,890
行进	1,600	1,780	1,860	1,890
四月	1,780	1,860	1,890	1,890
可能	1,780	1,860	1,890
六月	1,780	1,860	1,890
七月	1,780	1,860	1,890
八月	1,780	1,860	1,890
九月	1,780	1,860	1,890
十月	1,780	1,860	1,890
十一月	1,780	1,860	1,890
十二月	1,780	1,860	1,890

我想要生成一个总和，比如说，用户在单独的工作表上输入的两个日期之间的总和：

开始日期	2019 年 1 月 10 日
结束日期	2021 年 1 月 3 日
总金额	XX,XXX

哪个是最有效的公式，可以自动获取相关参考并得到总和......无需使用 VBA。

Answer 1

我认为“最有效”（或最容易遵循）的是 Office 365：

=LET(x,DATE(INDEX(monthData[#Headers],1,SEQUENCE(,COLUMNS(monthData)-1,2)),SEQUENCE(12),1),
     y, INDEX(monthData,SEQUENCE(12), SEQUENCE(,COLUMNS(monthData)-1,2)),
     SUM( (x>=Start)*(x<End)*y))

但是 Excel 2019 缺少SEQUENCE和LET，因此请尝试以下等效方法：

=SUM((DATE(INDEX(monthData[#Headers],1,COLUMN($B$1:INDEX($1:$1,,COLUMNS(monthData)))),ROW($A$1:INDEX($A:$A,12)),1)>=Start)
*(DATE(INDEX(monthData[#Headers],1,COLUMN($B$1:INDEX($1:$1,,COLUMNS(monthData)))),ROW($A$1:INDEX($A:$A,12)),1)<End)
*INDEX(monthData,ROW($A$1:INDEX($A:$A,12)),COLUMN($B$1:INDEX($1:$1,,COLUMNS(monthData)))))

在这两种情况下，我都将数据转换为桌子命名为monthData，并且还假设只有 12 行，如您所展示的。

• 创建一个由月份和年份交集组成的日期数组，与数据数组相对应。
• 如果月份超出范围，start-end则用零 (0) 替换，否则用一 (1) 替换。
• 将该数组乘以原始数组，并将SUM结果
• 请注意，我的日期是 MDY，并且结束月份不包括在 SUM 中。（如果这不是您想要的，可以轻松更改）。