Excel函数sumproduct详解.xls

SUMPRODUCT这是一个与SUM函数非常相似的函数。大多数情况这两个函数是可以互换使用的。SUMPRODUCT是在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。语法SUMPRODUCT(array1,array2,array3,...)Array1,array2,array3,...为2至30个数组,其相应元素需要进行相乘并求和。数组参数必须具有相同的维数,否则,函数SUMPRODUCT将返回错误值#VALUE!。函数SUMPRODUCT将非数值型的数组元素作为0处理。对参数有几点说明:1、帮助中说2-30个数组,当然也可以是一个数组,如果是一个数组,那么就是对这个数组的求和。(类似SUM)2、参数可以是直接引用的二维数组以及内存数组;不可以直接引用三维数组(sum可以)以及indirect,offset产生的三维引用。3、参数中如含有错误值,则返回对应的错误值。下面我就举几个例子对上面的观点进一步说明。例1:AB39=SUMPRODUCT(I20:I24,J20:J24)AA12说明:最基础的返回两个引用数组乘积的和BB23例1-3148=SUMPRODUCT(I20:I24,K20:K24,L20:L24)DD42说明:参数中非数值型的数组元素都作为0处理了,包括TUREEE54例1-2:#VALUE!=SUMPRODUCT(I20:I24,J20:J23)说明:参数维数不一致所以返回错误值#VALUE!。例1-3:#N/A=SUMPRODUCT(I20:I24,M20:M24)说明:参数中含有错误值#N/A,所以返回#N/A。例2:39=SUMPRODUCT(data1,data2)data1=OFFSET(sumproduct!$I$20,,,5)说明:返回两个内存数组的乘积的和data2=OFFSET(data1,,1,)例3:11=SUMPRODUCT((I20:I23I22:I24),(J20:J23J22:J24))说明:支持区域交叉,注意中间的空格以及参数两边的括号不可以省略。例4:#VALUE!=SUMPRODUCT((I20:I21,I23:I24),(J20:J21,J23:J24))说明:不支持联合区域,可以通过增加参数,或者分开相加的方式实现。例5:0=SUMPRODUCT(INDIRECT("r"&ROW(20:24)&"c9",),INDIRECT("r"&ROW(20:24)&"c10",))0=SUMPRODUCT(OFFSET(H19,1,COLUMN(A:A),ROW(5:5)),OFFSET(H19,1,COLUMN(B:B),ROW(5:5)))0=SUMPRODUCT(INDIRECT(ROW(1:2)&"!B2"),INDIRECT(ROW(1:2)&"!c2"))说明:不支持indirect产生的三维的引用,解决方法在后面说明。例6:8=SUMPRODUCT((I20:I24<3)*(I20:I24)*J20:J24)说明:支持公式产生的内存数组,这个例子也是目前大家最喜欢用的一种方式。了解了SUMPRODUCT的这些特点,现在我们来看看它的一些应用。一、把其作为一种查找函数来用。=SUMPRODUCT((H20:H24="CC")*I20:I24)=SUMPRODUCT((H20:H24="cc")*(I19:L19="b"),I20:L24)上面这两个公式中间我一个用了乘号,一个用了逗号。可能有些朋友要说了,这里的乘号和逗号都可以用,可以互换。这里是不可以的,不信你换换看(保持公式其他不变的前提下)。那是为什么呢?那么我们就对公式进行分解。=SUMPRODUCT((H20:H24="CC")*I20:I24)的“*”前后两段分别选中按F9分解。那么公式就变成:=SUMPRODUCT({FALSE;FALSE;TRUE;FALSE;FALSE}*{1;2;3;4;5})我想大家都知道TRUE,FALSE在进行加,减,乘,除,乘方等算术计算时TRUE相当于1,FALSE相当于0那么上面的公式就相当于=SUMPRODUCT({0;0;1;0;0}*{1;2;3;4;5})即=SUMPRODUCT({0;0;3;0;0})前面也讲过了就有一个参数的时候相当于求和。那么结果就是我们想要的“3”那么这里的“*”为什么不能换成“,”呢。我们看语法的第三条。如果用了逗号那么第一个参数就是由TRUE,FALSE组成的数组,非数值型。那么函数就将其全转换成0,那么结果肯定就是0。b例子我就不重复罗嗦了,道理和上面一样。这里为什么不能用“*”我想大家也应该明白了吧。(我抽口烟先*_*)这里我再唠叨一点就是各个参数的维数一定要相同,这也是初学者容易忽