先看一下原始成績(jī)表，如圖1所示。
第一列為學(xué)生的考號(hào)，其中考號(hào)的第5位和第6位數(shù)字是班級(jí)代碼。學(xué)生各科的成績(jī)分別在C、D、E、F列。而我們需要統(tǒng)計(jì)的是各學(xué)科各班的總分、平均分及各分?jǐn)?shù)段人數(shù)等項(xiàng)指標(biāo)，如圖2所示。
一、總分的統(tǒng)計(jì)
在不破壞原工作表順序的情況下，總分的統(tǒng)計(jì)我們可以使用SUM函數(shù)及數(shù)組公式來完成。
首先點(diǎn)擊L3單元格計(jì)算01班語文科的總成績(jī)。
輸入“=SUM(IF(MID($A$2:$A$150,5,2)= $K3,$C$2:$C$150))”，然后按下“Ctrl+Shift+Enter”快捷鍵，可以看到公式的最外層添加了數(shù)組公式標(biāo)記(一對(duì)大括號(hào))，公式變身成為“{=SUM(IF(MID($A$2:$A$150,5,2)= $K3,$C$2:$C$150))}”。切記這對(duì)大括號(hào)是不可以手工輸入的。
按下快捷鍵后，L3單元格立刻顯示出了01班語文科的總分?？彀?還有更快的呢。單擊此單元格，向下拖動(dòng)其填充句柄至L10單元格復(fù)制此公式，立刻各班的語文科總分就都有了。
方便吧?簡(jiǎn)單介紹一下公式中用到的相關(guān)函數(shù)，那咱們就可以舉一反三了。
MID($A$2:$A$150,5,2)= $K3：K3單元格是班級(jí)代碼。而MID函數(shù)可以截取字符串中的指定字符，如MID(A2,5,2)的含義是從A2單元格字符串的第5個(gè)字符開始截取2個(gè)字符組成新的字符串。至于公式中所寫的的MID($A$2:$A$150,5,2)就可以利用數(shù)組公式對(duì)A2至A150單元格中的字符串逐一截取了。
而IF函數(shù)當(dāng)然是用作判斷了。IF(MID($A$2:$A$150,5,2)= $K3是判斷截取的字符串是否與在K3單元格中的字符串相同。
整個(gè)公式的意思是：逐一判斷A2至A150單元格中第5、6字符串是否與K3單元格的字符串相同，如果相同，那么就把同一行中C列的單元格數(shù)據(jù)進(jìn)行累加。
明白了各班總分是怎么計(jì)算出來的，那么平均分的計(jì)算就易如反掌了。仍然點(diǎn)擊L3單元格，拖動(dòng)其填充句柄向右至M3單元格，松開鼠標(biāo)后在M3單元格同樣可以得到01班語文科的總分。不要緊，在公式編輯欄中把公式中的“SUM”函數(shù)更改為“AVERAGE”函數(shù)，再按下“Ctrl+Shift+Enter”快捷鍵。好了，現(xiàn)在看到結(jié)果了吧?
選中M3單元格，向下拖動(dòng)填充句柄至M10單元格復(fù)制此公式。行了，各班的語文平均分就都有了。
三、分?jǐn)?shù)段統(tǒng)計(jì)
在混合編班而又不破壞原表順序的情況下，我們就不能使用常用的COUNTIF函數(shù)或FREQUENCY函數(shù)來統(tǒng)計(jì)各分?jǐn)?shù)段人數(shù)。我們可以使用SUMPRODUCT函數(shù)來完成這個(gè)任務(wù)。
點(diǎn)擊N3單元格統(tǒng)計(jì)01班語文成績(jī)?cè)?0分以上的人數(shù)。在此單元格輸入公式“=SUMPRODUCT((MID($A$2:$A$150,5,2)=$K3)*($C$2:$C$150>=90))”，然后按下回車鍵。對(duì)，是回車鍵，而不是前面用的“Ctrl+Shift+Enter”快捷鍵。按下回車鍵后自然可以看到統(tǒng)計(jì)結(jié)果了。
這個(gè)公式的意思是：統(tǒng)計(jì)考號(hào)的5、6兩位字符串與K3單元格字符串相同且C2:C150單元格數(shù)據(jù)大于90的單元格個(gè)數(shù)。“*”符號(hào)前后的括號(hào)內(nèi)就是我們?cè)O(shè)置的條件。如果要設(shè)置的條件更多，那就再添加“*”，并在其后添加括號(hào)，在括號(hào)內(nèi)添加條件就是了。
如法炮制，在O3單元格輸入如下公式“=SUMPRODUCT((MID($A$2:$A$150,5,2)=$K3)*($C$2:$C$150>=80)*($C$2:$C$150<90))”，回車后就可以得到80-89分這一分?jǐn)?shù)段的人數(shù)了。
選中O3單元格，向右拖動(dòng)其填充句柄復(fù)制公式至S3單元格。并根據(jù)要統(tǒng)計(jì)的不同的分?jǐn)?shù)段，修改各單元格的公式。如S3單元格的公式應(yīng)修改為“=SUMPRODUCT((MID($A$2:$A$150,5,2)=$K3)*($C$2:$C$150<50))”。
好了，現(xiàn)在01班的各分?jǐn)?shù)段已經(jīng)統(tǒng)計(jì)出來了。選中N3:S3單元格區(qū)域，向下拖動(dòng)填充句柄至S10單元格復(fù)制相應(yīng)公式。OK，各班分?jǐn)?shù)段統(tǒng)計(jì)完畢。最終效果如圖3所示。
四、其它各科的成績(jī)統(tǒng)計(jì)
以數(shù)學(xué)科統(tǒng)計(jì)為例。細(xì)細(xì)想來，數(shù)學(xué)科的統(tǒng)計(jì)與語文科成績(jī)唯一的不同就是語文成績(jī)?cè)贑列而數(shù)學(xué)成績(jī)?cè)贒列。所以，我們只要復(fù)制語文統(tǒng)計(jì)表然后把其中的“$C$2:$C$150”替換為“$D$2:$D$150”就可以了。
先選中K1:S10單元格區(qū)域，按下“Ctrl+C”快捷鍵復(fù)制，再單擊K12單元格，然后按下“Ctrl+V”快捷鍵進(jìn)行粘貼。把K12單元格的“語文”修改為“數(shù)學(xué)”?，F(xiàn)在選中新復(fù)制過來的K12:S21單元格區(qū)域。然后按下“Ctrl+F”快捷鍵，打開“查找和替換”對(duì)話框。然后點(diǎn)擊“替換”選項(xiàng)卡，在“查找內(nèi)容”輸入框中輸入要查找的內(nèi)容“$C$2:$C$150”，再在“替換為”輸入框中輸入要替換的內(nèi)容“$D$2:$D$150”。點(diǎn)擊下方的“全部替換”按鈕。OK，數(shù)學(xué)成績(jī)的統(tǒng)計(jì)就算是大功告成了。如圖4所示。
其它各科的依此類推。
聯(lián)系客服
