如何用Excel函数按A列分组对比B/C/D列并生成匹配标记？

原始数据示例 #

A B C D
al id id id
df id desc desc
df id id desc
df id id id
ff desc id desc
ff desc id desc
al id id id
al id id desc
mn desc desc desc
mn desc desc desc
ff desc id desc

需求说明 #

• 按A列的重复值分组，提取每组对应的A、B、C、D列数据
• 对比每组内所有行的B、C、D列是否完全一致
• 生成结果列：组内所有行B/C/D完全匹配标记为1，存在不匹配则标记为0

方法一：Excel 365/2021 动态数组实现（推荐） #

1. 提取唯一分组值（对应结果列E） #

在E2单元格输入以下公式，公式会自动溢出所有A列的唯一值：

=UNIQUE(A2:A12)

2. 生成匹配标记（对应结果列F） #

在F2单元格输入以下公式，同样会自动溢出对应标记：

=--(ROWS(UNIQUE(FILTER(B:D,A:A=E2)))=1)

公式拆解：

• FILTER(B:D,A:A=E2)：筛选出当前分组对应的所有B、C、D列数据
• UNIQUE(...)：提取该组数据中的唯一行
• ROWS(...)：统计唯一行的数量，若数量为1则说明组内所有行B/C/D完全一致
• --(...)：将逻辑判断结果（TRUE/FALSE）转换为1/0

验证示例：

• 分组"al"对应的唯一行数量为2，标记为0
• 分组"ff"对应的唯一行数量为1，标记为1

方法二：兼容旧版Excel（无动态数组） #

1. 提取唯一分组值（对应结果列E） #

在E2单元格输入以下数组公式（输入完成后按Ctrl+Shift+Enter确认），下拉公式直到出现错误值后删除错误行：

=INDEX(A:A,MIN(IF(COUNTIF(E$1:E1,A$2:A$12)=0,ROW(A$2:A$12),99999)))

2. 生成匹配标记（对应结果列F） #

在F2单元格输入以下数组公式（按Ctrl+Shift+Enter确认），下拉公式：

=--(SUMPRODUCT(--(COUNTIFS(A:A,E2,B:B,B$2:B$12,C:C,C$2:C$12,D:D,D$2:D$12)=COUNTIF(A:A,E2)))=1)

公式拆解：

• COUNTIFS(A:A,E2,B:B,B$2:B$12,C:C,C$2:C$12,D:D,D$2:D$12)：统计当前分组内，每一行B/C/D组合出现的次数
• COUNTIF(A:A,E2)：获取当前分组的总行数
• 若某组合的出现次数等于分组总行数，说明组内所有行完全匹配，最终标记为1；否则标记为0

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