如何在Excel中使用动态数组公式根据日期匹配人员所属团队

数据源说明 #

人员角色表 #

记录人员的任职团队及时间区间，整理为表格如下：

签署记录表 #

需要根据签署人及签署日期匹配对应团队，预期结果如下：

动态数组公式实现 #

假设人员角色表数据范围为A2:D5（表头行以下区域），签署记录表中：

• 签署人姓名列在G2:G8
• 签署日期列在H2:H8
• 待填充的团队列从I2开始

方案1：使用LET函数简化结构（推荐） #

在I2单元格输入以下公式，按回车后自动溢出填充所有结果：

=LET(
    人员匹配结果, FILTER(人员角色表!$A$2:$D$5, 人员角色表!$A$2:$A$5=G2:G8),
    日期区间匹配, BYROW(人员匹配结果, LAMBDA(r, FILTER(r[所属团队], (H2:H8>=r[任职开始日期])*(H2:H8<=r[任职结束日期])))),
    IFERROR(日期区间匹配, "Not Found")
)

公式说明：

1. 人员匹配结果：筛选出每个签署人在角色表中的所有任职记录
2. 日期区间匹配：用BYROW遍历每个签署人的任职记录，筛选出签署日期落在区间内的团队
3. IFERROR：处理无匹配的情况（包括人员不存在于角色表），返回"Not Found"

方案2：简洁单数组公式 #

如果不需要拆分变量，也可以用以下公式直接填充：

=IFERROR(INDEX(人员角色表!$B$2:$B$5, MATCH(1, (人员角色表!$A$2:$A$5=G2:G8)*(H2:H8>=人员角色表!$C$2:$C$5)*(H2:H8<=人员角色表!$D$2:$D$5), 0)), "Not Found")

公式说明：

通过数组条件(人员姓名匹配)*(日期在区间内)找到符合条件的记录位置，用INDEX返回对应团队；无匹配时IFERROR返回"Not Found"

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