如何用Excel公式筛选仅购买指定3种物品各一次的客户

根据你的需求，我们需要找出仅购买胶带（编号111）、钢笔（编号555）、订书机（编号222）各一次，且没有购买其他物品、没有重复购买同一物品的客户。下面提供两种适配不同Excel版本的公式方案：

方案1：兼容所有Excel版本（无需动态数组） #

在期望结果列的第一个单元格（比如E2）输入以下公式，然后下拉填充：

=IF(AND(COUNTIFS($B:$B,$B2,$D:$D,111)=1,COUNTIFS($B:$B,$B2,$D:$D,555)=1,COUNTIFS($B:$B,$B2,$D:$D,222)=1,SUMPRODUCT(--(COUNTIFS($B:$B,$B2,$D:$D,$D:$D)>1))=0),"标记","")

公式拆解： #

• COUNTIFS($B:$B,$B2,$D:$D,111)=1：验证当前客户恰好购买了1次胶带（编号111），同理另外两个COUNTIFS分别验证钢笔和订书机的购买次数为1
• SUMPRODUCT(--(COUNTIFS($B:$B,$B2,$D:$D,$D:$D)>1))=0：检查该客户所有购买记录中，没有任何物品被重复购买（避免像Bob那样重复买订书机的情况）
• 所有条件通过时显示"标记"，否则留空

方案2：适配Excel 365/2021（利用动态数组简化公式） #

如果你的Excel支持动态数组函数，可以用更简洁的公式：

=IF(AND(TEXTJOIN(",",TRUE,SORT(UNIQUE(FILTER($D:$D,$B:$B=$B2))))="111,222,555",SUMPRODUCT(COUNTIFS($B:$B,$B2,$D:$D,$D:$D))=3),"标记","")

公式拆解： #

• FILTER($D:$D,$B:$B=$B2)：筛选出当前客户的所有物品编号
• UNIQUE(...)：提取该客户购买的唯一物品，排除重复项
• SORT(...)：对唯一物品编号排序，确保不管原顺序如何，都会变成111,222,555的固定顺序
• TEXTJOIN(...)：把排序后的编号拼接成字符串，和目标字符串对比，确保没有购买其他物品
• SUMPRODUCT(COUNTIFS(...))=3：验证该客户总购买次数恰好是3次（三个物品各买一次）

验证你的示例数据： #

• Bob：重复购买了订书机，不满足条件，无标记
• Greg：重复购买了钢笔，不满足条件，无标记
• Tim：购买了胶水（编号333，不在目标列表），不满足条件，无标记
• Mark：三个目标物品各买一次，无其他购买，会显示"标记"，和你的期望结果一致

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