Excel在學(xué)生成績(jī)統(tǒng)計(jì)分析中的有效應(yīng)用

向澤林 譚俊

摘? 要：學(xué)生成績(jī)統(tǒng)計(jì)分析是每位教師在教學(xué)工作中的重要一環(huán)，如何方便、快捷地處理，并對(duì)教學(xué)效果進(jìn)行客觀評(píng)價(jià)，一直是廣大教師特別關(guān)注的問(wèn)題。文章主要講述Excel中的函數(shù)、直方圖、預(yù)測(cè)工作表等功能在成績(jī)統(tǒng)計(jì)分析上的應(yīng)用，這些操作不僅可以優(yōu)化繁瑣的計(jì)算過(guò)程，還可以系統(tǒng)地分析成績(jī)分布、變化趨勢(shì)等，從而進(jìn)一步完善教學(xué)，改進(jìn)出題和考試方式等，使之真正為教學(xué)服務(wù)。

關(guān)鍵詞：Excel 2016;成績(jī);教學(xué)效果;評(píng)價(jià)學(xué)習(xí)

中圖分類號(hào)：TP391.1? ? ? ?文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：2096-4706（2020）07-0178-03

The Effective Application of the Excel in Statistical Analysis of Student Achievements

XIANG Zelin1，TAN Jun2

（1.Foundation Section of Chengdu Institute Sichuan International Studies University，Chengdu? 611844，China;2.English Department of Translation and Interpretation of Chengdu Institute Sichuan International Studies University，Chengdu? 611844，China）

Abstract：Statistical analysis of student achievements is an important part of every teachers teaching work. How to deal with it conveniently and quickly and to evaluate the teaching effect objectively，has been a special concern of most teachers. This paper focuses on the application of certain Excels functions，such as the function，histogram，forecasting table，in the statistical analysis process. Such applications not only optimize the tedious calculation process but also analyze the distribution of scores，the changing trends，etc. systematically. So as to further improve teaching and the ways of examination，thus better serve for the teaching.

Keywords：Excel 2016;achievement;teaching effects;evaluation

0? 引? 言

學(xué)生成績(jī)是檢查教學(xué)質(zhì)量的一個(gè)量化指標(biāo)。每次考試結(jié)束后，老師都要對(duì)學(xué)生的成績(jī)進(jìn)行仔細(xì)統(tǒng)計(jì)、詳細(xì)分析、全面評(píng)價(jià)。紙質(zhì)加人工的傳統(tǒng)管理方式，不僅效率低、效果差，而且容易出錯(cuò)，但利用MS Office辦公軟件中的Excel就能輕松、高效地解決這些的問(wèn)題。本文將結(jié)合四川外國(guó)語(yǔ)大學(xué)成都學(xué)院計(jì)算機(jī)等級(jí)考試成績(jī)和翻譯學(xué)院2017級(jí)英語(yǔ)專業(yè)四級(jí)模擬考試成績(jī)，介紹如何使用Excel 2016進(jìn)行成績(jī)統(tǒng)計(jì)、分析、圖表展示等，從而達(dá)到對(duì)學(xué)生的學(xué)習(xí)情況進(jìn)行客觀評(píng)價(jià)，以及對(duì)學(xué)生過(guò)級(jí)率進(jìn)行預(yù)測(cè)，進(jìn)而有助于有針對(duì)性地采取措施，改進(jìn)教學(xué)。

1? 成績(jī)查詢、排名、標(biāo)準(zhǔn)分計(jì)算及特殊標(biāo)注

1.1? VLOOKUP函數(shù)快速查找對(duì)應(yīng)班級(jí)成績(jī)

實(shí)際教學(xué)工作中，每位老師經(jīng)常會(huì)遇到要統(tǒng)計(jì)自己所教班級(jí)學(xué)生的成績(jī)，進(jìn)而與全院所有班級(jí)作對(duì)比，并查看與其他院系老師、學(xué)生的差異，從而評(píng)估自己的教學(xué)效果。比如負(fù)責(zé)2017級(jí)翻譯1班的老師，在專四模擬考試成績(jī)公布后，她所接收到的是翻譯學(xué)院2017級(jí)1～8班全體學(xué)生模考1的成績(jī)總表，其數(shù)據(jù)表結(jié)構(gòu)和記錄如表1所示（其他三次模考成績(jī)類似）。

從“學(xué)生模考成績(jī)表”可以看出表中羅列出的信息有學(xué)生的學(xué)號(hào)、各部分對(duì)應(yīng)的分值，以及總成績(jī)等，但沒(méi)有班級(jí)，并且每次模考數(shù)據(jù)單獨(dú)在一個(gè)工作表中。這些都對(duì)統(tǒng)計(jì)分析非常不方便，也不便于觀察學(xué)生的成績(jī)變化，因此我們就應(yīng)該將四個(gè)工作表的模考數(shù)據(jù)集中在一個(gè)“匯總”工作表中，如圖1所示。那怎樣才能在匯總表中快速得出翻譯1班所有學(xué)生每次考試一一對(duì)應(yīng)的成績(jī)呢？

