当 range_lookup 为 TRUE 但 table_array 未排序时,VLOOKUP 逻辑

当 range_lookup 为 TRUE 但 table_array 未排序时,VLOOKUP 逻辑

range_lookup = TRUEExcel 的 VLOOKUP 提示告诉我们,只有当已排序时才使用table_array。但是,有人知道当range_lookup = TRUEtable_array 是未分类

例如,我有一张从 A1 单元格开始的表格,如下所示:

A
1 1000
2 1000
3 3.9
4 1000
5 3.9
6 3.9
7 3.9
8 3.9

使用公式:

= VLOOKUP(3.9, A1:A8, 1, TRUE)

我得到的结果为#N/A

将上表中的任何 1000 改为 3.9,都会让我的公式输出3.9

扩展 table_array 以包含更多 3.9,并相应地修改公式,得到3.9而不是#N/A

A
1 1000
2 1000
3 3.9
4 1000
5 3.9
6 3.9
7 3.9
8 3.9
9 3.9

公式:

= VLOOKUP(3.9, A1:A9, 1, TRUE)

但是,将此扩展表的第 4 和第 5 个单元格中的 1000 和 3.9 交换会使公式#N/A再次输出。

A
1 1000
2 1000
3 3.9
4 3.9
5 1000
6 3.9
7 3.9
8 3.9
9 3.9

当我打算使用 VLOOKUP 从未排序的表中顶部选取接近 3.9 的值时,遇到了这个问题。

range_lookup = TRUE但是,当未排序时,VLOOKUP 的这种行为table_array似乎没有任何意义。

有人对此有任何见解吗?

答案1

事实证明,当 range_lookup = TRUE 时,VLOOKUP 使用的算法是二进制搜索算法,而不是线性搜索算法。这解释了我在文章中描述的不断变化的 #N/A 和 3.9 输出。

这种二分搜索算法的使用似乎没有得到很好的记录,即使在微软自己的 VLOOKUP 支持页面上也是如此。

我知道发布链接不是最佳做法,但我遇到的最好的解释是这个视频

引用另一个解释Reddit 上的 rnelsonee

是的,有序数据是正常默认模式的必要条件。VLOOKUP 和 MATCH 使用二分搜索,将范围减半,查看值,然后转到 25% 的位置,或 75% 的位置(取决于 50% 的值是 + 还是 - 查找)。它会一直这样做,直到找到一个大于查找值的数字,但前一个值 <= 查找值。这非常快,考虑到第一个 LOOKUP 函数是在 1979 年左右编写的,这很重要。现在速度不再是一个大问题,但向后兼容性使 VLOOKUP 继续使用二分搜索。

例如,VLOOKUP 在此处从 110 开始,依次为 30、40、50,然后看到 110>50,因此它报告 50。现在检查一下 - 我们劫持了 30,因此 VLOOKUP 认为所有低值都在第 1 行和第 2 行。但是,如果我们将 110 设置为 60,那么它甚至永远都看不到整个下半部分。

此外,在查找时,文本和数据区域都相同 - 数字在前,然后是特殊字符,然后是字母顺序。

相关内容