夜上9116
Excel函數公式:含金量超高的“一對多”查詢實用技巧解讀
實際工作中,一個人往往含有多種角色,也可能幹多種類型的工作,如何快速的查找對應的全部角色或全部工作的類型,是每位辦公白領必備的技能之一。
一、INDEX+SMALL+IF+ROW組合函數法。
目的:查詢銷售員的銷售情況。
實際效果:
從最終的效果圖中我們可以看出,單擊銷售員的姓名,系統自動檢索銷售地區和銷售額。而且當有多條記錄時,“地區”和“銷售額”全部予以顯示。一瞬間得到了該銷售員的銷售業績情況。那麼,該如何去實現呢?
步驟:
(一)、高亮度顯示該銷售員的銷售記錄。
方法:
1、選中目標單元格。
2、【開始】-【條件格式】-【新建規則】,選中【選擇規則類型】中的【使用公式確定要設置格式的單元格】,並在【為符合此公式的值設置單元格格式】中輸入:=($H$4=$B3)。
3、單擊右下角【格式】-【填充】,選取填充色,並【確定】-【確定】完成設置。
4、單擊銷售員姓名下拉列表,可以看到關於該銷售員的所有銷售記錄都被高亮度顯示。
(二)、查詢銷售員對應的銷售地區。
方法:
1、在目標單元格中輸入公式:=INDEX(E:E,SMALL(IF(B$3:B$9=H$4,ROW($3:$9),4^8),ROW(A1)))&""。
2、Ctrl+Shift+Enter填充。
3、拖動填充柄填充。
(三)、查詢銷售員在對應地區的銷售額。
方法:
1、選中目標單元格。
2、輸入公式:=IFERROR(LOOKUP(1,0/(($B$3:$B$9=$H$4)*($E$3:$E$9=$I4)),$C$3:$C$9),"")。
3、Ctrl+Enter填充。
二、COUNTIF+VLOOKUP+ROW組合函數法。
目的:查詢銷售員的銷售情況。
實際效果:
眼尖的同學們肯定已經注意到了,最終的效果和用INDEX函數等實現的是一樣的,但是數據源中多了一個“輔助列”。那麼輔助列有什麼作用呢?整體又該如何去實現呢?
步驟:
(一)、添加輔助列。
方法:
1、單擊選中【姓名】所在列。
2、快捷鍵:Ctrl+Shift++添加列,輸入列標。
3、選中添加列的所有目標單元格,並輸入公式:=COUNTIF($C$3:C3,$I$4),Ctrl+Enter填充。
備註:
公式:=COUNTIF($C$3:C3,$I$4)的主要作用是對出現的姓名進行累加統計。暨第一次出現為1,第二次出現為2,第三次出現為3……。
(二)、高亮度顯示該銷售員的銷售記錄。
方法同上,不在贅述。
(三)、查詢銷售員對應的銷售地區。
方法:
1、選定目標單元格。
2、輸入公式:=IFERROR(VLOOKUP(ROW(1:1),$B$3:$F$9,5,0),"")。
3、Ctrl+Enter填充。
(四)、查詢銷售員在對應地區的銷售額。
方法:
1、選中目標單元格。
2、輸入公式:=IFERROR(LOOKUP(1,0/(($C$3:$C$9=$I$4)*($F$3:$F$9=$J4)),$D$3:$D$9),"")。
3、Ctrl+Enter填充。
4、數據查詢。
結束語:
本文主要講解了“一對多”查詢的兩種方法,都是使用相關函數組合使用得來的結果。
如果大家在學習的過程中有困難,建議先學習單個基礎函數,然後進行組合使用。
歡迎大家在留言區留言討論。
Excel函數公式
用數據透視表就可以,或者分別按照姓名和項目名稱進行排序就可以了。希望對你有所幫助!