统计利器SUMPRODUCT函数—复杂条件计数与求和

统计利器SUMPRODUCT函数—复杂条件计数与求和

Excel中的SUMPRODUCT函数功能十分强大，被誉为万能统计器，凡涉及按条件计数、求和的统计工作都可以交给他去完成，条件再复杂也难不倒他。现在我们就来结识一下这位“大能勇士”吧！他很愿意和您交朋友！

这里有一张人事信息表：

需要根据人事信息表生成一张学历统计报表（人事部门经常做类似的工作）。

下面我们就以此为例来学习一下SUMPRODUCT函数的使用方法。

先来个最简单的——单条件计数：

=SUMPRODUCT((人事信息!G$2:G$21=A4)*1)

功能：得到人事信息表的G2：G21中与A4单元格内容匹配的数量。

公式编辑好下拉到B9单元格。

公式里为什么要乘1呢——*1？我们来分析一下函数执行过程就明白了：人事信息表从G2到G21共计20个单元格依次与本表A4单元格比较判断是否相等，从而得到20个逻辑值（true or false），最后对20个逻辑值求和，可是逻辑值不能进行求和运算，*1 的作用就是将逻辑值转换为数值：true-->1，false-->0。

性别的统计需要两个条件——多条件计数——“与”关系的表达

=SUMPRODUCT((人事信息!$G$2:$G$21=$A4)*(人事信息!$B$2:$B$21=C$3))

第一个条件： (人事信息!$G$2:$G$21=$A4) 判断学历

第二个条件： (人事信息!$B$2:$B$21=C$3) 判断性别

功能：人事信息表的G2：G21与A4单元格内容匹配，同时，人事信息表的B2：B21与C3单元格内容也匹配，得到两个条件同时满足的人数（交集）。

公式编辑好下拉到C9单元格在向右拉到D9单元格。

公式里单元格行标、列标前面为什么要加$呢？为什么有的加有的不加呢？这跟公式下拉或右拉复制有关系，加$是为了把行或列固定住，在拉动的时候不发生变化。

比如 $A4 ：下拉一个单元格变成 $A5，再下拉一个单元格变成 $A6，依此类推，但向右拉的时候保持A列不变。

C$3： 下拉的时候保持行号（3）不变，向右拉一个单元格会变成D$3。

记住这个口诀就没问题了：

下拉时需要固定行的在行号前加$，右拉时需要固定列的在列号前加$。

年龄结构的统计——多条件计数

E4单元格输入公式：

=SUMPRODUCT((人事信息!$G$2:$G$21=$A4)*(人事信息!$E$2:$E$21<25))

F4单元格输入公式：

=SUMPRODUCT((人事信息!$G$2:$G$21=$A4)*(人事信息!$E$2:$E$21>=25)*(人事信息!$E$2:$E$21<45))

G4单元格输入公式：

=SUMPRODUCT((人事信息!$G$2:$G$21=$A4)*(人事信息!$E$2:$E$21>=45))

选中E4、F4、G4三个单元格统一下拉。

公式的含义就不多解释了，有了前面的认识很容易理解。

计算平均工资——条件求和

H4单元格输入公式下拉：

=SUMPRODUCT((人事信息!$G$2:$G$21=$A4)*(人事信息!$H$2:$H$21))/SUMPRODUCT((人事信息!G$2:G$21=A4)*1)

公式里有两个SUMPRODUCT（），第一个求工资和，第二个统计人数，用工资和除以人数得到平均工资。

我们重点看一下第一个SUMPRODUCT（）：

=SUMPRODUCT((人事信息!$G$2:$G$21=$A4)*(人事信息!$H$2:$H$21))

条件： (人事信息!$G$2:$G$21=$A4) 判断学历（条件可以增加）

求和区域：(人事信息!$H$2:$H$21) 满足条件的单元格求和

依据人事信息表制作的学历统计报表做好了。

“或”关系的表达：

前面示例中出现的多条件都是“与”的关系，有时候也会用到“或”的关系，比如要统计博士和硕士学历的员工月工资总额，就要用到“或”的关系：

=SUMPRODUCT(((人事信息!G2:G21=A4)+(人事信息!G2:G21=A5))*(人事信息!H2:H21))

第一个条件： (人事信息!G2:G21=A4) 判断学历为博士

第一个条件：(人事信息!G2:G21=A5) 判断学历为硕士

功能：满足每个条件的记录并在一起视为最终满足条件的记录（并集），将其工资求和。

“与”、“或”复合运用：

曾经协助公安部门调查案件，要求查一下我们公司有没有符合这样条件的人：姓李或姓林、女、40到50岁、湖北或湖南人、大专或中专学历。

先用接近公式表达方式的语言描述一下：

=SUMPRODUCT(((姓李)+(姓林))*(女)*(40以上)*(50以下)*((湖北人)+(湖南人)))

为了公式易读，我们直接在人事信息表中设置公式：

=SUMPRODUCT(((LEFT(A2:A21,1)="李")+(LEFT(A2:A21,1)="林"))*(B2:B21="女")*(E2:E21>=40)*(E2:E21<=50)*((C2:C21="湖北")+(C2:C21="湖南")))

注意事项：

公式中所引用的单元格区域大小必须一致，不支持整列引用（例如A：A）。