回覆列表
-
1 # EXCEL學習微課堂
-
2 # 套路Excel
題主的問題，使用高階篩選是十分方便的。
1、資料區:
2、條件區:
3、若要查詢某個姓名的所有資訊，可在姓名下輸入想查詢的姓名。
3.1、查詢一個姓名，比如，張飛，高階篩選設定如下：
結果：
3.2、若要查詢多個姓名，比如劉備、關羽、張飛三兄弟的全部資訊。
在條件區域，姓名下輸入三兄弟的姓名，同時調整條件區域的範圍即可。
3.3、若要根據某個關鍵字查詢，比如查詢姓名中包含 “喬”這個字的所有姓名資訊。
條件區域設定如下：
結果：
其他性別和部門關鍵字的處理，和姓名一樣的道理。需要的時候，設定相應的條件即可實現目的。
除了用“高階篩選”的方法，藉助函式輔助列的方式，也是十分有用的。
1、獲取性別為“女”的所有人員資訊。
新增輔助列，對“女”記性計數。
公式為：=B2&COUNTIF(B$2:B2,B2)
查詢公式為：index+match組合，公式為：
=IFERROR(INDEX(A$2:A$13,MATCH("女"&ROW(A1),$E$2:$E$13,0),),"")
2、查詢性別為“男”的所有人員資訊。公式為：
=IFERROR(INDEX(A$2:A$13,MATCH("男"&ROW(A1),$E$2:$E$13,0),),"")
綜上，推薦用函式輔助列的方法來實現“高階篩選”。
輔助列在實際應用中是比較有效率又靈活的強大方法。往往會比陣列公式更加實用。
要實現輸入表格中的任一關鍵字就能查詢出符合條件的所有員工資訊，這個問題確實有點難度，一種方法是用VBA，但VBA不是每一個用EXCEL的人都會的，有難度，二是用陣列公式，但陣列公式遇到資料量大時，執行會有點卡，這裡介紹一種不用VBA，不用陣列公式，簡單易學，只用基本的函式就能實現的方法，可以實現輸入姓名關鍵字、性別關鍵字、學歷關鍵字、部門關鍵字等都能查出你想要的員工資訊，而且可以高亮顯示關鍵字。具體效果如下圖：
設計思路：1、用輔助列將所有員工資訊合併到一起，透過find函式查詢搜尋的關鍵字是否包含在合併資訊中，透過contif統計包含關鍵字的記錄有多少條，再根據序號，用vlookup一對多查詢，將所有符合條件的資訊查詢出來。
2、最後再用條件格式將包含查詢關鍵字用紅色字型標識出來。
實現步驟：一、在員工基本情況表中增加合併輔助列、匹配判斷輔助列和關鍵字出現次數輔助列1．在員工基本情況表中增加合併輔助列。如下圖所示，增加一個輔助列，將員工的姓名、員工編號、性別等都合併到輔助列。Excel 2016版本可用快速填充法或新增函式CONCAT合併D2到J2，也可以用&連線符合並。其它版本可用&連線符合並。
C2單元格公式：=CONCAT(D2:J2)或=D2&E2&F2&G2&H2&I2&J2
2、在員工基本情況表中增加輔助列“匹配”判斷在B2單元格輸入公式：=IFERROR(IF(FIND(查詢表!$C$2,C2)>0,"▲",""),"")
公式分步演示圖：
3．計算關鍵字出現的次數在A2單元格輸入公式：=COUNTIF(B$2:B2,"▲")
二、在查詢表中建立查詢並用條件格式設定關鍵字為紅色字型1、在查詢表中輸入序號（序號最好要多一點，能保證符合條件的記錄最多時夠用）
2、在F4單元格輸入公式
=IFERROR(VLOOKUP($E4,員工基本情況表!$A:$J,MATCH(查詢表!F$3,員工基本情況表!$A$1:$J$1,0),0),"")
3、向右填充後，再向下填充至出現空白即可。
4、選中查詢表，點【開始】→【條件格式】→【突出顯示單元格規則】→【文字包含】，設定單元格包含關鍵字中突出顯示單元格的規則為淺紅填充色深紅色文字。
動態演示圖：
1.《Excel全表查詢，輸入任一關鍵字都能查出符合條件的所有資訊！》
2.Excel查詢函式FIND，幫你從複雜的地址中提取城市、區和街道名！
3.Excel的IF函式還可以這樣用，你知道嗎？
4.比Vlookup好用10倍的自定義函式VLOOKUPS，解決VLOOKUP的難題！