我是【桃大喵學習記】,歡迎大家關註喲~,每天為你分享職場辦公軟體使用技巧幹貨!
——先發於微訊號:桃大喵學習記
今天跟大家分享的是一個Excel萬能尋找樣版的制作,如下圖所示,左側是員工資訊表格數據,有多個欄位資訊,我們透過查詢關鍵詞,只要右側表格任何欄位資訊包含這個關鍵詞,都會被自動篩選尋找出來。其實、只需使用一個公式組合即可實作,使用的組合公式化繁為簡,不但提高了可讀性,還能加速運算,讓我們的函式公式變得簡潔又優美!
一、主要函式介紹
要實作萬能查詢樣版的制作需要使用到下面這幾個主要的函式LET函式、SEARCH函式、IFERROR函式、FILTER函式等。下面先對這幾個函式進行簡單介紹。
1、LET函式介紹
功能: 將計算結果分配給名稱。可用於透過定義公式內的名稱來儲存中間計算結果和值。這些名稱僅在LET函式作用域內適用。
語法: =LET(名稱1,名稱1值,名稱2,名稱2值……,結果運算式)
第1參數名稱1:分配的第1個名稱
第2參數名稱1值:分配給第1個名稱的值
下面的參數依此類推
最後一個參數就是結果運算式。
例項:
公式:=LET(蘋果,10,價格,3.5,蘋果*價格)
結果是:35
解讀:
第一參數:蘋果,就是指定的第一個名稱
第二參數:10,函式會將這個10賦值給「蘋果」
第三參數:價格,就是指定的第二個名稱
第四參數:3.5,函式會將這個3.5賦值給「價格」
第五參數:蘋果*價格,這個就是結果運算式,現在蘋果=10,價格=3.5,所以它們相乘的結果就是35
2、SEARCH函式介紹
功能: 尋找字元首次出現位置
語法: =SEARCH(要尋找的字串,被尋找字串,[開始位置]
主要是利用這個函式資訊按列模糊匹配
3、IFERROR函式介紹
功能: 如果公式的計算結果為錯誤值, 則 IFERROR 返回您指定的值;否則, 它將返回公式的結果。
語法: =IFERROR(值,錯誤值)
4、FILTER函式介紹
功能: FILTER是基於定義的條件篩選一系列數據的函式,它由陣列,包括,空值三個參數所構成。
語法: 使用語法=FILTER(陣列,包括,空值)
解讀:
我們可以使用FILTER函式輕松實作單條件或者多條件查詢,使用FILTER進行多條件查詢竅門在第2個參數:
①如果需要多個條件同時滿足,就用*把多個條件連線
公式:=FILTER(返回陣列,(條件1)*(條件2)*(條件N),空值)
②如果需要多個條件滿足任意一個,就用+把多個條件連線
公式:=FILTER(返回陣列,(條件1)+(條件2)+(條件N),空值)
二、制作萬能查詢樣版
第一步、
先制作按「姓名」這列查詢
也就是當輸入查詢關鍵詞後,姓名這列數據中包含對應關鍵詞的所有員工全部尋找出來。
在目標單元格中輸入公式:
=FILTER(A:F,A:A=I1,"無內容")
然後點選回車。
解讀:
這一步只是實作了關鍵詞必須是完整姓名,這樣才能根據姓名這列數據進行查詢。
第二步、
根據按關鍵詞對「姓名」這列進行模糊查詢
要寫實作根據關鍵詞對姓名這列數據進行模糊查詢,我們可以把公式修改成:
=FILTER(A:F,IFERROR(SEARCH(I1,A:A),0),"無內容")
然後點選回車即可
解讀:
①查詢條件使用到了SEARCH函式,也就是只要查詢關鍵詞在A列姓名這列內,就滿足條件,最後就能篩選出對應的資訊。再結合IFERROR函式,把姓名不包含關鍵詞的返回錯誤值遮蔽掉,都返回0
②如果尋找關鍵詞是空,上面的公式是會尋找出所有資訊的,這時我們可以用IF函式做一個判斷。
公式修改成:
=FILTER(A:F,IF(I1="","",IFERROR(SEARCH(I1,A:A),0)),"無內容")
如果只修改判斷條件,當查詢條件是空值時,返回結果也會是錯誤值,因為FILTER函式第2參數沒有判斷條件。
再把公式修改成:
=IFERROR(FILTER(A:F,IF(I1="","",IFERROR(SEARCH(I1,A:A),0)),"無內容"),"")
這樣當查詢關鍵詞是空值時就不返回任何資訊了。
第三步,
進行多條件尋找
上面的公式只是尋找「姓名」這列數據符合條件的資訊,我們需要尋找所有列包含查詢關鍵詞的資訊。其實,就是利用FILTER函式多條件查詢,用多個SEACH公式去尋找搜尋,A列,B列,C列,D列,E列,F列,只要有任何一個滿足條件的數據,就會把對應的A:F列結果給篩選出來。
公式如下:
=IFERROR(FILTER(A:F,IF(I1="","",IFERROR(SEARCH(I1,A:A),0))+IF(I1="","",IFERROR(SEARCH(I1,A:A),0))+IF(I1="","",IFERROR(SEARCH(I1,C:C),0))+IF(I1="","",IFERROR(SEARCH(I1,D:D),0))+IF(I1="","",IFERROR(SEARCH(I1,E:E),0))+IF(I1="","",IFERROR(SEARCH(I1,F:F),0)),"無內容"),"")
第四步、
公式化繁為簡,提高可讀性
上面的函式公式太長了,中間有過個條件用「+」連線,看到都眼花繚亂了,這是我們可以使用LET函式讓函式公式化繁為簡,復雜公式變的簡潔又優美。
最終公式:
=LET(
姓名,IF(I1="","",IFERROR(SEARCH(I1,A:A),0)),
部門,IF(I1="","",IFERROR(SEARCH(I1,B:B),0)),
薪資,IF(I1="","",IFERROR(SEARCH(I1,C:C),0)),
性別,IF(I1="","",IFERROR(SEARCH(I1,D:D),0)),
考核成績,IF(I1="","",IFERROR(SEARCH(I1,E:E),0)),
級別,IF(I1="","",IFERROR(SEARCH(I1,F:F),0)),
IFERROR(FILTER(A:F,姓名+部門+薪資+性別+考核成績+級別,"無內容"),"")
)
然後點選回車即可
解讀:
很多新手小夥伴看到上面的公式是不是有點被驚到了,公式還能這樣寫?其實,上面的公式就是利用LET函式把多個計算結果,分配給指定的名稱,最後透過結果運算式中直接參照名稱來完成尋找匹配。
①我們把SEACH函式公式,根據關鍵詞分別對A列,B列,C列,D列,E列,F列這幾個公式,分別命名為:姓名、部門、薪資、性別、考核成績、級別,這樣在最後的結果運算式可以直接參照名稱。(備註:上面的定義的名稱,可以根據實際情況自行命名,我是直接使用了列標題名稱)
②在結果運算式FILTER函式第2參數條件就可以直接設定成:姓名+部門+薪資+性別+考核成績+級別,就就是只要滿足一個成立就可以返回對應結果,其實就是OR或的關系;如果是同時滿足中間就用「*」號連線,就是AND且的關系。
以上就是【桃大喵學習記】今天的幹貨分享~覺得內容對你有所幫助,別忘了動動手指點個贊哦~。大家有什麽問題歡迎關註留言,期待與你的每一次互動,讓我們共同成長!