Excel整合未分配项目员工至可用员工列表的公式优化需求

我在「Available Staff」工作表的A2单元格使用了一个LET公式，用来提取指定日期（I2单元格）后可用的员工，但这个公式只从「Staff Project Allocation」工作表的AllStaffProjectAllocationTbl中提取有过项目分配记录的员工，没包含「Staff Details」工作表里从未分配过项目的员工，另外还需要排除雇佣状态为「Employment Terminated」的员工。

我已经做了三步调整：

• Edit1：给原公式加入雇佣状态过滤逻辑，优化后的LET公式如下：

=LET(
staff, AllStaffProjectAllocationTbl,
employee, AllStaffProjectAllocationTbl[Employee],
role, AllStaffProjectAllocationTbl[Role],
discipline, AllStaffProjectAllocationTbl[Discipline],
endDate, AllStaffProjectAllocationTbl[End Date],
employmentStatus, AllStaffProjectAllocationTbl[Employment Status],
rowCt, ROWS(AllStaffProjectAllocationTbl),
roleSelection, $I$11,
employeeSelection, $I$8,
disciplineSelection, $I$14,
availableFromDate, $I$2,
empStatusSelection,"Employment Terminated",
mmult_1, MMULT(SEQUENCE(1,rowCt,1,0),TRANSPOSE(endDate>TRANSPOSE(endDate))*(employee=TRANSPOSE(employee))*(1)),
mmult_2, MMULT((TRANSPOSE(employee)= employee)+0,SEQUENCE(rowCt,1,1,0)),
roleCondition, IF(ISBLANK(roleSelection),1,(role=roleSelection)),
employeeCondition, IF(ISBLANK(employeeSelection),1,( employee=employeeSelection)),
disciplineCondition, IF(ISBLANK(disciplineSelection),1,(discipline=disciplineSelection)),
empStatusCondition, NOT(employmentStatus=empStatusSelection),

AvailabilityCalc,FILTER(staff,roleCondition*employeeCondition*disciplineCondition*empStatusCondition*(endDate<availableFromDate)*(TRANSPOSE(mmult_1)-mmult_2+1=0)),

IFERROR(SORT(INDEX(AvailabilityCalc,SEQUENCE(ROWS(AvailabilityCalc)),{1,4,5,10}),4),"")
)

• Edit2：用公式从StaffDetailsTbl提取在职开发人员，希望和原可用员工列表合并去重：

=FILTER(FILTER(StaffDetailsTbl,(StaffDetailsTbl[Employment Status]<>"Employment Terminated")*(StaffDetailsTbl[Dev Role]="Yes")),{1,1,1,0,0,0,1,0,0})

• Edit3：过滤出StaffDetailsTbl里没出现在AllStaffProjectAllocationTbl中的在职开发人员：

=FILTER(FILTER(StaffDetailsTbl,(StaffDetailsTbl[Employment Status]<>"Employment Terminated")*(StaffDetailsTbl[Dev Role]="Yes")*(IF(ISERROR(VLOOKUP(StaffDetailsTbl[Employee], AllStaffProjectAllocationTbl, 1, FALSE)),1,0 ))),{1,1,1,0,0,0,1,0,0})

现在需要把这些过滤逻辑整合到原LET公式里，构建一个支持角色、员工、学科、可用日期筛选的合并列表，同时想知道用VBA方案是不是更合适。

一、整合后的LET公式实现 #

下面的公式会把「有项目分配且可用的员工」和「无项目分配的在职开发人员」合并，同时保留所有筛选条件（角色、员工、学科、可用日期），并自动去重：

