从上一篇《PYTHON操作EXCEL》可以看到，Python 操作 Excel 已非常自如方便。但是 Python 和相关库毕竟是一个额外的依赖，若能从 Excel 自身解决此类问题，自然是更为易用。

1. VBA 中的哈希表

    用 Python 的着眼点主要是 VLOOKUP 公式太慢了，所以关键是要找到一种更高效的算法或数据结构定位数据。VLOOKUP 要求对列进行排序，内部应该是对列内数据进行二分查找，算法上不好再优化了，那就只好更换一种数据结构。搜索了一下，VBA 提供了 Scripting.Dictionary 这一词典结构，而且有文章说内部是哈希表实现，那就正是我要的东西了。

    这样，VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 这一公式就转为下面的词典查找方式来实现：

    若是仅仅 VLOOKUP 一次，倒也不必费劲先建立起一个词典。但当使用同样 VLOOKUP 公式的单元格很多时(比如几万个)，就显得其必要了。因为 Dictionary 只需要建立一次，就可以用 O(1) 的复杂度进行多次查找了。

2. VLOOKUP 慢，主要问题不在算法上

    从算法角度，词典查找的确快于二分查找，但优势并不是那么明显。所以在具体执行时，我发现使用词典查找的 VBA 宏运行速度并不比 VLOOKUP 快多少，运行时 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 公式或许是一种更为简单的实现。