Excel在高中考試成績分析中的應用

吳正芳，王敏

（1.襄陽市第三中學，襄陽　441000；2.湖北文理學院數學與計算機科學學院，湖北 襄陽 441053）

Excel在高中考試成績分析中的應用

吳正芳1，王敏2

（1.襄陽市第三中學，襄陽441000；2.湖北文理學院數學與計算機科學學院，湖北 襄陽 441053）

0　引言

考試是教育活動中評價教學質量、衡量教學效果的重要手段之一，對學生的成績進行統計分析，能夠給教學活動、學生管理提供參考，是提升教學效果和學生成績的指導方向。高中考試成績的統計分析通常包括個人單科排名、個人總分的排名、各科目的最高分、最低分、平均分、中位數，各分數段的人數統計、多次考試成績的比較以及成績預警等。學生多、考試科目多、考試頻繁，由此帶來了大量的統計分析工作負荷，運用現代信息技術手段高效完成上述統計是非常必要的，Ex cel電子表格程序正是一個合適的工具［1］。本文以Ex cel 2010為例介紹。

Excel是Microsoft Office中的電子表格程序，用戶界面友好、計算能力強大、操作簡便。使用Excel可以創建電子表格并設置格式，以便分析數據和做出更明智的業務決策，還可以使用Excel跟蹤數據，生成數據分析模型，編寫公式以對數據進行計算，以多種方式透視數據，并以各種具有專業外觀的圖表來顯示數據。

1　運用“條件格式”實現快速直觀分析

1.1成績預警

面對成績表格中的大量數據，在不改變數據現有排序的情況下，常常需要以醒目的樣式顯示成績低于某一具體指定分數的數據。使用條件格式可以快捷地以指定的樣式直觀地標記出符合指定規則的數據［2］。例如：把語文、數學、英語成績低于90分的數據用淺紅填充色深紅色文本標記。操作方法是：選擇要設置條件的單元格區域B2：D11，在“開始”選項卡的功能區中“樣式”組中選擇“條件格式”，進一步選擇“突出顯示單元格的規則”下的“小于”項，在“小于”對話框中設置數值為“90”，單元格格式為“淺紅填充色深紅色文本”。結果如圖1所示。

圖1　分數預警

1.2成績對比

在教學管理中，經常通過對相近兩次考試成績的比較來衡量學生這一階段的學習情況，并且希望能夠把比較的結果直觀地顯示出來。條件格式的數據條功能，可以非常直觀地查看選定區域中數值的前后變化情況。例如：比較學生模擬考試一和模擬考試二的成績。操作方法是：選擇要設置條件的單元格區域D2：D7，在“開始”選項卡的功能區中“樣式”組中選擇“條件格式”，進一步選擇“數據條”中的“綠色數據條”漸變填充。結果如圖2所示。

圖2　成績對比

2　運用函數實現常用綜合統計分析

基于學生的單科成績統計個人總分、排名，統計課程最高分、最低分、平均分、中位數以及各分數段的人數統計等，是在考試成績統計分析中經常用到的。Excel電子表格程序提供了豐富的函數，可以方便快捷地實現以上統計分析需求［2］。例如：統計學生語文、數學、英語成績的總分和排名，分別統計三科成績和總分的平均分、中位數、最高分、最低分，按分數段分別統計三科成績的分布情況。結果如圖3所示。

圖3　綜合統計分析

涉及的函數和操作分別是：

總分：SUM函數返回參數中的所有數字的和。在E2單元格中輸入=SUM（B2：D2），得到程1的總分，選中E2單元格，使用填充柄拖動至E11單元格，實現其他學生總分的填充輸入。

排名：RANK.EQ函數返回一個數字在數字列表中的排位，其大小與列表中的其他值相關，如果多個值具有相同的排位，則返回該組數值的最高排位。在F2單元格中輸入=RANK.EQ（E2，$E$2：$E$11），得到程1的排名，選中F2單元格，使用填充柄拖動至F11單元格，實現其他學生排名的填充輸入。這里省略了order參數，order省略或者order為0時默認的排序方式為降序，order取非零值時為升序。

