一、
Sumproduct函数是Excel中的几大神器之一,它兼具条件求和及计数两大功能。用它可以达到事半功倍的效果。Sumproduct(array1,array2,array3,...)即在给定的几组数组(array)中,将数组间相对应的元素相乘,并返回乘积之和。
Sumproduct函数有几个特点:
1.它支持数组间运算。
2.它会自动将非数值型的数组元素作为0处理。
3.数组参数必须有相同的高度,否则返回错误值。
二、
小编通过实例来演示下,小伙伴们对该函数的含义就更清晰了。产品、单价、销售数量要求出一月份的销售总额。
三、
这就是Sumproduct函数的一个简单的运用。它的运算过程是:B3:B6和C3:C6两个区域数组间的元素对应相乘再求和。展示开来就是:B3*C3+B4*C4....+B6*C6。
上面介绍的是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)。
五、公式分解及分析
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})
=6
六、
这里需要说明的是,公式编辑按照函数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})
=6
注意:true*1=1,false*1=1*false=0,true*0=0*true=0 。数组中用分号分隔,表示数组是一列数组,分号相当于换行。两个数组相乘是同一行的对应两个数相乘。
联系客服