[原创]关于用SUMPRODUCT取代SUM数组公式的想法

apolloh · 发表于 2005-5-12 15:11

以下是引用gdliyy在2005-5-12 13:17:00的发言： >
呵呵~~~谢谢指正，原来是想表达这个行列数的意思的，只是当时表达错误！
但对于第1个提法我还是有不同的意见： 1、如我之前讨论的问题中的例子：=Sum(if(A1:C10="A",B1:D10)*E1:E10)，在这个例子中，显然B1:D10中可能含有文本，所以不能直接相乘，必须用IF加以判断，在这样的核心表达式不修改的情况下，不管你是用Sum还是用Sumproduct来外套，都必须用三键来结束的；但是当我们合理地运用一下Sumproduct的优势，公式稍修改也可回车结束的。 2、但对于这样的一些公式，当IF条件用来屏蔽错误值的时候，那你用Sumproduct()来单键结束来代替也是无效的，必须使用数组公式三键结束。

我没找到你说的例子，请你先看一例，再请你看看你的帖子中的例子是否也能用相同的解法

xkMsoruH.rar (2.56 KB, 下载次数: 122)

{=SUM(IF($B$4:$F$6=A17,$C$4:$G$6)*$B$10:$B$12)}

V.s

=SUMPRODUCT(($B$4:$F$6=A17)*$B$10:$B$12,$C$4:$G$6)

gdliyy · 发表于 2005-5-12 23:26

以下是引用apolloh在2005-5-12 15:11:00的发言： >
我没找到你说的例子，请你先看一例，再请你看看你的帖子中的例子是否也能用相同的解法

其实你的这个例子与我第1条说的是一样的，这个例子当然可以的！

另外第2点可能表述得还不准确！“2、但对于这样的一些公式，当IF条件用来屏蔽错误值的时候，那你用Sumproduct()来单键结束来代替也是无效的，必须使用数组公式三键结束。” 这样说更准确一些：对于有些公式，当IF函数是用来屏蔽错误值时，如果你试图简化IF段，用Sumproduct()来代替Sum()可能是徒劳的，因为Sumproduct()只能忽略非数值普通数据，但是不能忽略错误值的，因此你同样还得使用IF函数，最终必须使用数组公式三键结束编辑。

看看下面这个例子呢？可能这个例子举得不太恰当，但仅此表达我的意思，黄兄应该理解的，不好意思了！：）

ZCGxHznA.zip (2.69 KB, 下载次数: 117)

chenjun · 发表于 2005-5-13 00:00

=SUMPRODUCT(IF($B$4:$F$6=A17,$C$4:$G$6)*$B$10:$B$12) 这样的公式必须用数组公式方式输入是因为IF的条件引用了多个单元格，如果不用数组公式方式输入excel就自动以“隐含的交叉引用”来代替多个单元格引用$B$4:$F$6，而事实上公式所在格的行是不与这个区域相交的（因为区域是多行列的，在同一工作表中根本不可能交叉），那样IF的条件就是错误，IF返回的结果也是错误，最后公式的结果也是错误！如果以数组公式的方式输入，excel必定不用隐含交叉的，那样就是一个正常的数组运算。

[此贴子已经被作者于2005-5-13 0:03:12编辑过]

gdliyy · 发表于 2005-5-13 00:45

呵呵~~~Chenjun兄所表述的，应该与我和黄兄所要讨论的不同吧！不过这个也是初学者不容易理解的一些常识性知识。

黄兄的见解是：所有的Sum(If(X1,X2)*X3)公式都可以用Sumproduct(X1*X3,X2)来代替，只要它们的数组尺寸相同。

apolloh · 发表于 2005-5-13 07:52

这个例子是特殊，不过确实说明了你的观点。

不过你说的情况我可以再给一个不用数组公式三键结束的例子，但有无此必要就应该考虑了。：）

=SUMPRODUCT(($B$4:$F$6=A17)*$B$10:$B$12,RIGHT(N(OFFSET($C$4,ROW($C$4:$G$6)-ROW($C$4),COLUMN($C$4:$G$6)-COLUMN($C$4))))*1)

w3RkCTV0.rar (4.83 KB, 下载次数: 126)

另老大，你说的情况在=SUMPRODUCT(($B$4:$F$6=A17)*$B$10:$B$12,$C$4:$G$6) 中应该不会发生的，因为没有用IF。

[此贴子已经被作者于2005-5-13 8:40:10编辑过]

chenjun · 发表于 2005-5-13 10:20

以下是引用gdliyy在2005-5-12 13:17:00的发言： >
呵呵~~~谢谢指正，原来是想表达这个行列数的意思的，只是当时表达错误！
但对于第1个提法我还是有不同的意见： 1、如我之前讨论的问题中的例子：=Sum(if(A1:C10="A",B1:D10)*E1:E10)，在这个例子中，显然B1:D10中可能含有文本，所以不能直接相乘，必须用IF加以判断，在这样的核心表达式不修改的情况下，不管你是用Sum还是用Sumproduct来外套，都必须用三键来结束的；但是当我们合理地运用一下Sumproduct的优势，公式稍修改也可回车结束的。 2、但对于这样的一些公式，当IF条件用来屏蔽错误值的时候，那你用Sumproduct()来单键结束来代替也是无效的，必须使用数组公式三键结束。

我解释的不是你们的附件中的公式，是上面第2点的原因。

Ea · 发表于 2005-5-13 10:29

曾經download 過！

dgsun · 发表于 2005-5-18 16:51

好好研究

晓野 · 发表于 2005-5-19 18:07

好[em05]

tsj970236 · 发表于 2005-5-26 11:33

不错呀

		自动登录	找回密码
密码			免费注册

[原创]关于用SUMPRODUCT取代SUM数组公式的想法

评分