編按：

Hello各位小伙伴們，Excel中的查找、引用函數(shù)公式，不只是簡單的查找數(shù)據(jù)，而是能讓數(shù)據(jù)具有更強(qiáng)的可移植性，也能讓公式具有更強(qiáng)大的數(shù)據(jù)處理能力。對于HR來說，公司員工信息太多，查找起來不方便？今天我們就用常用的幾個(gè)查找引用函數(shù)做一個(gè)員工信息查詢表。下面這個(gè)員工信息查詢表如果你能學(xué)會(huì)，那么你對于查找與引用函數(shù)的學(xué)習(xí)就進(jìn)入了高級層次了。

1、案例來源

我們公司擁有很多員工，為方便公司管理，老板讓我制作一個(gè)表格，可以選擇根據(jù)員工的工號、姓名、手機(jī)號三個(gè)具有身份屬性的字段，找到對應(yīng)的員工的所有信息。

2、案例分析

首先，自主選擇查詢的目標(biāo)字段（工號、姓名、手機(jī)號）。這就要求我們要首先進(jìn)行數(shù)據(jù)有效性設(shè)置。數(shù)據(jù)有效性是在工作表中輸入數(shù)據(jù)時(shí)，對于符合條件的數(shù)據(jù)，允許輸入，對不符合條件的數(shù)據(jù)禁止輸入。

然后再根據(jù)輸入的值查找并返回對應(yīng)的內(nèi)容。在表格中查找目標(biāo)字段，需要公式做多次查找和判斷。可以使用INDEX（）函數(shù)返回目標(biāo)所在行的內(nèi)容，使用MATCH（）函數(shù)取得行號，使用CHOOSE（）函數(shù)和MATCH（）函數(shù)嵌套來獲得外層MATCH（）函數(shù)的查找的區(qū)域，最終得到需要的結(jié)果。

3、具體步驟

① 建立基礎(chǔ)數(shù)據(jù)表。合并A2:G2單元格，在單元格中輸入表格標(biāo)題“員工資料查詢表”；合并A3:B3單元格，在單元格中輸入“查詢條件：”；合并C3:E3單元格，在F3單元格中輸入“查詢內(nèi)容”。

在A4:G4單元格分別輸入“工號”、“姓名”、“性別”、“手機(jī)號”、“入職時(shí)間”、“籍貫”、“工資”，并美化單元格。

② 選中“查詢條件”后面的合并單元格，單擊【數(shù)據(jù)】選項(xiàng)卡“數(shù)據(jù)工具”選項(xiàng)組中的“數(shù)據(jù)驗(yàn)證”按鈕，彈出“數(shù)據(jù)驗(yàn)證”對話框。

在 “允許”的下拉列表中選擇“序列”。在“來源”，文本框中輸入“工號,姓名,手機(jī)號”，單擊“確定”按鈕。

返回工作表中，單擊設(shè)置了有效驗(yàn)證條件的單元格，在單元格右側(cè)會(huì)出現(xiàn)一個(gè)下拉按鈕，單擊該按鈕可選擇指定序列內(nèi)容。注意，在輸入序列內(nèi)容時(shí)，以半角的逗號隔開不同的內(nèi)容項(xiàng)。

③ 選擇A5:G5單元格區(qū)域，輸入公式“=IFERROR(INDEX(員工檔案!$A$2:$G$85,MATCH(資料查詢!$G$3,CHOOSE(MATCH(資料查詢!$C$3,{"工號","姓名","手機(jī)號"},0),員工檔案!$A:$A,員工檔案!$B:$B,員工檔案!$D:$D),0)-1,),"-")”摁Ctrl+Shift+Enter鍵確認(rèn)輸入。

④ 選擇“查詢條件”后面的合并單元格，單擊右側(cè)的下拉按鈕，選擇三個(gè)選項(xiàng)中的任意一個(gè)，如選擇“姓名”選項(xiàng)。選擇G3單元格，輸入任意要查詢的姓名，摁Enter鍵確認(rèn)輸入。

4、公式說明

