Excel技术问询：按店铺统计单/双商品及全套装的唯一账单数

核心需求 #

统计每个店铺中：

• 仅购买1种商品的唯一账单数（同一账单买多个同商品只计1次）
• 购买2种商品的唯一账单数
• 购买**全套装（3种商品）**的唯一账单数

假设数据结构 #

原始数据列：

• A列：店铺名称（如m1、m3）
• B列：账单号
• C列：商品类型（如A、B、C）

结果表结构：

• D列：店铺名称（待统计的店铺）
• G列：单商品账单数
• H列：2种商品账单数
• I列：全套装账单数

兼容所有Excel版本的公式（SUMPRODUCT实现） #

1. 单商品账单数（G2单元格，对应D2的店铺）

=SUMPRODUCT(($A$2:$A$100=D2)*(MATCH($A$2:$A$100&$B$2:$B$100,$A$2:$A$100&$B$2:$B$100,0)=ROW($A$2:$A$100)-ROW($A$2)+1)*(COUNTIFS($A$2:$A$100,$A$2:$A$100,$B$2:$B$100,$B$2:$B$100,$C$2:$C$100,$C$2:$C$100)=1))

2. 2种商品账单数（H2单元格）

=SUMPRODUCT(($A$2:$A$100=D2)*(MATCH($A$2:$A$100&$B$2:$B$100,$A$2:$A$100&$B$2:$B$100,0)=ROW($A$2:$A$100)-ROW($A$2)+1)*(COUNTIFS($A$2:$A$100,$A$2:$A$100,$B$2:$B$100,$B$2:$B$100,$C$2:$C$100,$C$2:$C$100)=2))

3. 全套装账单数（I2单元格）

=SUMPRODUCT(($A$2:$A$100=D2)*(MATCH($A$2:$A$100&$B$2:$B$100,$A$2:$A$100&$B$2:$B$100,0)=ROW($A$2:$A$100)-ROW($A$2)+1)*(COUNTIFS($A$2:$A$100,$A$2:$A$100,$B$2:$B$100,$B$2:$B$100,$C$2:$C$100,$C$2:$C$100)=3))

Excel 365/2021简化版（动态数组函数） #

如果使用新版本Excel，公式更简洁：

单商品账单数

=COUNTA(UNIQUE(FILTER($B$2:$B$100,($A$2:$A$100=D2)*(COUNTIFS($A$2:$A$100,$A$2:$A$100,$B$2:$B$100,$B$2:$B$100,$C$2:$C$100,$C$2:$C$100)=1))))

2种商品账单数

把公式末尾的=1改成=2即可；全套装改成=3。

公式说明 #

1. MATCH+ROW部分：对每个「店铺+账单号」组合去重，只保留每组的第一行，避免同一账单被重复计数
2. COUNTIFS部分：统计每个「店铺+账单号」组合下的不同商品数量，判断是1/2/3种
3. SUMPRODUCT/COUNTA+UNIQUE+FILTER：按条件筛选后统计符合要求的唯一账单数量

使用注意事项 #

• 替换公式中的$A$2:$A$100等范围为你实际的原始数据区域
• 下拉公式即可批量统计所有店铺的结果
• 旧版Excel无法使用动态数组函数，需选用SUMPRODUCT版本

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