万能函数Sumproduct能解决4大类统计问题

提到求和，大家想到的肯定是Sum、Sumif、Sumifs等函数，而计数则想到的肯定是Count、Counta、Countif、Countifs等函数，排名则用Rank函数，但是，在Excel中，有一个函数它既能求和、计数、还会权重计算和排名等功能，它就是Sumproduct函数。

一、Sumproduct函数简介。

功能：返回相应的数组区域乘积的和。

语法：=Sumproduct(数组或单元格引用1，数组或单元格引用2……数组或单元格引用N）。

注意事项：

1、如果SUMPRODUCT函数具有多个参数数组，这些数组之间必需具有相同的维数，否则SUMPRODUCT将返回#VALUE!错误值REF！

2、函数Sumproduct将非数据类型的元素作为0处理。

示例：

方法：

在目标单元格中输入公式：=SUMPRODUCT(C3:C9,D3:D9)。

解读：

公式=SUMPRODUCT(C3:C9,D3:D9)可以理解为：C3_D3+C4_D4+……C9*D9，暨对应的同一行单元格乘积的和。

二、Sumproduct：求和。

1、单条件求和。

方法：

在目标单元格中输入公式：=SUMPRODUCT((E3:E9=H3)_C3:C9)、=SUMPRODUCT((E3:E9=H3)_C3:C9*D3:D9)。

解读：

1、首先判断E3:E9=H3条件是否成立，如果成立，则返回True，暨1，否则返回False,，暨0。

2、以“上海”地区的为例：公式=SUMPRODUCT((E3:E9=H3)_C3:C9)的计算过程就是：1_66+0_88+0_67+0_56+0_33+1_57+0_20=123。公式：=SUMPRODUCT((E3:E9=H3)_C3:C9_D3:D9)的计算过程就是：1_66_39+0_88_58+0_67_61+0_56_53+0_33_42+1_57_84+0_20_82=7362。

2、多条件求和。

方法：

在目标单元格中输入公式：=SUMPRODUCT((E3:E9=H3)(C3:C9>=50),C3:C9)、=SUMPRODUCT((E3:E9=H3)(C3:C9>=50),C3:C9*D3:D9)。

解读：

1、其实计算过程和单条件的类似，只是多了一个条件判断而已，具体请参阅单条件的计算过程。

2、为了是公式更具有条理性，我们可以将条件用（乘号）连接在一起，将数值用（乘号）连接在一起，条件和数值之间用，（逗号）分隔，但，（逗号）的作用还是乘。

3、连接符，（逗号）和（乘号）的区别在于：如果数据源中包含文本，必须用，（逗号）连接，而不能用（乘号）连接。故*（乘号）只能用于连接数据类型的值。

3、隔列求和。

方法：

在目标单元格中输入公式：=SUMPRODUCT(($C$3:$J$3=K$3)*$C4:$J4)。

解读：

1、隔列求和也很好理解，需要注意的就是相对引用和绝对引用的使用，如条件区域C3:J3不变，随意采用绝对引用，而条件值的列会发生变化，随意采用相对和绝对引用相结合的方式。

2、数据区域也是采用绝对和相对引用相结合的方式。

三、Sumproduct：计数。

1、单条件计数。

方法：

在目标单元格中输入公式：=SUMPRODUCT(N(E3:E9=H3))。

解读：

1、公式中用到了Excel中最短函数N，其主要作用为将非数值的值转换为数值。

2、首先判断E3:E9=H3是否成立，如果成立，返回True，否则返回False，然后用N函数将对应的值转换为1和0。

3、当Sumproduct函数只有一个数据区域时，对区域中的值进行求和处理。以“上海”为例：判断和转换后的结果为{1;0;0;0;0;1;0},求和的结果为2。达到了计数的目的。

2、多条件计数。

方法：

在目标单元格中输入公式：=SUMPRODUCT(N(E3:E9=H3)*(C3:C9>=50))。

解读：

1、以“上海”为例，公式可以理解为：=Sumproduct（{1;0;0;0;0;1;0},{1;0;0;0;0;1;0}），所以返回的结果为2。

四、Sumproduct：多权重计算。

方法：

在目标单元格中输入公式：=SUMPRODUCT(C$3:E$3,C4:E4)。

解读：

1、从表中我们可以看出要计算考生的最终成绩，其中体能占20%，笔试占30%，面试占50%。

2、各项所占的比是固定不变的，所以行绝对引用，不可以随着单元格的变动而变动，而每个人的成绩要随着人员的变化而变化，所以才用相对引用的形式。

3、如果增加“名次”列，则可以快速的对考生的成绩进行排序。排序请继续阅读。

五、Sumproduct：中国式排名。

方法：

在目标单元格中输入公式：=SUMPRODUCT(($F$4:$F$10>F7)/COUNTIF($F$4:$F$10,$F$4:$F$10))+1。

解读：

1、首先判断$F$4:$F$10>F7条件是否成立，如果成立，则返回True，暨1，否则返回False，暨0。

2、=COUNTIF($F$4:$F$10,$F$4:$F$10)的作用就是形成一个7个元素为1的数组。

3、第一步和第二步对应的数组元素进行除法运算，形成一个新的数组，而Sumproduct函数当数组区域为单一区域时，对区域中的值进行求和运算。

4、公式中的1为附加值。

5、以93.8分的名次为例，公式=SUMPRODUCT(($F$4:$F$10>F7)/COUNTIF($F$4:$F$10,$F$4:$F$10))就转化为=Sumproduct（{0;0;0;1;0;0;0}/{1;1;1;1;1;1;1}）,然后对应位置的数组相除得到新的数组=Sumproduct({0;0;0;1;0;0;0})，对其进行求和：0+0+0+1+0+0+0=1，再加上附加值1，最终结果为2。

结束语：

本文主要学习了万能函数Sumproduct，其不仅能够求和、计数、还能够计算权重和排名，功能非常的强大，这难倒是要抢“饭碗”的节奏？……