Excel如何建立可選項(xiàng)目的查詢系統(tǒng)？對(duì)于普通用戶的數(shù)據(jù)查詢系統(tǒng)來(lái)說(shuō)，需要制作一個(gè)可以根據(jù)各種項(xiàng)目隨意查詢的親和界面。它可以通過(guò)巧妙地使用VLOOKUP和OFFSET函數(shù)來(lái)實(shí)現(xiàn)。
一般情況下，需要對(duì)員工記錄、產(chǎn)品記錄、合同記錄、學(xué)生成績(jī)列表記錄等經(jīng)常查詢的記錄做一個(gè)查詢界面，通過(guò)輸入員工編號(hào)、姓名、合同編號(hào)、產(chǎn)品型號(hào)等簡(jiǎn)單文本，即可快速查詢到需要的記錄。在Excel2010中，大家通常使用VLOOKUP函數(shù)來(lái)做查詢接口，但是VLOOKUP只能根據(jù)記錄表中的第一列進(jìn)行查詢。在實(shí)際使用中，由于已知的查詢條件不同，經(jīng)常需要隨時(shí)選擇不同的列進(jìn)行查詢。就員工記錄而言，除了通過(guò)員工編號(hào)進(jìn)行查詢外，有時(shí)還需要通過(guò)姓名、身份證號(hào)碼和聯(lián)系電話進(jìn)行查詢。那么我們?nèi)绾瓮ㄟ^(guò)可選列進(jìn)行查詢呢？本文以員工記錄表的查詢?yōu)槔榻B了兩種方法。
一、查詢界面設(shè)置
無(wú)論使用哪種方法，查詢界面總是相同的。讓我們介紹一下查詢界面的設(shè)置。
使用Excel2010打開(kāi)“員工記錄”工作表，創(chuàng)建新的“查詢”工作表，并根據(jù)需要設(shè)計(jì)查詢界面。這里我們?cè)O(shè)計(jì)在B2單元格中輸入查詢關(guān)鍵字，A2單元格用于輸入要查詢的列標(biāo)題，查詢結(jié)果顯示在A4:D10單元格區(qū)域。選擇單元格A2，切換到數(shù)據(jù)選項(xiàng)卡，然后單擊數(shù)據(jù)有效性。在數(shù)據(jù)有效性窗口中，點(diǎn)擊“允許”下拉列表，選擇“系列”，輸入來(lái)源為“=員工記錄！1:1”是記錄工作表的標(biāo)題行(圖1)，并確認(rèn)設(shè)置。這樣，不僅可以方便地從A2的下拉列表中選擇要查詢的記錄列標(biāo)題，而且可以有效地避免在A2中輸入不存在的列標(biāo)題而導(dǎo)致的查詢錯(cuò)誤。設(shè)置后，在A2中選擇并輸入一列標(biāo)題“名稱”，并輸入正確的名稱，以免以后輸入公式時(shí)出現(xiàn)#不適用錯(cuò)誤。
然后選擇B7，右鍵選擇“設(shè)置單元格格式”，并在“數(shù)字”選項(xiàng)卡中選擇“文本”格式，以確保身份證號(hào)碼可以正常顯示。同樣，應(yīng)該為D5和D6設(shè)置相應(yīng)的日期，然后才能將其顯示為正常日期。其他具有特殊格式要求的單元格必須逐個(gè)設(shè)置，以確保查詢結(jié)果的正確顯示。
二、實(shí)現(xiàn)任選列查詢
在Excel中使用VLOOKUP和OFFSET函數(shù)可以方便地實(shí)現(xiàn)任意列的查詢。這里，我們將分別介紹這兩個(gè)函數(shù)的實(shí)現(xiàn)方法。實(shí)際上，我們只需要選擇一個(gè)。
方法一、OFFSET函數(shù)
使用OFFSET函數(shù)，您需要在員工記錄表中定義每列數(shù)據(jù)的名稱，然后才能實(shí)現(xiàn)可選列的查詢效果。操作相對(duì)簡(jiǎn)單，不會(huì)影響原始員工記錄表的布局。
切換到“員工記錄”工作表，選擇所有數(shù)據(jù)列(A:L)，然后在“公式”選項(xiàng)卡的“定義的名稱”組中單擊“根據(jù)選擇創(chuàng)建”。在“使用選定區(qū)域創(chuàng)建名稱”窗口中，僅選擇“第一行”選項(xiàng)(圖2)，然后單擊“確定”根據(jù)列標(biāo)題定義每列的名稱。切換到“查詢”工作表，選擇B4單元格并輸入公式=偏移量(記錄！$ A1，MATCH($ B2，INDIRECTIVE($ A2)，0)，0 .在單元格B4:B10和D4:D8中輸入此公式，但將公式中的最后一個(gè)0更改為1、2、3 … 11，以便分別顯示相應(yīng)列的內(nèi)容。
好了，現(xiàn)在只需要在“查詢”工作表中選擇單元格A2，點(diǎn)擊其后面的下拉按鈕，從下拉列表中選擇列標(biāo)題“聯(lián)系電話”，然后輸入查詢內(nèi)容“13605076742”，就可以查詢陳桂新的個(gè)人記錄，聯(lián)系電話為13605076742(圖3)。
注意：如果要查詢?nèi)珨?shù)字身份證號(hào)，必須在身份證號(hào)前加一個(gè)半角單引號(hào)，如“‘350621197602232010”，這樣身份證號(hào)才能正常顯示查詢。否則，無(wú)法正常顯示輸入的身份證號(hào)碼，也無(wú)法查詢結(jié)果。不要預(yù)先以文本格式設(shè)置B2單元格的值。雖然身份證號(hào)碼可以以文本格式顯示，但它會(huì)使輸入的電話號(hào)碼、序列號(hào)、日期和其他值變成文本，導(dǎo)致輸入電話號(hào)碼、序列號(hào)和日期時(shí)出錯(cuò)。
