Excel轮班排班自动化：按日期匹配自动填充人员的公式问题求助

几个月前曾解决过类似轮班排班自动化需求，本次对原有轮班规则升级后遇到匹配失效问题。工作簿共包含2个工作表：

• 表1
  
• 表2（工作表名Roster）
  

需求说明 #

根据表1C2单元格的日期，匹配表2中对应日期的轮班规则，自动在表1D列生成对应值班人员姓名。当前使用的公式仅能匹配2021/11/8的场景，修改C2日期为2021/11/9后D列内容无法同步更新。

当前使用的表1D列公式：
=INDEX(Roster!$D$21:$D$28,SMALL(IF(Sheet1!C4=Roster!$E$21:$E$28,ROW(Roster!$E$21:$E$28)-ROW(Roster!$E$21)+1),COUNTIF(Sheet1!$C$4:C4,C4)))

解决方案 #

问题原因 #

原公式仅匹配了班次字段，没有关联C2单元格的日期筛选条件，因此切换日期后无法自动对应新日期的轮班规则。

适配公式 #

Excel 365/2021及以上版本（支持动态数组）

在表1D4单元格输入以下公式，回车后会自动溢出填充所有对应班次的值班人员：
=FILTER(Roster!$D$21:$D$28,(INDEX(Roster!$20:$20,,MATCH(Sheet1!$C$2,Roster!$20:$20,0))=Roster!$E$21:$E$28)*(Roster!$E$21:$E$28=Sheet1!C4:C100),"无匹配人员")

公式逻辑：先匹配Roster表第20行的日期等于C2目标日期的对应列，再同时匹配班次字段，直接筛选出符合条件的值班人员。

旧版本Excel（不支持动态数组）

在表1D4单元格输入以下数组公式，按Ctrl+Shift+Enter三键结束输入，再向下拖动填充到所有需要的行：
=INDEX(Roster!$D$21:$D$28,SMALL(IF((INDEX(Roster!$E$20:$AZ$20,,MATCH(Sheet1!$C$2,Roster!$E$20:$AZ$20,0))=Roster!$E$21:$E$28)*(Roster!$E$21:$E$28=Sheet1!C4),ROW(Roster!$D$21:$D$28)-ROW(Roster!$D$20),999),1))

注意：可根据你Roster表实际存储日期的列范围调整$E$20:$AZ$20的范围，减少不必要的计算消耗。

生效说明 #

如果你的Excel开启了自动重算，修改C2单元格的日期后D列会自动同步更新；如果未开启自动重算，修改C2后按F9手动刷新即可更新。

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