如何修改Excel FILTER公式提取指定日期后可用员工数据？

你当前的公式逻辑完全颠倒了：FILTER(AllStaffProjectAllocationTbl,AllStaffProjectAllocationTbl[End Date]>$S$2) 是筛选出项目结束日期晚于S2的记录，自然会返回仍有项目在2022/11/01之后进行的员工，和你要找的「所有项目都已结束（最晚结束日期早于S2）」的需求相反。

要实现需求，核心是先找出每个员工的最晚项目结束日期，再筛选出该日期早于S2的员工，最后提取指定列信息并去重（因为员工有多条项目记录）。

针对Excel 365/2021版本，可使用以下公式：

=LET(
    -- 提取唯一员工列表
    uniqueStaff, UNIQUE(AllStaffProjectAllocationTbl[Employee Name]),
    -- 计算每个员工的最晚项目结束日期
    maxEndDates, MAXIFS(AllStaffProjectAllocationTbl[End Date], AllStaffProjectAllocationTbl[Employee Name], uniqueStaff),
    -- 筛选出最晚结束日期早于S2的员工
    eligibleStaff, FILTER(uniqueStaff, maxEndDates < $S$2),
    -- 提取指定列（对应原公式的{1,1,0,0,0,0,1,0,0,0}，即第1、2、7列）并去重
    result, XLOOKUP(eligibleStaff, AllStaffProjectAllocationTbl[Employee Name], CHOOSECOLS(AllStaffProjectAllocationTbl, 1, 2, 7)),
    UNIQUE(result)
)

如果你的Excel版本支持GROUPBY函数（最新365预览版），可以用更简洁的写法：

=LET(
    -- 按员工分组，计算最晚结束日期
    groupedData, GROUPBY(AllStaffProjectAllocationTbl[Employee Name], AllStaffProjectAllocationTbl[End Date], MAX),
    -- 筛选符合条件的员工
    eligibleGroups, FILTER(groupedData, groupedData[End Date] < $S$2),
    -- 提取指定列信息
    XLOOKUP(eligibleGroups[Employee Name], AllStaffProjectAllocationTbl[Employee Name], CHOOSECOLS(AllStaffProjectAllocationTbl, 1, 2, 7))
)

• UNIQUE：去除员工的重复记录，得到唯一员工列表
• MAXIFS：按员工匹配，计算每个员工的最晚项目结束日期
• FILTER：筛选出最晚结束日期早于S2的员工
• CHOOSECOLS：提取原表格中你需要的第1、2、7列（对应原公式的列筛选规则）
• XLOOKUP：根据合格员工列表，匹配提取对应的列信息
• 最后用UNIQUE确保结果中每个员工只出现一次

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