Excel中Sumproduct函数的强大你是否知道？

太平盛世在等你 2016-08-03

展开全文

一、

Sumproduct函数是Excel中的几大神器之一，它兼具条件求和及计数两大功能。用它可以达到事半功倍的效果。Sumproduct(array1,array2,array3,...)即在给定的几组数组（array）中，将数组间相对应的元素相乘，并返回乘积之和。

Sumproduct函数有几个特点：

1.它支持数组间运算。

2.它会自动将非数值型的数组元素作为0处理。

3.数组参数必须有相同的高度，否则返回错误值。

二、

小编通过实例来演示下，小伙伴们对该函数的含义就更清晰了。产品、单价、销售数量要求出一月份的销售总额。 Excel中Sumproduct函数的强大你是否知道？

三、

这就是Sumproduct函数的一个简单的运用。它的运算过程是：B3：B6和C3：C6两个区域数组间的元素对应相乘再求和。展示开来就是：B3*C3+B4*C4....+B6*C6。

Excel中Sumproduct函数的强大你是否知道？

四、

上面介绍的是Sumproduct函数的简单应用，接下来小编用另一个实例来演示它的强大。这组数据中，求出男、女各有多少高级工程师和中级工程师。公式在数据统计区域中的F2单元格编辑如下公式：=SUMPRODUCT(($B$2:$B$21=$E2)*($C$2:$C$21=F$1))相同的原理，向下复制到F3（公式中$E2改成$E3），向右复制到G2（公式中$F1改成$G1）,G3（公式中$E2改成$E3及$F1改成$G1）。

Excel中Sumproduct函数的强大你是否知道？

五、公式分解及分析

1、$B$2:$B$21=$E2在计算过程中，条件1是一个数组，返回多值，写成公式如下：=$B$2:$B$21=$E2具体操作：选中20个连续的单元格，输入上述公式后，按Ctrl+Shift+回车键确认，

返回10个逻辑判断值——true；true；false；false；true；false；true；false；false；true；false；true；false；true；true；true；false；true；false；true；。这20个逻辑判断值构成一个新的由true主false组成的数组1。

2、$C$2:$C$21=F$1与条件1相同，是一个数组，返回多值，写成公式如下：=$C$2:$C$21=F$1具体操作：同样的方法，选中对应的20连续单元格，输入上述公式，按Ctrl+Shift+回车键确认，返回10个逻辑判断值——true；false；true；false；true；true；false；false；true；false；false；true；false；false；true；false；false；true；false；true。这20个逻辑判断值构成另一个新的由true主false组成的数组2。

3、($B$2:$B$21=$E2)*($C$2:$C$21=F$1)由新构成的数组1乘以数组2，即：

=($B$2:$B$21=$E2)*($C$2:$C$21=F$1)

={数组1*数组2}

={ true；true；false；false；true；false；true；false；false；true；false；true；false；true；true；true；false；true；false；true }*{ true；false；true；false；true；true；false；false；true；false；false；true；false；false；true；false；false；true；false；true }

={true*true;true*false;false*true;false*false;true*true;false*true;true*false;false*false;false*true;true*false;false*false;true*true;false*false;true*false;true*true;true*false;false*false;true*true;false*false;true*true;}

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

其中,逻辑值TRU*与**LSE参与计算时:

true=1，false=0，true*true=1，true*false=false*true=0，false*false=0

因此{数组1*数组2}={1;0;0;0;1;0;0;0;0;0;0;1;0;0;1;0;0;1;0;1}由1和0构成了一个新的数组3。

4、=Sumproduct(($B$2:$B$21=$E2)*($C$2:$C$21=F$1))

函数Sumproduct对新的数组3中的所有数据求和。即：

=Sumproduct(($B$2:$B$21=$E2)*($C$2:$C$21=F$1))=Sumproduct({数组1*数组2})

=Sumproduct({数组3})

=Sumproduct({1;0;0;0;1;0;0;0;0;0;0;1;0;0;1;0;0;1;0;1})

六、

这里需要说明的是，公式编辑按照函数Sumproduct的一般格式，可以编辑如下等效的公式：

=Sumproduct(($B$2:$B$21=$E2)*1，($C$2:$C$21=F$1)*1)

函数Sumproduct的作用是对数组($B$2:$B$21=$E2)与数组($C$2:$C$21=F$1))计算其乘积的和，即：

=Sumproduct(($B$2:$B$21=$E2)*1，($C$2:$C$21=F$1)*1)

=Sumproduct({ true；true；false；false；true；false；true；false；false；true；false；true；false；true；true；true；false；true；false；true }*1，{ true；false；true；false；true；true；false；false；true；false；false；true；false；false；true；false；false；true；false；true}*1)

=Sumproduct({1;1;0;0;1;0;1;0;0;1;0;1;0;1;1;1;0;1;0;1},{1;0;1;0;1;1;0;0;1;0;0;1;0;0;1;0;0;1;0;1})

=Sumproduct({1*1,1*0,0*1,0*0,1*1,0*1,1*0,0*0,0*1,1*0,0*0,1*1,0*0,1*0,1*1,1*0,0*0,1*1,0*0,1*1})

=Sumproduct({1;0;0;0;1;0;0;0;0;0;0;1;0;0;1;0;0;1;0;1})

注意：true*1=1，false*1=1*false=0，true*0=0*true=0 。数组中用分号分隔，表示数组是一列数组，分号相当于换行。两个数组相乘是同一行的对应两个数相乘。
Excel中Sumproduct函数的强大你是否知道？