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

lhx120824

测试结果       
1、辅助列法 d2=IF(COUNTIF($e2:e$2,e2)=1,MAX(d1:d$1)+1,"")
         f2=IF(ISNA(VLOOKUP(ROW(1:1),$d$2:$e1000,2,0)),"",VLOOKUP(ROW(1:1),$d$2:$e1000,2,0))

1、辅助列       
0.141       
0.156       
0.156       
0.156       
       
       
2、 =INDEX(姓名,SMALL(IF(ROW(姓名)-1=MATCH(姓名,姓名,0),ROW(姓名)-1,""),ROW(1:1)))       
4.625       
4.563       
4.438       
       
3、=INDEX(E:E,SMALL(IF(MATCH(机构,机构,)=ROW(机构)-1,ROW(机构),4^8),ROW(1:100)))&""       
row(1:100)        row(1:50)
4.563        4.531
4.547        4.547
4.484        4.656
       
row(1:1000)       
4.563       
4.516       
4.656       
       
公式四=INDEX(机构,MATCH(,COUNTIF($e$1:e1,机构),))       
出现错误，有待解决。

lhx120824

从测试结果看出辅助列法与其他几种公认的提取不重复值公式相比简直不是一个档次的。
提取不重复值测试附件
提取不重复列测试.rar (43.85 KB, 下载次数: 106)

2010-5-12 15:58 上传

点击文件名下载附件

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

天遥

感谢分享......

bopanda

楼主的测试很认真。谢谢

gouyuanpei

与想象不同，的确是sumproduct快些，不过优势没那么明显，我是用随机数字试的，第一次sumproduc耗时5.938秒，随后稳定在2.25秒，sumif则是3.594和2.391

晚枫

原帖由 lhx120824 于 2010-5-12 15:47 发表
2、 =INDEX(姓名,SMALL(IF(ROW(姓名)-1=MATCH(姓名,姓名,0),ROW(姓名)-1,""),ROW(1:1)))        
4.625        
4.563        
4.438        
        
3、=INDEX(E:E,SMALL(IF(MATCH(机构,机构,)=ROW(机构)-1,ROW(机构),4^8),ROW(1:100)))&""        
row(1:100)        row(1:50)
4.563        4.531
4.547        4.547
4.484        4.656

这个测试结果有误吧？
第三个公式是多单元格数组公式，怎么可能和第二个公式一个速度？
【你是按多单元格数组公式输入的吗？如果不是，那当然和公式2的速度一样】

[ 本帖最后由 晚枫 于 2010-5-12 16:51 编辑 ]

lhx120824

原帖由 晚枫 于 2010-5-12 16:47 发表


这个测试结果有误吧？
第三个公式是多单元格数组公式，怎么可能和第二个公式一个速度？
【你是按多单元格数组公式输入的吗？如果不是，那当然和公式2的速度一样】

都是按数组公式输入的，有其他方法更好，希望有不同的意见，才能有比较。争论才能明朗。

lhx120824

原帖由 gouyuanpei 于 2010-5-12 16:42 发表
与想象不同，的确是sumproduct快些，不过优势没那么明显，我是用随机数字试的，第一次sumproduc耗时5.938秒，随后稳定在2.25秒，sumif则是3.594和2.391

你让数据量增大，越多越明显。

晚枫

原帖由 lhx120824 于 2010-5-12 17:00 发表

都是按数组公式输入的，有其他方法更好，希望有不同的意见，才能有比较。争论才能明朗。

ROW(1:00)，应该是表示100个单元格组成一个区域数组公式（选定100个单元格，一起按下三键），而不是写完一个按三键再向下拉。

lhx120824

原帖由 晚枫 于 2010-5-12 17:09 发表


ROW(1:00)，应该是表示100个单元格组成一个区域数组公式（选定100个单元格，一起按下三键），而不是写完一个按三键再向下拉。

我按你的方法试了试，根据需要改成row(1:250),选定一列的250个单元格，按F2后粘贴公式，再按三键，的确很快填满单元格，我对这个公式就不知怎么来测试速度值了。谢谢你的指点。让我对数组又加深了理解。

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