[PConline 應用]不久前,WPS官微發布了一條消息,說是自即日起WPS開始正式支持XLOOKUP函數。很多人就奇怪了,作為一款辦公軟件,增加個函數不是太正常了?這有啥可激動的?其實能讓WPS“激動”自然是有些道理,理由就是這個XLOOKUP實在太強了!

示例1. 反向查找  目的:通過“姓名“反查“工號“

公式:=XLOOKUP(G6,B:B,A:A)

日常工作中我們經常會遇到用姓名查工號的情況,由于原始數據中,“工號”字段通常是位于“姓名”之前,因此直接使用VLOOKUP肯定無法得到結果。通常的辦法,是借助IF函數建立一個虛擬數組”IF({1,0},B:B,A:A)”,將“工號”與“姓名”臨時對調一下,以滿足VLOOKUP的操作需求。不過它的問題就是,對于新手童鞋來說,這個數組太難理解了。

如果換作XLOOKUP呢?很簡單,直接輸入“=XLOOKUP(G6,B:B,A:A)”就行。整個語法基本參照了VLOOKUP的慣,先確定好要查找的內容(G6),然后告訴表格去哪里查找(B:B),最后返回對應列的結果就可以了(A:A)。相比之下,XLOOKUP的邏輯是不是就清晰多了!

示例2. 出錯處理  目的:當查詢無結果時,顯示“查無此人“

公式:=XLOOKUP(G6,B:B,A:A,"查無此人")

為了防止LOOKUP、VLOOKUP等函數意外出錯,我們通常會在函數外圍包裹一層IFERROR,用于手工控制出錯信息的顯示。不過這種做法一來會讓公式變長,二來也不怎么高效。而XLOOKUP的處理方法絕對是簡單粗暴,直接將出錯信息標在了函數里。高效的同時,也讓公式更加簡練,就像下面這樣:“=XLOOKUP(G6,B:B,A:A,"查無此人")”。

示例3. 批量化查詢  目的:通過“工號“查詢該員工所有信息

公式:=XLOOKUP(G8,A:A,B:E)

編寫搜索器時,會在原始數據中批量查詢所需的內容。通常有兩種解決方法,一是借助VLOOKUP手工確定要查詢的列,二是通過COLUMN函數配合VLOOKUP做一個半自動查詢器。

那么XLOOKUP有沒有更簡單的辦法呢?答案是有的,方法就是直接填寫“=XLOOKUP(G8,A:A,B:E)”。語法上依舊沿用了VLOOKUP的邏輯,先是確定好要查找的內容(G8),然后告訴表格去哪里查找(A:A),接下來返回B:E列里的對應信息即可。由于函數的“溢出效應”,相鄰幾個單元格(別、職務、部門)也會自動填好結果,連拖拽這一步都省去了。

示例4. 多條件查詢  目的:通過“姓名”和“別”兩組條件查詢員工信息

公式:=XLOOKUP(G7&H7,B:B&C:C,D:D)

現實中重名的情況并不少見,當一個條件無法確定一個人時,就要加載第二組條件。比如本例中,小編就使用了“姓名”+“別”的雙重條件驗證。對于此類需求,傳統的VLOOKUP需要借助IF函數生成一個虛擬數組。而在XLOOKUP之下,上述公式可以直接簡化為“=XLOOKUP(G7&H7,B:B&C:C,D:D)”。

示例5. 模糊查詢  目的:根據分值為每個人標注等級。

公式:=XLOOKUP(D2,$H$2:$H$5,$I$2:$I$5,,-1)

熟悉VLOOKUP的小伙伴,大多知道這個函數最后有一個“精確匹配FALSE”和“似匹配TRUE”的小參數。其中的“似匹配”,就是我們常說的模糊查找。通常來講,模糊查找主要用作區域數值的界定,比如90-100分為“優秀”、70-89分為“良好”,類似這樣的分數段篩選,就很適合使用模糊查找。不過它有一個前提,那就是數值源必須提前使用升序排列,否則無法得到準確結果。

而使用XLOOKUP就不用這么麻煩了,它的第五個參數(輸入公式時會有提示)直接提供了“0”、“-1”、“1”、“2”四種不同匹配條件。以本例使用的“-1”為例,它的含義就是當搜索結果達不到目標值499時,會自動向下查找(小于499)。正是借助這樣一個選項,我們就輕松配置出了一個業績等級設定表。

示例6. 橫向查找  目的:輸入產品名稱查詢該產品的銷量、銷售額、利潤、利潤率

公式:=XLOOKUP(B7,B1:E1,B2:E2)

在Excel中,除了縱向搜索的VLOOKUP外,還有一個支持橫向搜索的HLOOKUP。這兩組函數雖然作用不一,但語法卻基本相同。區別是一個在列中查找,一個在行中查找。而我們的XLOOKUP其實也集合了縱向和橫向兩種查詢機制,除了上面講到的縱向查詢外,你還可以通過變換查找區域來實現橫向搜索。具體效果,如上圖所示。

示例7. 搜索最后記錄  目的:快速查詢某商品的最新入庫價格

公式:=XLOOKUP(F4,B:B,C:C,,,-1)

很多出入庫表格,都需要查找最后一次出入記錄。這個看似簡單的要求,實現起來卻不容易。通常我們都是使用LOOKUP建立一個虛擬數組,然后再對其進行查找。但正如前面所言,這一類東東一來不適合新手理解,二來過多的數組函數對于系統能也是拖累。特別在一些大型表格中,頻繁地使用數組函數,會讓表格變得異常緩慢。

而XLOOKUP的加入,讓這個問題變得簡單多了。它的解決方法很簡單,直接用一個參數來搞定。依舊以上文為例,如果想查詢某商品的最一次入庫價格,只要在它的第6參數位中,輸入參數值“-1”。而返回的結果,正是該商品的最后一次入庫價。

寫在最后  怎么樣?看完上面這些案例,是不是有種豁然開朗的感覺?其實在日常使用中,XLOOKUP還有邏輯清晰、語句簡練等優勢。舉個最簡單例子,以往在使用VLOOKUP時,查找范圍后面的列數常常要我們手工去數,而XLOOKUP由于直接使用了列標作為返回列,因此也就省掉了這個步驟。同時由于XLOOKUP還是一個全能型選手,特別對于新手來說更加友好,再不用勞神記憶各種復雜的函數和數組,一個XLOOKUP統統就搞定了!