首頁  >  文章  >  專題  >  實用Excel技巧分享:怎麼多條件查找排名第一人

實用Excel技巧分享:怎麼多條件查找排名第一人

青灯夜游
青灯夜游轉載
2023-03-07 18:43:405196瀏覽

排名,簡單;但如果有多個項目類別,並且可能存在業績相同,怎麼快速找出各個分享排名第一的人物呢?這就要透過多條件去匹配,才能找出需要的排名第一者。這裡提供了兩個方案,但都不夠完美,你能把它們完善嗎?

實用Excel技巧分享:怎麼多條件查找排名第一人

一年一度的表彰大會馬上就要開始了,今年又是哪些同事成為了銷售冠軍呢?讓我們一起來把他們找出來吧!

某公司的電商平台各類電器銷售資料如圖:

實用Excel技巧分享:怎麼多條件查找排名第一人

#資料只有銷售單號、產品名稱、業務人員姓名及銷售額,現在需要按下圖的格式來統計每類產品的銷售冠軍。

實用Excel技巧分享:怎麼多條件查找排名第一人

看到這個問題,不知道大家想到哪些方法?透視表、MAX函數、還是VLOOKUP…

老菜鳥推薦給大家兩種方法:第一種輔助列 公式;第二種透視表 公式。

方法1:輔助列公式

第1步:新增輔助列

首先將每個人的銷售額按照產品名稱進行匯總。按條件求和,這裡用SUMIFS函數來進行統計。雖說可以使用透視表完成相同的結果,但是透視表並不能一次就得到最終需要的效果,因此用輔助列會更方便。

公式:

=SUMIFS(D:D,C:C,C2,B:B,B2)

實用Excel技巧分享:怎麼多條件查找排名第一人

公式格式:=SUMIFS(求和區域,條件區域1,條件1,條件區域2,條件2…)

SUMIFS是多條件求和函數,第一個參數是要求和的資料所在的列,後面的參數兩個一組,構成一組條件。在這個例子中,第一組條件是業務人員,因此條件區域1就是C列,條件1是C2;第二組條件是產品名稱,條件區域2就是B列,條件2是B2。

有了輔助列,下一步就可以找到每個品類中最高的銷售額是多少了。這裡要注意的是,統計結果表裡銷售冠軍姓名在前銷售額在後。實際統計時並非必須依照這樣的先後順序統計,哪個方便我們先統計哪一個。

第2步:統計最高銷售量

#通常一說最大值,首先想到的就是MAX函數。這個函數的用法和SUM很像,只要給定一組數或一個資料區域,就能得到這一組數中最大的值。

在今天這個例子中,因為我們要得到的是同一個品類中的最大值,也就是按條件統計最大值,所以無法直接用MAX函數得到結果,

這類別依條件統計最大值的有固定的套路公式:

=MAX(資料區域*(條件區域1=條件1)*(條件區域2=條件2)…)

本範例只有一個條件,就是產品名稱,因此公式為:=MAX($E$2:$E$750*($B$2:$B$750=G2))

實用Excel技巧分享:怎麼多條件查找排名第一人

 使用這個公式套路需要注意三個地方:

(1)範圍要準確,不建議選擇整列作為計算區域;

(2)公式涉及陣列運算,在輸入公式後需要按Ctrl Shift Enter鍵,按鍵後會自動在公式中添加一對大括號;

(3)因為公式要下拉,為了避免計算區域發生改變,所以涉及的範圍需要使用絕對引用。

這個公式具體原理涉及到邏輯值和陣列的計算原理,以後我們會專門進行講解。

到這一步,再找出每類產品下最高銷售額對應的業務人員就完成了全部的統計。

第3步:找出冠軍人員

根據銷售查詢人員,這其實就是一個查找引用,使用VLOOKUP或INDEX等引用函數都可以完成。

接近成功,現在要削蘋果了。削蘋果的特色就是細、準。

第一個細節:資料來源中的累計銷售位於業務人員的右側。

如果用VLOOKUP,我們就得使用反向尋找的套路,公式相對還是比較複雜。如果用INDEX與MATCH組合倒是可以,公式也不難:

=INDEX($C$2:$C$750,MATCH(I2,$E$2:$E$750,0))

實用Excel技巧分享:怎麼多條件查找排名第一人

第二個細節:最高銷售量可能存在相同。

這兩個函數組合堪稱經典搭檔。但還有一個細節問題:我們不能排除兩類產品的最高銷售額有相同的情況。為了避免可能存在的不同品類最高銷售額相同的查找失誤,我們必須要按產品名稱和銷售兩個條件去匹配,公式就變成:

=INDEX($C $2:$C$750,MATCH(G2&I2,$B$2:$B$750&$E$2:$E$750,0))

多條件匹配常用套路之一就是用連接符號&把多個條件串在一起組成一個新的條件來查詢,當然查詢區域也需要用&串在一起。

實用Excel技巧分享:怎麼多條件查找排名第一人

當然,像這種多條件查找,並且不願意利用Vlookup反相查找的話,也可以用LOOKUP函數來完成:

=LOOKUP(1,0/(($E$2:$E$750=I2)*($B$2:$B$750=G2)),$C$2:$C$750)

#多條件匹配常用套路之二就是把多個條件各自用等號=與查找區域建立起表達式,然後把表達式進行相乘。

實用Excel技巧分享:怎麼多條件查找排名第一人

公式的套路是:=LOOKUP(1,0/(條件區域=條件),目標區域),如果是多個條件的話,可以直接將套路升級為:=LOOKUP(1,0/((條件區域1=條件1)*(條件區域2=條件2)*(條件區域3=條件3)…,目標區域)

方法二:透視表公式

#第1步:統計績效並排名

將產品名稱和業務人員拖入行區域,銷售額拖兩次到值區域,然後按照部落窩教育去年的教程《嘿,滑鼠拖兩下一次搞定業績統計和排名!》設定銷售額2的值顯示方式為“降序排列”,基本欄位為“業務人員”獲得按產品分類的銷售業績統計和排名。

實用Excel技巧分享:怎麼多條件查找排名第一人

##第2步,整理透視表

點選透視表,點選「設計」標籤「版面」選項群組「報表版面」下拉選單中的「以表格形式顯示」和「重複所有項目標籤”命令。接著在透視表上按右鍵,選擇“分類匯總“業務人員””,取消表格中的分類匯總項目。表格變成下方模樣:

實用Excel技巧分享:怎麼多條件查找排名第一人

第3步,輸入公式取得冠軍姓名與績效

#在G2儲存格中輸入公式:

=INDEX(L$2:L$200,MATCH($G2&1,$K$2:$K$200&$N$2:$N$200,0))

#輸入完畢按Ctrl Shift Enter三鍵結束。

實用Excel技巧分享:怎麼多條件查找排名第一人

然後右拉、下拉公式即可。

實用Excel技巧分享:怎麼多條件查找排名第一人

 在今天的教程中我們學習了幾個函數,分別是SUMIFS、MAX、INDEX、MATCH、LOOKUP,也學習了多條件匹配的兩種套路,在遇到類似的問題時,可以直接使用。

#不過,今天的解決是不完全的。雖然教程中我們要求自己「削蘋果」關注細節,但我們還是遺漏了一個很重要的細節——同類產品最高銷售額可能出現相同。

#相關學習推薦:

excel教學

以上是實用Excel技巧分享:怎麼多條件查找排名第一人的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:itblw.com。如有侵權,請聯絡admin@php.cn刪除