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

lhx120824

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倍

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

lhx120824

有关函数的效率比较我会不断加入的。
以下的两个数组公式
=INDEX($A:$A,SMALL(IF($B$37:$B$58=$D37,ROW($A$37:$A$58),4^8),COLUMN(A:A)))&""
=OFFSET($A$36,SMALL(IF($B$37:$B$58=$D48,ROW($A$37:$A$58),4^8),COLUMN(A:A))-1,)&""
差别在哪里？

[ 本帖最后由 lhx120824 于 2010-7-28 01:55 编辑 ]

晚枫

原帖由 lhx120824 于 2010-7-28 01:53 发表
有关函数的效率比较我会不断加入的。
以下的两个数组公式
=INDEX($A:$A,SMALL(IF($B$37:$B$58=$D37,ROW($A$37:$A$58),4^8),COLUMN(A:A)))&""
=OFFSET($A$36,SMALL(IF($B$37:$B$58=$D48,ROW($A$37:$A$58),4^8),COL ...

就公式本身而言，这两个公式的运算速度应该没有什么差别，就是有，也很小很小。
问题是第二个公式使用了易失函数，前一个没有。  易失函数的使用会对整个表格的运算速度有较大的影响。所以在可能的情况下，还是避免使用为好。

lhx120824

原帖由 晚枫 于 2010-7-28 07:47 发表


就公式本身而言，这两个公式的运算速度应该没有什么差别，就是有，也很小很小。
问题是第二个公式使用了易失函数，前一个没有。  易失函数的使用会对整个表格的运算速度有较大的影响。所以在可能的情况下，还是 ...

你说的问题很重要，既然使用时有“易失”的问题，况且第二个函数需要五个参数，起始点和偏移的量不易把握，这也是应该在使用中应注意的问题，那就何不使用第一个？

[ 本帖最后由 lhx120824 于 2010-8-28 09:23 编辑 ]

314000272

学习中……

conywan53

还没用过这么庞大的数据呢。。哈哈。。顶了。

lhx120824

=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

[ 本帖最后由 lhx120824 于 2010-8-28 09:36 编辑 ]

红日的光芒

我以前做过802.16 e下的LDPC。QQ：280105847.我加lz了。

lhx120824

晚枫在帖子
逆向查询有多少种方法
http://club.excelhome.net/viewth ... 26amp%3Btypeid%3D40
的5楼中提供的：

IF({1,0}和CHOOSE是最慢的
OFFSET和INDRIRECT次之，
LOOKUP速度也不见得快
只有INDEX+MATCH是最快的
如果B列能够排序，那么MATCH就可以采用近似匹配，速度更快

0372nhyz

厉害，学习了