Excel 中用 VBA 字典尋找代替 VLOOKUP

標籤：style   blog   http   color   使用   os   

從上一篇《PYTHON操作EXCEL》可以看到，Python 操作 Excel 已非常自如方便。但是 Python 和相關庫畢竟是一個額外的依賴，若能從 Excel 自身解決此類問題，自然是更為易用。
1. VBA 中的雜湊表 用 Python 的著眼點主要是 VLOOKUP 公式太慢了，所以關鍵是要找到一種更高效的演算法或資料結構定位元據。VLOOKUP 要求對列進行排序，內部應該是對列內資料進行二分尋找，演算法上不好再最佳化了，那就只好更換一種資料結構。搜尋了一下，VBA 提供了 Scripting.Dictionary 這一詞典結構，而且有文章說內部是雜湊表實現，那就正是我要的東西了。
這樣，VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 這一公式就轉為下面的詞典尋找方式來實現：

• 使用要從中進行尋找的 table_array 內容構建詞典。用 table_array 第一列作為 key，table_array 第 col_index_num 列作為 value，插入 Dictionary 中：Dictionary.Add key, value;
• 尋找時只需直接取 Dictionary 內的值 Dictionary.Item(lookup_value)，即可完成尋找;

若是僅僅 VLOOKUP 一次，倒也不必費勁先建立起一個詞典。但當使用同樣 VLOOKUP 公式的儲存格很多時（比如幾萬個），就顯得其必要了。因為 Dictionary 只需要建立一次，就可以用 O(1) 的複雜度進行多次尋找了。
2. VLOOKUP 慢，主要問題不在演算法上 從演算法角度，詞典尋找的確快於二分尋找，但優勢並不是那麼明顯。所以在具體執行時，我發現使用詞典尋找的 VBA 宏運行速度並不比 VLOOKUP 快多少，運行時 Excel 仍然會導致系統假死幾個小時。按說如此簡單的程式不應該那麼慢，問題究竟在哪裡呢？
經過一段摸索，我才發現問題的根源所在：

• VBA 往 Excel 表格中填內容時，會引發表格中已有公式的自動計算，非常耗時；
• Excel 表格內容更新時，會觸發螢幕顯示內容的自動重新整理，代價也很高；

所以提高 VBA 指令碼執行效能的關鍵點，在於計算時關掉公式自動計算和螢幕重新整理，這也是我始料未及的。在 VBA 中實現這兩點很容易，但由於 VLOOKUP 本身即是公式，我沒能想通直接調用 VLOOKUP 時如何避免這兩點帶來的效能損失。
3. 樣本 VBA 代碼 在做了上面提到的兩次最佳化之後，原來 VLOOKUP N 個小時才能完成的任務，只用了 7 秒鐘就執行結束了。

下面是我寫的一段範例程式碼。我不熟悉 VBA 語言，只是照葫蘆畫瓢。代碼規範程度相差甚遠，但題意應是體現其中了。有心的朋友可以用作參考。

Sub 在機器表上產生一級分中心()‘‘ 在機器表上產生一級分中心 Macro‘Application.Calculation = xlCalculationManualApplication.ScreenUpdating = Falset0 = Timer‘ 詞典Set map_dict = CreateObject("Scripting.Dictionary")‘ 開啟分中心映射表Set map_sheet = Worksheets("分中心映射表")map_nrows = map_sheet.Range("A300").End(xlUp).RowSet my_rows = map_sheet.Range("A2:B" & map_nrows).Rows‘ 遍曆分中心映射表，獲得 分中心 對應的一級分中心，插入詞典For Each my_row In my_rows   center = my_row.Cells(1, 1).Value   city = my_row.Cells(1, 2).Value   If Not map_dict.Exists(center) Then       map_dict.Add center, city   End IfNext my_row‘ 開啟機器表Set dispatch_sheet = Worksheets("機器表")dispatch_nrows = dispatch_sheet.Range("a99999").End(xlUp).RowSet my_rows = dispatch_sheet.Range("a1:b" & dispatch_nrows).Rows‘ 遍曆開通表，通過詞典獲得 machine_id 對應的一級分中心，插入開通表For Each o_row In my_rows   center = o_row.Cells(1, 2).Value   o_row.Cells(1, 2).Value = map_dict.Item(center)Next o_rowMsgBox "在機器表上產生一級分中心。共處理 " & dispatch_nrows & " 條記錄，總耗時" & Timer - t0 & "秒。"‘ 銷毀建立的詞典Set map_dict = Nothing‘ 開啟自動計算和螢幕重新整理Application.Calculation = xlCalculationAutomaticApplication.ScreenUpdating = True‘End Sub

最後補充一點：我先實現的詞典尋找，後發現效能問題根源，所以未能去比較 VLOOKUP 與詞典尋找兩種方式的具體效能差異。我想如果差異可以忍受，那麼直接在 VBA 中調用 VLOOKUP 公式或許是一種更為簡單的實現。 4. 測試案例

上面的代碼如何測試呢？

id  name：分中心映射表，字典表    nickName id：機器表，待匹配表           匹配後結果：1    a1                              b1    1                              b1    a12    a2                              b2    2                              b2    a23    a3                              b3    3                              b3    a34    a4                              b4    4                              b4    a45    a5                              b5    5                              b5    a56    a6                              b6    6                              b6    a67    a7                              b7    7                              b7    a78    a8                              b8    8                              b8    a89    a9                              b9    9                              b9    a910   a10                             b10   10                             b10   a10

— EOF —