Excel在高中考試成績(jī)分析中的應(yīng)用

吳正芳，王敏

（1.襄陽(yáng)市第三中學(xué)，襄陽(yáng)　441000；2.湖北文理學(xué)院數(shù)學(xué)與計(jì)算機(jī)科學(xué)學(xué)院，湖北 襄陽(yáng) 441053）

Excel在高中考試成績(jī)分析中的應(yīng)用

吳正芳1，王敏2

（1.襄陽(yáng)市第三中學(xué)，襄陽(yáng)441000；2.湖北文理學(xué)院數(shù)學(xué)與計(jì)算機(jī)科學(xué)學(xué)院，湖北 襄陽(yáng) 441053）

0　引言

考試是教育活動(dòng)中評(píng)價(jià)教學(xué)質(zhì)量、衡量教學(xué)效果的重要手段之一，對(duì)學(xué)生的成績(jī)進(jìn)行統(tǒng)計(jì)分析，能夠給教學(xué)活動(dòng)、學(xué)生管理提供參考，是提升教學(xué)效果和學(xué)生成績(jī)的指導(dǎo)方向。高中考試成績(jī)的統(tǒng)計(jì)分析通常包括個(gè)人單科排名、個(gè)人總分的排名、各科目的最高分、最低分、平均分、中位數(shù)，各分?jǐn)?shù)段的人數(shù)統(tǒng)計(jì)、多次考試成績(jī)的比較以及成績(jī)預(yù)警等。學(xué)生多、考試科目多、考試頻繁，由此帶來(lái)了大量的統(tǒng)計(jì)分析工作負(fù)荷，運(yùn)用現(xiàn)代信息技術(shù)手段高效完成上述統(tǒng)計(jì)是非常必要的，Ex cel電子表格程序正是一個(gè)合適的工具［1］。本文以Ex cel 2010為例介紹。

Excel是Microsoft Office中的電子表格程序，用戶界面友好、計(jì)算能力強(qiáng)大、操作簡(jiǎn)便。使用Excel可以創(chuàng)建電子表格并設(shè)置格式，以便分析數(shù)據(jù)和做出更明智的業(yè)務(wù)決策，還可以使用Excel跟蹤數(shù)據(jù)，生成數(shù)據(jù)分析模型，編寫(xiě)公式以對(duì)數(shù)據(jù)進(jìn)行計(jì)算，以多種方式透視數(shù)據(jù)，并以各種具有專業(yè)外觀的圖表來(lái)顯示數(shù)據(jù)。

1　運(yùn)用“條件格式”實(shí)現(xiàn)快速直觀分析

1.1成績(jī)預(yù)警

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

圖1　分?jǐn)?shù)預(yù)警

1.2成績(jī)對(duì)比

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

圖2　成績(jī)對(duì)比

2　運(yùn)用函數(shù)實(shí)現(xiàn)常用綜合統(tǒng)計(jì)分析

基于學(xué)生的單科成績(jī)統(tǒng)計(jì)個(gè)人總分、排名，統(tǒng)計(jì)課程最高分、最低分、平均分、中位數(shù)以及各分?jǐn)?shù)段的人數(shù)統(tǒng)計(jì)等，是在考試成績(jī)統(tǒng)計(jì)分析中經(jīng)常用到的。Excel電子表格程序提供了豐富的函數(shù)，可以方便快捷地實(shí)現(xiàn)以上統(tǒng)計(jì)分析需求［2］。例如：統(tǒng)計(jì)學(xué)生語(yǔ)文、數(shù)學(xué)、英語(yǔ)成績(jī)的總分和排名，分別統(tǒng)計(jì)三科成績(jī)和總分的平均分、中位數(shù)、最高分、最低分，按分?jǐn)?shù)段分別統(tǒng)計(jì)三科成績(jī)的分布情況。結(jié)果如圖3所示。

圖3　綜合統(tǒng)計(jì)分析

涉及的函數(shù)和操作分別是：

總分：SUM函數(shù)返回參數(shù)中的所有數(shù)字的和。在E2單元格中輸入=SUM（B2：D2），得到程1的總分，選中E2單元格，使用填充柄拖動(dòng)至E11單元格，實(shí)現(xiàn)其他學(xué)生總分的填充輸入。

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

平均分：AVERAGE函數(shù)返回參數(shù)的算術(shù)平均值。在B12單元格中輸入=AVERAGE（B2：B11），得到語(yǔ)文成績(jī)的平均分，選中B12單元格，使用填充柄拖動(dòng)至E12單元格，實(shí)現(xiàn)數(shù)學(xué)、英語(yǔ)和總分的平均值的填充輸入。

中位數(shù)：MEDIAN函數(shù)返回給定數(shù)值序列的中值。在B13單元格中輸入=MEDIAN（B2：B11），得到語(yǔ)文成績(jī)的中位數(shù)，選中B13單元格，使用填充柄拖動(dòng)至E13單元格，實(shí)現(xiàn)數(shù)學(xué)、英語(yǔ)和總分的中位數(shù)的填充輸入。

