Excel多列条目按出现Rank计算得分并排序的自动化实现

原始数据表格 #

下面提供两种自动化实现方案，按需选择：

方案1：Excel公式法（适合快速单次处理） #

步骤1：提取所有唯一条目 #

如果用的是Excel 365/2021及以上版本，直接在空白单元格（比如F2）输入以下公式，自动生成所有不重复的条目：

=UNIQUE(VSTACK(B2:B6,C2:C6,D2:D6,E2:E6))

如果是旧版Excel，先把所有Database列的内容手动复制到一列，再用「数据」→「删除重复值」得到唯一条目。

步骤2：计算每个条目的总得分 #

在G2单元格输入公式，下拉到所有条目行，自动计算得分（未在某数据库出现的条目按6分计算）：

=SUM(IFERROR(VLOOKUP(F2,$B$2:$B$6,$A$2:$A$6,FALSE),6),IFERROR(VLOOKUP(F2,$C$2:$C$6,$A$2:$A$6,FALSE),6),IFERROR(VLOOKUP(F2,$D$2:$D$6,$A$2:$A$6,FALSE),6),IFERROR(VLOOKUP(F2,$E$2:$E$6,$A$2:$A$6,FALSE),6))

Excel 365用户可以用更简洁的版本：

=SUM(LET(cols,B:E,BYCOL(cols,LAMBDA(c,IFERROR(XMATCH(F2,c),6)))))

步骤3：排序结果 #

选中F:G列，点击「数据」→「排序」，选择「总得分」列作为关键字，按升序排序即可。

方案2：Power Query法（适合重复处理，可一键刷新） #

如果需要频繁更新原始数据，Power Query是更高效的选择：

1. 选中原始数据区域，点击「数据」→「从表格/区域」，确认弹窗后进入Power Query编辑器。
2. 选中所有Database列（B到E列），点击「转换」→「逆透视列」→「逆透视其他列」，此时表格会变成Rank、Attribute（数据库名称）、Value（条目）三列。
3. 点击「主页」→「分组依据」，设置：
   • 分组依据：Value
   • 新列名：已得分数
   • 操作：求和
   • 列：Rank
4. 添加自定义列计算总得分：点击「添加列」→「自定义列」，输入公式=[已得分数] + (4 - List.Count(List.Select(源[Value], each _ = [Value]))) * 6，然后删除已得分数列。
   注：公式中4是数据库的数量，根据实际表格调整
5. 点击「主页」→「排序」，选择「自定义」列（即总得分）按升序排序。
6. 点击「主页」→「关闭并上载」，将结果加载回Excel。后续更新原始数据后，右键结果表格→「刷新」即可自动更新计算结果。

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