向澤林 譚俊

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

關(guān)鍵詞：Excel 2016;成績;教學效果;評價學習

中圖分類號：TP391.1? ? ? ?文獻標識碼：A 文章編號：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? 引? 言

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

1? 成績查詢、排名、標準分計算及特殊標注

1.1? VLOOKUP函數(shù)快速查找對應班級成績

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

從“學生?？汲煽儽怼笨梢钥闯霰碇辛_列出的信息有學生的學號、各部分對應的分值，以及總成績等，但沒有班級，并且每次?？紨?shù)據(jù)單獨在一個工作表中。這些都對統(tǒng)計分析非常不方便，也不便于觀察學生的成績變化，因此我們就應該將四個工作表的?？紨?shù)據(jù)集中在一個“匯總”工作表中，如圖1所示。那怎樣才能在匯總表中快速得出翻譯1班所有學生每次考試一一對應的成績呢？

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

接下來，教師可用平均函數(shù)AVERAGE計算出翻譯1班和2017級翻譯學院所有學生四次?？嫉钠骄煽儯绫?所示。通過表格數(shù)據(jù)的比對，就能輕易看出這位老師所教翻譯1班的學生水平，基本上和年級平均分持平，屬于中等。當然，除此之外，教師還可以縱向比較，觀察不同年級學生的學習情況。

1.2? 名次及標準分計算

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

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

1.3? 重點突出顯示

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

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

2? 直方圖顯示學生成績情況

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

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

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

3? 預測成績趨勢變化

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

4? 結(jié)? 論

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

參考文獻：

[1] 教育部考試中心.全國計算機等級考試二級教程——MS Office高級應用：2013年版 [M].北京：高等教育出版社，2013.

[2] 韓輝珍.Excel在學生成績管理中的應用 [J].中國科技信息，2008（23）：39+41.

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

作者簡介：向澤林（1975.12—），男，漢族，四川資陽人，講師，研究生，研究方向：計算機基礎(chǔ)教學、信息化辦公;譚?。?978.07—），女，漢族，新疆伊寧人，講師，研究生，研究方向：英語語言學、英語教學法。