[已解决]sumif() 和vlookup() 效率谁快

vsnic

问个问题：sumif() 和vlookup() 哪个执行效率快？如果表里有一堆公式，他们2个用哪个执行能快些呢？

最佳答案

月排行榜 / 总排行榜

Zipall

2014-1-24 18:02

6w个随机数测试的结果vlookup的精确匹配比sumif要快的多.
根据测试值出现的位置不同,vlookup消耗的时间差异较大.但一定比sumif用时少.
测试代码如下

1. Sub test()
   
2.     Randomize
   
3.     For n = 1 To 10
   
4.         With Range("a1:b60000")
   
5.             .Formula = "=rand()"
   
6.             .Value = .Value
   
7.         End With
   
8.         
   
9.         With Application.WorksheetFunction
   
10.             t = .Large(Range("a:a"), Int(60000 * Rnd + 1))
    
11.             t1 = Timer
    
12.             For i = 1 To 1000
    
13.                 x = .VLookup(t, Range("a:b"), 2, 0)
    
14.             Next
    
15.             t2 = Timer
    
16.             Debug.Print "vlookup:" & t2 - t1;
    
17.             
    
18.             t1 = Timer
    
19.             For i = 1 To 1000
    
20.                 x = .SumIf(Range("a:a"), t, Range("b:b"))
    
21.             Next
    
22.             t2 = Timer
    
23.             Debug.Print " sumif:" & t2 - t1
    
24.         End With
    
25.     Next
    
26. End Sub

复制代码

结果如下


测试环境
笔记本i7+8G
win7 64 +excel 2010

跳转到最佳答案楼层

pureshadow

sumif快

Zipall

6w个随机数测试的结果vlookup的精确匹配比sumif要快的多.
根据测试值出现的位置不同,vlookup消耗的时间差异较大.但一定比sumif用时少.
测试代码如下

1. Sub test()
   
2.     Randomize
   
3.     For n = 1 To 10
   
4.         With Range("a1:b60000")
   
5.             .Formula = "=rand()"
   
6.             .Value = .Value
   
7.         End With
   
8.         
   
9.         With Application.WorksheetFunction
   
10.             t = .Large(Range("a:a"), Int(60000 * Rnd + 1))
    
11.             t1 = Timer
    
12.             For i = 1 To 1000
    
13.                 x = .VLookup(t, Range("a:b"), 2, 0)
    
14.             Next
    
15.             t2 = Timer
    
16.             Debug.Print "vlookup:" & t2 - t1;
    
17.             
    
18.             t1 = Timer
    
19.             For i = 1 To 1000
    
20.                 x = .SumIf(Range("a:a"), t, Range("b:b"))
    
21.             Next
    
22.             t2 = Timer
    
23.             Debug.Print " sumif:" & t2 - t1
    
24.         End With
    
25.     Next
    
26. End Sub

复制代码

结果如下


测试环境
笔记本i7+8G
win7 64 +excel 2010

tgydslr

vlookup精确查找在所查找的数据在大区域后面时会感觉很慢
不过sumif需要查找两个区域再处理的
所以根据情况不同会有不同的结果
应该说在相同情况下，比方数据相同是vlookup会稍块些，自我感觉
但是由于vlookup模糊查找使用二分法查找，在符合条件的情况下，会比sumif稍快些

丢丢表格

Zipall 发表于 2014-1-24 18:02
6w个随机数测试的结果vlookup的精确匹配比sumif要快的多.
根据测试值出现的位置不同,vlookup消耗的时间差异 ...

你的测试方法有问题,
不能用行号来查,应该用文本来查,就慢下来了.
不过 VLOOKUP 与 SUMIF 不是同一类函数,很难说谁快谁慢,这个要看你在什么地方用,如何用的问题了.

Zipall

丢丢表格 发表于 2014-1-24 19:25
你的测试方法有问题,
不能用行号来查,应该用文本来查,就慢下来了.
不过 VLOOKUP 与 SUMIF 不是同一类函 ...

没明白你说的"行号"具体是指什么?

冠军欧洲2010

数据多的时候，用公式，都不会很快吧。特别是数组公式。
卡的要死。

tgydslr

冠军欧洲2010 发表于 2014-1-24 20:16
数据多的时候，用公式，都不会很快吧。特别是数组公式。
卡的要死。

那是，大数据的话公式处理起来不好了

Zipall

tgydslr 发表于 2014-1-24 18:37
vlookup精确查找在所查找的数据在大区域后面时会感觉很慢
不过sumif需要查找两个区域再处理的
所以根据情 ...

测试中两函数要查找的区域大小是没有区别的,因为vlookup找到值就停了,要查找的值出现在前面和后面耗时区别就大;sumif要遍历所有值,所以sumif每次耗时都基本一样.
数值换成文本值,速度同样是vlookup快,但是区别没有数值这么明显.