一文看懂EXCEL计算加权平均数式和等价数

日常工作中，不仅简单的算术平均数，我们也须要估算加权平均数。诸如：通过包含金额、利率两个数组的表册测算金额的加权平均利率。本篇文章我们就简单介绍EXCEL估算加权平均数。 #

一：哪些是加权平均

假定对于一组数据n条数据：每条数据包含金额和利率两个数组，我们以{a1,a2,a3,…,an}表示n条金额数据；以{r1,r2,r3,…,rn}表示每条数据对应的利率。以金额为权重的加权平均利率应该这么估算： #

r=a1/sum{a1,a2,a3,…,an}*r1

+a2/sum{a1,a2,a3,…,an}*r2

#

+…

+an/sum{a1,a2,a3,…,an}*rn（1） #

假如我们定义金额权重为{w1,w2,w3,…,wn}，其中，wi=ai/sum{a1,a2,a3,…,an}，很其实： #

r=w1*r1+w2*r2+…+wn*rn（2） #

其实，考虑到（1）式中每一项的分母相同，也可以这样叙述： #

r=(a1*r1,a2*r2,…,an*rn)/(a1,a2,…,an)（3）

其实，以上的（1）式、（2）式和（3）式等价。

#

二：基本的Excel估算

#

在中，谷歌加入了()函数，我们主要使用这个函数作为估算加权平均利率的函数。

#

注意上面的（3）式： #

r=(a1*r1,a2*r2,…,an*rn)/(a1,a2,…,an) #

它的分子是两组数据对应序号的元素分别相加，并估算乘积的和，这就是的基本功能。谷歌在该函数的帮助文档中开宗明义：

#

“在给定的几组字段中，将字段间对应的元素相加，并返回乘积之和。” #

这样我们可以很简单地通过公式函数估算加权平均利率。 #

如前面的表格：我们通过两个方法估算金额的加权平均利率： #

方式1：

#

第一步：在D列估算金额的权重=本行B列数值/B列数据的和，比如：D2单元格的数值=500/11706

第二步：在E列估算金额权重*利率=D列数值*C列数值

第三步：求E列数值的和，这样就是按照前面（1）式的定义估算的加权平均利率。

#

方式2：在E19单元格输入公式：=(C2:C17,B2:B17)/sum(B2:B17) #

这样，估算（3）式中的分子；sum函数估算（3）式中的分母。

方式1和技巧2是等价的，结果一致。

#

三：条件加权平均

在第二节中借助函数，我们可以估算加权平均利率。在实际工作中，我们可能须要借助报表估算符合一定条件下的明细条目的加权平均数。此时，我们可以借助几个办法实现。 #

3.1，公式估算法 #

3.1.1辅助列法

#

我们剖析“计算符合某一条件的明细的加权平均数”这一需求：我们要做的是从分子和分母中剔除不符合条件的数据。如何剔除呢？借助逻辑运算：我们加入一列，当本条明细符合条件时值为1；当本条明细不符合条件时值为0，并用这一列数据乘原有数据。因此我们引入一个新的数组：逻辑判定数组={l1,l2,l3,…,ln}来扩充（3）式： #

r=(a1*r1*l1,a2*r2*l2,…,an*rn*ln)/sum(a1*l1,a2*l2,…,an*ln)

这样加权平均法公式，当遇见不符合条件的数据时li=0，分子和分母中都剔除了相应的值；当遇见符合条件的数据时，li=1，该条数据被保留在分子和分母中。诸如：

我们想要估算名称为E的所有条目的加权平均利率，可以这样实现： #

第一步：在E列输入公式，实现这样的判定“本行A列的值=E，结果为1，否则结果为0”。诸如：E2单元格的公式为=if(A2=”E”,1,0)。

第二步：编辑公式=(C2:C26,B2:B26,E2:E26)/(B2:B26,E2:E26) #

这儿我们须要注意：由于分母须要引入逻辑判定，不能再简单地使用sum函数，也须要用求两组数据对应项目的乘积的和。

3.1.公式的扩充 #

里面的方式可以实现需求，但缺点是我们须要加入一个辅助列并插入估算公式（其实，这并不绝对是一个缺点，最后我们再回到这一点）。我们也可以借助的特点，在公式中这样实现：

#

=(B2:B26*(A2:A26="E"),C2:C26)/(B2:B26*(A2:A26="E")) #

注意绿色的部份，B2:B26*(A2:A26=”E”)：B2:B26指金额。(A2:A26=”E”)的实质是对名称数组是否等于E做判定并返回一个链表，它的功能和上一小节我们构建的辅助列是一样的，只是这儿的链表只在内部使用。分母同样须要使用对金额是否满足条件做限制。通过前面的公式，我们就可以求取条件加权平均利率了。 #

这儿须要注意的是：条件估算部份一定要用括弧扩起， #

B2:B26*(A2:A26=”E”)是正确的 #

B2:B26*A2:A26=”E”是不正确的 #

3.2，数据透视表法

上一节我们通过来估算加权平均。它比较容易理解，但有一个缺点：查看多个条件下的加权平均利率必须列举多个公式。当某个数组值好多时，这个过程比较复杂。下边我们通过数据透视表快速估算加权平均数。 #

再看（3）式： #

r=(a1*r1,a2*r2,…,an*rn)/(a1,a2,…,an)

#

如今加权平均法公式，分母（金额）我们早已有数据；须要的是建立分子：金额*利率。实现过程如下：

#

第一步：构造辅助列 #

如上图D列，每一项=本行B列*本行C列的值，这样，分子数据建立完毕

第二部：插入数据透视表

对A1:E26这个区域插入数据透视表。如右图，我们把数据透视表插入到当前工作表中以便对比。

第三步：插入估算公式并设计布局 #

选择数据透视表区域，在底部的“数据透视表剖析”选项卡中依次选择数组、项目和集>>估算数组。（在中依次选择“数据透视表工具”>>选项>>公式>>估算数组） #

在弹出的选项卡中，可以看见涵盖当前透视表中所有数组的数组列表，我们须要的是选定须要的数组进行运算。由于我们要估算的内容是（金额*利率）/金额，我们先选择“辅助列：金额*利率”，并“单击插入数组”；手工输入加法符号“/”；再选择“金额”字段并单击插入数组，最终产生新的估算数组，如右图： #

如今，我们将“名称”字段推入行数组，将新添加的“加权平均利率”字段推入值数组，并设置为“求和”，最终能够获得按名称分类的加权平均利率报表： #

最终，我们可以得到如下的数据透视表： #

对于名称数组中的每一类，我们都能见到加权平均利率，最终的总计项反映了所有数据条目的加权平均利率。 #

在下一文章中，我们将延续本文的内容，扩充条件加权平均利率的估算。

#