妙用FILTER函數(shù)實現(xiàn)任意關(guān)鍵詞查詢數(shù)據(jù)
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2023-05-17 13:30:24點擊:1907
編按:
今天給大家分享用任意一個關(guān)鍵詞皆可查找數(shù)據(jù)的最簡單方法。只要輸入數(shù)據(jù)源存在的任意一個關(guān)鍵詞,即可得到符合條件的所有數(shù)據(jù)。利用該方法可以實現(xiàn)萬能查找,在庫存查詢、員工信息查詢、物流查詢等都用得上。
哈嘍,大家好。
今天來分享利用任意一個關(guān)鍵詞進(jìn)行數(shù)據(jù)查找的方法,如下圖員工信息表所示,能夠按照編號、部門、學(xué)歷、性別、姓名、婚姻狀況中的任意字符進(jìn)行查找。
譬如可以查找編號包括7的人,所有姓趙的人,所有部門是財務(wù)的人,所有大專學(xué)歷的人等等。
實現(xiàn)這種任意關(guān)鍵詞查找,并不需要很復(fù)雜的步驟,或者VBA代碼之類的,只需要一個公式即可。
下面就為大家詳細(xì)說明,為了便于理解,分成三個階段進(jìn)行解讀。
第一階段:關(guān)鍵字單列查找
關(guān)鍵字查找,意味條件為包含關(guān)系,就是查找包含關(guān)鍵字字符的數(shù)據(jù)。因此,通常需要借用SEARCH或者FIND函數(shù)來確定是否包含條件字符。
關(guān)鍵字單列查找,就是用關(guān)鍵字與某一列數(shù)據(jù)進(jìn)行包含判斷查找。
例如,只在姓名列數(shù)據(jù)中進(jìn)行查找關(guān)鍵字,公式為:
=FILTER(A:I,IFERROR(SEARCH(K2,C:C),),0)
SEARCH函數(shù)的作用是在一個文本值中查找另一個指定文本值(不區(qū)分大小寫)的位置,得到結(jié)果是一個數(shù)字。
例如:=SEARCH(K2,C2:C20)得到如圖結(jié)果,表示在范圍C2:C20的每個單元格都找一下K2的內(nèi)容(娜)是否存在,如果沒有返回錯誤值,如果有則返回娜在對應(yīng)單元格的位置(第幾個字)。
再添加一個IFFERROR函數(shù),將錯誤值變?yōu)?span>0(后面有用),公式為=IFERROR(SEARCH(K2,C2:C20),0)
FILTER的第二參數(shù)本來應(yīng)該是一個邏輯值(條件比較得到的就是邏輯值),但是在Excel中,邏輯值和數(shù)字之間有個對應(yīng)關(guān)系,0相當(dāng)于FALSE,非零數(shù)相當(dāng)于TRUE。因此SEARCH得到的非零值就相當(dāng)于符合查找條件的值。
所以公式=FILTER(A:I,IFERROR(SEARCH(K2,C:C),0))就能按照關(guān)鍵字進(jìn)行查找,這一點一定要想明白,假如就是想不明白的話,記下公式套路就行吧。
至此,我們實現(xiàn)了關(guān)鍵字單列查找的結(jié)果,距離最終目標(biāo)之差一步。
第二階段:關(guān)鍵字多列查找。
關(guān)鍵字多列查找,就是用關(guān)鍵字分別與多列數(shù)據(jù)進(jìn)行包含判斷查找,只要關(guān)鍵字被多列數(shù)據(jù)的任何一列包含,就會查找到相應(yīng)數(shù)據(jù)。因此各列的包含判斷是“或”關(guān)系,用加號來組合它們的判斷結(jié)果。
譬如:在上面姓名列查找的基礎(chǔ)上,我們增加部門列查找。
完整的公式為:=FILTER(A:I,IFERROR(SEARCH(K2,B:B),0)+IFERROR(SEARCH(K2,C:C),0),0)
如果還要按照其他列查找的話,只需要繼續(xù)加IFERROR和SEARCH這部分即可。
第三階段:解決條件為空等的查找讓公式更人性化
當(dāng)前公式,如果條件為空,會返回所有值;如果沒有符合條件的,返回是0。
所以需要調(diào)整公式,讓條件為空時返回空;讓沒有符合條件的,顯示為“無符合條件值”。
最終公式:
=IF(K2="","",FILTER(A:I,IFERROR(SEARCH(K2,A:A),0)+IFERROR(SEARCH(K2,B:B),0)+IFERROR(SEARCH(K2,C:C),0)+
IFERROR(SEARCH(K2,D:D),0)+IFERROR(SEARCH(K2,E:E),0)+IFERROR(SEARCH(K2,F:F),0),"無符合條件值"))
用FILTER函數(shù)實現(xiàn)任意關(guān)鍵字查找就介紹到這里。任意關(guān)鍵字查找中的條件關(guān)系是包含、或,如果條件是且關(guān)系,則公式不同。有興趣的可以查看文章《強(qiáng)大的篩選函數(shù)FILTER用法集》。
總結(jié):
如果表格復(fù)雜列數(shù)很多,用加號形式的或關(guān)系查找公式就會很長。這時,可以在表格最前列插入一個輔助列A列。然后利用公式把每行的值合并到該列中。查找時,只比較輔助列是否包含條件字符即可完成查找。譬如上方,合并后的查找公式=IF(L2="","",FILTER(B:J,IFERROR(SEARCH(L2,A:A),0),"無符合條件值")),簡潔了很多。
提示:
憑借任意一個關(guān)鍵詞查詢數(shù)據(jù)的優(yōu)點是操作簡單;缺點是不夠精準(zhǔn),尤其是在包含多列數(shù)字(含日期)的表格中用數(shù)字查詢,準(zhǔn)確度低。
本文配套的練習(xí)課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
UNIQUE函數(shù)的經(jīng)典用法!新手必看,秒殺萬金油公式!
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
版權(quán)申明:
本文作者老菜鳥;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(liá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單元格中的算式,四種求和方法請收好!