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

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

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