通過(guò)仔細(xì)觀察，我們發(fā)現(xiàn)兩個(gè)工作表共同擁有“學(xué)號(hào)”這個(gè)字段，這種情況下我們就可以利用Excel中的VLOOKUP函數(shù)來(lái)完成。方法是，在E3單元格中輸入公式：=VLOOKUP（A3，模考1！$B$2：$P$277，15，F(xiàn)ALSE），確定即可計(jì)算出第一位學(xué)生模考1的成績(jī)，然后向下填充，計(jì)算出其他所有學(xué)生模考1的成績(jī)。按照同樣的方法和步驟，快速計(jì)算出每位學(xué)生其余3次模考的成績(jī)。

接下來(lái)，教師可用平均函數(shù)AVERAGE計(jì)算出翻譯1班和2017級(jí)翻譯學(xué)院所有學(xué)生四次模考的平均成績(jī)，如表2所示。通過(guò)表格數(shù)據(jù)的比對(duì)，就能輕易看出這位老師所教翻譯1班的學(xué)生水平，基本上和年級(jí)平均分持平，屬于中等。當(dāng)然，除此之外，教師還可以縱向比較，觀察不同年級(jí)學(xué)生的學(xué)習(xí)情況。

1.2? 名次及標(biāo)準(zhǔn)分計(jì)算

很多老師在分析學(xué)生成績(jī)高低時(shí)，習(xí)慣性用排序（降序）的方式，這種方法固然可以實(shí)現(xiàn)，但排序后所有記錄的前后順序就會(huì)被打亂，比如不會(huì)再按照學(xué)號(hào)從小到大排列了。因此遇到這種情況就可以利用RANK函數(shù)來(lái)進(jìn)行操作。為了數(shù)據(jù)更有針對(duì)性，后面操作就用最臨近考試的第四次模考成績(jī)作為依據(jù)。方法是，在“匯總”表中I3單元格輸入公式：="第"&RANK（H3，$H$3：$H$33，0）&"名"，得出第一位同學(xué)在翻譯1班的名次（以“第幾名”的形式顯示），然后向下填充，計(jì)算出其余同學(xué)的班級(jí)排名，這樣的好處就是數(shù)據(jù)的順序沒(méi)有任何改變。

為進(jìn)一步了解學(xué)生此次考試情況，教師還可以利用學(xué)生成績(jī)算出標(biāo)準(zhǔn)差，進(jìn)一步算出學(xué)生標(biāo)準(zhǔn)分，標(biāo)準(zhǔn)分=（個(gè)人分-人均分）÷標(biāo)準(zhǔn)差（標(biāo)準(zhǔn)分是評(píng)價(jià)一個(gè)學(xué)生成績(jī)的重要指標(biāo)）。方法如下，在J3單元格輸入公式：=（H3-AVERAGE（$H$3：$H$33））/STDEVP（$H$3：$H$33），然后填充得出每位學(xué)生標(biāo)準(zhǔn)分。分值越大，說(shuō)明該學(xué)生成績(jī)?cè)絻?yōu)秀，與成績(jī)分?jǐn)?shù)、排名都不謀而合，但更能客觀反映學(xué)生學(xué)習(xí)情況。

1.3? 重點(diǎn)突出顯示

有時(shí)候教師查看學(xué)生成績(jī)后，希望將滿足一些特定條件的學(xué)生重點(diǎn)標(biāo)注出來(lái)（比如成績(jī)較高的或不及格的），便于教師在后續(xù)教學(xué)中真正做到因材施教。若采取逐條記錄的方法去操作，效率過(guò)低，特別是數(shù)據(jù)量較大時(shí)。這種情況下我們就可以利用Excel提供的條件格式，通過(guò)編輯公式來(lái)輕松實(shí)現(xiàn)。例如，在“匯總”數(shù)據(jù)表中，假設(shè)學(xué)生模考1、模考2、模考3三次模考每次成績(jī)都在48分以上，就屬于過(guò)專四比較有保障的范疇，于是需要特別標(biāo)注出來(lái)，那如何實(shí)現(xiàn)呢？方法是，選中所有數(shù)據(jù)區(qū)域（標(biāo)題行除外），條件格式中新建規(guī)則，在使用公式設(shè)置格式文本框中輸入：=AND（$F3>=48，$G3>=48，$H3>=48），然后根據(jù)需要設(shè)置顯示格式（本例為灰色填充），確定即可將滿足條件的記錄突出顯示。

通過(guò)以上一系列操作，就完成了翻譯1班學(xué)生四次模擬考試成績(jī)的統(tǒng)計(jì)分析和格式設(shè)置，結(jié)果如圖2所示。

2? 直方圖顯示學(xué)生成績(jī)情況

