如何依据薪资起始日期匹配税期区间，关联两表计算雇主月度NI？

场景1：Excel中用公式实现 #

假设两张表的结构如下：

• EmployeeSalaryTbl：包含员工ID、Salary Monthly（月度薪资）、Salary Start Date（薪资起始日期）列
• EmployersNIContributionTbl：包含Tax Period Start（税期起始日）、Tax Period End（税期结束日）、Secondary Threshold（二次阈值）、Employers Contribution %（雇主缴费比例）列

步骤1：匹配税期获取阈值和比例 #

方法1：使用XLOOKUP（Excel 365/2021及以上版本）

在EmployeeSalaryTbl的空白列中，分别插入以下公式：

• 获取Secondary Threshold：
  =XLOOKUP(1, ([@[Salary Start Date]] >= EmployersNIContributionTbl[Tax Period Start]) * ([@[Salary Start Date]] <= EmployersNIContributionTbl[Tax Period End]), EmployersNIContributionTbl[Secondary Threshold])
• 获取Employers Contribution %：
  =XLOOKUP(1, ([@[Salary Start Date]] >= EmployersNIContributionTbl[Tax Period Start]) * ([@[Salary Start Date]] <= EmployersNIContributionTbl[Tax Period End]), EmployersNIContributionTbl[Employers Contribution %])

方法2：使用INDEX+MATCH（兼容旧版Excel）

如果是旧版Excel，用数组公式实现匹配（输入后按Ctrl+Shift+Enter生效）：

• 获取Secondary Threshold：
  =INDEX(EmployersNIContributionTbl[Secondary Threshold], MATCH(1, ([@[Salary Start Date]] >= EmployersNIContributionTbl[Tax Period Start]) * ([@[Salary Start Date]] <= EmployersNIContributionTbl[Tax Period End]), 0))
• 获取Employers Contribution %：
  =INDEX(EmployersNIContributionTbl[Employers Contribution %], MATCH(1, ([@[Salary Start Date]] >= EmployersNIContributionTbl[Tax Period Start]) * ([@[Salary Start Date]] <= EmployersNIContributionTbl[Tax Period End]), 0))

步骤2：计算月度雇主NI #

添加新列，用以下公式计算（避免薪资低于阈值时出现负数，用MAX(0,...)处理）：
=MAX(0, ([@[Salary Monthly]] - [@Secondary Threshold]) * [@[Employers Contribution %]])

场景2：SQL查询实现 #

如果是在数据库中处理，用JOIN语句匹配日期区间并计算：

SELECT
    es.EmployeeID,
    es.[Salary Monthly],
    enc.[Secondary Threshold],
    enc.[Employers Contribution %],
    -- 薪资低于阈值时NI为0，避免负数
    MAX(0, (es.[Salary Monthly] - enc.[Secondary Threshold]) * enc.[Employers Contribution %]) AS MonthlyEmployerNI
FROM
    EmployeeSalaryTbl es
INNER JOIN
    EmployersNIContributionTbl enc
ON
    es.[Salary Start Date] BETWEEN enc.[Tax Period Start] AND enc.[Tax Period End];

注意事项 #

• 确保两张表的日期格式一致，避免因格式不兼容导致匹配失败
• 检查EmployersNIContributionTbl的税期区间，确保无重叠，否则会返回多行匹配结果
• 计算时必须处理薪资低于Secondary Threshold的情况，保证NI结果非负

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