查找和引用函數

XLOOKUP函數的 使用方法

發布時間:2021-07-29 難度:初級

本教程適用版本:WPS Office For Windows 個人版 點此下載

此函數需更新至WPS Office最新版本使用

WPS Office官網:https://platform.wps.cn/


XLOOKUP函數是一個查找函數。

它的語法是XLOOKUP(查找值,查找數組,返回數組,未找到值,匹配模式,搜索模式)


中插頁_01.jpg


微信圖片_20210809150042.png

一、縱向查找

以此表格為例,若我們想查找小鄭的分數。

單擊「公式」選項卡-「插入函數」按鈕,找到XLOOKUP函數。

在「查找值」中輸入小鄭姓名單元格E13;

「查找數組」中輸入需要查找的數組姓名列;

「返回數組」中輸入將要返回的分數列;

整個公式的含義是,使用XLOOKUP函數,查找小鄭在成績單的位置并返回對應的分數。


2.gif



二、橫向查找

VLOOKUP函數不能橫向查找,此時就可以使用XLOOKUP函數。

以此表格為例,若我們想查找小周的分數。

在「查找值」中輸入小周姓名單元格B7,「查找數組」中輸入需要查找的數組姓名行

「返回數組」中輸入將要返回的分數行。

點擊確定,就可以橫向查找小周的分數了。


2.2.gif


三、反向查找

VLOOKUP函數不能反向查找,此時就可以使用XLOOKUP函數。

若我們想查找學號1103是哪位同學。

在「查找值」中輸入學號單元格;「查找數組」中輸入需要查找的學號列;「返回數組」中輸入將要返回的姓名列,點擊確定,就可以反向找到學號1103的同學是哪位了。


5.gif


四、整行查找

XLOOKUP函數可以查找整行數據。若我們想查找小王同學的學號、考試成績、

首先選中G4和H4需要填寫數據的單元格區域、

在「查找值」中輸入小王單元格;

「查找數組」中輸入需要查找的姓名列;「返回數組」中輸入將要返回的學號與成績列。

最后使用Ctrl+Shift+Enter鍵,使用數組填充的方式,將學號與考試成績批量填充到單元格區域中。

3.gif


五、整列查找

XLOOKUP函數可以查找整列數據,若我們想查找這四位同學的成績。

首先選中G3至G6需要填寫數據的單元格區域。

在「查找值」中輸入四位同學的單元格區域;「查找數組」中輸入需要查找的姓名列;「返回數組」中輸入將要返回的成績列。

最后使用Ctrl+Shift+Enter鍵,使用數組填充的方式,就可以將成績批量填充到單元格區域中。

4.gif

六、倒序查找

XLOOKUP函數可以根據查找值在返回數組中倒著查找。

演示文稿1_01.jpg

以此表格為例,若想知道蔡秋水同學最后一次的鉛球成績。

在「查找值」中輸入查找名字;「查找數組」中輸入需要查找的姓名列;「返回數組」中輸入將要返回的分數列;在「搜索模式」中輸入參數對應的參數。

「搜索模式」參數如圖所示,因為我們想要查找最后一次成績,所以選擇倒序查找搜索模式,輸入-1。

點擊確定,就可以得出蔡秋水同學最后一次的跳高成績。

6.gif


七、多條件查找

XLOOKUP函數支持多條件查找,想要查找高一(1)班參加跳高項目的同學名字。

在「查找值」中輸入查找條件,高一(1)班&跳高,并用&將兩個條件連接。

「查找數組」中輸入需要查找班級列和項目列,并用&將兩個查找區域連接。

「返回數組」中輸入將要返回的姓名列。

就可以找到高一(1)班參加跳高項目的同學名字了。

7.gif


八、通配符查找

XLOOKUP函數可以使用通配符進行查找,若我們想查找1班李同學的報名項目。

在「查找值」中輸入*李*&*1*,*是通配符的一種符號,代表任意字符。

關于通配符的更多用法可以在WPS學堂里搜索學習。

「查找數組」中輸入姓名列和班級列,「返回數組」中輸入將要返回的項目列。

在「匹配模式」中輸入參數對應的參數。

中插頁_02.jpg

「匹配模式」參數如圖所示,因為我們想要通過通配符查找,所以輸入參數2。

點擊確定,就可以得出李同學的報名項目。

8.gif



九、交叉行列查找

當遇到表格行列過多,無法精準的判斷返回條件的區域,可以使用XLOOKUP函數行列交叉查找。

如我們先知道徐傳民同學在保齡球項目中,第6次的成績。

輸入公式=XLOOKUP,在查找值中輸入“徐傳民”單元格;在查找區域中輸入次數區域。

在返回數組中,應該填寫需要返回的行或列的區域,但是由于數據過多,無法判斷返回數組的區域位置,可以再嵌套一層XLOOKUP函數,輸入XLOOKUP(E12,C2:L2,C3:L9)。

意思是用XLOOKUP函數查找6次在次數行的位置,并返回對應的分數區域。

這樣就可以查找到徐傳民在保齡球項目中,第6球的分數了。

9.gif


十、匹配模式

XLOOKUP函數語法中的「匹配模式」可以幫助我們更好的進行表格查找。

「匹配模式」的參數如表所示:下面就給大家演示如何使用匹配模式,匹配最小項。

以保齡球分數表格為例,那如何根據評分規則,對分數進行評判呢。

選中需要填寫的單元格區域,輸入公式=XLOOKUP

「查找值」中輸入總分區域列;

「查找數組」中輸入評分規則的分數區域列;「返回數組」中輸入要返回的等級列。

在「匹配模式」中輸入參數對應的參數。

中插頁_02.jpg

「匹配模式」參數如圖所示,因為我們想要匹配最小項,所以輸入參數-1。

最后使用數組快捷鍵Ctrl+Shift+Enter批量填充到單元格區域中。

10.gif


十一、查找不到返回指定值

當使用VLOOKUP函數進行查找時,當表格中不包含查找值會返回#N/A。

而XLOOKUP函數可以直接返回為指定內容。

以此表格為例,若我們想查找王安妮的報名項目。

在「查找值」中輸入需要查找條件;「查找數組」中輸入需要查找的姓名列;「返回數組」中輸入將要返回的項目列;「未找到值」中輸入未找到值時顯示的內容。

這樣,當報名表中沒有找到王安妮的報名信息,就會返回指定的文本內容了。

11.gif



97无码免费人妻超级碰碰碰碰