怎样当查找不到符合条件的数据时，屏蔽错误的结果为空白？

WYS67 · 发表于 2020-3-8 13:01

本帖最后由 WYS67 于 2020-3-8 18:36 编辑

LOOKX 修改.zip (36.13 KB, 下载次数: 2)

从网上下载了一个别的老师编写的自定义函数【上面附件的第一个工作表里有详细的参数设置、运算规则和代码】，几乎综合了LOOKUP、HLOOKUP、VLOOKUP的所有查找功能，很强大，使用起来非常方便！

只是在截图所示的《9.自定义筛选》工作表里，选定A14输入公式  =IFERROR(LOOKX($A$11,$B$2:$B$7,A$2:A$7,,ROW(A1)),"") ,右拉至C14，下拉至A20:C20时，发现了bug【详见上图黄色填充的A17:C20区域】，

按运算规则应该是：当查找不到符合条件的数据时，屏蔽错误的结果为空白！

所以我在公式外面套上了IFERROR，用于把A17:B17区域内显示的错误结果屏蔽为空白，不知是什么原因，A17:C20没有变成空白，仍然显示了明显错误的筛选结果。代码如下：

Function LOOKX(v, vY, vh, Optional m = 0, Optional n = 1) '匹配未完成
Dim arr, arr1, arr2()
Dim k As Integer
On Error Resume Next
If Len(v) = 0 Then v = 0
arr = vY
arr1 = vh
If UBound(arr1) = 1 Then
      arr1 = Application.Transpose(arr1)
      arr = Application.Transpose(arr)
End If
ReDim arr2(1 To 1)
For x = 1 To UBound(arr1)
      If m = 2 Then
         flg = arr(x, 1) Like v    '通配符匹配
      ElseIf m = 3 Then
         flg = InStr(arr(x, 1), v)  '包含匹配
      Else
         flg = (arr(x, 1) = v)    '精确匹配
      End If
      If flg And n = 1 Then
         If UBound(arr1, 2) > 1 Then
            LOOKX = arr1(x, 1)
            With Application.ThisCell
                  For j = 1 To UBound(arr1, 2) '自动填充
                     If .Offset(, j) = "" Then s = Null Else s = "*"
                        .Offset(, j).Replace s, arr1(x, j + 1)
                  Next j
            End With
         End If
         LOOKX = arr1(x, 1)
         Exit Function
      Else
         If arr(x, 1) = v Then
            k = k + 1
            ReDim Preserve arr2(1 To k)
            arr2(k) = arr1(x, 1)
         End If
      End If
Next x
If Abs(m) = 1 Then
      LOOKX = JS(v, vY, vh, m)
Else
      If n = 0 Then LOOKX = Join(arr2, ",")
      If n < 0 Then LOOKX = arr2(k)
      If n > 0 Then LOOKX = arr2(n)
End If
End Function

Private Function JS(J1, R1, R2, m) '取上下接近值
Dim Jarr1, Jarr2
Dim x
Jarr1 = R1
Jarr2 = R2
For x = 1 To UBound(Jarr1)
      If x + 1 > UBound(Jarr1) Then
         JS = Jarr2(x, 1)
         Exit Function
      ElseIf J1 >= Jarr1(x, 1) And J1 < Jarr1(x + 1, 1) Then
         If m = -1 Then JS = Jarr2(x, 1) Else JS = Jarr2(x + 1, 1)
         Exit Function
      End If
Next x
End Function

请老师们给予诊断，看看代码错在哪里了？怎样修改，才能使输入的公式，在查找不到符合条件的数据时，屏蔽错误的结果为空白？【最好把屏蔽为空的运算规则写进代码，以后输入公式时不用再外套IFERROR函数】

yjh_27 · 发表于 2020-3-8 15:12

参见附件。

yjh_27 · 发表于 2020-3-8 15:33

不改也行。因返回的不是错误，是空。所以IFERROR函数无效
空数值显示0；日期显示1900/1/0 设置零值不显示即可。

WYS67 · 发表于 2020-3-8 15:40

yjh_27 发表于 2020-3-8 15:12
参见附件。

If IsEmpty(LOOKX) Then LOOKX = ""
If IsError(LOOKX) Then LOOKX = ""

老师高明！谢谢了！

WYS67 · 发表于 2020-3-8 15:43

yjh_27 发表于 2020-3-8 15:33
不改也行。因返回的不是错误，是空。所以IFERROR函数无效
空数值显示0；日期显示1900/1/0 设置零值 ...

改了好。可以把不符合运算规则的结果屏蔽为空，看着干净！再次谢谢老师！

WYS67 · 发表于 2020-3-9 12:18

yjh_27 发表于 2020-3-8 15:12
参见附件。

LOOKX .zip (46.45 KB, 下载次数: 1)

老师：还是1楼那个代码，在区间查找【即第四参数指定为1或-1的情况下】时，发现当指定条件超出数据源数据的小大范围【如《区间查找 2》E列数据源的数据在6~252之间，第一参数指定为5，小于6；或者指定为253，大于252】时，I5:J5，J88，P5，O88的计算结果就会显示错误。

