用詞典查找代替VLOOKUP

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 = xlCalculationManual
Application.ScreenUpdating = False

t0 = Timer
' 詞典
Set map_dict = CreateObject("Scripting.Dictionary")

' 打開分中心映射表
Set map_sheet = Worksheets("分中心映射表")
map_nrows = map_sheet.Range("A300").End(xlUp).Row
Set 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 If
Next my_row

' 打開機器表
Set dispatch_sheet = Worksheets("機器表")
dispatch_nrows = dispatch_sheet.Range("G99999").End(xlUp).Row
Set my_rows = dispatch_sheet.Range("K2:L" & dispatch_nrows).Rows

' 遍歷開通表，通過詞典獲得 machine_id 對應的一級分中心，插入開通表
For Each o_row In my_rows
   center = o_row.Cells(1, 1).Value
   o_row.Cells(1, 2).Value = map_dict.Item(center)
Next o_row

MsgBox "在機器表上生成一級分中心。共處理 " & dispatch_nrows & " 條記錄，總耗時" & Timer - t0 & "秒。"

' 銷毀建立的詞典
Set map_dict = Nothing

' 打開自動計算和屏幕刷新
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
'
End Sub

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