如何用单个公式多条件匹配生成未参训员工及课程的溢出列表

核心公式（Excel 365/2021 适用） #

=LET(
    员工信息, Table1[员工号]:Table1[姓名],
    必训课程清单, Table2[课程名称],
    已参训记录, CHOOSECOLS(Table3, "emp num", "course"),
    应训全组合, CROSSJOIN(员工信息, 必训课程清单),
    已训去重, UNIQUE(已参训记录),
    未训数据, FILTER(应训全组合, NOT(ISNUMBER(XMATCH(TEXTJOIN("|",,应训全组合), TEXTJOIN("|",,已训去重))))),
    IFERROR(未训数据, "无未参训人员/课程")
)

公式说明 #

• 用LET定义变量，把复杂逻辑拆解成简单模块，方便修改和维护
• CROSSJOIN直接生成每位员工+每门必训课程的完整应训组合，自动溢出所有可能的配对
• 通过XMATCH+TEXTJOIN比对应训组合和实际参训记录，筛选出完全未匹配的条目，就是未参训的员工+课程配对
• 最后用IFERROR兜底，没有未参训数据时返回友好提示

注意事项 #

1. 替换公式里的表名/列名：比如Table1是你的员工主列表，Table2是必训课程清单，Table3是参训名单，确保列名和你的表格完全对应（比如emp num是员工号列的标题）
2. 必须使用支持溢出功能的Excel版本（365/2021及以上），否则无法自动生成完整列表

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