如何匹配日期区间,从Grade表中获取CTC薪资表对应员工的职级
员工薪资记录职级匹配操作指南
基础数据说明
你需要用到两份业务表数据:
- CTC薪资表:包含全量员工薪资明细,每条记录对应唯一的薪资生效日期
- Grade职级表:包含全量员工职级变动记录,每条记录对应对应职级的生效起始日期
匹配逻辑说明
要为CTC薪资表的每条记录匹配正确职级,遵循如下规则:
提取CTC表单条记录的薪资生效日期,查询该员工在Grade表中所有生效起始日期早于等于该薪资生效日期的记录,取其中生效日期最新的一条,将该行对应的职级字段值填充到CTC表记录即可。
落地实现方案
1. Excel 公式实现(适合10万行以内数据)
假设两张表的字段结构如下:
| 表名 | 列顺序 | 字段名 |
|---|---|---|
| CTC表 | A列 | 员工ID |
| CTC表 | B列 | 薪资生效日期 |
| CTC表 | C列 | 待填充匹配职级 |
| Grade表 | A列 | 员工ID |
| Grade表 | B列 | 职级生效起始日 |
| Grade表 | C列 | 对应职级 |
在CTC表的C2单元格输入如下数组公式,按下Ctrl+Shift+Enter组合键确认生效后,下拉填充所有行即可:
=LOOKUP(2,1/(Grade!$A:$A=A2)/(Grade!$B:$B<=B2),Grade!$C:$C)
2. Python Pandas 实现(适合10万行以上大数据量)
如果数据量较大,Excel运算卡顿,可使用Pandas的merge_asof能力高效匹配,核心代码如下:
import pandas as pd # 读取两份表文件,自动识别日期格式字段 ctc_df = pd.read_excel("CTC薪资表.xlsx", parse_dates=["薪资生效日期"]) grade_df = pd.read_excel("Grade职级表.xlsx", parse_dates=["职级生效起始日期"]) # 两张表都按员工ID、对应日期字段升序排序,是merge_asof匹配的前提 ctc_df = ctc_df.sort_values(by=["员工ID", "薪资生效日期"]).reset_index(drop=True) grade_df = grade_df.sort_values(by=["员工ID", "职级生效起始日期"]).reset_index(drop=True) # 按员工ID关联,取小于等于薪资生效日的最新职级记录 result_df = pd.merge_asof( ctc_df, grade_df, by="员工ID", left_on="薪资生效日期", right_on="职级生效起始日期" ) # 导出结果 result_df.to_excel("已匹配职级的CTC薪资表.xlsx", index=False)
内容的提问来源于stack exchange,提问作者Grace
相关产品推荐
相关产品推荐

