[已解决]求教l一个lookup的公式

josonxu · 发表于 2014-2-11 20:54

969341591 发表于 2014-2-11 20:19
昨晚我的提问你给我的答复中有:"lookup 会自己默认第二参数{0;#DIV/0!;#DIV/0!;#DIV/0!;0;0} 是升序排 ...

首先  lookup这个函数  使用时  第二参数必须为升序排列    若不是升序排列  它会认为是升序排列这时就会使用二分法查找
即  lookup（1，｛0，0，0｝，｛1，2，4｝）查找1时  它会  先从二分位  第二个 0  开始查起 0比 1小那前面两个0就排除
就和组后一个0 比较虽然0<1  但这个这个0 是最后一个0 根据lookup在查找值无法被找到时  会返回小于等于查找值的最大的一个值    因为第二参数默认成升序  所以最大的值  就是最后一个0 然后对应 4    （这不是一点两点能讲清的，这牵扯二分法）

969341591 · 发表于 2014-2-11 21:22

josonxu 发表于 2014-2-11 20:54
首先 lookup这个函数使用时第二参数必须为升序排列若不是升序排列它会认为是升序排列这时 ...

非常感谢你的回复,我再悟悟.

josonxu · 发表于 2014-2-11 22:43

969341591 发表于 2014-2-11 21:22
非常感谢你的回复,我再悟悟.

香川群子 · 发表于 2014-2-12 21:59

josonxu 发表于 2014-2-10 21:04
lookup精确查找用法 0/ 是把B9:B12=B15 得到的false 必成错误值 true变成零 lookup查找会忽略 ...

该数组公式要点：

1. 首先 0/(数组判断式) → 得到一个【0或错误值】的数组结果。
  a. (数组判断式) → 得到一个【True 或 False】的数组结果;
  b. 0/【True 或 False】数组结果 → 【0/1=0  或 0/0=除以0错误值】→ 公式自动忽略错误值 →代表有效位置的【0】组成的数组

2. Lookup(1,【0数组】,【对应数组】)
  相当于在0数组中查询=1 → 始终找不到，于是返回最后一个非错误值的位置，即最后一个【有效0】位置；
  返回该位置对应的结果。

至于为什么是返回最后一个非错误值位置？
这么理解可能简单一些：
每次查找时，Lookup会和查询结果相比较，然后记住当前状态【大于、小于、等于】和位置。
如果是【大于】，就继续往后找，并不断更新成新的位置，直到数组结束。

而这就是本数组公式的状态：查询第1参数=1，始终比第2参数的【0】数组中的有效结果要大，1>0

可以到此结束了。

……
但是也可以略微探讨一下，如果第一参数不是=1，而是其它呢？

如果是【等于】呢？即第一参数=0
用二分法查找，直到第一个匹配位置时，再继续逐个往后检查直到【出现更大值、或出现错误值、或检查到数组末尾】

这个就比较复杂了……把公式中第1参数改为=0 就会出现此结果。（如果原始数据未经从小到大排序，则返回结果可能是非预期结果）
呵呵 =LOOKUP(,0/(B9:B28=B31),E9:E28)

……
如果是【小于】呢？即第一参数=-1或更小
也是用二分法查找，但由于第2参数是一个【0】数组，所以会找不到直到并且往上半句进行二分法直到检查到【0】数组的首位位置时，仍然找不大匹配的，
于是公式会返回#N/A错误而结束公式是 =LOOKUP(-1,0/(B9:B28=B31),E9:E28) 肯定返回=#N/A 错误结果。

……
如果是其它【大于0】的数，则结果和参数=1是一样的。

香川群子 · 发表于 2014-2-12 22:24

本帖最后由香川群子于 2014-2-12 22:25 编辑

969341591 发表于 2014-2-11 20:19
昨晚我的提问你给我的答复中有:"lookup 会自己默认第二参数{0;#DIV/0!;#DIV/0!;#DIV/0!;0;0} 是升序排 ...

本质上，Lookup不是默认第2参数会是从小到大升序排列的，而是：

