3-Excel用SUMPRODUCT實現(xiàn)多條件排名

3-Excel用SUMPRODUCT實現(xiàn)多條件排名3-Excel用SUMPRODUCT實現(xiàn)多條件排名1/43-Excel用SUMPRODUCT實現(xiàn)多條件排名Excel用SUMPRODUCT實現(xiàn)多條件排名前些日子市里搞了一次模擬考試，下發(fā)了匯總后的成績表。全市三所學校各個專業(yè)的學生成績都放到了一個工作表中，格式如圖1所示。為了做好成績分析，主任要求做好兩個排名：一是排出每位學生在全市相同專業(yè)的學生中的名次；二是排出每位學生在本校本專業(yè)中的名次；兩個排名都以總分為依據(jù)。（圖1）這個工作以前也做過，每次都得將數(shù)據(jù)按專業(yè)、按學校分別篩選出來復制到不同的工作表中，然后在不同的工作表中用RANK函數(shù)進行排序。全市三所學校一千多個學生，每所學校都有七到八個專業(yè)，所以這個篩選復制工作也是費時費力，篩選復制完成后還要在十多個工作表中進行排名工作，非常麻煩。不過這一次，工作完成得卻異常順利，只需要十分鐘就可以完成全部的工作了。因為，這次我們使用了SUMPRODUCT函數(shù)來完成這個有條件的排名工作。具體實現(xiàn)過程如下：一、準備工作：選定總分所在的H2:H1032單元格區(qū)域，點擊功能區(qū)“公式”選項卡“定義的名稱”功能組中“定義名稱”按鈕，在彈出的“新建名稱”對話框“名稱”輸入框中輸入為此區(qū)域定義的名稱“zongfen”。此時，對話框下方的“引用位置”后的輸入框中已經(jīng)自動輸入我們選定的單元格區(qū)域“=對口!$H$2:$H$1032”，如圖2所示。（圖2）按同樣的方法，選定學校所在單元格區(qū)域I2:I1032、專業(yè)所在單元格區(qū)域J2:J1032，分別為它們指定名稱“xuexiao”和“zhuanye”。完成后，這準備工作就算是結束了。二、排定名次：在K1單元格輸入標題“按專業(yè)排名”。點擊K2單元格，輸入公式:“=SUMPRODUCT((zhuanye=$J2)*($H2<zongfen))+1”，按下回車鍵，結果出來了吧？向下拖動其填充句柄至最后一行，OK，按專業(yè)排名就算完成了。在K1單元格輸入標題“按專業(yè)排名”。點擊K2單元格，輸入公式:“=SUMPRODUCT((zhuanye=$J2)*($H2<zongfen))+1”，按下回車鍵，結果出來了吧？向下拖動其填充句柄至最后一行，OK，按專業(yè)排名就算完成了。（圖3）如果您也遇到類似的問題，比如平行班的成績匯總在一張工作表中，而我們又需要學生的班內名次，那么不妨照此辦理一回，呵呵，那效果，真的是誰用誰知道啊。說明：這里我們使用的是SUMPRODUCT函數(shù)的多條件計數(shù)功能。語法：＝SUMPRODUCT((條件1)*(條件2)*(條件3)*…(條件n))作用：統(tǒng)計同時滿足條件1、條件2到條件n的記錄的個數(shù)。SUMPRODUCT函數(shù)的具體介紹請參見下文。

SUMPRODUCT函數(shù)介紹SUMPRODUCT函數(shù)在Excel中，用途極其廣泛、統(tǒng)計功能何其強大。下面，就給您詳細介紹SUMPRODUCT函數(shù)的使用吧。一、SUMPRODUCT函數(shù)語法及功能SUMPRODUCT函數(shù)功能：計算工作表內多列中對應值相乘之后的和，即乘積之和。其語法為：SUMPRODUCT(array1,array2,array3,…)其中，Array1,array2,array3,…為2到30個數(shù)組，其相應元素需要進行相乘并求和。數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù)SUMPRODUCT將返回錯誤值#VALUE!。該函數(shù)將非數(shù)值型的數(shù)組元素作為0處理。二、SUMPRODUCT函數(shù)乘積之和舉例表如下：ABCD（列號）121020（第2行）343040（第3行）565060（第4行）公式：=SUMPRODUCT(A2:B4,C2:D4)，說明：兩個數(shù)組的所有元素對應相乘，然后把乘積相加，即1*10+2*20+3*30+4*40+5*50+6*60(結果為910)三、多條件求和+求個數(shù)Ａ、使用SUMPRODUCT進行多條件計數(shù)，計數(shù)就是統(tǒng)計滿足條件的個數(shù)。語法：＝SUMPRODUCT((條件1)*(條件2)*(條件3)*…(條件n))作用：統(tǒng)計同時滿足條件1、條件2到條件n的記錄的個數(shù)。實例：=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱"))，公式解釋：統(tǒng)計性別為男且職稱為中級職稱的職工的人數(shù)Ｂ、使用SUMPRODUCT進行多條件求和語法：＝SUMPRODUCT((條件1)*(條件2)*(條件3)*…(條件n)*某區(qū)域)作用：匯總同時滿足條件1、條件2到條件n的記錄指定區(qū)域的匯總金額。實例：=SUMPRODUCT((A2:A10="男")*(B2:B10="中級職稱")*C2:C10)公式解釋：統(tǒng)計性別為男性且職稱為中級職稱的職工的工資總和（假設C列為工資）SUMPRODUCT(條件1*條件2*條件3...條件N)利用此函數(shù)進行多條件計數(shù)時，*：滿足所有條件；SUMPRODUCT(條件1+條件2+條件3...+條件N)+：滿足任一條件例子一：=SUMPRODUCT((G1:G3="男")*(E1:E3<=60))這個公式的意思就是要這樣來統(tǒng)計，G1:G3滿足條件是男的，同時E1:E3的數(shù)值小于等于60，滿足這兩個條件的總人數(shù)。首先這是一個數(shù)組公式，要按Ctrl+Shift+Enter結束。例子二：多條件求和求男性及格人數(shù)：=SUMPRODUCT((C2:C13>=60)*1,(B2