SUMIFCOUNTIF和SUMPRODUCT函數(shù)在成績統(tǒng)計中的應(yīng)用

1、SUMIF、COUNTIF和SUMPRODUCT函數(shù)在成績統(tǒng)計中的應(yīng)用在學(xué)校組織的學(xué)年考試考務(wù)工作中，為了便于評卷實行流水作業(yè)和避免學(xué)生作弊，經(jīng)常將同一年級不同班級的學(xué)生拆散混編。在考試登分完畢之后，如何做到不改變原始表格的狀態(tài)而進(jìn)行分班成績統(tǒng)計呢？利用SUMIF、COUNTIF和SUMPRODUCT這三個函數(shù)，一切都將會變得十分輕松。下面舉例說明。 一、造冊登分 登分冊一般有班級、考號、姓名及語文、數(shù)學(xué)等學(xué)科名稱，我們可以把這個工作表建立在Sheet1，并將其重命名為“登分冊”，將學(xué)生成績輸入其中（見圖一）。將Sheet2命名為“統(tǒng)計表”，在其中設(shè)置好班級、科目、平均分、及格人數(shù)、及格率、優(yōu)

2、生人數(shù)、優(yōu)生率等統(tǒng)計信息列標(biāo)題，輸入班級和需要統(tǒng)計的科目內(nèi)容（如圖二）。 二、建立函數(shù) 在“統(tǒng)計表”中的C2單元格輸入數(shù)組公式=SUMIF(登分冊!$A$2：A$13,"一（1）",登分冊!$D$2：D$13)，按住Ctrl和Shift鍵回車可以計算出一（1）班的總分，將公式向下復(fù)制到各單元格，并將其中的“一（1）”班分別改為相應(yīng)班級名稱。這個數(shù)組公式的含義是：在“登分冊”工作表中查找“一（1）班對應(yīng)的成績，并返回一（1）班所有成績之和。如圖三 在“統(tǒng)計表”D2單元格輸入公式=ROUND(C2/COUNTIF(登分冊!$A$2：A$13,統(tǒng)計表!A2),2)計算出一（1）班

3、的平均分，向下拖動復(fù)制公式到相應(yīng)單元格。該公式的含義是：將C2中的總分除以“登分冊”A列中與A2顯示的值（一（1)班）相同的人數(shù)，并保留兩位小數(shù)。 在E2中輸入數(shù)組公式計算及格人數(shù)。該數(shù)組函數(shù)為=SUMPRODUCT(登分冊!$A$2：A$13=統(tǒng)計表!$A2)*(登分冊!$D$2：D$13>=60)，其含義是：若“登分冊”工作表A列中有與“統(tǒng)計表”A2相同的信息，即“一（1）班”，則返回與“一（1）班”相對應(yīng)的成績中大于或等于60分的人數(shù)（如圖四）。將公式向下復(fù)制后，在F2單元格中輸入公式=E2/COUNTIF(登分冊!$A$2：A$13,統(tǒng)計表!$A2)計算該班的及格率。 將E兩列中

4、的公式復(fù)制到G列，并將公式是的">=60 "修改成學(xué)校規(guī)定的優(yōu)生標(biāo)準(zhǔn)（如是80分為優(yōu)生，則將60改為80即可）（如圖五）。同理，將F列中的公式復(fù)制到H列即可得到優(yōu)生率的統(tǒng)計結(jié)果。 需要提醒大家的是，使用數(shù)組函數(shù)，一定不要忘記是Ctrl+Shift+回車。用VLOOKUP在教師工資表中實現(xiàn)錯序修正一、 問題的提出 工資改革后，縣教育系統(tǒng)的工資信息每年都要整理一次，財政局常要求各學(xué)校按其下發(fā)的工資人員信息表的順序來處理本校教師的工資信息。但由于各學(xué)校處理工資信息的人對計算機(jī)操作不熟練，做出來的順序通常與財政局的工資表人員順序不同，這導(dǎo)致財政局去處理這些信息時比較麻煩，要實

5、現(xiàn)每個人員信息相對應(yīng)，則要不停核對每一個人員信息，工作量非常大。 那我們使用計算機(jī)處理一下，是否會更快、更準(zhǔn)確呢？答案是肯定的。我們只要掌握了WPS表格的函數(shù)VLOOKUP的應(yīng)用就可以順利解決上面的問題。 二、問題的處理 1、VLOOKUP的功能分析 作用：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。即將一個數(shù)據(jù)通過與某數(shù)據(jù)表第一列比較，找出與其相對應(yīng)的某列的值。 語法格式：VLOOKUP（“查找值”、“數(shù)據(jù)表”、“列序數(shù)”、“匹配條件”） 參數(shù)分析： 查找值：此參數(shù)表明需要在“數(shù)據(jù)表”中第一列需要查找的值。 數(shù)據(jù)表：此參數(shù)指的是數(shù)據(jù)在哪查找。 列序數(shù)：此