【正确的第2参数用法，要求第2参数数组中所有元素必须是从小到大升序排列的，否则可能会返回非预期的错误结果】

原因我在上个帖子中略有介绍，是因为Lookup是在默认第2参数已经按升序排列以后，采用了及其高效的二分法匹配检查算法来查找比对。

其二分法详细规则如下：
1. 第一次从数组中间开始检查
2. 如果查询值(第1参数)和当前值相等，则：
逐个向后检查，直至出现【更大值、或错误值、或检查至数组末尾】时停止，并返回之前的最后一个相等值的位置。
3. 如果查询值大于当前值，则忽略上半区（按升序原则，此时上半区所有值都应该比查询值小所以不用检查了）
然后进入下半区（当前位置以后区域）、继续进行二分检查。（可以认为是递归或迭代检查）
4. 如果查询值小于当前值，则忽略下半区（按升序原则，此时下半区所有值都应该比查询值大所以不用检查了）
然后进入上半区（当前位置之前区域）、继续进行二分检查。（可以认为是递归或迭代检查）
5. 如果查询值始终小于当前值，则很快会递归至数组第1个位置，此时查询值仍小于当前值(第1个值)时，则函数返回错误结果
因为默认按升序排列时，如果查询值小于第1个值时，后面所有的值都会比查询值大，所以肯定没有匹配值，函数应该返回错误值。
6. 如果查询值始终大于当前值，则很快会递归至数组最后1个位置，此时查询值仍大于当前值(最后1个值)时，按规定函数返回最后一个有效位置。

这样的函数设计有这样的好处：便于归档

假设第2参数数组是： 0,10,100,1000,10000，那么：
查询23返回10
查询123返回100
查询3返回0
查询2345返回1000
查询98723返回10000
查询128346182634189364189也返回档位最大值10000

就是这个意思。

…………
至于，设计出第2参数并不按升序排列时，通过查询逻辑值转换的【0】数组匹配1，
然后利用这个Lookup二分法查询规则而返回最后一个【有效0】位置，
虽然非常巧妙，但确实不是当初微软工程师的有意设计。

呵呵。

香川群子 · 发表于 2014-2-12 22:32

下面代码，是我根据二分法规则写的二分法查询过程自定义函数代码，
可以通过VBE窗口 F8逐步观察，用来理解二分法规则的具体步骤。
（但应该没有很大的实际使用价值，因为Lookup函数已经够好了）

Sub VLCfm()
ReDim r$(25)
For i = 0 To 25
r(i) = Chr(97 + i) '生成26个字符对应的一维数组r
Next
ReDim b(25)
For i = 0 To 25
b(i) = VL(Chr(97 + i), r) '用我写的通用二分法检查比较自定义函数返回对应序号值
Next
MsgBox Join(b, vbCr)
End Sub
Function VL&(x$, r) '这是我根据二分法查询规则写的二分法查询过程代码
Dim h&, l&, m&, t&, q&, y$
m = UBound(r): h = m + 1: l = 0: t = l + (h - l) \ 2
Do
y = r(t)
If x < y Then
If t = 0 Then VL = -1: Exit Do
h = t: t = l + (h - l) \ 2
Else
If x = y Then
q = t
For t = t + 1 To h - 1
y = r(t): If x = y Then q = t Else Exit For
Next
VL = q: Exit Do
End If
If t = h - 1 Then VL = t: Exit Do
p = t: l = t: t = l + (h - l) \ 2
End If
Loop
End Function

复制代码

香川群子 · 发表于 2014-2-12 22:36

josonxu 发表于 2014-2-11 20:54
首先 lookup这个函数使用时第二参数必须为升序排列若不是升序排列它会认为是升序排列这时 ...

我想你应该能够理解我26楼的二分法检查自定义函数代码……呵呵。

969341591 · 发表于 2014-2-12 22:57

香川群子发表于 2014-2-12 21:59
该数组公式要点：

非常感谢您的翔实解答.

		自动登录	找回密码
密码			注册