如何根据ID区间查找表自动填充Excel表格的Group编号

问题场景说明 #

需要给目标表的每条记录匹配对应分组，匹配规则为：当ID值落在查找表对应记录的Min和Max区间范围内时，返回该区间对应的Group编号。

• 待填充目标表示例：
  
• 区间查找表示例：
  

预期匹配结果：

• John 对应 Group 编号 1
• Peter 和 Alex 对应 Group 编号 2
• Dani 对应 Group 编号 3

要求方案适配查找表定期更新、行数动态扩充的场景，不需要每次调整配置。

前置必做配置（适配动态表更新） #

不管用下面哪种方案，先把两张表转为Excel超级表，这是实现动态引用的核心，操作步骤：

• 选中目标表任意单元格，按快捷键Ctrl+T，弹出窗口勾选「表包含标题」后点确定，可在顶部「表设计」选项卡修改表名，默认命名为Table1即可
• 用同样操作把查找表（含Min、Max、Group三列）转为超级表，默认命名为Table2

转成超级表后，后续在查找表末尾新增区间行、修改Min/Max数值、删除无效区间，所有引用该表的公式/查询都会自动识别最新数据范围，不需要手动修改引用区域。

方案1：Excel 365/2021及以上版本（最简单） #

直接在目标表Group列的第一个数据单元格输入以下公式，超级表会自动把公式填充到整列：

=XLOOKUP(1, ([@ID]>=Table2[Min])*([@ID]<=Table2[Max]), Table2[Group], "无匹配分组")

公式逻辑：逐行比对当前记录的ID，找到第一个同时满足「ID≥区间Min、ID≤区间Max」的区间记录，返回对应的Group值，没有匹配到区间时返回「无匹配分组」，可根据需要修改兜底文本。

方案2：兼容Excel 2019及更早版本 #

旧版Excel没有XLOOKUP函数，可以用LOOKUP数组公式实现同样效果，在目标表Group列输入：

=IFERROR(LOOKUP(1,0/(([@ID]>=Table2[Min])*([@ID]<=Table2[Max])),Table2[Group]),"无匹配分组")

注意：该写法要求查找表的ID区间不能交叉重叠，否则会返回最后一个匹配到的分组值，如果区间存在重叠可能出现匹配错误，这种情况推荐用方案3。

方案3：大数据量/高频更新场景（长期维护推荐） #

如果查找表更新频率高、或者两张表数据量过千条，用Power Query实现匹配，后续更新只需要点一下刷新即可，不需要修改公式：

1. 选中目标表任意单元格，点击顶部「数据」选项卡→「从表格/区域」，会自动把表加载到Power Query编辑器
2. 用同样操作把查找表Table2也加载到Power Query编辑器
3. 回到目标表的Power Query查询页，点击「添加列」→「自定义列」，输入以下公式后确定：

   = Table.SelectRows(Table2, (x) => x[Min] <= [ID] and x[Max] >= [ID])[Group]{0}?
   

4. 点击「关闭并上载」，把匹配完成的结果导出到Excel工作表即可

后续使用时，只要修改/新增/删除查找表的区间数据，右键结果表选择「刷新」，所有分组就会自动重新匹配，不需要做其他调整。

结果校验 #

用上述任意方案处理示例数据，返回结果和预期完全一致：

• John（ID=121）落在100-200区间 → 返回Group 1
• Peter（ID=232）、Alex（ID=299）落在201-300区间 → 返回Group 2
• Dani（ID=340）落在301-400区间 → 返回Group 3

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