給普通用戶的數(shù)據(jù)查詢系統(tǒng)，制作一個能按各種項目隨意查詢的親和界面是必要的。巧用VLOOKUP和OFFSET函數(shù)，就能實現(xiàn)。

面向大眾的員工記錄、產(chǎn)品記錄、合同記錄、學生成績列表記錄等經(jīng)常要查詢的記錄表，一般需要制作一個查詢界面，以便于通過輸入員工號、姓名、合同號、產(chǎn)品型號等簡單文本來快速查詢出所需記錄內(nèi)容。在Excel2010中通常大家都會使用VLOOKUP函數(shù)制作查詢界面，不過VLOOKUP只能以記錄表中的首列為依據(jù)進行查詢，而在實際使用中由于已知的查詢條件不同往往需要隨時選擇不同列進行查詢。以員工記錄來說，除了按員工編號進行查詢外，有時也需要按姓名、身份證號碼、聯(lián)系電話進行查詢。那么要如何才能做到按任選列進行查詢呢?在此就以員工記錄表的查詢?yōu)槔齺斫榻B兩種方法。

一、查詢界面設置

不管用哪種方法實現(xiàn)，查詢界面總是一樣的，我們就先統(tǒng)一介紹一下查詢界面的設置吧。

用Excel2010打開“員工記錄”工作表，新建一個“查詢”工作表并按需要設計好查詢界面，在此我們設計在B2單元格輸入查詢關(guān)鍵詞，A2單元格則用于輸入要查詢的列標題，查詢結(jié)果則顯示在A4:D10單元格區(qū)域。選中A2單元格，切換到“數(shù)據(jù)”選項卡，單擊“數(shù)據(jù)有效性”。在“數(shù)據(jù)有效性”窗口中單擊“允許”的下拉列表選擇“序列”，并輸入來源為“=員工記錄!1:1”即記錄工作表的標題行(圖1)，確定完成設置。這樣我們不僅能方便地從A2的下拉列表中選擇要查詢的記錄列標題，還可有效避免因在A2中輸入不存在的列標題出現(xiàn)的查詢錯誤。設置好后先在A2選擇輸入一個列標題“姓名”，并輸入一個正確姓名，以免后面輸入公式時顯示#N/A錯誤。

再來選中B7右擊選擇“設置單元格格式”，在“數(shù)字”選項卡中選擇“文本”格式，以確保能正常顯示身份證號碼。同樣對D5、D6也要分別設置相應的日期才能顯示為正常的日期。其它有特殊格式要求的單元格都得逐一設置過以確保正確顯示查詢結(jié)果。

二、實現(xiàn)任選列查詢

在Excel中用VLOOKUP和OFFSET函數(shù)都能輕易實現(xiàn)任選列查詢。在此分別介紹一下兩種函數(shù)的實現(xiàn)方法，實際操作中大家只要任選一種即可。

方法一、OFFSET函數(shù)

用OFFSET函數(shù)需要先在員工記錄表中為各列數(shù)據(jù)定義名稱后，方可實現(xiàn)任選列查詢效果，操作比較簡單，不會影響到原人員記錄表布局。

切換到“員工記錄”工作表，選中所有數(shù)據(jù)列(A:L)，在“公式”選項卡的“定義的名稱”組中單擊“根據(jù)所選內(nèi)容創(chuàng)建”。在“以選定區(qū)域創(chuàng)建名稱”窗口中只選中“首行”復選項(圖2)，單擊確定即可把各列分別按列標題定義名稱。切換到“查詢”工作表，選中B4單元格輸入公式=OFFSET(記錄!$A$1,MATCH($B$2,INDIRECT($A$2),0),0)。同樣在B4:B10、D4:D8單元格中都輸入這個公式，不過要把公式中最后那個0順次改成1、2、3……11以分別顯示相應列的內(nèi)容。

OK，現(xiàn)在你只要在“查詢”工作表中選中A2單元格，單擊其后的下拉按鈕從下拉列表中選擇要查詢的列標題為“聯(lián)系電話”，再輸入查詢內(nèi)容“13605076742”，即可查詢到聯(lián)絡電話是13605076742的陳桂鑫個人記錄(圖3)。

注：若要查詢的是全數(shù)字的身份證號，輸入時必需在證號前面加一個半角的單引號，例如“350621197602232010”，這樣身份證號才能正常顯示查詢。否則輸入的身份證號不能正常顯示將無法查詢出結(jié)果。不要事先把B2單元格數(shù)值設置為文本格式，雖然設為文本格式也能顯示身份證號，但會讓輸入的電話號、編號、日期等數(shù)值變成文本，導致輸入電話、編號、日期查詢時出錯。