如何按唯一标识符提取饮品类别下数量总和最高的品牌（A1:D11）

注：数据结构说明：A列为唯一标识符，B列为品牌，C列为饮品类别，D列为数量

需求1：显示与唯一标识符匹配的、对应最大价值的品牌 #

若“最大价值”指该标识符下单条数据中数量最大值对应的品牌，可使用数组公式实现（假设目标标识符在F2单元格）：

=INDEX(B:B,MATCH(MAX(IF(A:A=F2,D:D,0)),D:D,0))

注意：Excel 2019及以前版本需按Ctrl+Shift+Enter触发数组公式；2021及以后版本直接回车即可生效

若需按标识符分组后取组内数量总和最高的品牌，参考需求2的解法。

需求2：按每个唯一标识符提取饮品类别中数量总和最高的品牌 #

方法1：Power Query（高效适配大数据量） #

1. 选中数据区域A1:D11，点击「数据」选项卡→「从表格/区域」（勾选“我的表格有标题”）
2. 在Power Query编辑器中操作：
   • 点击「转换」→「分组依据」：分组列选「唯一标识符」+「品牌」，操作选「求和」，列名设为「总数量」，依据列选「数量」
   • 再次点击「分组依据」：分组列选「唯一标识符」，操作选「最大值」，列名设为「最大总数量」，依据列选「总数量」
   • 添加自定义列，公式：=if [总数量] = [最大总数量] then [品牌] else null
   • 筛选自定义列不为空的行，删除冗余列，仅保留「唯一标识符」和「品牌」
   • 点击「关闭并上载」将结果导出到新工作表

方法2：数组公式（适合小数据量快速计算） #

假设唯一标识符的不重复列表在F列（F2开始），在G2单元格输入公式后下拉填充：

=INDEX(B:B,MATCH(MAX(IF(A:A=F2,SUMIFS(D:D,A:A,F2,B:B,B:B),0)),SUMIFS(D:D,A:A,F2,B:B,B:B),0))

触发规则同需求1的数组公式

示例输出结果：

• 9929 = Lemonade
• 5567 = Cranberry

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