如何从不同工作表筛选所有考试不及格学生并写入Sheet3

需求 #

从Sheet1（英语科目）和Sheet2（数学科目）中筛选出**两门考试成绩均为F（不及格）**的学生，将其姓氏（S列）和名字（N列）写入新建的Sheet3。Sheet3需设置表头：姓氏、名字、状态，其中状态列统一填不及格。

原始数据表格 #

Sheet1（英语科目） #

Sheet2（数学科目） #

预期Sheet3结果 #

实现方法 #

方法1：数组公式法 #

1. 新建Sheet3，在A1、B1、C1单元格分别输入姓氏、名字、状态。
2. 在Sheet3的A2单元格输入以下数组公式，输入完成后按Ctrl+Shift+Enter确认（Excel 365及以后版本直接回车即可）：

=INDEX(Sheet1!$B:$B,SMALL(IF((Sheet1!$D:$D="F")*COUNTIFS(Sheet2!$B:$B,Sheet1!$B:$B,Sheet2!$C:$C,Sheet1!$C:$C,Sheet2!$D:$D="F")>0,ROW(Sheet1!$B:$B)),ROW(A1)))

3. 在Sheet3的B2单元格输入公式：

=INDEX(Sheet1!$C:$C,SMALL(IF((Sheet1!$D:$D="F")*COUNTIFS(Sheet2!$B:$B,Sheet1!$B:$B,Sheet2!$C:$C,Sheet1!$C:$C,Sheet2!$D:$D="F")>0,ROW(Sheet1!$B:$B)),ROW(A1)))

4. 在Sheet3的C2单元格输入="不及格"，然后下拉填充所有公式至出现#NUM!错误，最后删除错误行即可得到目标结果。

方法2：Power Query高效处理 #

1. 导入数据：分别选中Sheet1和Sheet2的表格数据，点击「数据」选项卡 → 「从表格/区域」，将数据导入Power Query编辑器。
2. 单表筛选：对两个查询分别筛选成绩列等于F的行，然后删除科目列，仅保留姓氏和名字列。
3. 合并查询：点击「合并查询」→ 选择两个处理后的查询，以姓氏和名字作为匹配列，选择「内部连接」（仅保留两边都存在的记录）。
4. 添加状态列：合并完成后删除多余的合并列，添加自定义列并输入"不及格"，将列名改为状态。
5. 加载结果：点击「关闭并上载」，将处理好的数据加载到新建的Sheet3中。

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