編按：

在日常工作中，需要對數(shù)據(jù)進(jìn)行排名的情況有很多，不管是專業(yè)的財(cái)務(wù)、會計(jì)等，還是公司小組領(lǐng)導(dǎo)，甚至是看似八竿子打不到一起的學(xué)校老師，都會遇到。

而在Excel的眾多排名問題中，最常見又最能難住大家的，就不得不提，讓小白犯慫，讓大神翻白眼的“組內(nèi)排名”問題了。今天，小E和大家介紹的就是快速解決組內(nèi)排名問題的三個(gè)函數(shù)公式！

相信很多朋友在接觸Excel時(shí)，都會遇到一個(gè)數(shù)據(jù)集里有很多組，一個(gè)組里有很多數(shù)據(jù)，需要獲得該數(shù)據(jù)在其所屬的組中的排名值的情況，這就不可避免的接觸到了按條件排名。

在Excel眾多的函數(shù)中，有按條件求和的SUMIF函數(shù)，有按條件求平均值的AVERAGEIF函數(shù),也有按條件計(jì)數(shù)的COUNTIF函數(shù)……那么，按條件求組內(nèi)排名，又該怎么做呢？

舉個(gè)例子，某個(gè)武俠公司共有四個(gè)部門，分別是IT部、人事部、財(cái)務(wù)部、銷售部，需要對員工在所在部門內(nèi)部的綜合評分進(jìn)行排名。以綜合評分值的高低，作為新一年每個(gè)部門的后備干部選拔的參考依據(jù)，這就需要獲得數(shù)據(jù)在其所屬組中的排名。

今天春風(fēng)就分別通過三個(gè)公式來介紹三種組內(nèi)排名的方法！希望小伙伴們在學(xué)習(xí)方法的同時(shí)，也能夠回顧一些重要的函數(shù)的用法。

方法一：COUNTIFS（）函數(shù)法

在F2單元格輸入公式“=COUNTIFS($D$2:$D$21,D2,$E$2:$E$21,">"&E2)+1”，按Enter鍵結(jié)束公式輸入，下拉至F21單元格，此時(shí)F列中顯示員工在各自所在部門中綜合評分的排名值。

函數(shù)解析：

COUNTIFS（）函數(shù)中的條件可以應(yīng)用于跨區(qū)域的多個(gè)單元格，同時(shí)計(jì)算符合所有條件的次數(shù)。COUNTIFS（）函數(shù)的語法為(條件區(qū)域1,條件1,條件區(qū)域2,條件2,…)

參數(shù)說明：

條件區(qū)域1為第一個(gè)需要計(jì)算其中滿足某個(gè)條件的單元格數(shù)目的單元格區(qū)域，條件1為第一個(gè)區(qū)域中將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。同理，條件區(qū)域2、條件2依次類推。最終結(jié)果為多個(gè)區(qū)域中滿足所有條件的單元格個(gè)數(shù)。

以F2單元格為例，使用COUNTIFS（）函數(shù)統(tǒng)計(jì)出在$D$2：$D$21中是“IT部”（與D2單元格一致）的單元格，再在其中查找綜合評分大于自身綜合評分的總個(gè)數(shù)，然后再加上1，即可獲得員工郭靖的綜合評分在IT部中的排名。

方法二：INDEX（）函數(shù)法

在F2單元格輸入公式“=INDEX(FREQUENCY(($D$2:$D$21=D2)*$E$2:$E$21,E2),2)+1”，按Enter鍵結(jié)束公式輸入，下拉至F21單元格，此時(shí)F列中顯示員工綜合評分在所在部門中的排名值。

函數(shù)解析：

INDEX（）函數(shù)是返回表或區(qū)域中的值或值的引用，INDEX()函數(shù)有兩種形式，數(shù)組形式和引用形式。數(shù)組形式通常返回?cái)?shù)值或數(shù)值數(shù)組；引用形式通常返回引用。本例中為數(shù)組形式。

以F2單元格為例，在公式中：

①“($D$2:$D$21=D2)*$E$2:$E$21”用于判斷是否屬于當(dāng)前部門，如果是，則返回對應(yīng)的總分值，否則返回0，這樣可以得到一個(gè)數(shù)組。

②“FREQUENCY(($D$2:$D$21=D2)*$E$2:$E$21,E2)”表示以當(dāng)前E2單元格的數(shù)值作為分段點(diǎn)，使用FREQUENCY（）函數(shù)獲得小于或等于E2單元格和大于E2單元格值的數(shù)據(jù)分布頻率，這時(shí)將獲得包含這兩個(gè)頻率值的數(shù)組。FREQUENCY（）函數(shù)用于計(jì)算值在值范圍內(nèi)出現(xiàn)的頻率，然后返回?cái)?shù)值的垂直數(shù)組。

③最后使用INDEX（）函數(shù)從這個(gè)數(shù)組中提取第2個(gè)數(shù)組，即大于E2單元格值的總分個(gè)數(shù)，再加上1，即可獲得E2單元格數(shù)值在所屬部門中的排名。

方法三：SUMPRODUCT（）函數(shù)法

在F2單元格輸入公式“=SUMPRODUCT(($D$2:$D$21=D2)*($E$2:$E$21>E2))+1”，按Enter鍵結(jié)束公式輸入，下拉至F21單元格，此時(shí)F列中顯示綜合測評分在部門中的排名值。

函數(shù)解析：

SUMPRODUCT（）函數(shù)是在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和。

以F2單元格為例，在公式中，"$D$2:$D$21=D2"用于判斷是否屬于當(dāng)前部門，"$E$2:$E$21>E2"用于判斷總分是否大于自身總分值。使用SUMPRODUCT（）函數(shù)將數(shù)組對應(yīng)的值相乘并累加就可以得出大于自身總分的人數(shù)，然后將其加上1，即可獲得員工在所在部門的排名值。

總結(jié)：

好了，我們分別用COUNTIFS（）函數(shù)、INDEX（）函數(shù)、SUMPRODUCT（）函數(shù)對小組內(nèi)部的數(shù)據(jù)進(jìn)行了排名。這三個(gè)公式雖然很長，但都是寫在單元格上的，只要逐步測試，驗(yàn)證，最后就能明白各個(gè)部分的意義，使用哪個(gè)公式都可以。相信大家理解了公式的原理以后，結(jié)合具體問題去自己套用是完全沒問題的。

只有夠靈活應(yīng)用各個(gè)函數(shù)，才能成為EXCEL的高手，但這需要長時(shí)間的練習(xí)和應(yīng)用。希望大家多多分享點(diǎn)贊，支持春風(fēng)哦！你的每一次點(diǎn)贊和轉(zhuǎn)發(fā)都是支持筆者堅(jiān)持的動力。小伙伴們，還有別的什么想法，歡迎到公眾號中留言。

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

在excel中，按條件進(jìn)行排名，竟是如此簡單！

多條件查找排名第一人的方案等你來完善！

排名公式大雜燴：不管是常規(guī)排名還是特殊排名都在這里

數(shù)據(jù)透視表送溫暖來了：嘿，鼠標(biāo)拖兩下一次搞定業(yè)績統(tǒng)計(jì)和排名！

版權(quán)申明：

本文作者賦春風(fēng)；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。