2652 |
自訂函數-強大的WLOOKUP函數 |
在上個月,為大家介紹了關於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個參數中的查找結果,傳回該陣列中對應位置的值。
查找模式:
-2 |
是區間查找 |
0 |
查找最後一個 |
-1 |
是一對多查找 |
N |
查找第N個符合條件的值 |
【實例演練】
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函數就可以逐個傳回所有值。
以上就是今天主要學習的知識點,希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~
部落格相關範例
2021.11.03 |
|
2021.11.02 |
|
2021.11.01 |
|
2021.10.31 |
|
2021.10.30 |
|
2021.10.29 |
|
2021.10.28 |
|
2021.10.27 |
|
2021.10.26 |
|
2021.10.25 |
|
2021.10.24 |
|
2021.10.23 |
|
2021.10.22 |
|
2021.10.21 |
|
2021.10.20 |
|
2021.10.19 |
|
2021.10.18 |
|
2021.10.17 |
留言列表