Excel用SUMPRODUCT實現有條件排名

 　　前些日子市裡搞了一次模擬考試，下發了匯總後的成績表。全市三所學校各個專業的學生成績都放到了一個工作表中，格式如圖1所示。為了做好成績分析，主任要求做好兩個排名：一是排出每位學生在全市相同專業的學生中的名次;二是排出每位學生在本校本專業中的名次;兩個排名都以總分為依據。

　　圖1(點擊看大圖)

　　這個工作以前也做過，每次都得將數據按專業、按學校分別篩選出來復制到不同的工作表中，然後在不同的工作表中用RANK函數進行排序。全市三所學校一千多個學生，每所學校都有七到八個專業，所以這個篩選復制工作也是費時費力，篩選復制完成後還要在十多個工作表中進行排名工作，非常麻煩。不過這一次，工作完成得卻異常順利，只需要十分鐘就可以完成全部的工作了。因為，這次我們使用了SUMPRODUCT函數來完成這個有條件的排名工作。具體實現過程如下：

　　一、准備工作

　　選定總分所在的H2:H1032單元格區域，點擊功能區“公式”選項卡“定義的名稱”功能組中“定義名稱”按鈕，在彈出的“新建名稱”對話框“名稱”輸入框中輸入為此區域定義的名稱“zongfen”。此時，對話框下方的“引用位置”後的輸入框中已經自動輸入我們選定的單元格區域“=對口!$H$2:$H$1032”，如圖2所示。

　　圖2

　　按同樣的方法，選定學校所在單元格區域I2:I1032、專業所在單元格區域J2:J1032，分別為它們指定名稱“xuexiao”和“zhuanye”。

　　完成後，這准備工作就算是結束了。

　　二、排定名次

　　在K1單元格輸入標題“按專業排名”。點擊K2單元格，輸入公式“=SUMPRODUCT((zhuanye=$J2)*($H2

　　在L1單元格輸入標題“校內專業排名”。點擊L2單元格，輸入公式“=SUMPRODUCT((zhuanye=$J2)*($H2

　　圖3(點擊看大圖)

　　如果您也遇到類似的問題，比如平行班的成績匯總在一張工作表中，而我們又需要學生的班內名次，那麼不妨照此辦理一回，呵呵，那效果，真的是誰用誰知道啊。a