直方圖能直觀反應(yīng)一組數(shù)據(jù)的分布特征，用它可以觀察數(shù)據(jù)是否對(duì)稱分布（正態(tài)分布），判斷數(shù)據(jù)有誤異常，它能很好地體現(xiàn)學(xué)生整體學(xué)習(xí)情況。比如在“匯總”表中，獲得翻譯1班所有學(xué)生的模考4的成績(jī)后，教師就可以通過(guò)創(chuàng)建直方圖分析該班此次考試成績(jī)整體情況如何，方法如下。

選取模考4成績(jī)列區(qū)域，繪制圖表類型中的直方圖，并設(shè)置好圖表標(biāo)題、坐標(biāo)軸標(biāo)題、圖例、箱寬度等，本例“箱寬度（分?jǐn)?shù)段間隔）”設(shè)置為5，最后得到的效果如圖3所示。

通過(guò)圖表，可以直觀地看到翻譯1班模考4的成績(jī)基本符合正態(tài)分布（低分和高分比較對(duì)稱，占比小;中等分占絕大多數(shù)），且主要分布在34到44分之間。如果按照45分以上在全國(guó)英語(yǔ)專四考試時(shí)及格概率比較大來(lái)推算的話，僅僅只有7個(gè)人左右能取得合格證書，所以要整體增加通過(guò)率的話，教師必須攻克[34，44]這部分分?jǐn)?shù)段的學(xué)生，讓他們的成績(jī)提升起來(lái)。

3? 預(yù)測(cè)成績(jī)趨勢(shì)變化

在全國(guó)計(jì)算機(jī)等級(jí)考試MS Office一級(jí)考試中，我校學(xué)生的一次性通過(guò)率基本上都能維持在80.00%以上。現(xiàn)在，教師希望通過(guò)2016年3月至2019年9月的等級(jí)考試成績(jī)的歷史數(shù)據(jù)來(lái)創(chuàng)建預(yù)測(cè)，推斷今后幾年考試的通過(guò)情況、波動(dòng)幅度等，從而為接下來(lái)的教學(xué)做好相應(yīng)的調(diào)整。操作方法如下：選取日期和過(guò)級(jí)率兩列數(shù)據(jù)，創(chuàng)建“預(yù)測(cè)工作表”，即可自動(dòng)生成預(yù)測(cè)數(shù)據(jù)和圖表，如圖4所示。它不僅詳細(xì)地顯示了歷史數(shù)據(jù)和預(yù)測(cè)數(shù)據(jù)，還創(chuàng)建了預(yù)測(cè)圖，并清楚地展現(xiàn)出接下來(lái)幾次考試通過(guò)率的走勢(shì)，同時(shí)標(biāo)注了上限和下限范圍。預(yù)測(cè)數(shù)據(jù)雖然有波動(dòng)，但整體還是保持在80.00%到85.00%之間。當(dāng)然，如果歷史數(shù)據(jù)更多一些，預(yù)測(cè)的準(zhǔn)確性也就會(huì)更客觀、更準(zhǔn)確。

4? 結(jié)? 論

目前Office版本很多，本文基于Excel 2016進(jìn)行操作（本文所有數(shù)據(jù)、圖表都是在Excel應(yīng)用軟件平臺(tái)完成），它操作簡(jiǎn)單、易學(xué)，同時(shí)功能非常強(qiáng)大。由于篇幅原因，文章只是簡(jiǎn)單闡述了它在學(xué)生成績(jī)管理中最常用、最基本的一些統(tǒng)計(jì)和分析應(yīng)用，它使得成績(jī)管理工作實(shí)現(xiàn)程序化和自動(dòng)化，從而極大地提高了工作效率。另外，本文用到的所有計(jì)算、統(tǒng)計(jì)方法都可以直接應(yīng)用到教師平常對(duì)學(xué)生成績(jī)的統(tǒng)計(jì)分析工作中。

參考文獻(xiàn)：

[1] 教育部考試中心.全國(guó)計(jì)算機(jī)等級(jí)考試二級(jí)教程——MS Office高級(jí)應(yīng)用：2013年版 [M].北京：高等教育出版社，2013.

[2] 韓輝珍.Excel在學(xué)生成績(jī)管理中的應(yīng)用 [J].中國(guó)科技信息，2008（23）：39+41.

[3] 袁西亮，巫錦潤(rùn).Excel在學(xué)生成績(jī)統(tǒng)計(jì)與分析中的應(yīng)用 [J].考試周刊，2010（36）：170-171.

作者簡(jiǎn)介：向澤林（1975.12—），男，漢族，四川資陽(yáng)人，講師，研究生，研究方向：計(jì)算機(jī)基礎(chǔ)教學(xué)、信息化辦公;譚俊（1978.07—），女，漢族，新疆伊寧人，講師，研究生，研究方向：英語(yǔ)語(yǔ)言學(xué)、英語(yǔ)教學(xué)法。