ARTICLE · 1057733
Excel 函数与公式 -- 内容搜索(二)
Excel 函数与公式 -- 内容搜索(二)上一篇文章我们介绍了 Excel 中最基本的搜索函数 LOOKUP，并且说明，要使用 LOOKUP 搜索工作表中的内容，那么，搜索区域必须是已经排好序的。从这个要求可以猜测，LOOKUP 函数内部实现或许是使用的二分搜索算法。 今天我们就根据这个猜测来实现我们自己的 LOOKUP 函数 MYLOOKUP。 首先来介绍一下二分搜索（binary search）。二分搜索又叫折半搜索，是一种比较快的搜索算法，唯一的要求是要搜索的区域（不论是 Excel 的 Range，还是一般的数组）必须是经过排序的，否则搜索将失效。 二分搜索的基本思路是：设要搜索的值是 sv，要搜索的区域 A（下界和上界分别是 low 和 hi，则 A(low) 表示 A 的第一个元素，A(hi) 表示 A 的最后一个元素），取中间位置（用 m 表示）的元素（用 A(m) 表示），与 sv 比较： 这种搜索算法的核心是：每次取搜索区域的中间元素，然后根据比较结果决定下一步行动。因为每次的搜索区域都缩小一半，因此称为二分（或折半）搜索。这个搜索算法之所以可行，是因为搜索区域事先经过排序，也就是说，A 是一个有序区间，如果 sv > A(m)，那要查找的元素如果存在的话，肯定位于 A 的后半截，反之亦然。 说完了核心算法，再来看 LOOKUP 函数的一般格式（我们的 MYLOOKUP 也采用相同的格式）： 
关于各个参数的意义，请参见上一篇文章，此处不再赘述。 下面是我们的 MyLookup 函数的基本框架： 
函数的三个参数中，lookup_value 声明为 ByVal 的变体类型，以保持 lookup_value 的多义性：既可以是常量值，也可以是单元格。lookup_vector 和 result_vector 因为是 Excel 工作表中的区域，所以声明为 ByRef 的 Range 类型，而且 result_vector 是可选参数（用 optional 关键字指明）。 有了这些准备，下面贴出函数 MyLookup 的实现代码。 
图1 MyLookup 函数实现代码 在代码中，首先取得 lookup_value 的值。如果 lookup_value 的类型是 Excel Range，则表示是一个单元格对象，所以要通过该对象的 Value 属性获取单元格的值：lookup_value.Value。否则，表示 lookup_value 是一个常量值。 接下来，判断要搜索的区域是行向量（row vector）还是列向量（column vector）：如果行数大于列数，则表示是一个列向量，否则，则是行向量。因为行向量和列向量的处理不同，因此要有此判断。 然后，将搜索区域（Range）对象赋给动态数组 A，此赋值语句完成了 Excel Range 对象向二维数组的转换。如果 lookup_vector 是一个列向量，则 A 的下标只有第一维（即行）在变化，行向量正相反，是第二维（即列）在变化。 下面就根据 isColVector 的值分别处理： 此时，搜索区域 lookup_vector 的位置已找到，接下来，根据是否提供了 result_vector 参数，程序分为两种情况处理： 下面是内部函数 BinSearchForCol 的实现代码： 
图2 BinSearchForCol 的代码 这段代码比较简单，只有一点需要说明：如果经过二分查找，没有找到与 v 相等的元素，则选择最接近 v 的元素。这里，“最接近”的意思是：元素的值与 v 的值之差的绝对值最小。这一点与 Excel 的 LOOKUP 不同，LOOKUP 返回小于 v 的元素中的最大值。 下面通过具体例子来说明。 例1. MyLookup 与 LOOKUP 的对比 
图3 MyLookup 与 LOOKUP 的对比 从图中可以看出，对于 E1 单元格中的 5，E2 和 E3 给出的结果是不同的。E2 使用的是 =LOOKUP(E1, A2:A6, B2:B6)，E3 使用的是 =MyLookup(E1, A2:A6, B2:B6)。 注：本例使用的区域 A2:A6 就是一个列向量（column vector）。 我觉得，对于这种情况，MyLookup 的实现逻辑是比较合理的。 如果你也有这样的需求，而 Excel 的搜索函数（LOOKUP 系列）不能满足需求，则实现自己的搜索函数就是合理而必要的。本文提供了一个思路，供参考。 附：Excel/VBA 错误代码列表 
相关阅读 Excel 函数与公式 -- 内容搜索（一）
sv = A(m)：表示搜索目标已找到，成功返回； sv > A(m)：说明搜索目标位于 A 的后半截，令 low = m + 1，此时 low 和 hi 组成新的搜索区域，继续搜索； sv < A(m)：说明搜索目标位于 A 的前半截，令 hi = m - 1，此时 low 和 hi 组成新的搜索区域，继续搜索； low > hi：表示搜索完毕，未找到搜索目标。
isColVector = True：列向量，对于二维数组 A（i, j) 来说，j 是不变的，查找 A 中元素，就是找到相应的下标 i。 如果查找的值 v 小于 A 中的最小值，则引发错误 xlErrNA； 如果 v 大于 A 中的最大值，则取最大值的下标； 否则，调用内部函数 BinSearchForCol(v, A, j) 查找与 v 对应的元素下标 i。 isColVector = False：行向量，对于二维数组 A(i, j) 来说，i 不变，查找 A 中元素，就是找到相应的下标 j。 如果查找的值 v 小于 A 中的最小值，则引发错误 xlErrNA； 如果 v 大于 A 中的最大值，则取最大值的下标； 否则，调用内部函数 BinSearchForRow(v, A, i) 查找与 v 对应的元素下标 j。
如果 result_vector 参数未提供，此时 result_vector Is Missing Or result_vector Is Nothing，直接返回搜索区域中的值： lookup_vector.Cells(i, j).Value 如果用户提供了 result_vector 参数，则返回结果区域中的值： result_vector.Cells(i, j).Value