Excel跨表匹配查询：按姓名与日期区间返回对应薪资

前提说明（先对应你的实际列） #

假设你的表格列对应如下（如果和你的实际列不符，直接替换公式里的列号即可）：

• Sheet2「薪资历史记录」：
  • 员工姓名：B列
  • Date of Revision（修订日期）：C列
  • Effective until（生效截止日期）：D列
  • New Salary（新薪资）：E列
• Sheet1「薪资平均值计算」：
  • 日期：A列
  • 员工姓名：B列
  • 需要返回对应薪资的单元格：C列

公式方案 #

方案1：Excel 365/2021及以上版本（用XLOOKUP，更简洁）

在Sheet1的C2单元格输入公式，下拉填充：

=XLOOKUP(1,(Sheet2!$B:$B=$B2)*(Sheet2!$C:$C<=$A2)*(Sheet2!$D:$D>=$A2),Sheet2!$E:$E,"无匹配薪资")

公式解释：

• (Sheet2!$B:$B=$B2)：匹配Sheet2中与当前行相同的员工姓名
• (Sheet2!$C:$C<=$A2)：确保Sheet1的日期不早于修订日期
• (Sheet2!$D:$D>=$A2)：确保Sheet1的日期不晚于生效截止日期
• 三个条件同时满足时，返回对应的New Salary；无匹配时显示「无匹配薪资」

方案2：旧版Excel（用INDEX+MATCH组合）

在Sheet1的C2单元格输入公式，下拉填充：

=INDEX(Sheet2!$E:$E,MATCH(1,(Sheet2!$B:$B=$B2)*(Sheet2!$C:$C<=$A2)*(Sheet2!$D:$D>=$A2),0))

注意：输入完公式后，需要按 Ctrl+Shift+Enter 触发数组公式（Excel 365版本无需此操作）

计算特定时段薪资平均值 #

假设要计算员工「张三」在2023年1月1日至2023年12月31日的薪资平均值，用AVERAGEIFS函数：

=AVERAGEIFS(Sheet1!$C:$C,Sheet1!$B:$B,"张三",Sheet1!$A:$A,">=2023/1/1",Sheet1!$A:$A,"<=2023/12/31")

内容的提问来源于stack exchange，提问作者HR- Specialized