在Excel VBA中使用SQL到底優勢在哪兒

NewJune發表於2022-02-13
ExcelSQL

　　小爬在之前的博文中多次提到，可以在VBA中寫SQL來操作Excel檔案，實現各類資料處理和分析需求。那麼，你可能有這樣的疑問：Excel原生的VBA，資料透視表，資料分析功能不夠嗎，為啥一定要用SQL來實現呢？在Excel VBA中使用SQL到底優勢在哪兒？今天小爬就來帶大家好好捋一捋這些問題。

　　首先，在VBA中寫SQL，這基本算VBA的高階功能，不是所有人都掌握，因此，上述的場景除了SQL，確實都有其它技術方案。其次，如果我們對別的技術運用的更熟練，我們完全可以用自己最擅長的技術棧來解決問題。以結果論來說，只要能解決實際問題的方案，都是好方案。

　　不過，小爬這裡想討論的是，假如我們對VBA原生方法，資料透視表，SQL等知識都很熟悉的前提下，我們該如何選擇技術棧來解決實際問題呢，這個時候，SQL的優勢在於什麼？我認為有以下幾點：

一、執行效率最高

跟VBA原生方法和Excel公式比較起來，SQL執行時速度要快很多。當表格數量超過一萬行時，這種優勢越發明顯。資料量越大，越適合用SQL來解決問題，越發高效；

二、程式碼極為簡潔

①、資料匹配場景：我們如果有多個欄位要匹配時，原則上需要多個Vlookup或者VBA中建立多個字典，但是，如果我們用SQL的外連線，可能一行程式碼足夠了；

②、資料分組聚合場景：如果我們在VBA中錄製巨集生成資料透視表程式碼，來分組聚合求最大值，EXCEL後臺會自動生成大量程式碼，這些程式碼可讀性極差，可如果我們使用SQL Group By，結合max，min，average等聚合函式，我們也只用一行SQL語句即可。

三、實現較為簡單

　　有一些特殊場景，使用原生VBA方法將極為複雜，但是使用SQL可能就一行程式碼。以我之前一篇博文VBA如何實現篩選條件之“排除某些值”舉例，這個場景，那篇文章用到了很大篇幅來使用純VBA技巧實現這樣一個看似很簡單的需求，單的一個“篩選——不包含某些值”的VBA場景，我們需要用到錄製巨集功能，一維陣列、二維陣列功能、陣列的轉置方法、字典的remove方法、字典鍵快速存入陣列方法等。但是其實，如果我們使用SQL，需要用到的知識點就少多了。

　　我們重新回顧下這個場景，【源資料】表含有【姓名】【通訊地址】【郵政編碼】三列，【例外清單】表中儲存著待排除在外的姓名，【結果】表要求返回【源資料】表中不包含【例外清單】姓名的所有資料。我們使用Python faker庫生成20000行資料，方便比對大樣本時使用不同方法效率上的差異。

 

 

 　　如果用SQL，本質上我們需要將【源資料】表左外連線（Left Join)【例外清單】表，基於【姓名】列，再加上where條件即可。

 

 

具體程式碼如下：

 1 Sub myQuery()
 2   Dim conn As Object, rs As Object, sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet, sql As String, startTime As Date, endTime As Date
 3   startTime = Timer
 4   Set conn = CreateObject("ADODB.Connection")
 5   Set rs = CreateObject("ADODB.recordset")
 6   Set sht1 = ThisWorkbook.Sheets("源資料")
 7   Set sht2 = ThisWorkbook.Sheets("例外清單")
 8   Set sht3 = ThisWorkbook.Sheets("結果")
 9   conn.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties=excel 12.0;data source=" & ThisWorkbook.FullName
10   sql = "SELECT a.* FROM [源資料$]a LEFT JOIN [例外清單$]b ON a.姓名=b.姓名 WHERE b.姓名 IS NULL"
11   Set rs = conn.Execute(sql)
12   For i = 0 To rs.Fields.Count - 1 '輸出recordset欄位名到【結果】表
13     sht3.Cells(1, i + 1) = rs.Fields(i).Name
14   Next
15   sht3.Cells(2, 1).CopyFromRecordset rs '輸出recordset結果到【結果】表
16   conn.Close
17   Set conn = Nothing
18   endTime = Timer
19   sht3.Activate
20   MsgBox "累計執行" & (endTime - startTime) & "秒"
21 
22 End Sub

執行結果如下所示，耗時約0.63秒：

 

我們再回顧下使用VBA字典來實現這一需求的方法，程式碼如下：

 1 Sub dictWay()
 2   Dim conn As Object, rs As Object, sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet, sql As String, startTime As Date, endTime As Date, maxRow1 As Integer, myDic As Object, maxRow2 As Integer
 3   startTime = Timer
 4   Application.ScreenUpdating = False
 5   Set sht1 = ThisWorkbook.Sheets("源資料")
 6   Set sht2 = ThisWorkbook.Sheets("例外清單")
 7   Set sht3 = ThisWorkbook.Sheets("結果")
 8   Set myDic = CreateObject("scripting.dictionary")
 9   maxRow1 = sht1.Cells(Rows.Count, 1).End(xlUp).Row
10   maxRow2 = sht2.Cells(Rows.Count, 1).End(xlUp).Row
11   Dim i As Integer, j As Integer, k As Integer
12   For i = 2 To maxRow2
13     myDic.Add sht2.Cells(i, 1).Value, ""
14   Next
15   
16   k = 1
17   For i = 1 To maxRow1
18     If myDic.exists(sht1.Cells(i, 1).Value) = False Then
19         For j = 1 To 3
20             sht3.Cells(k, j).Value = sht1.Cells(i, j).Value
21         Next
22         k = k + 1
23     End If
24   
25   Next
26 
27   endTime = Timer
28   sht3.Activate
29   Application.ScreenUpdating = True
30   MsgBox "累計執行" & (endTime - startTime) & "秒"
31 
32 End Sub

為了讓該方法儘可能提速，我們使用了字典，同時關掉了Excel的螢幕重新整理。最終20000行資料時，耗時約4秒：

 

可以看到，與之前VBA的方法比起來，只需要一行SQL語句，邏輯清晰易懂，程式碼簡潔明瞭，程式執行耗時是傳統方法的1/6不到，這就是小爬力推在VBA中使用SQL的主要原因~~

歡迎掃碼關注我的公眾號 獲取更多爬蟲、資料分析的知識！

 

相關文章