如何在Excel中按照另一参考列的固定顺序对目标数据数组排序

方法1：辅助列+常规排序（全版本Excel通用，无数据量限制） #

这个方法不需要记复杂函数，兼容性最好，不会出现版本不兼容问题：

• 打开Species工作表，在参考物种列的右侧插入1列空列，从第一行有效物种数据开始，依次填充从1开始的连续自然数，给每个参考物种标记唯一的排序优先级，序号越小排序位置越靠前。
• 切换到Data工作表，在现有两列调研数据的右侧插入1列空列作为排序辅助列，在第一行有效数据对应的辅助列单元格输入匹配公式：
  • 2021及之后版本/365版本Excel用：=XLOOKUP(当前行物种名单元格, Species!$参考物种列$起始行:$参考物种列$结束行, Species!$序号列$起始行:$序号列$结束行, 999999)
  • 2019及更早版本Excel用：=IFERROR(VLOOKUP(当前行物种名单元格, Species!$参考物种列$起始行:$序号列$结束行, 2, 0), 999999)

  公式里的999999是极大值标记，代表Data表存在但Species参考表没有收录的物种，会自动排到所有已收录物种的末尾，不会打乱整体排序逻辑。输入完第一行公式后下拉填充到所有数据行即可。

• 选中Data表包含调研数据、辅助列在内的全部有效区域，点击顶部菜单栏「数据」选项卡下的「排序」按钮，排序关键字选择刚才创建的辅助列，排序依据选「数值」，次序选「升序」，点击确定即可完成全量数据排序。
• 排序完成后可直接删除辅助列，已经排好的顺序不会发生变化。

方法2：动态数组一键排序（适用于Excel 365/2021及以上版本） #

之前用SORTBY未成功，核心原因是没有将参考表的物种顺序映射为可排序的数值序列，直接在Data表的空白区域选中首个空白单元格，输入以下公式按回车，会自动溢出生成全部排好序的结果，不需要手动下拉：

=SORTBY(Data!$调研数据列$起始行:$调研数据列$结束行, XLOOKUP(Data!$物种列$起始行:$物种列$结束行, Species!$参考物种列$起始行:$参考物种列$结束行, SEQUENCE(ROWS(Species!$参考物种列$起始行:$参考物种列$结束行)), 999999),1)

之前方案失效的原因说明 #

• 自定义列表100条条目是旧版Excel的硬限制，千级/万级的全量物种排序完全不需要依赖该功能
• 原生SORT/SORTBY函数默认仅支持按单元格值的数值大小、文本拼音/笔画规则排序，无法直接识别其他工作表的自定义排序规则，必须先通过匹配函数将自定义顺序转换为数值序列才能正常排序
• 如果出现匹配错位、排序错误的情况，先检查两个工作表的物种名是否存在多余前后空格、全角/半角字符不一致、学名拼写差异的问题，可提前用TRIM()函数清理两表的物种名文本后再执行匹配排序。

内容的提问来源于stack exchange，提问作者Andrés Felipe Tigreros-Andrade