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