如何在Excel中判断分组列数据是否同质并生成颜色汇总

原始数据示例 #

目标汇总结果 #

实现方法 #

方法1：公式直接计算（适用于全版本Excel） #

1. 先提取唯一的GroupID列表：
   • 复制GroupID列到空白区域，使用「数据」→「删除重复值」保留唯一值；
   • 若使用Excel 365/2021及以上版本，可直接用=UNIQUE(B:B)生成唯一列表。
2. 在OverallColor列对应单元格输入公式（假设唯一GroupID在F列，F2为第一个值）：

   =IF(COUNTIFS($B:$B,F2,$C:$C,"<>"&INDEX($C:$C,MATCH(F2,$B:$B,0)))=0,INDEX($C:$C,MATCH(F2,$B:$B,0)),"Mixed")
   

   下拉填充公式即可完成所有分组的判断。

方法2：数据透视表+辅助列（可视化操作友好） #

1. 添加辅助列（例如D列），在D2单元格输入公式并下拉：

   =IF(COUNTIFS($B:$B,B2,$C:$C,C2)=COUNTIF($B:$B,B2),C2,"Mixed")
   

   该公式会标记当前行所在分组的颜色是否完全一致。
2. 插入数据透视表：
   • 将GroupID拖至「行」区域，辅助列拖至「值」区域；
   • 修改值字段设置为「最大值」——同一分组中若存在"Mixed"，最大值会保留该值；若全为同色，则返回对应颜色。

方法3：Power Query（适合数千行大数据批量处理） #

1. 选中数据区域，点击「数据」→「从表格/区域」，导入Power Query编辑器。
2. 按GroupID分组：
   • 点击「转换」→「分组依据」；
   • 分组依据选择GroupID，新列名设为OverallColor，操作选择「自定义」，输入自定义公式：

     if List.Count(List.Distinct([Color]))=1 then List.Distinct([Color]){0} else "Mixed"
     

3. 点击「关闭并上载」，即可直接得到汇总结果表。

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