close

2652

自訂函數-強大的WLOOKUP函數

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

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

建立自訂函數-WLOOKUP

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

ALT+F11打開VBA視窗。

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

image

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

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

image

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

image

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

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

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

image

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

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

WLOOKUP函數

【語法】

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

【參數說明】

查找內容:查找的值。

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

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

查找模式:

-2

是區間查找

0

查找最後一個

-1

是一對多查找

N

查找第N個符合條件的值

【實例演練】

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

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

image

2、從右向左查找

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

image

3、按列上下查找

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

image

4、多條件查找

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

image

【注意】

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

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

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

如查找第2

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

image

【注意】

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

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

6、查找最後一個

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

image

【注意】

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

7、一對多查找

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

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

image

【注意】

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

8、區間查找

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

image

【注意】

當第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

2650讓自動校正輸入統一的文字

2021.11.02

2649Excel中自訂梯形面積函數

2021.11.01

2648把資料徹底隱藏起來

2021.10.31

2644關於歐元的轉換

2021.10.30

2643Excel中攝影功能的妙用

2021.10.29

2642計算儲存格中的總字數

2021.10.28

2640建立文字型長條圖

2021.10.27

2641用特殊符號補齊字元數

2021.10.26

2630使用Excel數組公式自製日曆

2021.10.25

2639Excel找出計算結果的位置

2021.10.24

2638如果要將一個數值除指定的數,並能自動分配在不同格子上要怎麼做...

2021.10.23

2632欄位字串替換

2021.10.22

2629Excel查表對照值

2021.10.21

2628VBA 將字串改為函數

2021.10.20

2627相對位址

2021.10.19

2626絕對與相對的儲存格位址

2021.10.18

2624Excel秘密功能-顯示儲存格公式

2021.10.17

2623Excel秘密功能-固定小數位數

 


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()