大型表格按技能编号排序需求及现有方案困境求助

需求说明 #

现有包含数千条姓名的表格，需将每行的技能按技能编号从小到大排序：技能1对应编号最小的技能，技能2对应次小的技能，以此类推（例如Jill的技能2需替换为pool，因其编号低于darts）。此前尝试转置生成过多列、VLOOKUP条件格式无法批量应用，均未解决问题。

解决方案 #

方法1：Excel动态数组公式（适用于365/2021版本） #

假设技能-编号对应表在$A$1:$B$19（A列是技能名称，B列是编号），原始数据姓名在D列，技能列从E到P列：

1. 匹配技能编号：在空白列（如Q2）输入公式 =XLOOKUP(E2,$A$1:$A$19,$B$1:$B$19,"")，横向拖动到所有技能列的右侧，得到每行技能对应的编号。
2. 按编号排序技能：在新区域（如T2）输入公式 =SORTBY(E2:P2,XLOOKUP(E2:P2,$A$1:$A$19,$B$1:$B$19,""),1)，回车后自动溢出该行排序后的技能。
3. 批量应用：选中T2单元格，双击右下角填充柄，即可批量应用到所有行。

方法2：Power Query（适用于所有Excel版本，大数据更高效） #

1. 选中原始数据区域（含姓名和技能列），点击「数据」→「从表格/区域」，导入Power Query编辑器。
2. 选中所有技能列，点击「转换」→「逆透视列」→「逆透视其他列」，数据将转为「姓名」「属性」「值」三列。
3. 添加编号匹配列：点击「添加列」→「自定义列」，输入公式 =Lookup([值], 技能编号表[技能], 技能编号表[编号])（技能编号表需提前整理，可通过Excel.CurrentWorkbook()引用Excel中的对应表格）。
4. 排序数据：选中「姓名」列，按住Shift选中「自定义列」，点击「开始」→「排序」→「升序」。
5. 重新透视列：选中「姓名」列，点击「转换」→「透视列」，值列选「值」，聚合函数选「不要聚合」，确定后恢复每行多技能的结构。
6. 加载回Excel：点击「开始」→「关闭并上载」，得到排序后的表格。

注意事项 #

• 技能-编号对应表需确保每个技能对应唯一编号，避免匹配错误。
• 动态数组公式出现#N/A时，检查技能名称是否与编号表完全一致（注意大小写、空格）。
• Power Query处理数千行数据更稳定，不会出现公式卡顿问题。

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