

计算加权平均单价，一个SUMPRODUCT函数就可以了

Excel数据分析之道

2022-10-27

一个学生问我，如何从左侧的表，制作右侧的表？

这是两个条件下的加权平均值计算问题：一个条件是工厂，一个条件是供应商，有人说，直接使用SUMIFS函数计算出采购总额，计算出采购数量，将两个数相除不就行了吗？先生，想得太简单了，这个计算出来的不是加权平均，是算术平均。

为了计算指定工厂、指定供应商的加权平均，就需要将该工厂、该供应商的几次采购金额合计及采购数量进行计算，然后才能将两个数值相除，得出加权平均值来，这里就有了两个条件判断问题：将该工厂和该供应商的数据取出来，才能相乘相加再相除。

在不添加辅助列（采购金额）的情况下，我们可以使用条件表达式来处理，然后再使用SUMPRODUCT函数进行计算，计算公式如下：

=SUMPRODUCT(($A$2:$A$20=H3)*($D$2:$D$20=I3),$B$2:$B$20,$C$2:$C$20)

/SUMPRODUCT(($A$2:$A$20=H3)*($D$2:$D$20=I3),$C$2:$C$20)

公式中，下面的表达式就是判断指定工厂、指定供应商的：

($A$2:$A$20=H3)*($D$2:$D$20=I3)

这个表达式的结果是由1和0组成的数组：

{1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

$B$2:$B$20是获取采购单价的，是单价数组：

{2.585;2.62;2.81;2.81;2.523;2.585;2.45;2.45;2.45;2.45;2.45;2.113;2.295;2.415;2.465;2.446;2.48;2.58;2.43}

$C$2:$C$20是获取采购数量，是数量数组：

{120;0.28;32.37;33.2;33.2;30;30.637;30.6;20.677;10;30.657;1.67763;1.85954;59.76;29.88;89.64;29.88;96;64}

那么，将这三个数组的各个数字相乘相加，就是指定工厂、指定供应商的采购总金额：

SUMPRODUCT(($A$2:$A$20=H3)*($D$2:$D$20=I3),$B$2:$B$20,$C$2:$C$20)

将第一个数组与第三个数组组的各个数字相乘相加，就是指定工厂、指定供应商的采购总数量：

SUMPRODUCT(($A$2:$A$20=H3)*($D$2:$D$20=I3),$C$2:$C$20)

最后就是将这两个SUMPRODUCT函数的结果相除，就是我们需要的加权平均单价了。

----------------------

本文不难，逻辑也很简单，但要了解条件表达式的基本概念和规则，构建满足条件数组的基本技能，以及SUMPRODUCT函数的基本用法。

仔细阅读本文，你会有不一样的收获。

---------------------------------

近期文章：

【声明】内容源于网络

Excel数据分析之道

全面介绍Excel在企业数据分析中的各种应用，实际案例模板分享，经验心得，帮助个人和企业提升Excel应用能力。

内容 1106

粉丝 0

Excel数据分析之道全面介绍Excel在企业数据分析中的各种应用，实际案例模板分享，经验心得，帮助个人和企业提升Excel应用能力。

总阅读54

粉丝0

内容1.1k