如何创建仅显示可用员工的下拉列表以填补指定日期区间班次？

完全可行，以下是适配不同Excel版本的具体实现方案：

一、Excel 365/2021（支持动态数组） #

假设你的数据结构如下：

• 员工姓名列：C3:C5
• 已排班次开始日期：A3:A5
• 已排班次结束日期：B3:B5
• 新班次开始日期单元格：D1，结束日期：E1

1. 提取无冲突员工列表
   在空白单元格（比如F3）输入以下动态数组公式：

=FILTER(C3:C5, NOT((A3:A5<=E1)*(B3:B5>=D1)), "无可用员工")

公式逻辑：

• (A3:A5<=E1)*(B3:B5>=D1) 判断员工已排班次与新班次是否重叠（和你之前的冲突判断逻辑一致）
• NOT(...) 取反筛选出无冲突的员工
• FILTER 直接返回符合条件的员工数组，自动溢出显示

2. 设置下拉列表

• 选中需要放置下拉的目标单元格（比如G1）
• 点击「数据」选项卡 → 「数据验证」
• 允许类型选「序列」，来源框输入=F3#（#代表动态数组的溢出范围）
• 确定后，下拉列表就只会显示无冲突的员工姓名

二、旧版Excel（不支持动态数组） #

需要通过「定义名称」结合函数实现：

1. 定义员工范围名称

• 点击「公式」选项卡 → 「定义名称」
• 名称设为AllEmployees，引用位置输入：

=OFFSET($C$3,0,0,COUNTA($C$3:$C$5),1)

这个名称用来指代所有员工姓名的范围。

2. 定义筛选后员工名称

• 新增名称AvailableEmployees，引用位置输入：

=INDEX(AllEmployees, SMALL(IF(NOT((A3:A5<=E1)*(B3:B5>=D1)), ROW(AllEmployees)-ROW($C$3)+1, ""), ROW(INDIRECT("1:"&SUMPRODUCT(--NOT((A3:A5<=E1)*(B3:B5>=D1)))))))

公式逻辑：通过SUMPRODUCT统计无冲突员工数量，再用INDEX+SMALL提取对应姓名。

3. 设置下拉列表

• 选中目标单元格，打开数据验证，允许类型选「序列」，来源输入=AvailableEmployees
• 确定后即可生成仅显示无冲突员工的下拉列表

补充说明 #

• 你原有的冲突判断公式可简化为：=IF(COUNTIFS($A$3:$A$5,"<="&E1,$B$3:$B$5,">="&D1),"Overlap","Do not overlap")（合并重复的判断逻辑）
• 若新班次日期为空，下拉列表会显示所有员工；若所有员工都有冲突，动态数组版本会显示「无可用员工」，旧版可额外添加容错处理

内容的提问来源于stack exchange，提问作者David Whitehouse