作者：IT168 長江邊上

　　如果你是一位教師，那么每次考試結束后都會需要對考試進行質量分析，相信老師們應該很少去使用計算器進行手工作業，取而代之的是強大的Excel，而下面的兩份表格可以說是老師們非常熟悉的樣表，當然還需要繪制正態分析曲線圖。

    一、成績統計表快速填寫

    現在，當完成考試成績的錄入工作后，你并不需要去借助函數或公式計算最高分、總分、平均分什么的，因為當前工作表窗口底部的狀態欄中已經顯示了平均值(即平均分)、計數、求和這三個數值，如圖1所示，右擊后可以從快捷菜單中選擇最小值(最低分)、最大值(最高分)，雖然及格率、差分率、優分率在這里無法直接體現出來，但只要通過簡單的排序和計算就可以完成。

圖1

圖2

    這里，我們以滿分100分為例進行說明，假如使用計算器或者更為古老的算盤，一方面會相當繁瑣，另外也非常容易出錯，既然是在Excel中統計成績，自然要充分利用Excel的強大功能，而最新版本的Excel 2007則更為簡單。下面，我們介紹獲取成績分布頻數的三種方法：

    (一)利用FREQUENCY函數

    FREQUENCY函數可以計算數值在某個區域內的出現頻率，然后返回一個垂直數值，我們正好利用這個函數來統計考試成績的頻數分布。不過，由于FREQUENCY函數返回的是一個數組，因此它必須以數組公式的形式輸入。

    語法：FREQUENCY(data_array,bins_array)

    Data_array：是一個數組或對一組數值的引用，我們主要通過該值計算頻率，如果此處不包含任何數值，那么函數FREQUENCY 將返回一個零數組。

    Bins_array：是一個區間數組或對區間的引用，該區間用于對data_array中的數值進行分組，如果此處不包含任何數值，那么函數FREQUENCY返回的值與 data_array中的元素個數相等。

    第1步：插入列

    在適當位置手工插入區間分割點、人數、百分比這三列，注意請完全按照教務部門發下來的樣表進行輸入(見圖3)，這樣做的好處當然是免得做無用功。

圖3

    第2步：設置區間分割點

    區間分割點的設置是極為重要的一個步驟，這里以應該參考"分數段"進行設置，例如"29"表示考試成績小于或等于29分的分數個數，"39"表示考試成績介于30～39分的分數個數，其余的依次類推(見圖4)。

圖4

    第3步：輸入數組公式

    選擇準備放置各分數段人數的單元格區域，這里選擇"F3：F11"，選擇結束后按下F2鍵，如圖5所示，在這里輸入公式"=FREQUENCY(C3:C44,E3:E10)"，為了避免出錯，建議通過插入函數的方法進行，這樣比較簡單方便。

圖5

    有些朋友可能會發現，這里選擇了"F3：F11"而非""F3：F10"，這是為什么呢？也就是說，這里較左側的區間分割點多選擇了一個單元格，其實這個多出來的單元格主要用于返回大于99分的學生人數，原因是因為返回的數組中的元素個數(分布頻數)比bins_array(分段點)中的元素個數多1，多出來的單元格將返回大于最末一個間隔值的數值個數，否則返回結果會出錯。

    第4步：返回數組個數

    前面已經說明，由于FREQUENCY返回的結果是數組形式，因此公式輸入完成后必須按下"Ctrl+Shift+Enter"組合鍵進行確認，其標志是出現一對大括號"｛｝"，計算結果如圖6所示。

圖6

    如果需要在查看結果和查看返回結果的公式之間切換，請按 Ctrl+`(重音符)，或在"工具"菜單上，指向"公式審核"，再單擊"公式審核模式"。

    第5步：計算百分比

    接下來的工作就非常簡單了，選中G3單元格，輸入"＝F3/42"，將單元格數值設置為百分比格式，這一步工作可以在"單元格格式→數字"窗口中完成，最終結果如圖7所示。

圖7

    COUNTIF函數可以計算區域中滿足給定條件的單元格的個數，命令語法是"COUNTIF(range,criteria)"，這里的"range"是一個或多個要計數的單元格，期中包括數字或名稱、數組或包含數字的引用，當然如果是空值或文本值將被忽略；而"criteria"的形式可以為數字、表達式、單元格引用或文本。

    第1步：設置區間分割點

    首先，我們還是手工創建區間分割點、人數、百分比三個空白列，但在設置區間分割點時，注意與使用FREQUENCY函數時有一些區別，請按照圖8所示進行設置，這里的"99.9"表示考試成績為滿分100的學生數，"89.9"表示考試成績在90～99之間的學生數，其余的依次類推。

圖8

    第2步：計算滿分的學生數

    選中F3單元格，在這里輸入公式"=COUNTIF(C$3:C$44,">"&$E3)"，這里的"C$3:C$44"表示需要計數的單元格區域，而"">"&$E3"則表示大于99.9分，計算結果如圖9所示。

圖9

    第3步：計算90～99分數區間段的學生數

    選中F4單元格，在這里輸入公式"=COUNTIF(C$3:C$44,">"&$E4)-COUNTIF(C$3:C$44,">"&$E3)"，很快就可以得到圖10所示處于90～99分數區間段的學生人數。

圖10

    這個公式分成兩個環節：

    (1) COUNTIF(C$3:C$44,">"&$E4)：計算出考試成績大于89.9分的學生人數
    (2) COUNTIF(C$3:C$44,">"&$E3)：計算出考試成績大于99.9分的學生人數

    根據成績分布頻數樣表的要求，我們需要的各個區間分割點的學生人數，而非累加的數字，因此需要對這兩份數字進行減運行，否則最后所得到的結果就不是我們所需要的了。

    接下來的工作就相當簡單了，選中F3單元格，拖曳右下角的填充柄即可得到圖11所示的成績分布頻數，至于各個區間學生數的百分比，可以按照前面的方法進行計算，這里就不再多作介紹。

圖11

    SUMPRODUCT函數的功能是在給定的幾組數組中，將數組間對應的元素相乘，并返回乘積之和，命令語法是"SUMPRODUCT(array1,array2,array3, ...)，這里的"Array1, array2, array3, ..."為2～255個數組，其相應元素需要進行相乘并求和。需要注意的是，數組參數必須具有相同的維數，否則將會錯誤值"#VALUE!"。

    使用SUMPRODUCT函數時，前面的步驟都與COUNTIF函數相同，但在F3單元格統計滿分段的學生人數時，輸入的公式應該是"=SUMPRODUCT((C$3:C$44=100)*(C$3:C$44>0))"，執行后可以獲得滿分段的學生人數(見圖12)；接下來選中F4單元格，輸入公式"=SUMPRODUCT((C$3:C$44>$E4)*(C$3:C$44<$E3))"，執行后即可獲取90～99分數區間段的學生人數，最后拖曳填充柄即可得到其它區間段的學生人數，最終結果如圖13所示。

圖12

圖13