如何在Excel中根据第二列值组合筛选对应目标行

原始数据 #

43833   N/A
43833   Eye
43834   Multiple
43834   Legs
43841   Legs
43841   N/A
43845   Neck
43845   Feet
43845   N/A
43848   Arms
43848   N/A
43856   Neck
43856   Eye
43857   N/A
43857   Eye

需求 #

• 根据第二列的指定值组合，筛选出第一列对应唯一ID的所有行
• 示例：当指定值组合为N/A和Eye时，期望结果为：

43833    N/A
43833    Eye
43857    N/A
43857    Eye

• 仅展示完全符合指定值组合的重复ID对应的行（即该ID的第二列值只能包含指定的组合，不能有其他值）

遇到的问题 #

尝试用IF/AND/OR及COUNTIF函数实现，但未得到正确结果。

解决方案1：辅助列+筛选（简单易上手） #

假设ID在A列，对应值在B列，要找同时包含N/A和Eye且无其他值的ID：

1. 插入新列C，在C2单元格输入以下公式，下拉填充：

=AND(COUNTIFS(A:A,A2,B:B,"N/A")>0,COUNTIFS(A:A,A2,B:B,"Eye")>0,COUNTIFS(A:A,A2)=2)

• 前两个条件确保当前ID同时存在N/A和Eye
• 第三个条件限制该ID只有两行（避免包含其他额外值）

2. 筛选C列值为TRUE的行，即可得到目标结果。若指定组合为3个值，只需将第三个条件改为COUNTIFS(A:A,A2)=3，并增加对应值的COUNTIFS判断。

解决方案2：数组公式（无需辅助列） #

不想添加辅助列的话，可使用数组公式直接提取符合条件的行：
在空白单元格（如D2）输入以下公式，输入完成后按Ctrl+Shift+Enter触发数组运算，下拉填充至出现#NUM!为止：

=INDEX(A:A,SMALL(IF((COUNTIFS(A:A,A:A,B:B,"N/A")>0)*(COUNTIFS(A:A,A:A,B:B,"Eye")>0)*(COUNTIFS(A:A,A:A)=2),ROW(A:A),""),ROW(A1)))

对应提取B列值时，只需将公式中的A:A替换为B:B即可。

解决方案3：Power Query（大数据量首选） #

数据量较大时，用Power Query处理更高效：

1. 选中数据区域，点击「数据」选项卡→「从表格/区域」，进入Power Query编辑器
2. 点击「转换」→「分组依据」，分组列选择ID，操作选择「所有行」，新列名设为「明细」
3. 添加自定义列，输入公式：

= List.ContainsAll([明细][值], {"N/A", "Eye"}) and List.Count([明细][值])=2

4. 筛选自定义列值为TRUE的行，展开「明细」列，删除自定义列后加载回Excel即可。

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