在Excel中筛选仅购买编码555/777对应商品的客户

你的需求很明确：要找出只购买过商品编码555和777（均为钢笔），没有购买过其他任何商品的客户。你之前尝试的公式没达到预期效果，是因为它没有对客户的全部订单进行校验，只是做了局部条件判断。下面给你几个可行的解决方案：

方法1：传统数组公式（兼容旧版Excel） #

在F2单元格输入以下公式，按Ctrl+Shift+Enter完成数组公式输入（Excel 365/2021可直接回车），再下拉填充：

=IF(AND(COUNTIFS($B:$B,$B2,$E:$E,555)+COUNTIFS($B:$B,$B2,$E:$E,777)=COUNTIFS($B:$B,$B2),COUNTIFS($B:$B,$B2,$E:$E,555)+COUNTIFS($B:$B,$B2,$E:$E,777)>0),"Check","")

公式逻辑： #

• COUNTIFS($B:$B,$B2,$E:$E,555)+COUNTIFS($B:$B,$B2,$E:$E,777)：计算当前客户购买555和777的总订单数
• COUNTIFS($B:$B,$B2)：计算当前客户的全部订单数
• 第一个条件：购买目标编码的订单数 = 全部订单数（确保没有买其他商品）
• 第二个条件：至少购买过其中一个目标编码（避免从未买过钢笔的客户被误标记）

方法2：Excel 365/2021动态数组公式（更简洁） #

如果用的是支持动态数组的Excel版本，可以用更直观的公式：

=IF(AND(COUNTA(FILTER($E:$E,$B:$B=$B2,NOT($E:$E=555,$E:$E=777)))=0,COUNTA(FILTER($E:$E,$B:$B=$B2))>0),"Check","")

公式逻辑： #

• FILTER($E:$E,$B:$B=$B2,NOT($E:$E=555,$E:$E=777))：筛选当前客户订单中既不是555也不是777的编码
• COUNTA(...) = 0：确认没有其他商品编码
• COUNTA(FILTER($E:$E,$B:$B=$B2))>0：确保客户至少有一个订单（避免空行误判）

方法3：辅助列法（更易理解调试） #

1. 新增辅助列（比如G列），在G2输入以下公式并下拉：

=TEXTJOIN(",",TRUE,UNIQUE(FILTER($E:$E,$B:$B=$B2)))

这个公式会把当前客户所有唯一的商品编码用逗号连接成字符串，比如Greg的G列值是555，Jack是777，Bob是111,555。

2. 在F2输入判断公式并下拉：

=IF(OR(G2="555",G2="777",G2="555,777",G2="777,555"),"Check","")

这种方式逻辑直观，适合新手理解和手动验证结果。

方法4：Power Query（适合大量数据） #

如果你的数据量很大，用Power Query处理更高效：

1. 选中数据区域，点击「数据」选项卡→「从表格/区域」导入Power Query编辑器
2. 点击「开始」选项卡→「分组依据」，设置：
   • 分组依据：CustomerID
   • 新列名：AllItems
   • 操作：所有行
3. 添加自定义列，输入公式：

List.AllTrue(List.Transform([AllItems][Item #], each _ = 555 or _ = 777)) and List.Count([AllItems][Item #])>0

这个公式判断该客户的所有商品编码都是555或777，且至少有一个订单。
4. 筛选出自定义列为True的行，点击「合并查询」→「将查询合并为新查询」，把原表和筛选后的表按CustomerID合并，最后加载回Excel并添加Check标记即可。

用这些方法，就能准确筛选出像示例中Greg（ID3）和Jack（ID8）这样仅购买过555或777的客户，排除购买了其他商品的用户。

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