當前位置: 妍妍網 > 寵物

美爆了!Excel萬能尋找樣版,公式如此優雅,驚呆眾人!

2024-07-15寵物

我是【桃大喵學習記】,歡迎大家關註喲~,每天為你分享職場辦公軟體使用技巧幹貨!

——先發於微訊號:桃大喵學習記

今天跟大家分享的是一個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且的關系。

以上就是【桃大喵學習記】今天的幹貨分享~覺得內容對你有所幫助,別忘了動動手指點個贊哦~。大家有什麽問題歡迎關註留言,期待與你的每一次互動,讓我們共同成長!