錦子老師

在對Excel資料進行分析時，經常需要獲得資料的排名。在對資料進行排名時，資料被分為不同的群組，需要獲得其在所屬的群組中的排名值。例如：在對同年級學生的成績進行分析時，存在一個同年級成績統計表中，該表包含各個班級中各個學生的各科學習成績，需要獲得成績在班級中的排名值作為成績分析的參考。

本單元以介紹如何統計每位學生成績在班級中的排名值，由Excel進行分析作業的四種方法。

n  1.使用COUNTIF函數

步驟1：啟動Excel並打開工作表452.XLSX。

步驟2：框選I2:I17後，點取編輯列輸入公式：「=COUNTIFS ( $B$2:$B$17 , B2 , $H$2:$H$17 , ">"&H2 ) +1」後，按Ctrl + Enter鍵結束I2:I17公式輸入，此時儲存格範圍中顯示總分在班級中的排名值，如下圖所示。

提示：在Excel中，COUNTIFS()函數將條件應用於跨多個範圍的儲存格同時計算符合所有條件的次數。此時，其支持多條件計數。這裡，使用COUNTIFS()函數統計出在班級中大於自身成績總分的總分個數，然後再加上1即可獲得該總分在班級中的排名。

n  2.使用SUMPRODUCT函數

步驟1：框選I2:I17後，點取編輯列輸入公式：「=SUMP RODUCT ( ( $B$2:$B$17=B2 ) * ( $H$2:$H$17>H2 ) ) +1」，按Ctrl + Enter鍵結束I2:I17公式輸入，此時儲存格範圍中顯示總分在班級中的排名值，如下圖所示。

提示：在公式中「$B$2:$B$20=B2」用於判斷是否屬於當前班級，「$H$2:$H$17>H2」用於判斷總分是否大於自身總分值。使用SUMPRODUCT()函數將陣列對應的值相乘並累加就可以得出大於自身總分的人數，然後將其加上1即可獲得班級排名值。

n  3.使用INDEX + FREQUENCY函數

步驟1：框選I2:I17後，點取編輯列輸入公式：「=INDEX ( FREQUENCY ( ( $B$2:$B$20=B2 ) * $H$2:$H$20,H2 ) ,2 ) +1」，按Ctrl + Enter鍵結束I2:I17公式輸入，此時儲存格範圍中顯示總分在班級中的排名值，如下圖所示。

提示：在公式中「($B$2:$B$17=B2)*$H$2:$H$17」用於判斷是否屬於當前班級，如果是，則傳回對應的總分值，否則傳回0，這樣可以得到一個陣列。以當前H2儲存格的數值作為分段點，使用FREQUENCY()獲得小於或等於H2儲存格和大於H2儲存格值的資料分佈頻率，這時將獲得包含這兩個頻率值的陣列。最後使用INDEX()函數從這個陣列中提取第2個陣列，即大於H2儲存格值的總分個數，再加上1即可獲得H2儲存格數值在所屬班級中的排名。

n  4.使用MATCH + LARGE + ROW函數

步驟1：點取H2儲存格，輸入公式為：「=MATCH ( H2,LARGE ( ( $B$2:$B$17=B2 ) *$H$2:$H$17,ROW ( $2:$17 ) -1 ) , ) 」後，按Ctrl + Shift + Enter鍵創建陣列公式。

步驟2：將H2公式向下複製到H3~H17儲存格中，此時儲存格中會顯示排名值，如下圖所示。

提示：與前面公式相似，這裡的公式中首先以班級為條件來查找同班總分值，屬於其他班的總分值被置為0，使用LARGE()函數將獲得的總分值進行排名獲得一個陣列。使用MATCH()函數獲得當前總分在陣列中的位置，該位置即為班級排名值。