Excel如何按编码分组自动批量计算各编码对应加权平均值

问题场景 #

• 数据表计算规则：条目名称存在差异时，只要编码相同，即归入同组计算加权平均值
• 现有公式=SUMPRODUCT(IF(B2:B6=B2,C2:C6*D2:D6))仅支持单个指定编码的加权值计算，将匹配值从单个单元格B2替换为全列编码范围B2:B6后返回#N/A错误，无法批量输出全量编码结果
• 需求：可复用公式，选中编码列表后自动输出每个编码对应的加权平均值

可用公式 #

公式默认B列为编码列、C列为待计算数值列、D列为权重列，实际使用时替换为自身表格的实际范围即可，无需额外手动分组，会自动匹配相同编码的所有行纳入计算，不受条目名称差异影响。

动态数组版本（Excel 365/2021及以上） #

在结果列首个单元格输入以下公式，回车后自动溢出所有结果，无需手动下拉：

• 若需要按不重复编码输出分组结果：

=SUMPRODUCT((B2:B6=TRANSPOSE(UNIQUE(B2:B6)))*C2:C6*D2:D6)/SUMIF(B2:B6,UNIQUE(B2:B6),D2:D6)

• 若需要和原表行顺序保持一致，逐行输出当前行编码对应的加权平均值：

=SUMPRODUCT((B2:B6=TRANSPOSE(B2:B6))*C2:C6*D2:D6)/SUMIF(B2:B6,B2:B6,D2:D6)

公式逻辑：通过TRANSPOSE将目标编码范围转置为横向数组，和纵向原始数据做矩阵匹配，批量计算每个编码对应的「数值*权重」乘积和，再除以对应编码的权重总和，得到最终加权平均值。

兼容版本（Excel 2019及更早无动态数组支持版本） #

在结果列首个单元格输入以下公式，按下Ctrl+Shift+Enter确认数组公式后，下拉填充到所有结果行即可：

=SUMPRODUCT(($B$2:$B$6=B2)*$C$2:$C$6*$D$2:$D$6)/SUMIF($B$2:$B$6,B2,$D$2:$D$6)

注意：原始数据范围需添加$锁定为绝对引用，避免下拉填充时引用范围偏移。

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