錦子老師

在上個月，為大家介紹了關於Vlookup接班人Xlookup函數，一方面驚歎於Xlookup強大的查找功能，另一方面也擔心自已只能遠觀而不能使用。畢竟Office 365的用戶並不是每台電腦皆裝。

那怎麼辦？下面為大家編寫了一個比Xlookup更強大的查找函數，它就是Wlookup函數。

建立自訂函數-WLOOKUP

Wlookup函數要想在你的活頁簿中可以使用，需要按下面的步驟操作建立。

按ALT+F11打開VBA視窗。

點取「插入 > 模組」指令，新增Module 1模組。

把下面的代碼輸入到右側的空白區域中。

Function Wlookup(V, vY, vh, Optional m)

 Dim arr, arr1, arr2()

 Dim k As Integer

 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 arr(x, 1) = V Then

       Wlookup = arr1(x, 1)

       If IsMissing(m) Then

         Exit Function

       Else

        k = k + 1

        ReDim Preserve arr2(1 To k)

        arr2(k) = arr1(x, 1)

       End If

    End If

  Next x

  If m = 0 Then

    Wlookup = arr2(k)

  ElseIf m = -1 Then

   Wlookup = Join(arr2, ",")

  ElseIf m = -2 Then

   Wlookup = JS(V, vY, vh)

  Else

    Wlookup = arr2(m)

  End If

End Function

Function JS(J1, R1, R2) '取接近值

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

       JS = Jarr2(x, 1)

       Exit Function

  End If

Next x

End Function

點取「檔案 > 儲存活頁簿1」指令。

在【另存新檔】對話方塊，點取「存檔類型」下拉方塊右方▼鈕，選擇「Excel啟用巨集的活頁簿」項目。

點取「檔案名稱」下拉方塊，輸入檔案名稱，本例為WLOOKUP。

點取「儲存」鈕，以後這個活頁簿中就可以正常使用Wlookup函數了

按ALT+F4鍵，關閉VBA視窗回到VLOOKUP活頁簿視窗。

WLOOKUP函數

【語法】

Wlookup(查找內容，查找值範圍，傳回值範圍，查找模式)

【參數說明】

查找內容：查找的值。

查找值範圍：在該區域/陣列中查找。

傳回值範圍：根據在第2個參數中的查找結果，傳回該陣列中對應位置的值。

查找模式：

【實例演練】

1、查找第1個符合條件的值

點取B14儲存格輸入公式：=Wlookup(A14,A2:A11,C2:C11)

2、從右向左查找

點取B14儲存格輸入公式：=Wlookup(A14,B2:B11,A2:A11)

3、按列上下查找

點取A6儲存格輸入公式：=Wlookup(A6,A1:E1,A2:E2)

4、多條件查找

點取B14儲存格輸入公式：=Wlookup(A14&B14,A2:A11&B2:B11,D2:D11) 由於是陣列公式故必須按Ctrl+Shift+Enter鍵完成輸入。

【注意】

多條件查找只需要用&連接即可。

5、查找第N個符合條件的值

=Wlookup(A14,B2:B11,C2:C11, N)

如查找第2個

點取B14儲存格輸入公式：=Wlookup(A14,B2:B11,C2:C11, 2)

【注意】

最後一個參數是正整數時，查找傳回對應第N個符合條件的值。

如果是多列查找第N個，也只需用&連接即可，同4。

6、查找最後一個

點取B14儲存格輸入公式：=Wlookup(A14,B2:B11,C2:C11,0)

【注意】

第4個參數為0時，查找最後一個

7、一對多查找

Wlookup可以同時傳回所有符合條件的結果，並用逗號連接。

點取B14儲存格輸入公式：=Wlookup(A14,B2:B11,C2:C11,-1)

【注意】

第4個參數為-1時，為一對多查找

8、區間查找

點取E2儲存格輸入公式：=Wlookup(A14,A2:A11,B2:B11,-2)

【注意】

當第4個參數為-2時，可以實現區間匹配查找。

9、擴展應用：篩選功能

點取B14儲存格輸入公式：

=IFERROR(Wlookup($A$14,$B$2:$B$11,C$2:C$11,ROW()-13),"")

再將公式複製到B15:B16儲存格。

【注意】

因為Wlookup可以傳回第N個值，所以這裡用ROW函數就可以逐個傳回所有值。

以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

部落格相關範例