★ 編按 ★ 這個函數本身是有一點難度的,一共有六個參數,這固然使函數具備了強大功能,但也讓一些初學者望而卻步。今天就通過一些我們平時常見的案例,來和大家一起了解XLOOKUP的基本用法。 HLOOKUP函數的基本結構是: =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]) 翻譯成大白話就是: =XLOOKUP(查找值,查找范圍,結果范圍,[找不到時顯示的值],[匹配方式],[查詢模式]) 在這六個參數中,前三個是必須的,后面三個根據自己的需要選擇使用。 示例1:常規(guī)匹配(對標LOOKUP) 按照姓名匹配入職日期,公式為=XLOOKUP(G2,B:B,C:C)。 這種用法和LOOKUP的使用結構非常像,但是有本質的區(qū)別。LOOKUP函數要求查找范圍必須升序排列,而XLOOKUP則無此限制。就這個問題使用LOOKUP的結果如圖所示: 關于LOOKUP的用法不在本文討論范圍,請參閱公眾號之前的相關教程。 同事打死不愿說,到底這Excel函數有什么玄機?我看完才懂! 再比如按姓名匹配員工ID,這在VLOOKUP的用法中叫反向查找,XLOOKUP還是一樣的用法,公式為=XLOOKUP(G2,B:B,A:A)。 這是給大家介紹的第一種用法,XLOOKUP(查找值,查找范圍,結果范圍),沒有順序要求,沒有方向要求,這體驗一下子就超過了VLOOKUP和LOOKUP。 示例2:一次查找多個值(數組用法) 如果XLOOKUP的第一參數選擇一個單元格區(qū)域,可以對應得到多個結果。在Excel365中更容易看到這種數組自動擴展的效果。 在此提醒那些用VLOOKUP時,第一參數習慣選一列的朋友,如果你這樣用XLOOKUP的話,電腦能卡死! XLOOKUP的這種特性非常重要,比如要統計某幾個人的崗位津貼總和,就可以直接用公式=SUM(XLOOKUP(F2:F5,A:A,D:D))得到結果,這個公式非365用戶需要按Ctrl+Shift+Enter三鍵。 示例3:第四參數的妙用 來看這個例子,按照姓名找對應的成績,當出現數據源中不存在的姓名是,結果為#N/A。 通常遇到這種情況我們的第一反應是外面嵌套一個IFERROR函數,實際上XLOOKUP的第四參數就可以取代IFERROR函數了。 公式修改為=XLOOKUP(D2,A:A,B:B,"姓名有誤")。 對于這個參數,我想大家都很容易掌握,畢竟使用一個參數就能少嵌套一個函數,這是非常好的體驗。 示例4:多樣的匹配方式 XLOOKUP提供了四種匹配方式。 從函數自帶的提示不難看出四種匹配方式的意思。0或者省略是精確匹配,之前的例子都是這種方式。-1是精確匹配或下一個較小的項,例如按照成績匹配等級,可以使用公式=XLOOKUP(B2,F:F,G:G,"",-1)。 這個公式的意思是在F列中找52,找不到的時候就找小于52的一個值,也就是0,最后得到的結果就是0所對應的等級。如果就這樣看的話,似乎用LOOKUP更簡單。 但是LOOKUP要求查找范圍升序,假如數據變成這樣,結果就全錯了。 可以看出XLOOKUP函數完全不受順序的影響,LOOKUP則多了一些限制。 如果匹配方式用1的話則正好相反,找不到要找的值時,則會找較大的一個值。例如公式=XLOOKUP(B2,F:F,G:G,"",1)就會得到這樣的結果。 從這個例子可以看出,在做區(qū)間匹配時,-1對應下限值,1對應上限值。 示例5:使用通配符 有時候在匹配數據時會用到通配符,例如根據單位檢查匹配對應的業(yè)務人員,公式為=XLOOKUP("*"&D2&"*",A:A,B:B,"無對應人員",2)。 XLOOKUP函數默認不支持通配符的,如果要用通配符,第五參數必須填2,這也是XLOOKUP函數的一個特殊之處。 示例6:多種查詢方式 查詢方式和匹配方式是不一樣的概念,XLOOKUP提供了四種查詢方式: 查詢方式1是從上向下,默認的也是這種方式。查詢方式-1是從下向上,如果要查找的值是唯一的,那么這兩種方式得到的結果是一樣的,但是當要查找的值有多個的時,兩種方式的區(qū)別就出現了。 例如公式=XLOOKUP(E2,B:B,C:C,"",0,1)得到的就是每個人的首日銷量。 而公式=XLOOKUP(E2,B:B,C:C,"",0,-1)得到的則是每個人的末日銷量。 這兩種查詢方式都是遍歷法原理,只是查詢方向的區(qū)別,而查詢方式2和-2,則用的是二分法原理,區(qū)別就是二分法的時候默認升序還是降序。關于遍歷法和二分法原理,理論性較強,可以參考之前的這篇教程,本文就不贅述了。久等!VLOOKUP坐字法合并單元格查找的秘密終于破了! 示例7:橫向匹配和多列匹配 橫向查找之前多是用HLOOKUP函數來解決,現在也可以用XLOOKUP,只要查找范圍和結果范圍是橫向的就行。例如公式=XLOOKUP(B6,1:1,2:2)就是橫向查找的結果。 在沒有XLOOKUP的時候,我們使用VLOOKUP做多列匹配往往要用到COLUMN函數,現在就方便了,只要將結果區(qū)域選擇多列即可,注意這種用法只能對連續(xù)的多列匹配適用。 以上就是XLOOKUP函數的基本用法,功能確實很多也很強大,希望有條件的伙伴能夠趕緊練起來,也許在你使用的過程中有什么心得也歡迎留言和大家分享。 閱讀推薦 關注我們,發(fā)現更多Excel優(yōu)質教程 靠一只“豬”一秒拆分上千個工作表?!同事的騷操作看呆我...... |
|