平均分：AVERAGE函數返回參數的算術平均值。在B12單元格中輸入=AVERAGE（B2：B11），得到語文成績的平均分，選中B12單元格，使用填充柄拖動至E12單元格，實現數學、英語和總分的平均值的填充輸入。

中位數：MEDIAN函數返回給定數值序列的中值。在B13單元格中輸入=MEDIAN（B2：B11），得到語文成績的中位數，選中B13單元格，使用填充柄拖動至E13單元格，實現數學、英語和總分的中位數的填充輸入。

最高分：MAX函數返回一組值中的最大值。在B14單元格中輸入=MEDIAN（B2：B11），得到語文成績的最高分，選中B14單元格，使用填充柄拖動至E14單元格，實現數學、英語和總分的最高分的填充輸入。

最低分：MIN函數返回一組值中的最小值。在B15單元格中輸入=MEDIAN（B2：B11），得到語文成績的最高分，選中B15單元格，使用填充柄拖動至E15單元格，實現數學、英語和總分的最低分的填充輸入。

分數段統計：FREQUENCY函數返回一個垂直數組，用于計算數值在某個區域內的出現頻率。在F18：F21單元格區域中從小到大依次輸入89.9，99.9，109.9，119.9作為區間分割點。選中B18：B22單元格區域，輸入=FREQUENCY（B2：B11，$F$18：$F$21），按＜Ctrl＞+ ＜Shift＞+＜Enter＞，得到語文成績的分數段統計情況，選中B18：B22單元格區域，使用填充柄拖動至D22單元格，實現數學、英語成績的分數段統計。

需要注意的是，公式和函數中涉及的標點符號必須使用半角狀態下的英文標點符號；在涉及單元格引用時，默認為相對引用，有些地方需要使用絕對引用；用于分數段統計的函數FREQUENCY返回的結果為數組，輸入時要以數組公式的形式輸入，即輸入時要選擇結果區域，公式編輯完成后要按＜Ctrl＞+＜Shift＞+＜Enter＞。用同樣的方法可以實現對單科成績排名的統計，也能在分數段統計中設置更多的分割點以實現更細致的分段。

3　結語

本文介紹了使用Excel電子表格程序的條件格式功能和函數實現高中考試成績的成績預警、成績對比、計算總分、平均分、中位數、最高分、最低分、按分數段統計人數、排名等成績統計分析需求，為教學活動、學生管理提供參考。可用于解決類似的統計分析問題，具有一定的實際意義。在此基礎上，還可以通過鎖定公式、設計模板、VBA等方式實現高效的成績統計分析［3］。

［1］宋赟.基于數據挖掘技術的學生成績分析［D］.重慶師范大學，2009

［2］王小燕.MS Excel數據分析在成績分析暨試卷質量評估的應用［J］.現代教育技術，2010，10：135～137

［3］婁青.基于Excel VBA開發普通高中成績分析系統［J］.中國教育信息化，2012，10：76～79

Excel；Examination Analysis；Conditional Formatting；Function

Application of Excel in High School Examination Results Analysis

WU Zheng-fang1，WANG Min2
（1.No.3 High School of Xiangyang，Xiangyang 441000；2.School of Mathematics and Computer Science，Hubei University of Arts and Science，Xiangyang 441053）

1007-1423（2015）09-0051-03

10.3969/j.issn.1007-1423.2015.09.012

吳正芳（1978-），女，湖北襄陽人，本科，中教一級，從事領域為高中數學教學工作

王敏（1982-），女，山西晉城人，碩士，講師，研究方向為計算機教育、現代教育技術

2015-01-13

2015-02-28

介紹使用Excel電子表格程序的條件格式功能和函數實現高中考試成績的成績預警、成績對比、計算總分、平均分、中位數、最高分、最低分、按分數段統計人數、排名等成績統計分析需求，為教學活動、學生管理提供參考依據。

Excel；成績分析；條件格式；函數

湖北文理學院實踐性課程教學方法改革項目

Introduces the application of conditional formatting and function of Excel in high school examination results analysis.Realizes the results warning，result contrast and calculation of total score，average score，median score，score section etc by using conditional formatting and function of Excel.The method can provide reference for teaching activity and student management.