Excel VBA 8.47模糊查询的高阶玩法 数组完胜单元格遍历
模糊查询的高阶玩法 数组完胜单元格遍历
点击上方“Excel和VBA”,选择“置顶公众号”
致力于原创分享Excel的相关知识,源码,源文件打包提供
一起学习,一起进步~~
在不久之前,我们学习了利用range.find的方法来实现单元格内容的模糊查找,并且将数据单独放到一个区域展示,相对于传统的CTRL+F的数据查找方法,效率是提高了很多,但是这个方法还不是最好的方法,因为之后有小伙伴们就碰到这样的情况了。
利用range.find方式进行大批量数据查找,Excel卡到。。。。
这里我们来说下,range.find方法,终究还是利用单元格的方法,即range的附属方法,在面对大数据的时候,遍历所有的单元格去循环查找,并不是一个明智的选择。
那么如果数据量过大的情况,我们要如何来实现数据的查找呢?
场景说明
这是我们今天构造的一个数据源,量不算很大,大概有1500行数据,同时总共有5列,那么总共有1500*5=7500左右各数据 这个量并不算大,那么我们现在在执行下之前我们的代码
利用range.find方法的代码来看看
这里我们就不展示代码了,直接看看获取结果所需要的时间
从1500行的数据,就需要大概12s左右的时间,虽然不算很慢,但是毕竟数据量也不算很大,并且我们这里也是减少了数据量,只针对A列进行查找,因为数据量再多点,我怕电脑会卡死(家里电脑太差....)
所以我们需要一个高阶查询
代码区
Sub test4()
t = Now()
Dim arr, arr1
arr = Range("a2:a1509")
arr = WorksheetFunction.Transpose(arr)
arr1 = Filter(arr, "A1", True)
Range(Cells(17, 8), Cells(UBound(arr1) + 1, 8)) = WorksheetFunction.Transpose(arr1)
t1 = Now() - t
MsgBox t1
End Sub
又来了
说是高阶查找,但是代码反而越来越少
既然我们已经知道用range.find方法行不通,那么我们肯定要换种方法来使用了。
这里就是我们的数组方法,搭配一个数组函数,filter
看看效果
可以看到原来需要12s的数据,现在瞬间完成,所以这里展示的是0
代码解析
代码量减少了,自然说明方法也是更加的简单啦。
首先我们声明两个变量,用来承接数组
arr,和arr1
首先将A列的数据装入数组中
arr = Range("a2:a1509")
然后我们上面说过,搭配一个数组函数Filter
那么现在需要让她出场了。
arr1 = Filter(arr, "A1", True)
来说下Filter的用法。
Filter(数组,匹配的内容,布尔值)
1.数组,就是我们要判断的内容
这里需要注意,这里必须是一个数组。不能是一个文本或者其他的
2.匹配的内容,就是我们要配置的数据
这里需要注意的是,这里并不需要搭配任何通配符,因为Filter本身就是用模糊匹配的。
3.布尔值
熟悉编程的小伙伴都知道,布尔值只有两个,TRUE和FALSE,这里我们来讲解下
TRUE,包含,即我们所谓的正向查找
比方说这个结果都是包含A1的,如果我选择FALSE呢?
瞬间可以将所有不包含A1的数据提取出来,非常的方便,快捷。
有了结果,那么我们直接写入单元格就可以了。数组的写入是一个个写入的嘛?
很明显不是,是批量直接一次性写入的,这样效率更高
那么代码要如何体现呢?
Range(Cells(17, 8), Cells(UBound(arr1) + 1, 8)) = WorksheetFunction.Transpose(arr1)
arr = WorksheetFunction.Transpose(arr)
这里我们将第二行的代码也一起展示出来
我们可以看到代码中都包含了一个相同的部分
WorksheetFunction.Transpose
这是Filter的特性决定的。
因为Filter只能针对一维数组,一维数组是什么概念呢?
看下案例,一行我们可以简单理解为就是一维数组,因为我们这里选择的是A列,所以我们需要用WorksheetFunction.Transpose,将一列转置为一行,形成一个一维数组
而最后单元格的写入的时候,就反过来了
因为arr1是一个一维数组,那么我们要写入一列,所有要将一维数组,在转换成为多维数组
所以这里也要使用一次WorksheetFunction.Transpose
你GET 了吗?