Excel函數(shù)公式在高?！皞€性化”成績分析表中的運用

文/陳英芳

1 案例分析，制作成績表

班級：普通?？曝攧展芾?8-1班，課程：《計算機應用基礎》，人數(shù)：32人，班上學習積極，但是計算機水平參差不齊，部分學生基礎好，但是班上的幾位藏族同學在以前很少接觸過電腦，基礎差，因此成績表要反應出學生名次、全班平均成績、漢族和藏族學生差異、各分數(shù)段人數(shù)。本課程屬于考查課，注重過程化評價，平時成績40%，期末上機卷面成績占60%，其中平時成績=課堂活動表現(xiàn)*40%+模擬考試一*30%+模擬考試二*30%（所有成績百分制），成績表中需反應出平時成績與期末卷面成績的差距，關注考試“失?！笨忌?，并備注：可疑。根據(jù)以上需求，建立如圖1所示的工作表。

2 利用Excel函數(shù)公式完善工作表

（1）用Excel公式計算平時和期末成績，在目標單元格輸入：=表達式，在本案例中，在H2單元格輸入：=E2*40%+F2*30%+G2*30%，在J2單元格輸入：=H2*40%+I2*60%，然后利用Excel的自動填充功能向下拖動鼠標，平時成績和綜合成績就全算出來了。

（2）利用RANK函數(shù)計算成績排名。RANK函數(shù)在不改變原表單順序情況下進行排序,語法為：RANK（Number,ref,order),主要功能是返回某數(shù)字在一列數(shù)字中相對于其他數(shù)字的大小排位。其中“Number”是要查找排名的數(shù)字（單元格地址），“ref”是一組數(shù)或?qū)σ粋€數(shù)據(jù)列表的引用，“order”表示排序方式參數(shù)，如果為0或忽略，降序；非零值，升序。本案例中，如圖2，在K2單元格輸入=RANK(J2,$J$2:$J$33,0)，然后利用Excel的自動填充功能向下拖動鼠標，名次就全算出來了，需要注意的是：如果采用自動填充功能，ref的參數(shù)框要用絕對地址$J$2:$J$33，不然會出錯。

圖1

圖2

（3）利用IF函數(shù)判定成績是否異常。平時成績和卷面成績的差距過大，則考試可能有異常情況，先計算平時成績和卷面成績的差距，若差距大于20，在備注欄標記可疑。具體方法：在L2單元格輸入公式：=I2-H2然后按回車鍵，利用Excel的自動填充功能，得出所有學生的平時成績和卷面成績之差，然后用IF函數(shù)進行判定，在M2單元格輸入：=IF(OR(L2>20,L2<-20),"可疑"," ")，這里用到了IF函數(shù)的“或”關系，指平時成績和卷面成績相差20分即標注為可疑，其他顯示為空白。

（4）利用AVERAGE函數(shù)計算全班學生的平均成績。AVERAGE函數(shù)的語法為：AVERAGE(number1,number2……)其功能是計算所有參數(shù)數(shù)值的平均值。本案例中在O3單元格輸入：=AVERAGE(J2:J33)就得到全班平均成績。

（5）利用COUNTIF函數(shù)計算漢族學生人數(shù)和藏族學生人數(shù)。COUNTIF函數(shù)的主要功能是計算區(qū)域中滿足給定條件的單元格個數(shù)，在本案例中，O5單元格輸入：=COUNTIF(D2:D33,"漢")，O6單元格輸入：=COUNTIF(D2:D33,"藏")即可得到相應人數(shù)。

（6）利用SUMIF（）函數(shù)求出漢族學生成績之和和藏族學生成績之和，除以相應人數(shù)得到漢族和藏族同學的平均成績。

本案例中，在O8單元格輸入：=SUMIF(D2:J33,"漢",J2:J33)/O5得到漢族學生平均成績，在O9單元格輸入：=SUMIF(D2:J33,"藏",J2:J33)/O6得到藏族學生平均成績，了解民族學生之間的計算機水平差異能更好地因材施教，促進教學。

（7）FREQUENCY函數(shù)是一個頻率分布函數(shù)，其語法為：FREQUENCY(data array,bins array)，它是以一列垂直數(shù)組返回一組數(shù)組的頻率分布，其中，“data array”是用來計算頻率的數(shù)組，“bins array”是數(shù)據(jù)接收區(qū)間，也就是分段的界值，該函數(shù)返回的是數(shù)組，必須以數(shù)組公式的形式進行輸入。本案例中，學生綜合成績保留的一位小數(shù)，分段界值設置為59.9，如圖3，選中P13到p17五個單元格區(qū)域，插入函數(shù)FREQUENCY,輸入第一個參數(shù)J2:J33,第二個參數(shù)O13:O17，然后按下組合鍵Ctrl+Shift+Enter，就得到各分數(shù)段人數(shù)。

3 結語

結合本班需求的“個性化”成績分析表能更好地反應出本班的學習情況，反饋和導向教師教學，Excel擁有強大的數(shù)據(jù)處理功能，合理運用函數(shù)公式來制作“個性化”成績分析表，教師充分地利用“數(shù)據(jù)”反饋教學效果，調(diào)整教學，有效促進教學。

圖3