6、參數(shù)指的是查找到符合值后，返回相對應(yīng)的第幾列數(shù)據(jù)。 匹配條件：設(shè)置是否為精確匹配。 錯序的修正 對于有錯序的修正，必須提供一份正確排序的數(shù)據(jù)信息。對于工資信息來講，之前每個月發(fā)放工資的順序是不變的，可以復(fù)制過來一份作為參數(shù)數(shù)據(jù)表。而由各學(xué)校未按順序處理的表格，可參考這份正確順序的信息表來修正。 假設(shè)我們在工作表“Sheet1”中保存了正確順序的工資人員信息，如下圖： 再假設(shè)我們在工作表“Sheet2”中保存了錯序的工作人員信息，如下圖： 對照兩表，可以看到，只要我們能將錯序的姓名加上正確的工資編號，然后再按工資編號排序一下就完成了修正工作。 在這里我們?yōu)榱耸褂肰LOOKUP函數(shù)結(jié)合姓名來查找到

7、工資編號，我們將正確順序的表sheet1工資編號與姓名位置互換一下。如下圖： 下面我們使用VLOOKUP函數(shù)把各人的工資編號從sheet1表中找出來。 在Sheet2表B2單元格輸入公式“=VLOOKUP(A2,Sheet1!A2:B12,2,FALSE)”并確定即可找到張誠的正確工資編號了。為復(fù)制公式時數(shù)據(jù)表不發(fā)生變化，我們將公式修改為 “=VLOOKUP(A2,Sheet1!$A$2:$B$12,2,FALSE)”，然后再執(zhí)行復(fù)制操作，即可找到各人所對應(yīng)的正確工資編號了。 最后，我們將sheet2表，按工資編號進(jìn)行升序排序一次，即可實現(xiàn)正確排序了。利用ET函數(shù)，輕松統(tǒng)計小學(xué)畢業(yè)考試成績我縣

8、教研室自2007年開始實行全縣小學(xué)畢業(yè)統(tǒng)一考試，印發(fā)的成績登計冊如圖一所示。小學(xué)畢業(yè)考試一般以鄉(xiāng)鎮(zhèn)為單位，要求各學(xué)校的學(xué)生打亂編排考號。經(jīng)過反復(fù)研究，我鎮(zhèn)利用ET函數(shù)，解決了考場編排，登分統(tǒng)計的問題。 一、建立全鄉(xiāng)鎮(zhèn)總冊，表冊欄目見圖二，將工作表命名為“總冊”，并將各學(xué)校學(xué)生的信息以班級為單位逐一錄入總冊。 二、按圖一所示建立各班級成績登計表，將工作表按學(xué)校（班級）名稱命名，以便查找，同時將總冊中某一班級的學(xué)生姓名復(fù)制粘貼到成績登計表中，并填寫好表格上方的學(xué)校、班級、各科教師姓名、學(xué)生人數(shù)等信息。 三、建立總冊函數(shù)。 首先在總冊A2單元格中輸入函數(shù)“rand()”回車后拖動光標(biāo)向下復(fù)制，A列的

9、各單元格將產(chǎn)生一個隨機(jī)數(shù)（如圖三）。 選定表格，以“隨機(jī)數(shù)”為關(guān)鍵字排序，然后在B2單元格輸入0，B3單元格輸入1，選中B2:B3，拖住B3單元格右下角十字光標(biāo)向下復(fù)制到所需單元格（如圖四）。 將F列格式設(shè)置為文本格式，在F2單元格按照文件要求輸入準(zhǔn)考證號06303610001，拖動F2右下角十字光標(biāo)向下復(fù)制。在G2單元格公式“=INT(B2/30)+1”并向下復(fù)制，可以自動計算出各個考號對應(yīng)的考場號（如圖五）。至此，總冊的函數(shù)編寫完畢。 四、建立班級分表函數(shù)。 以安底小學(xué)六（1）班為例，我們首先將六（1）班的學(xué)生姓名復(fù)制粘貼到“安小1”工作表之中，在A5單元格中輸入函數(shù)“=IF($B5>

10、;0,VLOOKUP($B5,(總冊!$E$2:$K$65535),2,FALSE),"")、在C5中輸入“=IF($B5>0,VLOOKUP($B5,(總冊!$E$2:$K$65535),4,FALSE),"")”、在E5中輸入“=IF($B5>0,VLOOKUP($B5,(總冊!$E$2:$K$65535),5,FALSE),"")”、在G5中輸入“=IF($B5>0,VLOOKUP($B5,(總冊!$E$2:$K$65535),6,FALSE),"")”，將這些函數(shù)分別向下復(fù)制。 同理，在K

11、5中輸入“=IF($L5>0,VLOOKUP($L5,(總冊!$E$2:$K$65535),2,FALSE),"")”、M5中輸入“=IF($L5>0,VLOOKUP($L5,(總冊!$E$2:$K$65535),4,FALSE),"")”、O5中輸入“=IF($L5>0,VLOOKUP($L5,(總冊!$E$2:$K$65535),5,FALSE),"")”，Q5中輸入“=IF($L5>0,VLOOKUP($L5,(總冊!$E$2:$K$65535),6,FALSE),"")”并向下復(fù)制。

12、 上述函數(shù)建立后，整個班級學(xué)生的考號、各科成績將自動顯示。 若要將學(xué)生的成績由分?jǐn)?shù)轉(zhuǎn)換為等第，可在D5中輸入“=IF(B5>0,IF(C5>=80,"A",IF(C5>=70,"B",IF(C5>=60,"C",IF(ISNUMBER(C5),"D",IF(ISBLANK(C5),""),"")”向下復(fù)制，然后將該列的公式復(fù)制到F、H、N、P、R列。 在表格上方的鞏固率后面的單元格中，輸入“=IF(C2>0,ROUND(S1/C2,2),"")”可自動計算學(xué)生鞏固率；