range_lookup = TRUE
Excel 的 VLOOKUP 提示告诉我们,只有当已排序时才使用table_array
。但是,有人知道当range_lookup = TRUE
table_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,那么它甚至永远都看不到整个下半部分。
此外,在查找时,文本和数据区域都相同 - 数字在前,然后是特殊字符,然后是字母顺序。