回覆列表
-
1 # Excel函式公式
-
2 # 玩Office
用函式組合,PowerQuery還有VBA都可以解決這個問題。
但是我一向的原則是,在能解決問題的前提下,怎麼簡單怎麼做。我分享一下如何用資料透視表來解決這個問題。
不要以為資料透視表僅僅是在資料統計和分析的時候才能用到,實際上資料透視表的功能比我們想象中的要更強大。
資料透視表中,允許多個欄位同時拖入行欄位,或者列欄位,或者值欄位。
利用這個特性,我們就可以將名字和專案欄位同時拉入行欄位區域,從而輕易地解決“每個人分別報了哪些專案?”的問題。
操作方法看動圖:
這樣就已經可以很直觀地看到,每一個人對應了哪些專案。
這個時候可以說是實現了基本功能,但是我們還需要進一步美化一下。
彙總,分類彙總這些我們是不需要的。
樣式,名字居中這些也可以美化一下。
相信這樣就已經非常清晰了。
實際工作中,一個人往往含有多種角色,也可能幹多種型別的工作,如何快速的查詢對應的全部角色或全部工作的型別,是每位辦公白領必備的技能之一。
一、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、資料查詢。
結束語:
本文主要講解了“一對多”查詢的兩種方法,都是使用相關函式組合使用得來的結果。
如果大家在學習的過程中有困難,建議先學習單個基礎函式,然後進行組合使用。