vba查询输入的值是否在某列中存在 |
您所在的位置：网站首页 › 判断一列数据是否在另外一列,在显示是 › vba查询输入的值是否在某列中存在 |
vba查询输入的值是否在某列中存在
|
01 应用场景
场景：碰到一个Excel表，大概有50万行的数据，手头有差不多1万个待查询的信息，需要到50万行数据中去匹配。用了Excel自带的vlookup，大概要等5分钟才能将1万行的数据匹配好。根据每个人使用电脑的不同，耗时可能有些出入。但更多的时候，我们要查询数据往往超过1万行，而Excel的支持就不那么友好了。
02 介绍
今天我来介绍下VBA的秘密武器——字典。
可能跟想象的不太一样，一本字典能称为秘密武器吗？其实，在这里更准确的说法应该是hashmap，它有一个非常好的优势，就是它的算法复杂度为O(1),注意这个1是个常数，也就是一，二，三的一。按算法来说，一般随着计算量的增加，我们要解决问题的难度也会加大。比如我们要在15个人里面看看有那几个人手里拿着旗子？可能一眼就能得到答案：3。但是如果现在有100万人呢？问题解决的难度就变得非常大了(O(n))。
O(1)的算法有一个非常的好的特性，不管数据多寡，它总是以固定的时间将问题的答案抛给提问者。就像面对一面魔镜，只要问一个问题，不管问题多难，魔镜都能瞬间给出答案。而hashmap，或字典就有这样的能力。 03 VBA字典VBA的自带数据类型里并没有字典。需要我们自己引入。VBA有个古老的Scripting库，从Scripting中我们可以引入Dictionary，也就是常说的字典。引入语法如下 Set dict = CreateObject("Scripting.Dictionary") 字典的主要属性如下 CompareMode Count Item key 方法如下 Add Exists Items Keys Remove RemoveAll Dictionary 跟我们日常使用的字典相似点如下： Dictionary的键相当于我们字典要查的”词“ Dictionary的值相当于我们字典中查到”词的解释“ 并且一个dictionary实例的键是不能重复的。 今天主要用到的属性跟方法是 Item=>对某个键赋值 Add=>增加一对键、值 Exists=>判断某个键是否存在 关于如何使用VBA的Dictionary，先告一段落，接下来要解决我们开头的那个应用场景 04 解决问题 第一个版本在Excel表中模拟了差不多50万行的记录，有三个字段，分别是索引字段 索引值1 索引值2 这些数据都是随机生成的，并没有什么意义。其中索引字段是从26个英文子母中随机挑选了7个，索引值1跟2随机从1到200中取了个整数。 接下来，我从索引字段中随机抽取了差不多1万个待检索值，目标是在结果中显示内容。
首先，我们选中f4:f10000,在其中填入自定义函数NEWVLOOKUP，这个函数有三个参数，第一个是要查询的一列数组，也就是E4:E10000，第二个参数是目标索引的列，也就是A3:A500000,第三个参数就是要索引的内容，在这里我们选择索引自身，也就是1。
输完公式后使用Ctrl + Shift + Enter三键，让这个公式变成一个数组公式。
具体形式见下，在公式的左右会自动带上大括号。
在公式后台，我统计了整个过程的运行时间。具体见下
从运行时间来看，我们使用Dictionary的效率比vlookup要快10倍左右(35s VS 300s)，在日常的使用中使用这个公式可以快速地提高效率吧。 具体代码见下： Public Function NEWVLOOKUP(query_array, lookup_array As Range, offset_col As Integer) As VariantApplication.ScreenUpdating = FalseDim start_timeDim ini_timeDim finish_timeDim rng As Range'统计时间start_time = Timer'生成字典Dim dict As ObjectSet dict = CreateObject("Scripting.Dictionary")'数据载入字典For Each rng In lookup_array If Not dict.exists(rng.Value) Then dict.Add rng.Value, rng.Offset(0, offset_col - 1).Value End IfNext'统计时间ini_time = TimerDim iDim temp_array()i = 1If TypeName(query_array) = "Range" Then ReDim temp_array(1 To query_array.Count, 1 To 1) For Each rng In query_array If dict.exists(rng.Value) Then temp_array(i, 1) = dict.Item(rng.Value) Else temp_array(i, 1) = "#N/A" End If i = i + 1 Next NEWVLOOKUP = temp_arrayElse If dict.exists(query_array) Then NEWVLOOKUP = dict.Item(query_array) Else NEWVLOOKUP = "#N/A" End IfEnd If'统计时间finish_time = TimerApplication.ScreenUpdating = True' 输出相关时间信息Dim processTimeDim totalTimeprocessTime = finish_time - ini_timetotalTime = finish_time - start_timeDebug.Print "字典生成时间:" & ini_time - start_time & "秒"Debug.Print "检索时间:" & processTime & "秒"Debug.Print "总时间:" & totalTime & "秒"Debug.Print "=========================="End Function'输出结果'字典生成时间:34.03516秒'检索时间:.6328125秒'总时间:34.66797秒'==========================emmmmm.... 说好的O(1)算法，为啥用了整整35秒，哪里快了呀，喂。
从结果来看，虽然速度上去了。但是从解决问题的角度来说，速度依然慢得跟蜗牛一样。 从Python或JS的角度，加载200万的数据也不过区区几秒钟。这50万行数据VBA竟然用了35秒。 那么VBA到底在摸什么鱼？ 看到结果，一开始我觉得无从下手。经过几天研究，我发现一个细节。就是字典加载时间并不是线性的。可能50万行加载出来的字典跟25万行加载的时间并不是一倍关系，而是比一倍要多。那就说明VBA加载字典，字典越大速度越慢，而如果我将字典拆开来几个小字典分别加载，那么从时间角度来看应该比单个大字典要快(也就是1+1 |
【本文地址】
今日新闻 |
推荐新闻 |