Excel函數(shù)公式在高校“個(gè)性化”成績(jī)分析表中的運(yùn)用

文/陳英芳

1 案例分析，制作成績(jī)表

班級(jí)：普通專(zhuān)科財(cái)務(wù)管理18-1班，課程：《計(jì)算機(jī)應(yīng)用基礎(chǔ)》，人數(shù)：32人，班上學(xué)習(xí)積極，但是計(jì)算機(jī)水平參差不齊，部分學(xué)生基礎(chǔ)好，但是班上的幾位藏族同學(xué)在以前很少接觸過(guò)電腦，基礎(chǔ)差，因此成績(jī)表要反應(yīng)出學(xué)生名次、全班平均成績(jī)、漢族和藏族學(xué)生差異、各分?jǐn)?shù)段人數(shù)。本課程屬于考查課，注重過(guò)程化評(píng)價(jià)，平時(shí)成績(jī)40%，期末上機(jī)卷面成績(jī)占60%，其中平時(shí)成績(jī)=課堂活動(dòng)表現(xiàn)*40%+模擬考試一*30%+模擬考試二*30%（所有成績(jī)百分制），成績(jī)表中需反應(yīng)出平時(shí)成績(jī)與期末卷面成績(jī)的差距，關(guān)注考試“失常”考生，并備注：可疑。根據(jù)以上需求，建立如圖1所示的工作表。

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

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

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

圖1

圖2

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

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

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

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

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

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

3 結(jié)語(yǔ)

結(jié)合本班需求的“個(gè)性化”成績(jī)分析表能更好地反應(yīng)出本班的學(xué)習(xí)情況，反饋和導(dǎo)向教師教學(xué)，Excel擁有強(qiáng)大的數(shù)據(jù)處理功能，合理運(yùn)用函數(shù)公式來(lái)制作“個(gè)性化”成績(jī)分析表，教師充分地利用“數(shù)據(jù)”反饋教學(xué)效果，調(diào)整教學(xué)，有效促進(jìn)教學(xué)。

圖3