更高效的搜索式下拉菜單,你一定要懂!
?
作者:小螃蟹來源:部落窩教育發(fā)布時間:2018-06-15 10:17:14點擊:9074
小編有話說:小編第一眼看到這篇文章的時候,還以為就是很普通的一個數(shù)據(jù)驗證,沒想到竟然是像百度搜索一樣的下拉菜單,可以只輸入關(guān)鍵詞查找,簡直太神奇啦!當你數(shù)據(jù)很多的時候,這個方法簡直就是神器呀!快往下看吧!
工作中大家經(jīng)常會用Excel數(shù)據(jù)驗證制作下拉菜單,規(guī)范數(shù)據(jù)輸入、節(jié)省數(shù)據(jù)輸入時間。但是當下拉菜單的數(shù)據(jù)選項很多的時候,就會出現(xiàn)數(shù)據(jù)難找的困擾。比如下圖,下拉菜單中的數(shù)據(jù)選項太多,通過拖動旁邊的滾動條來“找出”需要的數(shù)據(jù)項很費時,直接降低了我們的工作效率。
那有沒有辦法解決這種選項多數(shù)據(jù)難找的問題呢?
有,我的方法就是搜索式下拉菜單!
就如同在百度上搜索,輸入關(guān)鍵字后會彈出下拉菜單顯示包含關(guān)鍵字的搜索題目供選擇。我們要做的效果就是在單元格中輸入關(guān)鍵字,然后點擊下拉菜單,菜單中只顯示包含關(guān)鍵字的數(shù)據(jù),從而提高數(shù)據(jù)錄入效率。
下圖是我們本次教程的數(shù)據(jù)源。注意,必須對數(shù)據(jù)源按關(guān)鍵字排序,升序降序都可以。
選擇E2:E6單元格區(qū)域點擊【數(shù)據(jù)】選項卡,單擊【數(shù)據(jù)驗證】,在彈出的“數(shù)據(jù)驗證”對話框的“設(shè)置”選卡中設(shè)置驗證條件為“序列”。
在來源中輸入公式:
=OFFSET($A$1,MATCH(E2&"*",$A$2:$A$17,0),0,COUNTIF($A$2:$A$17,E2&"*"),1)
公式說明:
這里主要使用OFFSET函數(shù)返回包含關(guān)鍵字的數(shù)據(jù)。OFFSET函數(shù)的作用是根據(jù)指定的參照單元格,通過給定的偏移量返回新的引用數(shù)據(jù)。
OFFSET(參照系,行偏移量,列偏移量,新引用區(qū)域的行數(shù),新引用區(qū)域的列數(shù))
1.第一參數(shù)引用了A1單元格作為參照系。
2.第二參數(shù)用MATCH(E2&"*",$A$2:$A$17,0)確定行偏移量。MATCH為查找函數(shù),根據(jù)查找值E2&”*”(*號是通配符,代表任意不確定字符)在$A$2:$A$17區(qū)域中查找,查找方式為0(精確查找)。當E2單元格輸入關(guān)鍵字時,該函數(shù)將查找出包含關(guān)鍵字的數(shù)據(jù)在$A$2:$A$17區(qū)域中第一次出現(xiàn)的位置。
3.第三參數(shù)為0,因為我們的數(shù)據(jù)源只有A列一列,所以列偏移量為0,表示不偏移。簡單來說,就是offset函數(shù)以A1單元格為參照,不橫向偏移,只向下偏移。
4.第四參數(shù) COUNTIF($A$2:$A$17,E2&"*")統(tǒng)計A2-A17區(qū)域內(nèi)滿足條件E2&"*",也就是包含E2單元格內(nèi)的關(guān)鍵詞的單元格出現(xiàn)的次數(shù),也就是最終在數(shù)據(jù)驗證下拉菜單中一共會出現(xiàn)幾行。
5.第五參數(shù)為新引用區(qū)域的列數(shù),因為只有A列一列,所以為1。
如下所示:offset函數(shù)以A1為參照系向下查找,通過match函數(shù)在A2-A17中找到包含E2關(guān)鍵字“碎花”的數(shù)據(jù)第一次出現(xiàn)的位置是從A2開始的第10行,再通過countif函數(shù)找到總共有3行,最終在下拉菜單中返回這3行1列的數(shù)據(jù)。
再回到教程當中。我們在“數(shù)據(jù)驗證”對話框“來源”中輸入公式后點擊“確定”。但是當我們在E2單元格輸入關(guān)鍵字“碎花”后,會立馬彈出警告框,這是為什么呢?
原因是我們輸入關(guān)鍵字“碎花”后,由公式得到的下拉菜單中沒有只含“碎花”兩個字的選項,所以會報錯。
如下圖所示,我們需要再次選擇E2:E6單元格區(qū)域點擊【數(shù)據(jù)】選項卡【數(shù)據(jù)驗證】按鈕進入到“數(shù)據(jù)驗證”對話框中,在“出錯警告”選卡中取消勾選【輸入無效數(shù)據(jù)時顯示出錯警告】選項,然后點擊“確定”即可。
最后在F2單元格輸入公式=IFERROR(VLOOKUP(E2,$A$2:$B$17,2,0),"") 。使用VLOOKUP函數(shù)在A2-B17單元格區(qū)域中查找E2數(shù)值所在位置,并返回對應(yīng)的第2列(也就是B列)庫存,0代表精確查找。當查找不到返回錯誤值時用IFERROR函數(shù)將錯誤值轉(zhuǎn)為空。
至此,搜索式下拉菜單就制作完成啦!
搜索式下拉菜單可以成倍提高數(shù)據(jù)錄入效率,尤其是下拉菜單選項很多的時候特別高效。同學(xué),趕緊打開你的excel去操作一下吧。
本文配套的練習(xí)課件請加入QQ群:316492581下載。
如果您因工作所需使用到Excel,不妨關(guān)注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學(xué)習(xí)。
相關(guān)推薦:
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!