如何用Excel从部门计分卡表格中找出得分最高的人员

前提说明 #

假设你的原始数据在A-D列，表头分别为who、vs、who、scores，数据从第2行开始。

方法一：公式分步实现 #

1. 拆分双方得分

   • 在E2单元格输入公式提取左侧人员得分：=--LEFT(D2,FIND(":",D2)-1)，下拉填充至所有行（--用于将文本转为数值，也可替换为VALUE()函数）
   • 在F2单元格输入公式提取右侧人员得分：=--RIGHT(D2,LEN(D2)-FIND(":",D2))，下拉填充至所有行

2. 整合所有人员与对应得分

   • 若使用Excel 365/2021，可直接用数组公式一次性生成人员列表：=TOCOL(A2:A4,C2:C4,1)（输入后按回车）
   • 对应得分列表用数组公式：=TOCOL(E2:E4,F2:F4,1)
   • 旧版Excel可手动将A列、C列的人员依次列在新列（如G列），对应得分列（如H列）依次填入E列、F列的数值

3. 查找最高得分的人员

   • 获取最高得分：=MAX(H:H)
   • Excel 365/2021用XLOOKUP直接匹配：=XLOOKUP(MAX(H:H),H:H,G:G,,"",1)
   • 旧版Excel用INDEX+MATCH组合：=INDEX(G:G,MATCH(MAX(H:H),H:H,0))

方法二：Power Query 高效处理 #

1. 选中原始数据区域，点击「数据」选项卡 → 「从表格/区域」（勾选「我的表格有标题」）
2. 在Power Query编辑器中操作：
   • 拆分scores列：选中该列，点击「转换」→「拆分列」→「按分隔符」，选择「冒号」，拆分至新列
   • 将拆分后的两列重命名为「得分1」「得分2」，设置数据类型为「整数」
   • 逆透视列：选中vs列以外的所有列，点击「转换」→「逆透视列」→「逆透视其他列」
   • 清理列：删除「属性」列，将「值」列重命名为「得分」，调整得到「人员」「得分」两列
   • 筛选最高分：点击「得分」列筛选箭头，选择「数字筛选」→「最大值」
   • 点击「关闭并上载」，即可得到得分最高的人员

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