Excel比对两张表格并自动统计每人缺失必修课程的公式求解

单元格区域假设（可根据你的实际表格调整） #

• 选课表（表名选课表）：A列存人员ID，B列存已选课程，多门课程用中文顿号、分隔，数据从第2行开始
• 必修课表（表名必修课表）：A列存必修课名称，数据从第2行开始
• 结果输出在选课表C列，每行对应对应人员的缺失课程列表

公式方案 #

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

第一步：生成不重复的必修课列表

你提供的必修课表存在重复的CourseB，先去重处理：
如果严格用第二张表的内容作为必修范围，公式为：
=UNIQUE(必修课表!A2:A5)
执行后得到不重复的3门必修课：CourseA、CourseB、CourseD。
如果要达到你给出的预期结果（CourseC也计入必修），可以把所有选课表中出现过的课程去重作为必修范围，公式为：
=UNIQUE(TEXTSPLIT(TEXTJOIN("、",TRUE,选课表!B:B),"、"))
执行后得到4门必修课：CourseA、CourseB、CourseC、CourseD。

第二步：匹配单人行缺失课程

把去重后的必修课列表放在任意空白区域，假设放在E2:E5，在选课表C2单元格输入以下公式，下拉即可自动生成所有人员的缺失课程：
=TEXTJOIN("、",TRUE,FILTER(E$2:E$5,ISERROR(SEARCH("、"&E$2:E$5&"、","、"&B2&"、")),"无缺失")
输入后直接就能得到你要的结果：

• John行输出：CourseB
• Bruno行输出：CourseA、CourseC、CourseD

Excel 2019 及更早版本（不支持动态数组） #

先手动整理好不重复的必修课列表放在E2:E5，在选课表C2输入以下数组公式，输入完成后按Ctrl+Shift+Enter生效，再下拉填充即可：
=TEXTJOIN("、",TRUE,IF(ISERROR(SEARCH("、"&E$2:E$5&"、","、"&B2&"、")),E$2:E$5,""))

注意事项 #

• 如果你的课程分隔符是英文逗号、空格等其他符号，把公式里的、替换成对应分隔符即可
• 公式前后添加、是为了避免短课程名误匹配（比如防止CourseA被误匹配到CourseAA这类相似名称的课程）

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