Excel應對特殊學產生績分析統計

1.考試混合編，成績統一理——老方法遇到新問題

關於使用Excel進行學產生績處理，已經是老話題了。但在實際工作中還是會有很多新問題，例如，現在很多學校都是全年級各班混在一起考試，以防考試改卷中的不正當競爭。而統計成績時，則是將已判分但未拆封的考卷統一交到教務處，先按座位號順序（每本考卷的自然順序）錄入各科分數，再分析統計出全年級各科成績。舉例說明，如圖1（記錄11至830隱藏了），要統計二（1）班優秀人數，傳統做法就是先按考試號排序，再通過公式“=COUNTIF（分數！D2：D69，">=96"）”求出。它的弊端是要手工逐個修改 “D2：D69”這個參數中的兩個行號（2和69），這可是一項工作量很大的工作。當然，簡單的方法還是有的，往下看吧。

2.初步準備——考試號裡提班級

如圖1，從B列的考試號中取出前三位（班級編號）放在S列，即在儲存格S2輸入公式“=LEFT（B2，3）”，然後雙擊（或拖動）S2儲存格右下角的填滿控點即可。

3.再做輔表——班級等級變形平板

在圖1所示的活頁簿中再建立一工作表，並將其命名為“等級”，在儲存格A1中輸入公式“=分數！A1”，斷行符號，選定A1，按住A1右下角的填滿控點向右下拖至C840儲存格，將“分數”工作表中的姓名、考號、座位號引用到“等級”工作表中（注意，千萬不能複製粘貼過來，這樣不能保持兩表資料的一致性）。再選定C1，按住C1右下角的填滿控點向右拖至L1儲存格，將語文、數學等9個學科科目引用過來。接著，在D2儲存格中輸入IF嵌套公式“=IF（分數！D2>=96，分數！$S2&&"a"，IF（分數！D2>=72，分數！$S2&&"b"，IF（分數！D2<48，分數！$S2&&"d"，分數！$S2&&"c"）））”。D2儲存格中公式的含義是：看“分數！D2”儲存格中的分數（即“分數”工作表中李悅的語文分數）是否大於等於96.如果是，則在D2儲存格中填入“201a”——“分數”工作表中S2儲存格中的字元“201”加上“a”（“201”表示二（1）班，“a”表示成績等級為“優秀”）；如果不是（即小於96），再看是否大於等於72.如果是，則在D2儲存格中填入“201b”；如果不是（即小於72），再看是否小於48.如果是，則在D2儲存格中填入“201d”；如果不是（即小於72大於48），則在D2儲存格中填入“201c”。最後按住D2儲存格右下角的填滿控點向右下拖至L840儲存格，就可以將每個學生各科成績的等級及所屬班級都填好了

4.最終統計——所需資料瞬間齊

輔表制好之後，言歸正傳回到“統計”工作表（如圖2）中，在A17到E28儲存格範圍中利用自動填滿功能再製作一小塊輔助資料（如圖2）。

萬事俱備，下面開始班級總人數及優秀率、及格率等的統計了。仍以二（1）班優秀率為例，現在就改用這樣的公式了“COUNTIF（等級！$D：$D，$B17）”，即對“等級”工作表中D列所有儲存格進行統計（等級！$D：$D），找出值為“201a”（本工作表即“統計”工作表的$B17的值，代表二（1）班優秀率）的儲存格數目。 具體做法如下：

（1）班級總人數（在B4儲存格中輸入）：“=COUNTIF（分數！$S：$S，A17）”；

（2）優秀人數（在C4儲存格中輸入）：“=COUNTIF（等級！$D：$D，$B17）”；

（3）優秀率（在D4儲存格中輸入）：“=C4/$B4 100”；

（4）及格人數（在E4儲存格中輸入）：“=COUNTIF（等級！$D：$D，$B17）+COUNTIF（等級！$D：$D，$C17）”；

（5）及格率（在F4儲存格中輸入）：“=E4/$B4 100”；

（6）低分人數（在G4儲存格中輸入）：“=COUNTIF（等級！$D：$D，$E17）”；

（7）低分率（在H4儲存格中輸入）：“=G4/$B4 100”；

到此為止，其餘資料通過自動填滿功能，瞬間即可完成。

5.方法點評——一表成，終年用，一勞而永逸

（1）不同年級成績統計的簡單套用：比如，首先製作好了一年級的統計表，通過複製粘貼將第一個工作表（“分數”工作表）的內容更改為二年級的資料表，則二年級的成績統計便自然而成。

（2）多次考試成績統計的簡單套用：這次考試的統計表，到下次考試成績統計時，照用不誤，只將第一個工作表換成新生的成績記載就可以了。