教大家Excel 如何制作任選項(xiàng)目的查詢系統(tǒng)

近日有關(guān)于Excel 如何制作任選項(xiàng)目的查詢系統(tǒng)的問題受到了很多網(wǎng)友們的關(guān)注，大多數(shù)網(wǎng)友都想要知道Excel 如何制作任選項(xiàng)目的查詢系統(tǒng)的具體情況，那么關(guān)于到Excel 如何制作任選項(xiàng)目的查詢系統(tǒng)的相關(guān)信息，小編也是在網(wǎng)上進(jìn)行了一系列的信息，那么接下來就由小編來給大家分享下小編所收集到與Excel 如何制作任選項(xiàng)目的查詢系統(tǒng)相關(guān)的信息吧(以下內(nèi)容來自于網(wǎng)絡(luò)非小編所寫，如有侵權(quán)請與站長聯(lián)系刪除)

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

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

一、查詢界面設(shè)置

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

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

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

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

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

方法一、OFFSET函數(shù)

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

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

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

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