Excel基于CELL/INDEX/MATCH按日期动态求和公式报错解决

需求目标 #

固定B2为求和起点，根据指定单元格输入的报告日期，自动匹配A列对应日期所在行的B列单元格作为求和终点，计算二者之间的销售额总和：

• 输入报告日期1/3/2021时，对B2:B4区间求和，返回结果14
• 输入报告日期1/4/2021时，对B2:B5区间求和，返回结果21

原公式错误原因 #

原公式=SUM(B2:CELL("address",INDEX(A2:B63,MATCH(G1,A2:A63,1),2)))无法正常运行，核心问题有3个：

• CELL("address",...)返回的是文本格式的地址字符串，无法直接被SUM识别为有效区间引用，会触发引用错误
• 示例中附带的公式存在语法疏漏，MATCH(D2,A1:A5,1)2处缺失INDEX函数的参数分隔逗号
• MATCH查找区域与INDEX引用区域的起始行不对齐，会导致行号匹配偏移，返回错误的求和终点

可用正确公式 #

如果报告日期输入在D2单元格，直接使用以下公式即可，无需嵌套CELL转地址：

=SUM(B2:INDEX(B:B,MATCH(D2,A:A,1)))

如果数据范围固定为A2:B63，可使用限定范围版本，运算效率更高：

=SUM(B2:INDEX(B2:B63,MATCH(D2,A2:A63,1)))

公式逻辑解释 #

• MATCH(D2,A:A,1)：使用模糊匹配模式（查找小于等于查询值的最大项），定位输入的报告日期在A列对应的行号。使用该模式需提前将A列日期按升序排序
• INDEX(B:B,匹配到的行号)：直接返回B列对应行的单元格引用，作为动态求和的终点，不需要额外做地址格式转换
• SUM(B2:动态终点)：直接计算固定起点到动态终点的区间数值总和

适配调整提示 #

• 如果需要精确匹配日期（输入日期不存在时直接返回错误，而非匹配最近的较早日期），将MATCH函数的第三个参数从1改为0即可
• B列的货币格式不会影响数值计算，公式可自动识别单元格内的数值内容

内容的提问来源于stack exchange，提问作者Brendan Lesinski