最高分：MAX函數(shù)返回一組值中的最大值。在B14單元格中輸入=MEDIAN（B2：B11），得到語(yǔ)文成績(jī)的最高分，選中B14單元格，使用填充柄拖動(dòng)至E14單元格，實(shí)現(xiàn)數(shù)學(xué)、英語(yǔ)和總分的最高分的填充輸入。

最低分：MIN函數(shù)返回一組值中的最小值。在B15單元格中輸入=MEDIAN（B2：B11），得到語(yǔ)文成績(jī)的最高分，選中B15單元格，使用填充柄拖動(dòng)至E15單元格，實(shí)現(xiàn)數(shù)學(xué)、英語(yǔ)和總分的最低分的填充輸入。

分?jǐn)?shù)段統(tǒng)計(jì)：FREQUENCY函數(shù)返回一個(gè)垂直數(shù)組，用于計(jì)算數(shù)值在某個(gè)區(qū)域內(nèi)的出現(xiàn)頻率。在F18：F21單元格區(qū)域中從小到大依次輸入89.9，99.9，109.9，119.9作為區(qū)間分割點(diǎn)。選中B18：B22單元格區(qū)域，輸入=FREQUENCY（B2：B11，$F$18：$F$21），按＜Ctrl＞+ ＜Shift＞+＜Enter＞，得到語(yǔ)文成績(jī)的分?jǐn)?shù)段統(tǒng)計(jì)情況，選中B18：B22單元格區(qū)域，使用填充柄拖動(dòng)至D22單元格，實(shí)現(xiàn)數(shù)學(xué)、英語(yǔ)成績(jī)的分?jǐn)?shù)段統(tǒng)計(jì)。

需要注意的是，公式和函數(shù)中涉及的標(biāo)點(diǎn)符號(hào)必須使用半角狀態(tài)下的英文標(biāo)點(diǎn)符號(hào)；在涉及單元格引用時(shí)，默認(rèn)為相對(duì)引用，有些地方需要使用絕對(duì)引用；用于分?jǐn)?shù)段統(tǒng)計(jì)的函數(shù)FREQUENCY返回的結(jié)果為數(shù)組，輸入時(shí)要以數(shù)組公式的形式輸入，即輸入時(shí)要選擇結(jié)果區(qū)域，公式編輯完成后要按＜Ctrl＞+＜Shift＞+＜Enter＞。用同樣的方法可以實(shí)現(xiàn)對(duì)單科成績(jī)排名的統(tǒng)計(jì)，也能在分?jǐn)?shù)段統(tǒng)計(jì)中設(shè)置更多的分割點(diǎn)以實(shí)現(xiàn)更細(xì)致的分段。

3　結(jié)語(yǔ)

本文介紹了使用Excel電子表格程序的條件格式功能和函數(shù)實(shí)現(xiàn)高中考試成績(jī)的成績(jī)預(yù)警、成績(jī)對(duì)比、計(jì)算總分、平均分、中位數(shù)、最高分、最低分、按分?jǐn)?shù)段統(tǒng)計(jì)人數(shù)、排名等成績(jī)統(tǒng)計(jì)分析需求，為教學(xué)活動(dòng)、學(xué)生管理提供參考。可用于解決類似的統(tǒng)計(jì)分析問(wèn)題，具有一定的實(shí)際意義。在此基礎(chǔ)上，還可以通過(guò)鎖定公式、設(shè)計(jì)模板、VBA等方式實(shí)現(xiàn)高效的成績(jī)統(tǒng)計(jì)分析［3］。

［1］宋赟.基于數(shù)據(jù)挖掘技術(shù)的學(xué)生成績(jī)分析［D］.重慶師范大學(xué)，2009

［2］王小燕.MS Excel數(shù)據(jù)分析在成績(jī)分析暨試卷質(zhì)量評(píng)估的應(yīng)用［J］.現(xiàn)代教育技術(shù)，2010，10：135～137

［3］婁青.基于Excel VBA開(kāi)發(fā)普通高中成績(jī)分析系統(tǒng)［J］.中國(guó)教育信息化，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-），女，湖北襄陽(yáng)人，本科，中教一級(jí)，從事領(lǐng)域?yàn)楦咧袛?shù)學(xué)教學(xué)工作

王敏（1982-），女，山西晉城人，碩士，講師，研究方向?yàn)橛?jì)算機(jī)教育、現(xiàn)代教育技術(shù)

2015-01-13

2015-02-28

介紹使用Excel電子表格程序的條件格式功能和函數(shù)實(shí)現(xiàn)高中考試成績(jī)的成績(jī)預(yù)警、成績(jī)對(duì)比、計(jì)算總分、平均分、中位數(shù)、最高分、最低分、按分?jǐn)?shù)段統(tǒng)計(jì)人數(shù)、排名等成績(jī)統(tǒng)計(jì)分析需求，為教學(xué)活動(dòng)、學(xué)生管理提供參考依據(jù)。

Excel；成績(jī)分析；條件格式；函數(shù)

湖北文理學(xué)院實(shí)踐性課程教學(xué)方法改革項(xiàng)目

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.