回覆列表
  • 1 # 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、資料查詢。

    結束語:

    本文主要講解了“一對多”查詢的兩種方法,都是使用相關函式組合使用得來的結果。

    如果大家在學習的過程中有困難,建議先學習單個基礎函式,然後進行組合使用。

  • 2 # 玩Office

    用函式組合,PowerQuery還有VBA都可以解決這個問題。

    但是我一向的原則是,在能解決問題的前提下,怎麼簡單怎麼做。

    我分享一下如何用資料透視表來解決這個問題。

    不要以為資料透視表僅僅是在資料統計和分析的時候才能用到,實際上資料透視表的功能比我們想象中的要更強大。

    資料透視表中,允許多個欄位同時拖入行欄位,或者列欄位,或者值欄位。

    利用這個特性,我們就可以將名字和專案欄位同時拉入行欄位區域,從而輕易地解決“每個人分別報了哪些專案?”的問題。

    操作方法看動圖:

    這樣就已經可以很直觀地看到,每一個人對應了哪些專案。

    這個時候可以說是實現了基本功能,但是我們還需要進一步美化一下。

    彙總,分類彙總這些我們是不需要的。

    樣式,名字居中這些也可以美化一下。

    相信這樣就已經非常清晰了。

  • 中秋節和大豐收的關聯?
  • 實木複合地板、強化複合地板價格怎麼樣?