怎样修改代码才能使：当指定条件超出数据源数据的小大范围时，也能得出符合运算规则的结果【即模拟结果H5,H88,N5,N88】？

Function LOOKX(v, vY, vh, Optional m = 0, Optional n = 1) '匹配未完成
Dim arr, arr1, arr2()
Dim k As Integer
On Error Resume Next
If Len(v) = 0 Then v = 0
arr = vY
arr1 = vh
If UBound(arr1) = 1 Then
      arr1 = Application.Transpose(arr1)
      arr = Application.Transpose(arr)
End If
ReDim arr2(1 To 1)
For x = 1 To UBound(arr1)
      If m = 2 Then
         flg = arr(x, 1) Like v    '通配符匹配
      ElseIf m = 3 Then
         flg = InStr(arr(x, 1), v)  '包含匹配
      Else
         flg = (arr(x, 1) = v)    '精确匹配
      End If
      If flg And n = 1 Then
         If UBound(arr1, 2) > 1 Then
            LOOKX = arr1(x, 1)
            With Application.ThisCell
                  For j = 1 To UBound(arr1, 2) '自动填充
                     If .Offset(, j) = "" Then s = Null Else s = "*"
                        .Offset(, j).Replace s, arr1(x, j + 1)
                  Next j
            End With
         End If
         LOOKX = arr1(x, 1)
         If IsEmpty(LOOKX) Then LOOKX = ""
         If IsError(LOOKX) Then LOOKX = ""
         Exit Function
      Else
         If arr(x, 1) = v Then
            k = k + 1
            ReDim Preserve arr2(1 To k)
            arr2(k) = arr1(x, 1)
         End If
      End If
Next x
If Abs(m) = 1 Then
      LOOKX = JS(v, vY, vh, m)
Else
      If n = 0 Then LOOKX = Join(arr2, ",")
      If n < 0 Then LOOKX = arr2(k)
      If n > 0 Then LOOKX = arr2(n)
End If
If IsEmpty(LOOKX) Then LOOKX = ""
If IsError(LOOKX) Then LOOKX = ""
End Function

Private Function JS(J1, R1, R2, m) '取上下接近值
Dim Jarr1, Jarr2
Dim x
Jarr1 = R1
Jarr2 = R2
For x = 1 To UBound(Jarr1)
      If x + 1 > UBound(Jarr1) Then
         JS = Jarr2(x, 1)
         Exit Function
      ElseIf J1 >= Jarr1(x, 1) And J1 < Jarr1(x + 1, 1) Then
         If m = -1 Then JS = Jarr2(x, 1) Else JS = Jarr2(x + 1, 1)
         Exit Function
      End If
Next x
End Function

WYS67 · 发表于 2020-3-9 14:37

貌似6楼的代码，对一些影响计算结果准确性的意外情况估计得不足，导致屡现bug，6楼的区间查找，其运算规则是：
当第四参数指定为-1时，返回数据源里略小于指定条件的最大值；而当第四参数指定为1时，则返回数据源里略大于指定条件的最小值。

而6楼发现的错误则是：当指定条件超出数据源数据的最小、最大值【如《区间查找 2》E列数据源的数据在6~252之间，第一参数指定为5，小于6（即数据源最小值）；或者指定为253，大于252（即数据源最大值）】时，I5:J5，J88，P5，O88的计算结果就会显示有悖于运算规则的错误；

应该修改为：
1.公式忽略数据源【即第三参数】里的空白；
2.条件区域和数据区域可以是两【多】列，也可以是同一列（就如附件《请教查查找 2》里的公式那样，条件区域和数据区域都是E列）；3.当指定条件小于数据区域的最小值时，第四参数指定为-1时，其结果应该如N5那样显示空白；而第四参数指定为1时，其结果应该如H5那样显示数据区域的最小值；
3.当指定条件大于数据区域的最大值时，第四参数指定为-1时，其结果应该如N88那样显示数据区域的最大值；而第四参数指定为1时，其结果应该如H88那样显示空白。

请老师们按此规则修改6楼里的相关代码。

yjh_27 · 发表于 2020-3-9 15:17

查找区域要求升序，后面空格不合要求。其他已修正

WYS67 · 发表于 2020-3-9 15:45

yjh_27 发表于 2020-3-9 15:17
查找区域要求升序，后面空格不合要求。其他已修正

老师：两个疑问：
1.查找区域要求升序--指的是E列吧？不可以乱序吗？感觉乱序查找的适用性更广泛；
2.公式忽略数据区域【E列】的空单元格后，小于指定条件M88的254的最大值应该都是N88的【即数据区域的E98】252吧？怎么O88和P88显示的不一样？

半小时前，我已上传了更具实用性的运算规则【和上边差不多】，等审核通过后，敬请老师按新的运算规则修改相关代码。

WYS67 · 发表于 2020-3-9 15:49

yjh_27 发表于 2020-3-9 15:17
查找区域要求升序，后面空格不合要求。其他已修正

老师，7楼的运算规则已通过。再加一句：最好能让查找区域乱序执行查找。这个更有实用性，平时用起来更方便。

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

[已解决] 怎样当查找不到符合条件的数据时，屏蔽错误的结果为空白？

评分

评分

评分

最新热点 /1