Excel多条件求和问题：计算梦幻橄榄球球队位置年度预期积分

业务规则说明 #

• 核心逻辑：以Table1的球队字段为条件，从Schedule2中取出该队全年对手列表（含重复对阵的球队），匹配Table1中对手、当前行位置两个条件，对符合要求的2021年对手对阵该位置失分值求和，得到该球队该位置的年度预期总积分。
• 示例：达拉斯队外接手（WR）全年对阵NYG、MIA、WAS、MIA，结果为4支球队对阵WR的失分之和，MIA的失分计算两次。
• 约束：不得使用辅助单元格，单公式实现，重复对阵不得去重。
• 术语说明：WR=外接手、RB=跑卫、TE=近端锋、D/ST=防守组、QB=四分卫。

原公式失效原因 #

1. 引用错误：公式内使用相对引用G14获取位置信息，下拉填充时G14会自动偏移，且使用Table1[[#All],[Position]]全列匹配单个单元格，逻辑本身存在错误，仅第一行碰巧匹配到正确值。
2. 逻辑冗余：为处理重复对阵拆分两次SUM，完全没有必要，反而增加出错概率。
3. 匹配逻辑错误：使用MATCH判断是否存在会自动去重，不符合重复对阵重复计算的要求，即使修正引用，计算结果也会漏掉重复对阵的分数。

修复后可用公式 #

Excel 365/2021版本（直接下拉填充即可生效） #

=SUM(COUNTIF(INDEX(Schedule2,0,MATCH(Table1[@Team],Schedules!$AU$3:$CA$3,0)),Table1[Opponent])*IF(Table1[Position]=Table1[@Position],Table1[2021 Opp Position],0))

低版本Excel使用方式 #

输入公式后按Ctrl+Shift+Enter组合键以数组公式形式确认，再下拉填充。

公式逻辑说明 #

• INDEX(Schedule2,0,MATCH(Table1[@Team],Schedules!$AU$3:$CA$3,0))：提取当前行对应球队的全年完整对手列表，包含重复对阵的球队
• COUNTIF(上述对手列表,Table1[Opponent])：统计Table1中每个对手在当前球队赛程中的出现次数，出现2次返回2，未出现返回0，天然支持重复计分要求
• IF(Table1[Position]=Table1[@Position],Table1[2021 Opp Position],0)：筛选出和当前行位置一致的失分数据
• 最后用SUM对「对手出现次数 * 对应位置失分」的乘积求和，得到最终预期总积分

相关截图 #

• Table1表截图：
• Schedule2表截图：

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