1、SUMPRODUCTSUMPRODUCT 2007-05-20 09:34:36 这是一个与SUM函数非常相似的函数。大多数情况这两个函数是可以互换使用 的。 SUMPRODUCT是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。 语语法法 SUMPRODUCTSUMPRODUCT(array1array1,array2array2,array3, .) Array1, array2, array3, . 为 2 至 30 个数组，其相应元素需要进行相乘并求和。 数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。 函数 SUMPRODUCT
2、 将非数值型的数组元素作为 0 处理。 对参数有几点说明： 1、帮助中说230个数组，当然也可以是一个数组，如果是一个数组，那么就是对这个数组的求和。（类似SUM） 2、参数可以是直接引用的二维数组以及内存数组；不可以直接引用三维数组（sum可以）以及indirect，offset产生的三维引用。 3、参数中如含有错误值，则返回对应的错误值。 下面我就举几个例子对上面的观点进一步说明。 例1：AB 39 =SUMPRODUCT(I20:I24,J20:J24)AA12 说明：最基础的返回两个引用数组乘积的和BB23 例1-1：CC31 48 =SUMPRODUCT(I20:I24,K20:K2
3、4,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 =SUMPRO
4、DUCT(I20:I23 I22:I24),(J20:J23 J22:J24) 说明：支持区域交叉，注意中间的空格以及参数两边的括号不可以省略。 例4： #VALUE! =SUMPRODUCT(I20:I21,I23:I24),(J20:J21,J23:J24) 说明：不支持联合区域，可以通过增加参数，或者分开相加的方式实现。 例5： #VALUE! =SUMPRODUCT(INDIRECT(“r“FALSE;TRUE;FALSE;FALSE*1;2;3;4;5) 我想大家都知道TRUE,FALSE在进行加，减，乘，除，乘方等算术计算时TRUE相当于1，FALSE相当于0 那么上面的公式就相当
5、于SUMPRODUCT(0;0;1;0;0*1;2;3;4;5)即SUMPRODUCT(0;0;3;0;0) 前面也讲过了就有一个参数的时候相当于求和。那么结果就是我们想要的“3” 那么这里的“*”为什么不能换成“，”呢。我们看语法的第三条。如果用了逗号那么第一个参数就是由TRUE,FALSE 组成的数组，非数值型。那么函数就将其全转换成0，那么结果肯定就是0。 b例子我就不重复罗嗦了，道理和上面一样。这里为什么不能用“*”我想大家也应该明白了吧。（我抽口烟先*_*） 这里我再唠叨一点就是各个参数的维数一定要相同，这也是初学者容易忽视的。 二、对满足条件的数进行求和 例如对非“AA”“BB”“
6、B”下面的数进行求和 7 =SUMPRODUCT(H20:H24“aa“)*(H20:H24“bb“)*(I19:L19=“b“),I20:L24) 道理同上不再多讲，再次强调参数间的维数一定要相同。 三、参数如何使用由indirect，或offset产成的三维的引用。 上面的例5的用法，我们如何能正确使用呢？我们在使用indirect或offset的时候往往会用row或column对区域的引用。 而row和column产生的数组，这样它又对你的引用增加了一维，造成SUMPRODUCT函数不能进行使用。 解决办法降低其维数，达到我们引用的目的如下： 39 =SUMPRODUCT(N(INDIR
7、ECT(“r“0;1;0;0*1;2;3;4;5)即SUMPRODUCT(0;0;3;0;0) 那么这里的“*”为什么不能换成“，”呢。我们看语法的第三条。如果用了逗号那么第一个参数就是由TRUE,FALSE b例子我就不重复罗嗦了，道理和上面一样。这里为什么不能用“*”我想大家也应该明白了吧。（我抽口烟先*_*） 上面的例5的用法，我们如何能正确使用呢？我们在使用indirect或offset的时候往往会用row或column对区域的引用。 而row和column产生的数组，这样它又对你的引用增加了一维，造成SUMPRODUCT函数不能进行使用。 =SUMPRODUCT(N(INDIRECT
8、(“r“&ROW(20:24)&“c9“,),N(INDIRECT(“r“&ROW(20:24)&“c10“,) =SUMPRODUCT(OFFSET(H19,1,MIN(COLUMN(A:A),MAX(ROW(5:5),OFFSET(H19,1,SMALL(COLUMN(B:B),1),LARGE(ROW(5:5),1) 我们通过N,MIN,MAX,SMALL,LARGE等这些函数返回结果为数值的特点，就可以把这些数组转换成数值。从而降低维数。 当然公式不需要像上面那样负责，之所以用那么多函数是要说明这些函数都可以达到我们的目的。 上面我们通过一些办法达到了三维的引用，但是总是觉得意义不是很
9、大。不能充分实现我们实际应用中的需求。 例如我们想用sumproduct函数进行多表引用时，往往想利用每个表的两列以上。这样我们用indirect函数引用就会超过三维。 =SUMPRODUCT(-(INDIRECT(ROW(1:12)&“!a1:a5“)=“b“),INDIRECT(ROW(1:12)&“!b1:b5“) =SUMPRODUCT(-(T(INDIRECT(ROW(1:12)&“!a1:a5“)=“b“),N(INDIRECT(ROW(1:12)&“!b1:b5“) 也不正确！我们通过分段F9大法发现公式只是取了第一行数。也就是跟indirect参数的维数有关了。（这里更想是介绍
10、indirect的用法了*_*） 再设想一下我们想要的效果，我们想要得到的更像是两个矩阵的乘积的和。（有点眉目了，容我再来一支烟*_*） 那么我们更象把各表A列拼在一起形成数组1，B列拼在一起形成数组2。有了这两个数组离我们的结果也就不远了。 =SUMPRODUCT(-(T(INDIRECT(COLUMN(A:L)*ROW(1:5)/ROW(1:5)&“!r“&ROW(1:5)*COLUMN(A:L)/COLUMN(A:L)&“c1“,)=“b“),N(INDIRECT(COLUMN(A:L)*ROW(1:5)/ROW(1:5)&“!r“&ROW(1:5)*COLUMN(A:L)/COLUMN
11、(A:L)&“c2“,) 上面公式用了row与column进行了区域的扩充。注意column和row的位置，因为它们分别产生横向数组和纵向数组。 以上是鄙人对SUMPRODUCT函数一点看法，希望对您学习此函数能够有一定帮助，如有不妥之处还望指正。 Office精英俱乐部Knifefox 2006/9/20 1:07 =SUMPRODUCT(-(T(INDIRECT(COLUMN(A:L)*ROW(1:5)/ROW(1:5)&“!r“&ROW(1:5)*COLUMN(A:L)/COLUMN(A:L)&“c1“,)=“b“),N(INDIRECT(COLUMN(A:L)*ROW(1:5)/ROW
12、(1:5)&“!r“&ROW(1:5)*COLUMN(A:L)/COLUMN(A:L)&“c2“,) 品种数量单价 a205 b143 c136 d127 品种数量单价 a105 b123 c136 b117 品种数量单价 a205 b143 c136 d127 品种数量单价 a105 b123 c136 b117 品种数量单价 a205 b143 c136 d127 品种数量单价 a105 b123 c136 b117 品种数量单价 a205 b143 c136 d127 品种数量单价 a105 b123 c136 b117 品种数量单价 a205 b143 c136 d127 品种数量单价 a105 b123 c136 b117 品种数量单价 a205 b143 c136 d127 品种数量单价 a105 b123 c136 b117