=LET(
    --- 定义原可用员工部分的变量 ---
    staffAlloc, AllStaffProjectAllocationTbl,
    empAlloc, staffAlloc[Employee],
    roleAlloc, staffAlloc[Role],
    discAlloc, staffAlloc[Discipline],
    endDateAlloc, staffAlloc[End Date],
    empStatusAlloc, staffAlloc[Employment Status],
    rowCtAlloc, ROWS(staffAlloc),
    roleSel, $I$11,
    empSel, $I$8,
    discSel, $I$14,
    availDate, $I$2,
    termStatus, "Employment Terminated",
    --- 计算员工项目结束状态的MMULT逻辑 ---
    mmult1, MMULT(SEQUENCE(1,rowCtAlloc,1,0),TRANSPOSE(endDateAlloc>TRANSPOSE(endDateAlloc))*(empAlloc=TRANSPOSE(empAlloc))),
    mmult2, MMULT((TRANSPOSE(empAlloc)=empAlloc)+0,SEQUENCE(rowCtAlloc,1,1,0)),
    --- 原可用员工的筛选条件 ---
    roleCond, IF(ISBLANK(roleSel),1,(roleAlloc=roleSel)),
    empCond, IF(ISBLANK(empSel),1,(empAlloc=empSel)),
    discCond, IF(ISBLANK(discSel),1,(discAlloc=discSel)),
    empStatusCond, NOT(empStatusAlloc=termStatus),
    availCond, endDateAlloc<availDate,
    noFutureProjCond, TRANSPOSE(mmult1)-mmult2+1=0,
    --- 提取符合条件的有项目分配员工（保留需要的列） ---
    availStaffWithProj, IFERROR(SORT(INDEX(FILTER(staffAlloc,roleCond*empCond*discCond*empStatusCond*availCond*noFutureProjCond),SEQUENCE(ROWS(FILTER(staffAlloc,roleCond*empCond*discCond*empStatusCond*availCond*noFutureProjCond))),{1,4,5,10}),4),""),
    --- 定义无项目分配的在职开发人员部分 ---
    staffDetails, StaffDetailsTbl,
    empDetails, staffDetails[Employee],
    roleDetails, staffDetails[Role],
    discDetails, staffDetails[Discipline],
    empStatusDetails, staffDetails[Employment Status],
    devRoleDetails, staffDetails[Dev Role],
    --- 筛选无项目记录的在职开发人员，同时匹配筛选条件 ---
    noProjCond, ISERROR(XMATCH(empDetails,empAlloc)),
    devCond, devRoleDetails="Yes",
    empStatusDetailsCond, empStatusDetails<>termStatus,
    roleDetailsCond, IF(ISBLANK(roleSel),1,(roleDetails=roleSel)),
    discDetailsCond, IF(ISBLANK(discSel),1,(discDetails=discSel)),
    empDetailsCond, IF(ISBLANK(empSel),1,(empDetails=empSel)),
    --- 提取符合条件的无项目员工（保留对应列，和有项目员工列对齐） ---
    availStaffNoProj, IFERROR(FILTER(INDEX(staffDetails,,{1,4,5,7}),noProjCond*devCond*empStatusDetailsCond*roleDetailsCond*discDetailsCond*empDetailsCond),""),
    --- 合并两个列表并去重 ---
    combinedList, IFERROR(VSTACK(availStaffWithProj,availStaffNoProj),""),
    uniqueCombined, IFERROR(UNIQUE(combinedList,FALSE,FALSE),""),
    --- 最终排序输出 ---
    IFERROR(SORT(uniqueCombined,4),"")
)

公式说明： #

1. 拆分原公式逻辑，分别处理「有项目分配的可用员工」和「无项目分配的在职开发人员」
2. 对无项目员工部分，加入和原公式一致的角色、员工、学科筛选条件，确保整体筛选逻辑统一
3. 使用XMATCH替代VLOOKUP判断员工是否有项目记录，效率更高
4. 用VSTACK合并两个列表，通过UNIQUE去重后排序输出

二、VBA方案的适用性分析 #

适合用VBA的场景： #

• 数据量极大：比如上万条员工/项目记录，公式的MMULT和FILTER会明显卡顿，VBA通过数组操作可大幅提升效率
• 复杂自定义逻辑：如果需要加入审批状态、技能匹配等复杂规则，VBA的灵活性远超公式
• 自动触发需求：需要打开文件自动更新列表、修改筛选条件后自动重新计算时，VBA可通过事件触发实现，比公式的自动计算更可控

适合继续用公式的场景： #

• 数据量较小：几千条以内的记录，公式足够流畅，且无需维护代码，普通用户也能修改筛选条件
• 无代码化需求：不需要懂VBA，依赖Excel原生功能即可实现，避免宏安全限制
• 实时性要求高：公式会随单元格内容变化自动更新，无需手动操作

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