前景提要
大資料的模糊查詢我會選擇用陣列
大資料查詢你還在遍歷那你耐心應該挺好
模糊查詢的高階玩法陣列完勝單元格遍歷
模糊查詢的高階玩法程式碼少更易理解
在不久之前,我們學習了利用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了嗎?