如何计算某球队主客场对应J/K列最后5个数值的总和？

常见#REF!错误原因 #

出现#REF!通常是因为：

• 公式引用的行/列超出工作表有效范围
• AGGREGATE筛选出的行号与INDEX的引用范围不匹配
• 未处理球队比赛不足5场的情况导致错误值

假设数据结构 #

以下公式基于常规赛事表格结构：

• A列：主场球队名称
• B列：客场球队名称
• 行号与比赛日期正相关（行号越大，比赛日期越晚，最近的比赛在表格下方）
• J列：主场对应统计数值
• K列：客场对应统计数值
• M2单元格：目标球队名称

方案1：Excel 365/2021 动态数组（简洁高效） #

=SUM(TAKE(FILTER(IF(A:A=M2,J:J,IF(B:B=M2,K:K,"")), (A:A=M2)+(B:B=M2)), -5))

• 逻辑：先用FILTER筛选目标球队参与的所有比赛，同步匹配提取J（主场）或K（客场）列数值；再用TAKE(-5)取最后5行（最近5场比赛）；最后SUM求和。
• 注意：若表格按日期降序排列（最近比赛在顶部），将-5改为5即可。

方案2：兼容旧版Excel（无动态数组） #

=SUMPRODUCT(IFERROR(INDEX(IF(A:A=M2,J:J,K:K), AGGREGATE(14,6,ROW(A:A)/((A:A=M2)+(B:B=M2)),ROW(INDIRECT("1:5")))),0))

• 逻辑拆解：
  1. (A:A=M2)+(B:B=M2)：判断该行是否为目标球队的主/客场比赛，返回1（符合）或0（不符合）
  2. ROW(A:A)/((A:A=M2)+(B:B=M2))：符合条件的行返回行号，不符合的返回错误值
  3. AGGREGATE(14,6,...ROW(INDIRECT("1:5")))：用14（LARGE）取最大的5个行号（对应最近5场），6表示忽略错误值
  4. INDEX(...)：根据行号提取对应J/K列的数值
  5. IFERROR(...,0)：处理球队比赛不足5场时的错误值，用0填充
  6. SUMPRODUCT：对提取的数值求和

关键优化建议 #

• 避免整列引用（如A:A），替换为实际数据范围（比如A2:A1000），减少计算量同时避免空行引发的错误
• 确保行号与日期对应：若行号不按日期排序，需先对表格按日期排序，或在AGGREGATE中加入日期排序逻辑

内容的提问来源于stack exchange，提问作者Kristóf Polányi