Google Sheets实现球队最近3场主客场指数滚动求和方法

前提约定 #

假设你的数据表结构如下，数据从第2行开始存储（第1行为表头）：

• A列：Home team（主队）
• B列：Away team（客队）
• C列：Home index（主队指数）
• D列：Away team（客队指数）
• E列：待填充home rolling score（主队滚动得分）
• F列：待填充away rolling score（客队滚动得分）

可用公式 #

直接在对应单元格输入公式后下拉填充全列即可：

1. E2单元格（主队滚动得分）公式：

=IFERROR(QUERY(FILTER({SEQUENCE(ROWS(A$2:A2)),IF(A$2:A2=A2,C$2:C2,IF(B$2:B2=A2,D$2:D2,))},(A$2:A2=A2)+(B$2:B2=A2),SEQUENCE(ROWS(A$2:A2))<ROW()-1),"SELECT SUM(Col2) WHERE Col2 IS NOT NULL ORDER BY Col1 DESC LIMIT 3 LABEL SUM(Col2) ''"),0)

2. F2单元格（客队滚动得分）公式：

=IFERROR(QUERY(FILTER({SEQUENCE(ROWS(A$2:A2)),IF(A$2:A2=B2,C$2:C2,IF(B$2:B2=B2,D$2:D2,))},(A$2:A2=B2)+(B$2:B2=B2),SEQUENCE(ROWS(A$2:A2))<ROW()-1),"SELECT SUM(Col2) WHERE Col2 IS NOT NULL ORDER BY Col1 DESC LIMIT 3 LABEL SUM(Col2) ''"),0)

逻辑匹配说明 #

• 首次参赛无历史记录时，公式通过IFERROR兜底返回0，符合初始值要求
• 身份匹配逻辑：遍历当前行以上的所有赛事，球队为主队时累加对应行的Home index，为客队时累加对应行的Away index，取值准确
• 范围限制：自动排除当前行的赛事数据，仅统计当前行之前的记录
• 最近场次限制：将历史参赛记录按行号倒序排列（赛事时间越近排序越靠前），仅取前3条记录求和，完全匹配滚动3场的规则
• 样例场景适配：第10行Philadelphia作为主队对阵Atlanta时，公式会自动筛选Philadelphia在第10行之前的所有参赛记录，取最近3场指数累加返回3，和样例预期值一致

调整说明 #

• 若数据起始行不是第2行，将公式中所有A$2/B$2/C$2/D$2替换为实际数据首行的对应单元格即可
• 若需要调整滚动统计的场次数量，修改公式中LIMIT 3的数字即可，例如统计最近5场则改为LIMIT 5

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