運用SUMPRODUCT函數(shù)快速統(tǒng)計高中成績

李新會

在excel 2003中，SUMPRODUCT函數(shù)功能是返回相應區(qū)域或數(shù)組乘積的和，我們可以運用它來對符合條件的單元格數(shù)目進行求和，可以設(shè)置多個求和條件，使用場合非常廣泛。靈活運用SUMPRODUCT函數(shù)，可以完成很多復雜的統(tǒng)計任務。我結(jié)合高中成績統(tǒng)計實例介紹一下該函數(shù)的用法。

一、建立對應工作表

首先在EXCEL中建立成績、上線分、分數(shù)段、學校、班級五個工作表。成績表中存放學生成績，格式如圖1。考號第2位代表學校，前4位代表班級，學校、班級這兩列數(shù)據(jù)用mid（）函數(shù)從考號中提取即可。上線分表用來存放各科的分數(shù)線，如圖2。分數(shù)段、學校、班級三個表用來存放統(tǒng)計結(jié)果。

二、定義區(qū)域，簡化公式

在統(tǒng)計時，公式中往往需要指明計算的單元格區(qū)域，公式寫得很長，容易出錯。可以將相應單元格區(qū)域定義一下，簡化公式，增強可讀性。選中成績表所有數(shù)據(jù)，單擊 “插入→名稱→指定”，在打開的窗口中選擇名稱創(chuàng)建于“首行”，單擊“確定”，這樣就快速定義了序號，考號，語文等多列區(qū)域。比如要計算語文一列的平均分，如果沒有定義區(qū)域，必須輸入公式“=AVERAGE（d2：d3000）”，現(xiàn)在只需輸入公式“=AVERAGE（語文）”。在成績表中定義的區(qū)域在其他工作表中可以直接使用，這個技巧在編寫復雜公式時效果尤其明顯。

三、統(tǒng)計不同分數(shù)段人數(shù)

切換到分數(shù)段工作表，如圖3所示，需要統(tǒng)計各校不同分數(shù)段的人數(shù)，可以用SUMPRODUCT函數(shù)完成。在B5單元格中輸入公式“=SUMPRODUCT（（學校="1"）*（總分<>0））”，可以求出一中（其學校代碼為1）實際參加考試人數(shù)，指定總分<>0可以將缺考的學生去掉。在C5單元格中輸入公式“=SUMPRODUCT（（學校="1"）*（總分>=900））”求出一中總分在940分以上的人數(shù)。用同樣方法算出一中在不同分數(shù)段的人數(shù)。寫完一中對應的公式后，復制公式到下一行，用查找替換將所有公式中的“1”改為“2”，馬上可以完成二中（代碼為2）各項數(shù)據(jù)的統(tǒng)計，以此類推，統(tǒng)計其它學校的數(shù)據(jù)。

提示：SUMPRODUCT函數(shù)中指定的條件最少寫兩個，不夠兩個時用1代替另一個條件。即SUMPRODUCT（（學校="1"）*1）表示求一中人數(shù)。

四、統(tǒng)計上線人數(shù)

切換到學校工作表，如圖4所示，統(tǒng)計各校上線人數(shù)，在C5單元格中輸入公式“=SUMPRODUCT（（學校="1"）*（總分>=上線分！$B$2））”求出總分上重點線的人數(shù)，其中“上線分！$B$2”表示引用上線分工作表中B2單元格的數(shù)據(jù)，B2單元格存放著總分的重點分數(shù)線。注意這里我們用“上線分！$B$2”而不是直接寫一個具體數(shù)字，是為了將來再次考試統(tǒng)計時方便，因為每次考試的重點線都不一樣，再次統(tǒng)計時只需要修改上線分表中B2單元格的數(shù)據(jù)即可，不用再修改C5單元格的公式。同理，在D5單元格輸入公式“=SUMPRODUCT（（學校="1"）*（總分>=上線分！$C$2））”，上線分！$C$2單元格存放著總分的本科分數(shù)線。E5單元格（本率即本科人數(shù)占總?cè)藬?shù)比率）輸入公式“=D5/$B5*100”。G5單元格（存放總分的平均分）輸入公式“=SUMPRODUCT（（學校="1"）*總分）/SUMPRODUCT（（學校="1"）*（總分<>0））”。其中SUMPRODUCT（（學校="1"）*總分）求出一中所有學生總分之和，SUMPRODUCT（（學校="1"）*（總分<>0））求出一中的實考人數(shù)，兩者相除求出一中平均分。要統(tǒng)計語文等學科的重點人數(shù)，本科人數(shù)等數(shù)據(jù)時，寫入類似公式即可。寫完一中對應的公式后，復制公式到下一行，用查找替換將所有公式中的“1”改為“2”，馬上可以完成二中學校各項數(shù)據(jù)的統(tǒng)計，以此類推，統(tǒng)計其它學校的數(shù)據(jù)。

提示：可以將函數(shù)某個條件直接寫成字段名，表示對符合條件的單元格數(shù)據(jù)進行合計，比sumif函數(shù)更靈活。即 SUMPRODUCT（（學校="1"）*總分）即表示求一中所有學生總分之和。

五、統(tǒng)計班級數(shù)據(jù)

切換到班級工作表，如圖5所示，需求出每個班級上線人數(shù)。在D5單元格輸入公式“=SUMPRODUCT（（班級= B5）*（總分<>0））”求出1101班級實考人數(shù)。在E5單元格中輸入公式“=SUMPRODUCT（（班級= B5）*（總分>=上線分！$B$2））”求出1101班級總分上重點線的人數(shù)。在I5單元格中輸入公式“=SUMPRODUCT（（班級=B5）*總分）/SUMPRODUCT（（班級= B5）*（總分<>0））”求出1101班級總分的平均分。其它學科的統(tǒng)計公式與總分類似。1101班右邊的公式編寫好后，用復制再查找替換的方法編寫出每個班級的計算公式。注意“班級”一列格式設(shè)為文本。

到此為止，所有公式編寫完畢，這么多公式第一次編寫確實很累，但卻是一勞永逸。因為每次考試統(tǒng)計的項目都是一樣的，以后你只需把成績表和上線分表中的數(shù)據(jù)替換一下，馬上可完成所有數(shù)據(jù)的統(tǒng)計。