由SUMIF与sumproduct的速度比较展开联想，实现同一功能的不同公式如何选择？

lhx120824 · 发表于 2010-5-12 06:18

一、两个求和公式的比较
=SUMIF(A:A,"a",B:B)
＝sumproduct((A1:A4="a")*B1:B4)
论坛中应用sumproduct的很普遍，大有取代SUMIF的趋势，上面的两个条件求和方法在数据量比较大时哪那个速度快？

看附件的测试结果

测试数据再次修改20100512.rar (284.9 KB, 下载次数: 314)
欢迎大家参与讨论，尤其是测试的方法和代码的可靠性。

如果测试代码和方法正确，可看出两个公式速度相差近一倍。SUMPRODUCT比SUMIF快。

二、一列中提取不重复值的公式得比较讨论：
从17楼开始讨论一列中提取不重复值的公式：
公式一（辅助列法）：
=IF(COUNTIF($e2:e$2,e2)=1,MAX(d1:d$1)+1,"")
=IF(ISNA(VLOOKUP(ROW(1:1),$d$2:$e1000,2,0)),"",VLOOKUP(ROW(1:1),$d$2:$e1000,2,0))
公式二（区域数组公式  ）：
=INDEX(e:e,SMALL(IF(MATCH(机构,机构,)=ROW(机构)-1,ROW(机构),4^8),ROW(1:250)))&""
公式三（单个数组公式  ）：
=INDEX(姓名,SMALL(IF(ROW(姓名)-1=MATCH(姓名,姓名,0),ROW(姓名)-1,""),ROW(1:1)))
公式四（条件计数与查找）：
=INDEX(机构,MATCH(,COUNTIF($e$1:e1,机构),))
猜猜以上的四个公式哪个速度最快，哪个速度最慢？

三、几个查找公式的比较：
先需要大家的献言献策，期待……

晚枫老师的：
关于IF({1,0}的运算速度问题：
http://club.excelhome.net/thread-562336-1-1.html

czzqd在帖子
[求助]如何在单元格中取一列数中最后一个非零的数值？
的14楼指出：
http://club.excelhome.net/viewth ... 3Ddigest&page=2

介绍一下bosco_yip 对相关公式的测速结果：

=LOOKUP(2,1/(A1:A10<>""),A1:A10) - 为效率较低的函数公式 - 公式一 : LOOKUP(9.99999999999999E307,A:A) - 公式二 : LOOKUP(2,1/(A1:A65535<>””),A1:A65535) - 公式一要比公式二, 速度效率快约500倍

＝＝＝＝＝＝＝＝＝＝

INDEX(A1:A10,MAX(IF(A1:A10<>"",ROW(A1:A10)))) - 亦是效率较低的函数公式 - 公式一 : LOOKUP(9.99999999999999E307,A:A) - 公式三 : INDEX(A:A,MAX(IF(A1:A65535<>””,ROW(A1:A65535)))) - 公式一要比公式三, 速度效率快约396倍

=MATCH("一二三四五",TEXT(ROW(A:A),"[dbnum1]0"),)数组
惊心的是大数组（row(1:65536),公式短，但速度慢。
比较一下 wshcw的：
一至九的转换:
=MID(SUM(FIND(TEXT(ROW($1:$9),"[dbnum1]0"),"一二三四五六七八九")/10^ROW($1:$9)),3,9)
看来公式中引用的区域范围的大小对速度的影响也是一个重要的因素。
参看讨论的帖子：
“一二三四五”如何用公式转化成“12345”
http://club.excelhome.net/thread-617752-3-1.html

晚枫在帖子
逆向查询有多少种方法
http://club.excelhome.net/viewth ... 26amp%3Btypeid%3D40
的5楼中提供的：
IF({1,0}和CHOOSE是最慢的
OFFSET和INDRIRECT次之，
LOOKUP速度也不见得快
只有INDEX+MATCH是最快的
如果B列能够排序，那么MATCH就可以采用近似匹配，速度更快。

[ 本帖最后由 lhx120824 于 2010-9-14 16:59 编辑 ]

cflood · 发表于 2010-5-12 07:12

没试过，感觉应该sumif快

zhc19620430 · 发表于 2010-5-12 07:17

因为sumproduct是数组得出的结论吧，楼主试试，凡我不会使用他去替代sunif。

lhx120824 · 发表于 2010-5-12 08:44

我用以下代码做了一个测试，
Sub getTimer()
Dim myTime
myTime = Timer
Application.Calculate
myTime = Timer - myTime
MsgBox Format(myTime, "本次计算总花费0.000秒")
End Sub

数据见附件，结果同大家的猜测正相反
=SUMPRODUCT(($P2:$P1822=$S2)*D2:D1822)
0.375
0.344
0.328

=SUMIF($P:$P,$S2,D:D)
1.172
1.188
1.414

不知是代码有问题，还是大家感觉错了？

测试数据20100512.rar (283.38 KB, 下载次数: 62)

[ 本帖最后由 lhx120824 于 2010-5-12 13:07 编辑 ]

lhx120824 · 发表于 2010-5-12 08:51

以上测试这样做的：把公式选项改为手动，在s2中输入
=SUMPRODUCT(($P2:$P1822=$S2)*D2:D1822)
回车然后右拖再双击向下自动填充，单击测速，记录下结果0.375，同样的操作反复三次，分别得到0.375 0.344 0.328

用同样的方法测试公式
=SUMIF($P:$P,$S2,D:D)

lhx120824 · 发表于 2010-5-12 09:03

我又做了修改，结果差不多是第一种方法增加一倍的数据时间差不多增一倍。第二种方法改为同第一个公式同样的引用区域，时间差不多，见修改后的附件。都是引用3643行。
我怀疑的就是代码的问题，请大家讨论。

测试数据修改20100512.rar (284.39 KB, 下载次数: 40)

[ 本帖最后由 lhx120824 于 2010-7-16 22:27 编辑 ]

lhx120824 · 发表于 2010-5-12 09:54

再次修改附件，请参与讨论。

测试数据再次修改20100512.rar (284.9 KB, 下载次数: 71)

cflood · 发表于 2010-5-12 10:09

公式应该这样才对
=SUMIF($P$2:$P$3643,$S2,D$2:D$3643)
=SUMPRODUCT(($P$2:$P$3643=$S2)*D$2:D$3643)
似乎是sumproduct快一些

[ 本帖最后由 cflood 于 2010-5-12 10:16 编辑 ]

mn860429 · 发表于 2010-5-12 10:13

觉得应该sumif快

爱学若渴 · 发表于 2010-5-12 10:15

明白了明白了

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

[讨论] 由SUMIF与sumproduct的速度比较展开联想，实现同一功能的不同公式如何选择？