一. 前言
注意不是vlookup, 是lookup.
碰到一个这样的问题, 下面动图的函数实现原理:
(图源: LOOKUP函数10种经典用法 新手必看)
这个函数并不难理解, 比较大的坑点, 在于其查询内容是需要经过排序的.
注意这里的排序, 这个点类似于pandas中的loc在定位不到数据时(这个功能在pandas上正逐步被废弃), 假如数据不是经过排序, 其返回的内容是异常的.
相关内容见, NoteBook/Pandas-loc的执行逻辑.ipynb at main - Kyouichirou/NoteBook (github.com)
假如不注意到文档的排序要求, 上面出现的各种奇怪内容, 会让人完全摸不着路数.
二. 语法
LOOKUP(lookup_value, lookup_vector, [result_vector])
LOOKUP 函数向量形式语法具有以下参数:
-
lookup_value 必需. LOOKUP 在第一个向量中搜索的值. *Lookup_value* 可以是数字 文本 逻辑值 名称或对值的引用.
-
lookup_vector 必需. 只包含一行或一列的区域. *lookup_vector* 中的值可以是文本 数字或逻辑值.
重要: lookup_vector* 中的值必须按升序排列*: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; 否则 LOOKUP 可能无法返回正确的值. 文本不区分大小写.
-
result_vector 可选. 只包含一行或一列的区域. *result_vector* 参数必须与 *lookup_vector* 参数大小相同. 其大小必须相同.
2.1 备注
- 如果 LOOKUP 函数找不到 *lookup_value* 则该函数会与 *lookup_vector* 中小于或等于 *lookup_value* 的最大值进行匹配.
- 如果 *lookup_value* 小于 *lookup_vector* 中的最小值 则 LOOKUP 会返回 #N/A 错误值.
LOOKUP 的数组形式与 HLOOKUP 和 VLOOKUP 函数非常相似. 区别在于: HLOOKUP 在第一行中搜索 *lookup_value* 的值 VLOOKUP 在第一列中搜索 而 LOOKUP 根据数组维度进行搜索.
-
如果数组包含宽度比高度大的区域( 列数多于行数) LOOKUP 会在第一行中搜索 *lookup_value* 的值.
-
如果数组是正方的或者高度大于宽度( 行数多于列数) LOOKUP 会在第一列中进行搜索.
-
使用 HLOOKUP 和 VLOOKUP 函数 您可以通过索引以向下或遍历的方式搜索 但是 LOOKUP 始终选择行或列中的最后一个值.
重要: 数组中的值必须按升序排列: ..., -2, -1, 0, 1, 2,
三. 使用
| 1pcs | 1 |
|---|---|
| 3个 | 3 |
| 50双 | 50 |
| 5000对 | 5000 |
来看看动图中的例子:
=-LOOKUP(1,-LEFT(A2,ROW($1:$5)))
对于excel中, 难以理解的公式(嵌套公式), 只需要拆开就没什么难以理解的.
# -, 这个符号的存在目的在于将数字提取出来, 假如不是数字的内容, 将#value!
=-LEFT(A5,ROW($1:$5))
row(), 函数只是负责一个任务, 就是传入一个[1,2,3,4,....]的数组给left函数, 返回left函数的数字部分的内容, 前面的负号作用在于区别开数字.
这意味着row(), 只需要拖动从第一行开始, 到最够大的行号即可(从1开始, 任意大小, 主要能够满足字符串最大长度即可).
因为这里的=-LEFT(A5,ROW($1:$5)), 逐个返回数据已经经过排序
# 已经暗含数据是经过排序的了
# 降序排列
-5
-50
-500
-5000
#VALUE!
=-LOOKUP(1,-LEFT(A2,ROW($1:$5)))
# 查找1, 找到1, 找到1, 返回的是1, 找不到1,
# 这里的场景, -LEFT(A2,ROW($1:$5))返回的全是负数
# 这里就涉及到lookup的函数找不到返回值的逻辑问题了
If the LOOKUP function can't find the *lookup_value*, the function matches the largest value in *lookup_vector* that is less than or equal to *lookup_value*.
如果 LOOKUP 函数找不到 *lookup_value* 则该函数会与 *lookup_vector* 中小于或等于 *lookup_value* 的最大值进行匹配.
这个逻辑, 是不是说找不到, 就返回最为接近的?
With the HLOOKUP and VLOOKUP functions, you can index down or across, but LOOKUP always selects the last value in the row or column.
使用 HLOOKUP 和 VLOOKUP 函数 您可以通过索引以向下或遍历的方式搜索 但是 LOOKUP 始终选择行或列中的最后一个值.
这个逻辑, 找不到, 返回最后一个值?
重要: *lookup_vector* 中的值必须按升序排列
假如不是升序, 降序的呢? 返回逻辑是?
微软这个坑货在这里挖的几个坑.
An array is a collection of values in rows and columns (like a table) that you want to search. For example, if you want to search columns A and B, down to row 6. LOOKUP will return the nearest match. To use the array form, your data must be sorted.
为什么在这个位置, your data must be sorted, 不进行加粗
=LOOKUP(5, L2:L5, L2:L5)
=LOOKUP(5, O2:O5, O2:O5)
# 其执行逻辑, 第一个查询值, 但是需要注意不能比查找区域的最小值还小, 否则在找不到值时出现错误.
10 -1 -1 3
3 2
2 3
-1 10
# 降序, 升序两种方式下, 返回的内容的差异
当查找的 5 不在目标时, 降序的列返回的是最后的值, 即 -1, 当满足上述升序这一要求的时候, 返回的是最为接近的数据 , 即 3.
好了, 既然搞明白了, 这个基本逻辑, 那么上面的公式, 就好理解了.
=-LOOKUP(1,-LEFT(A2,ROW($1:$5)))
# 在这里的场景之下, 1, 是不可能出现在-LEFT(A2,ROW($1:$5)
# 即返回的内容一定是最后的行的数据, 即最小的值, 转换为正数, 即最大
3.1 小结
将上面的问题进行更复杂的变化, 数字位置不再是开始的位置, 而可能是前中后都有(小数点也有).
| item | number |
|---|---|
| pcs11 | 11 |
| 3个 | 3 |
| 50双 | 50 |
| 5000对 | 5000 |
| 只有20.5只 | 20.5 |
实际上, 原理还是很简单
= MAX(IFERROR(ABS(MID(D2,ROW($1:$5),LEN(D2)-IF(ISNUMBER(-RIGHT(D2,1)),0,ROW($1:$5)))), 0))
- ROW()
- ISNUMBER()
- RIGHT
- LEN
- ABS
- IFERROR
- MAX
不用lookup(对数组排序暂时没什么好想法, 读者可自行思考, 能否实现), 使用max函数也能做到 , 其基本思路, 无非如此.
# 第一步判断最后一个文本是否为数字
RIGHT(D2,1) => 得到的是文本 => -RIGHT(D2,1) 暗含转换数字的意思
IF(ISNUMBER(-RIGHT(D2,1))
# 注意, 不然这里的数字判断会出问题
# 假如最后一位不是数字, 即, 逐段截取, 假如是数字, 整个文本返回
# 根据上面的内容对文本进行逐个截取
MID(D2,ROW($1:$5),LEN(D2)-IF(ISNUMBER(-RIGHT(D2,1)),0,ROW($1:$5)))
# mid函数, 文本, 起始位置, 长度
# 还是老套路, abs在这里的目的在于将文本和数字区分开来
ABS(MID(D2,ROW($1:$5),LEN(D2)-IF(ISNUMBER(-RIGHT(D2,1)),0,ROW($1:$5))))
# 将非数字部分剔除掉
IFERROR(ABS(MID(D2,ROW($1:$5),LEN(D2)-IF(ISNUMBER(-RIGHT(D2,1)),0,ROW($1:$5)))), 0)
# 最终取出长度(最大的数字)
MAX(IFERROR(ABS(MID(D2,ROW($1:$5),LEN(D2)-IF(ISNUMBER(-RIGHT(D2,1)),0,ROW($1:$5)))), 0))
excel函数很好玩, 各种函数组合之后貌似能够实现很酷炫(复杂)的功能, 但是完全没有必要, 学习切勿陷入钻研"茴"字的各种写法当中去, 层层嵌套的函数看似很高深莫测而已, 逐层解开, 其变数也多如此.
上面的公式还可以进行精简或者变化.
=MAX(IFERROR( ABS(MID(D6, {1;2;3;4;5},LEN(D6)-IF(ISNUMBER(-RIGHT(D6,1)),0,ROW($1:$5)))), 0))
row($1:$5) => {1;2;3;4;5}, 二者是等价的
# 大括号, => 数组, 即可以表示行, 也能表示列
{1, 5} => 1, 5
{1;5} =>
1
5
excel函数为了方便绝大部分的群体的使用, 在设计上是很费心思, 尽可能做到简洁易用, 没有必要为了复杂而复杂, 上面的内容, 换成正则表达式, 只是基本的入门问题.
过度复杂的嵌套函数不仅运行缓慢, 而且理解起来困难, 徒增维护成本(那怕是自己写的, 当忘记了细节, 想快速定位问题, 维护也是费劲).
瑞士军刀可以砍竹子, 但是瑞士军刀设计目标并不是砍竹子.
vba中可以引用vbs的正则组件, 在vba中也能使用正则表达式(写个function, 在表格中即可使用).
