如何使用Excel的双XLOOKUP计算每位学生的总得分？

嗨，我来帮你搞定这个学生总分计算的问题！先理清楚你的场景：你有两张表，一张是记录学生各维度评价的「学生表」，另一张是存着每个评价维度对应选项得分的「映射表」，我们要做的就是把每个学生的评价选项对应到得分，再把所有得分加起来。

假设你的两张表命名分别是学生表和映射表，我先默认一下表的结构（如果你的结构稍有不同，调整一下单元格区域就行）：

• 映射表：A列是评价维度（比如Participation、Teamwork这类），B列是该维度的可选值（比如Good、Excellent），C列是对应得分
• 学生表：A列是学生姓名，B列及以后的列标题和映射表的A列维度完全对应，每个单元格是学生在该维度的评价结果

高效计算总分的公式 #

直接用嵌套XLOOKUP配合SUMPRODUCT就能一步算出总分，比如计算学生表中A2单元格学生的总分，公式可以这么写：

=SUMPRODUCT(XLOOKUP(B2:E2, XLOOKUP(B1:E1, 映射表!A:A, 映射表!B:B), XLOOKUP(B1:E1, 映射表!A:A, 映射表!C:C)))

公式逻辑拆解 #

我给你掰碎了说这个公式怎么工作：

1. 内层第一个XLOOKUP：XLOOKUP(B1:E1, 映射表!A:A, 映射表!B:B) —— 先根据学生表的列标题（也就是各个评价维度），找到映射表中对应维度的所有选项值区域
2. 内层第二个XLOOKUP：XLOOKUP(B1:E1, 映射表!A:A, 映射表!C:C) —— 同样根据列标题，找到映射表中对应维度的所有得分区域
3. 外层XLOOKUP：用学生当前行的所有评价结果（B2:E2），去匹配刚才找到的对应维度的选项，拿到每个维度的得分，形成一个得分数组
4. SUMPRODUCT：把这个得分数组里的所有数值加起来，就是该学生的总得分

备选方案（适合维度少的情况） #

如果你的评价维度不多，也可以逐个维度计算得分再相加，更直观：

=XLOOKUP(B2, XLOOKUP(B1, 映射表!A:A, 映射表!B:B), XLOOKUP(B1, 映射表!A:A, 映射表!C:C)) + 
XLOOKUP(C2, XLOOKUP(C1, 映射表!A:A, 映射表!B:B), XLOOKUP(C1, 映射表!A:A, 映射表!C:C)) + 
XLOOKUP(D2, XLOOKUP(D1, 映射表!A:A, 映射表!B:B), XLOOKUP(D1, 映射表!A:A, 映射表!C:C))

注意事项 #

• 一定要保证学生表的列标题和映射表的评价维度完全一致，包括大小写、空格这些细节，不然匹配会出错
• 映射表里每个「维度+选项」的组合要唯一，不然XLOOKUP会返回第一个匹配的得分，可能不符合你的预期
• 如果你的Excel版本比较旧没有XLOOKUP功能，可以用INDEX+MATCH的组合来替代，逻辑是一样的，只是公式会稍微长一点

备注：内容来源于stack exchange，提问作者Sam