excel2016如何使用IF函數(shù)（excel2007 if函數(shù)怎么用）
2023-02-24
網(wǎng)站首頁
手機(jī)版
更新時(shí)間：2023-02-22 00:33:58作者：佚名
給普通用戶的數(shù)據(jù)查詢系統(tǒng)，制作一個(gè)能按各種項(xiàng)目隨意查詢的親和界面是必要的。巧用VLOOKUP和OFFSET函數(shù)，就能實(shí)現(xiàn)。
面向大眾的員工記錄、產(chǎn)品記錄、合同記錄、學(xué)生成績(jī)列表記錄等經(jīng)常要查詢的記錄表，一般需要制作一個(gè)查詢界面，以便于通過輸入員工號(hào)、姓名、合同號(hào)、產(chǎn)品型號(hào)等簡(jiǎn)單文本來快速查詢出所需記錄內(nèi)容。在Excel2010中通常大家都會(huì)使用VLOOKUP函數(shù)制作查詢界面，不過VLOOKUP只能以記錄表中的首列為依據(jù)進(jìn)行查詢，而在實(shí)際使用中由于已知的查詢條件不同往往需要隨時(shí)選擇不同列進(jìn)行查詢。以員工記錄來說，除了按員工編號(hào)進(jìn)行查詢外，有時(shí)也需要按姓名、身份證號(hào)碼、聯(lián)系電話進(jìn)行查詢。那么要如何才能做到按任選列進(jìn)行查詢呢?在此就以員工記錄表的查詢?yōu)槔齺斫榻B兩種方法。
一、查詢界面設(shè)置
不管用哪種方法實(shí)現(xiàn)，查詢界面總是一樣的，我們就先統(tǒng)一介紹一下查詢界面的設(shè)置吧。
用Excel2010打開“員工記錄”工作表，新建一個(gè)“查詢”工作表并按需要設(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ù)有效性”窗口中單擊“允許”的下拉列表選擇“序列”，并輸入來源為“=員工記錄!1:1”即記錄工作表的標(biāo)題行(圖1)，確定完成設(shè)置。這樣我們不僅能方便地從A2的下拉列表中選擇要查詢的記錄列標(biāo)題，還可有效避免因在A2中輸入不存在的列標(biāo)題出現(xiàn)的查詢錯(cuò)誤。設(shè)置好后先在A2選擇輸入一個(gè)列標(biāo)題“姓名”，并輸入一個(gè)正確姓名，以免后面輸入公式時(shí)顯示#N/A錯(cuò)誤。
再來選中B7右擊選擇“設(shè)置單元格格式”，在“數(shù)字”選項(xiàng)卡中選擇“文本”格式，以確保能正常顯示身份證號(hào)碼。同樣對(duì)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)任選列查詢效果，操作比較簡(jiǎn)單，不會(huì)影響到原人員記錄表布局。
切換到“員工記錄”工作表，選中所有數(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單元格中都輸入這個(gè)公式，不過要把公式中最后那個(gè)0順次改成1、2、3……11以分別顯示相應(yīng)列的內(nèi)容。
OK，現(xiàn)在你只要在“查詢”工作表中選中A2單元格，單擊其后的下拉按鈕從下拉列表中選擇要查詢的列標(biāo)題為“聯(lián)系電話”，再輸入查詢內(nèi)容“13605076742”，即可查詢到聯(lián)絡(luò)電話是13605076742的陳桂鑫個(gè)人記錄(圖3)。
注：若要查詢的是全數(shù)字的身份證號(hào)，輸入時(shí)必需在證號(hào)前面加一個(gè)半角的單引號(hào)，例如“'350621197602232010”，這樣身份證號(hào)才能正常顯示查詢。否則輸入的身份證號(hào)不能正常顯示將無法查詢出結(jié)果。不要事先把B2單元格數(shù)值設(shè)置為文本格式，雖然設(shè)為文本格式也能顯示身份證號(hào)，但會(huì)讓輸入的電話號(hào)、編號(hào)、日期等數(shù)值變成文本，導(dǎo)致輸入電話、編號(hào)、日期查詢時(shí)出錯(cuò)。