如何基于薪资上下限规则为Excel员工匹配对应职级

嘿，这个场景在HR薪资核算里太常见了，我整理了几个实用的方法，分情况给你用：

一、公式法（适合小型数据集，快速上手） #

假设你的员工数据在Sheet1（A列=姓名，B列=职位族，C列=薪资），规则表在Sheet2（A列=职位族，B列=职级，C列=最低薪资，D列=最高薪资），直接用下面的公式就行：

1. XLOOKUP（Excel 365/2021及以上版本推荐） #

这个函数比老函数更直观，容错性也强：

=XLOOKUP(1,(Sheet2!$A:$A=Sheet1!B2)*(Sheet2!$C:$C<=Sheet1!C2)*(Sheet2!$D:$D>=Sheet1!C2),Sheet2!$B:$B,"无匹配职级",0,1)

原理：把三个条件（职位族匹配、薪资≥最低、薪资≤最高）相乘，Excel里TRUE会被当成1，FALSE当成0，只有三个条件都满足时乘积才是1，XLOOKUP就找这个1对应的职级，找不到就返回你设置的"无匹配职级"。

2. INDEX+MATCH（兼容所有Excel版本） #

如果你的Excel版本比较老，用这个组合公式：

=INDEX(Sheet2!$B:$B,MATCH(1,(Sheet2!$A:$A=Sheet1!B2)*(Sheet2!$C:$C<=Sheet1!C2)*(Sheet2!$D:$D>=Sheet1!C2),0))

⚠️ 注意：旧版Excel里输入完要按Ctrl+Shift+Enter触发数组计算，新版直接回车就行。
原理：MATCH先找到满足三个条件的规则行号，INDEX再根据行号提取对应的职级。

二、Power Query方法（适合大型数据集，批量处理） #

如果员工数据有几百上千条，用Power Query更高效，还能避免公式卡顿：

• 先把员工表和规则表都导入Power Query：选中表格→「数据」选项卡→「自表格/区域」
• 回到员工表的查询界面，点击「合并查询」→选择规则表，匹配列选「职位族」
• 展开合并后的规则表列，得到同职位族的所有规则行
• 添加自定义列，输入判断逻辑：

  = if [薪资] >= [最低薪资] and [薪资] <= [最高薪资] then [职级] else null
  

• 筛选自定义列不为空的行，删掉多余的规则列，最后点击「关闭并上载」把结果导回Excel

几个关键注意事项 #

• 职位族要完全匹配：别让规则表和员工表的职位族有空格、大小写差异（比如"会计"和" 会计"），可以用TRIM(B2)清理单元格内容
• 避免薪资区间重叠：如果同一个职位族的职级区间有重叠，公式会返回第一个匹配的职级，建议先给规则表按职级降序排序，让高薪员工优先匹配到更高职级
• 处理无匹配的情况：如果员工薪资不在任何规则区间里，公式会返回你设置的提示（比如"无匹配职级"），方便后续排查

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