如何使用特定公式计算符合条件的最后N行平均值，示例为主队近10场赛事均值

原有公式问题说明 #

你当前使用的公式存在逻辑偏差：AVERAGE(AVERAGEIF(B$2:B2156,B2157,D$2:D2156),AVERAGEIF(C$2:C2156,B2157,E$2:E2156)) 是分别求主队侧、客队侧匹配结果的平均值后再二次平均，不是把所有匹配的单场数据合并计算平均值，会导致统计结果不符合实际。

Excel 365/2021及以上版本方案（推荐） #

直接用FILTER+TAKE组合实现，逻辑清晰不易出错，无需嵌套OFFSET：

=AVERAGE(TAKE(FILTER(IF(B$2:B2156=B2157,D$2:D2156,IF(C$2:C2156=B2157,E$2:E2156,NA())),NOT(ISNA(IF(B$2:B2156=B2157,D$2:D2156,IF(C$2:C2156=B2157,E$2:E2156,NA()))))),-10))

逻辑说明：

• 先遍历所有历史行，匹配到目标队名时返回对应的数据值，未匹配返回错误值
• 用FILTER筛选出所有匹配的数值，顺序和表格行顺序一致（行号越小比赛越早）
• 用TAKE(..., -10)取筛选结果的最后10条，即最近10场的数值
• 最后计算这10个数值的平均值，如果匹配不足10场就按实际匹配数量计算

旧版Excel兼容方案 #

如果你的Excel版本不支持新函数，可以用数组公式实现，输入公式后需要按Ctrl+Shift+Enter组合键生效：

=AVERAGE(IF(ROW($2:2156)>=LARGE(IF((B$2:B2156=B2157)+(C$2:C2156=B2157),ROW($2:2156),0),MIN(10,SUMPRODUCT(--((B$2:B2156=B2157)+(C$2:C2156=B2157))))),IF(B$2:B2156=B2157,D$2:D2156,E$2:E2156),""))

适配调整说明 #

如果你的表格是按新比赛在上、旧比赛在下的顺序排列，把上面新函数版本的TAKE(..., -10)改成TAKE(..., 10)，旧版本的LARGE改成SMALL即可。

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