[原创]IF函数替换法总结&25种单条件查询大法

qiyuanhu

QUOTE:

以下是引用cdyh2在2006-11-23 14:13:59的发言：

数据－有效性－输入信息

注意选择“选定单元格时显示输入信息”

谢谢！又学会了一招，原来数据有效性还能拿来当批注用，呵呵！～

zz5151353

收藏 , 謝謝 !!![em23][em23][em24][em24][em27][em27]

luo159

能否解释一下替换IF之公式中哪一个计算最快?

Kelidai

谢谢分享。

andysky

QUOTE:

以下是引用luo159在2006-11-24 16:11:36的发言：
能否解释一下替换IF之公式中哪一个计算最快?

別人帮我测试过速度。

关於这18个条件公式，用VBA公式, 每公式反覆重箅100,000次数, 速度为秒, 名次如下, 以作参考之用 :


名次         速度           公式
1]            2.09        =SMALL({500;800;1100;1400;1700},COUNTIF($A$9:$A$13,"<="&A1))
2]            2.11        =INDEX({500;800;1100;1400;1700},COUNTIF($A$9:$A$13,"<="&A1))
3]            2.14        =CHOOSE(COUNTIF($A$9:$A$13,"<="&A1),500,800,1100,1400,1700)
4]            4.58        =LOOKUP(A1,{0,10,20,30,40},{500,800,1100,1400,1700})
5]            4.66        =MIN(4,INT(A1/10))*300+500
6]            4.70        =MATCH(A1,{0,10,20,30,40})*300+200
7]            5.00        =MIN(40,FLOOR(A1,10))*30+500
8]            5.02        =HLOOKUP(A1,{0,10,20,30,40;500,800,1100,1400,1700},2,1)
9]            6.02        =200+SUM((A1>={0;10;20;30;40})*300)
10]          6.06        =FREQUENCY({0,10,20,30,40},A1)*300+200
11]          6.17        =MAX((A1>={0,10,20,30,40})*{500,800,1100,1400,1700})
12]          6.23        =INDEX({500;800;1100;1400;1700},MATCH(A1,{0;10;20;30;40},1))
13]          6.69        =CHOOSE(MATCH(A1,{0;10;20;30;40},1),500,800,1100,1400,1700)
14]          6.80        =500+SUM(IF(A1>={10,20,30,40},{300,300,300,300}))
15]          6.80        =IF(A1<10,500,IF(A1<20,800,IF(A1<30,1100,IF(A1<40,1400,1700))))
16]          7.91        =CHOOSE(SUM((A1>={0;10;20;30;40})*1),500,800,1100,1400,1700)
17]          9.36        =MAX((INT(A1/({10;20;30;40}))>0)*(ROW($1:$4)*300))+500
18]          9.44        =CHOOSE(MIN(INT(A1/(ROW($1:$4)*10))+1,5),500,800,1100,1400,1700)

gouweicao78

呵呵，包含Countif的竟然排在前面了，比Lookup升序查找还快，这倒是出乎意料的。

hangtian2006

请版主把楼主的这个贴子设为精华贴.

zzjackgao

谢谢! 学习中ing

chrisfang

QUOTE:

以下是引用gouweicao78在2006-11-25 16:11:28的发言：

呵呵，包含Countif的竟然排在前面了，比Lookup升序查找还快，这倒是出乎意料的。

草兄也觉得意外吧，呵呵因为这样的测试可比性并不强：countif的公式使用的条件区域是辅助列，而lookup的公式使用的条件区域是常量数组，这样的比较本身就不太公平。

我用同样的辅助列替换掉lookup中的条件参数后，再进行测试，结果：

1.8秒：   =SMALL({500;800;1100;1400;1700},COUNTIF($A$9:$A$13,"<="&A1))
1.5秒：   =INDEX({500;800;1100;1400;1700},COUNTIF($A$9:$A$13,"<="&A1))
1.3秒：   =CHOOSE(COUNTIF($A$9:$A$13,"<="&A1),500,800,1100,1400,1700)

0.9秒：   =LOOKUP(A1,$A$9:$A$13,{500,800,1100,1400,1700})

且不说前面三个带countif的到底谁更快，但lookup的速度确实在他们之上。

gouweicao78

呵呵，谢谢chrisfang兄测试。俺这个VBA文盲看着你们测试流口水，唉……