在A5:G5單元格區(qū)域的公式很復(fù)雜，咱們來詳細(xì)說明下?？傮w來說，公式“=IFERROR(INDEX(員工檔案!$A$2:$G$85,MATCH(資料查詢!$G$3,CHOOSE(MATCH(資料查詢!$C$3,{"工號","姓名","手機(jī)號"},0),員工檔案!$A:$A,員工檔案!$B:$B,員工檔案!$D:$D),0)-1,),"-")”的計(jì)算過程可以分為三個(gè)步驟。

首先，通過MATCH（）函數(shù)查找“查詢條件”后面合并單元格中的值所在數(shù)組{"工號","姓名","手機(jī)號"}中的位置序號，并根據(jù)該序號利用CHOOSE（）函數(shù)返回3個(gè)不同的單元格區(qū)域。

其次，通過外層的MATCH（）函數(shù)，在返回的單元格區(qū)域中查找G3單元格的值，并返回其在單元格區(qū)域中的序號上一行，因?yàn)榈谝恍袨闃?biāo)題，并將此序號作為INDEX（）函數(shù)的參數(shù)，借以返回該行號對應(yīng)的整行數(shù)據(jù)。

最后通過IFERROR（）函數(shù)檢測公式是否產(chǎn)生錯(cuò)誤，如果是，則返回文本“-”，表示未找到符合條件的記錄。否則返回查找的內(nèi)容。

未找到符合條件的記錄的原因可能有兩點(diǎn)：（1）用戶輸入的內(nèi)容在記錄中不存在。（2）用戶輸入的內(nèi)容不在C3單元格選擇的列。

5、拓展練習(xí)

由于用戶必須要選擇正確的查詢字段，并輸入該字段對應(yīng)的內(nèi)容才可以查詢到正確的結(jié)果，現(xiàn)在老板想要輸入3個(gè)字段中的任意內(nèi)容，無須手動(dòng)選擇要查詢的字段即可獲得正確的值，我們也可以通IFERROR（）函數(shù)的嵌套來完成。

在這之前可以先定義一些單元格名稱，以方便公式使用。大家可能不太了解單元格的名稱定義應(yīng)該是什么？實(shí)際上Excel表格中，每一個(gè)單元格都具有一個(gè)默認(rèn)的名字，命名規(guī)則是列標(biāo)和行標(biāo)，比如我們所說的：A1，表示的就是第一行第一列的單元格。

我們可以為我們的單元格重新命名，甚至是為單元格區(qū)域進(jìn)行名稱的定義，可以使用定義的名稱進(jìn)行導(dǎo)航和代替公式中的單元格地址，使工作表更容易理解和更新。

在“員工檔案”工作表中同時(shí)選擇A、B和D這三列，在【公式】選項(xiàng)卡中單擊“根據(jù)所選內(nèi)容創(chuàng)建”按鈕，在打開的對話框中選中“首行”復(fù)選框，單擊“確定”按鈕，創(chuàng)建3個(gè)單元格名稱。

切換到“信息查詢”工作表中，選擇A5:G5單元格區(qū)域，在編輯欄輸入公式“=IFERROR(INDEX(員工檔案!$A$2:$G$85,IFERROR(MATCH($G$3,工號,0),IFERROR(MATCH($G$3,姓名,0),(MATCH($G$3,手機(jī)號,0)))),),"-")”，摁Ctrl+Shift+Enter鍵確認(rèn)輸入。在G3單元格中輸入工號、姓名、手機(jī)號的任意內(nèi)容，即可得到所需要的結(jié)果。

OK，今天春風(fēng)通過工作中遇到的實(shí)際案例，為小伙伴們介紹了一張信息查詢表，同時(shí)也接觸了MATCH()函數(shù)、CHOOSE()函數(shù)、INDEX()函數(shù)，我們下期再見~

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

將excel中的數(shù)據(jù)按照指定數(shù)量進(jìn)行重復(fù)，非得用VBA才能實(shí)現(xiàn)嗎？

如何在特定位置批量插入空行等12種實(shí)用辦公技巧

4種刪除excel重復(fù)值的小妙招，速收藏

Power Query的數(shù)據(jù)替換技巧比Excel函數(shù)更萬能！

版權(quán)申明：

本文作者賦春風(fēng)；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。