李曉云
摘要: Excel電子表格軟件以其強大的函數(shù)和數(shù)據(jù)分析工具以及VBA程序等功能,廣泛應(yīng)用于信息應(yīng)用的各個領(lǐng)域中,該文運用Excel數(shù)組、函數(shù)等功能,創(chuàng)建了一個學(xué)期末總評成績處理模板,大大提高了任課教師學(xué)期末處理成績的效率,具有很好的實用價值。
關(guān)鍵詞:成績處理;Excel函數(shù)、數(shù)組;Excel模板
中圖分類號:G642 ? ? ? ?文獻標(biāo)識碼:A
文章編號:1009-3044(2020)25-0128-02
學(xué)期末任課教師所帶課程的成績處理是一項重要的基礎(chǔ)工作,特別是目前高職院校,基本上為過程性評價,評價項目多,如果不使用ExceL函數(shù)的高級處理功能,工作量將會非常煩瑣。就我校目前的情況,有許多課程是合班上課,班級人數(shù)眾多,而且目前仍有一些任課教師在信息化應(yīng)用技術(shù)上水平參差不齊,出現(xiàn) “手工”計算成績分析數(shù)據(jù)的情況,工作效率低,且容易出錯。
Excel強大的數(shù)組、函數(shù)功能及靈活的數(shù)據(jù)調(diào)用方式可以輕松地進行各類數(shù)據(jù)的統(tǒng)計與邏輯處理,本文創(chuàng)建了一個自動化處理期末成績的模板,任課教師只需要輸入班級名稱、課程名稱及各評分項目成績的基礎(chǔ)數(shù)據(jù),成績單及所有評價指標(biāo)數(shù)據(jù)自動生成,簡單易用,在本校中推廣應(yīng)用良好,大大提高了任課教師處理成績的工作效率。
1錄入班級初始成績表
本過程的主要目的是以最簡單的形式組織綜合評價中各項目的基礎(chǔ)分數(shù)據(jù),表格結(jié)構(gòu)盡量簡單,數(shù)據(jù)的正確性方面進行功能糾錯。此表用于教師輸入班級每個同學(xué)的各項評分數(shù)值。
(1)建立初始成績表結(jié)構(gòu)
創(chuàng)建一個班級工作簿文件,將sheet1工作表命名為“初始成績X1班”,數(shù)據(jù)表結(jié)構(gòu)有“學(xué)號、姓名、平時、期中、期末”五個字段,評價項目可根據(jù)課程評價標(biāo)準(zhǔn)自行調(diào)整。本模板的評價體系為:平時成績30分,期中100分,期末100分,總評=平時+期中*30%+期末*40%。
(2)設(shè)置數(shù)據(jù)有效性并輸入成績
該工作表要錄入每個同學(xué)的初始成績數(shù)據(jù),工作量較大, 為了保證數(shù)據(jù)在輸入過程中邏輯上的正確性,對成績數(shù)據(jù)區(qū)域進行 “有效性”的糾錯設(shè)置,類型為“數(shù)值型”,范圍為0至100,當(dāng)超出范圍時給予出錯提示。基于本校教師任課情況,復(fù)制三張工作表,分別重命名為各班級名稱。
2創(chuàng)建基礎(chǔ)信息及統(tǒng)計數(shù)據(jù)表
將sheet2工作表命名為“基礎(chǔ)信息及統(tǒng)計數(shù)據(jù)”,此表結(jié)構(gòu)包括班級基本信息和分數(shù)段等統(tǒng)計信息,前五項基礎(chǔ)信息項由任課教師根據(jù)各班級信息手工輸入,后面的統(tǒng)計數(shù)據(jù)項由函數(shù)計算取得。
(1)計算期末成績的“最高分、最低分,平均分”
分別運用MAN、MIN和AVERAGE函數(shù)求出“期末”成績的最高分、最低分及平均分,函數(shù)示例:“=MAX(初始成績X1班!E:E)”“=MIN(初始成績X1班!E:E)”“=ROUND(AVERAGE(初始成績X1班!E:E),2)”,使用拖充柄將公式復(fù)制到其他行,并對應(yīng)修改公式中的班級名稱。
(2)計算期末成績的各“分數(shù)段”數(shù)據(jù)
“分數(shù)段”人數(shù)統(tǒng)計,本文使用了FREQUENCY數(shù)組函數(shù)功能。按期末成績劃分為5個分數(shù)段:“90分以上”“80~89分”“70~79分”“60~69分”和 “60分以下”,將分段點分別設(shè)置為99、89、79、69和59,圖1中B10:C15區(qū)域,再運用FREQUENCY函數(shù)分別對各班級期末成績按分段點返回頻率分布值,即為各分數(shù)段的人數(shù),函數(shù)示例:“{=FREQUENCY(初始成績X1班!E:E,C14:C18)}”,注意EXCEL數(shù)組轉(zhuǎn)換要使用特定的組合鍵“SHIFT+CTRL+ENTER”,如圖1中D11:G15區(qū)域。因 FREQUENCY函數(shù)只能進行列向求頻率值[1],所以最后還要將這組數(shù)據(jù)引用到圖2中J2:N5的橫向數(shù)據(jù)清單對應(yīng)區(qū)域。
3制作期末總評成績模板
“期末總評成績”表是每個教師學(xué)期末要上交存檔的學(xué)生總評成績單,主要包括三個組成部分,表頭信息區(qū),成績評分區(qū)、總評成績分析區(qū),表結(jié)構(gòu)如圖2。
3.1 表頭和成績區(qū)數(shù)據(jù)處理
(1)制作期末總評成績表結(jié)構(gòu)調(diào)入初始數(shù)據(jù)
插入新工作表重命名為“期末總評成績X1班”,制作學(xué)校統(tǒng)一的班級期末總成績表格。
以“=”號開頭公式引用的方式,將表頭中的學(xué)年、班級、課程名稱等標(biāo)識信息從“參數(shù)與分析”表中依次調(diào)入;同樣將“平時、期中、期末”三項原始數(shù)據(jù)從對應(yīng)班級初始成績工作表中調(diào)用過來,調(diào)用公式為“=IF(初始成績X1班!B2="","",初始成績X1班!B2)”,將公式拖充復(fù)制到左中兩邊的所有數(shù)據(jù)行,這種調(diào)用一旦原始數(shù)據(jù)發(fā)生改動,調(diào)用表中數(shù)據(jù)會自動更新保持一致?!翱荚?查”選項可以使用IF函數(shù)根據(jù)考試性質(zhì)參數(shù)值返回“ü”信息,函數(shù)公式:“=IF(參數(shù)及統(tǒng)計!D2="考試","√","")”。
(2)計算期中、期末折合成績和總評成績
根據(jù)規(guī)定的折合比例,在“期中折合”和“期末折合”的第一個數(shù)據(jù)行中分別輸入“=IF(C6="","",ROUND(C6*30%,0))”和“=IF(E6="","",ROUND(E6*40%,0))”函數(shù)公式,復(fù)制到其數(shù)據(jù)行;“總評”成績?yōu)椤捌綍r+期中折合+期末折合”,但要判斷如果期中未考,則總評成績按“平時+期末*70%”計算,如果期末未考,則總評成績?yōu)?,使用兩級IF嵌套函數(shù)“=IF(A6<>"",IF(AND(C6="",E6<>""),ROUND(B6+E6*70%,0),IF(E6="","",B6+D6+F6)),"")”返回最后結(jié)果,拖動拖充柄復(fù)制到其他數(shù)據(jù)行,完成成績計算。
(3)進行缺考標(biāo)識和不及格標(biāo)識
根據(jù)總評成績數(shù)據(jù)值用IF函數(shù)判斷,總評無成績者為“缺考”,在“備注”欄輸入公式“=IF(AND(G6="",A6<>""),"缺考","")”,復(fù)制到其他數(shù)據(jù)行,標(biāo)識出所有缺考的學(xué)生行。
成績表中需要將總評成績不及格的數(shù)據(jù)做出突出標(biāo)點,以便觀察,具體步驟是:(1)選中工作表“總評”數(shù)據(jù)區(qū)域;(2)“開始”菜單找到“條件格式”單擊;(3)選中“突出顯示單元格規(guī)則小于” 圖標(biāo),對話框中輸入60并設(shè)置“文本紅色”,單擊“確認”按鈕。
3.2 總評成績分析區(qū)域數(shù)據(jù)處理
(1)班級平均分計算
班級平均分的數(shù)據(jù)源是本表左右兩欄中折算出來的G列和O列中的總評成績,在分析區(qū)域中J32單元格中輸入“=ROUND(AVERAGE(G6:G34,O6:O25),2)”, ROUND函數(shù)對數(shù)值四舍五入,保留2小數(shù)位數(shù)。
(2) “分數(shù)段”人數(shù)統(tǒng)計
各“分數(shù)段”人數(shù)的統(tǒng)計仍使用上述所說的FRFREQUENCY數(shù)組函數(shù),但注意成績數(shù)據(jù)是本表的“總評”成績列,“分段點”參數(shù)值在“參數(shù)及分析”工作表中提取,在圖3中選定J27:J31單元格區(qū)域, 輸入公式“=FREQUENCY (高職2013級某班XX課成績表! F:F,G2:G6)”,然后按“Ctrl+Shift+Enter”轉(zhuǎn)換為數(shù)組的形式,即可相應(yīng)單元格直接取得各分類段的人數(shù)結(jié)果。
(3)各“分數(shù)段”人數(shù)所占比例統(tǒng)計
統(tǒng)計學(xué)中單項占全體的結(jié)構(gòu)比較分析法,便于總體上表明全班考試成績分布的基本情況,使用剛得出的各分數(shù)段的人數(shù)與總?cè)藬?shù)相除取得,如“90分以上”的人數(shù)比例值在圖3L27單元格中輸入 “=ROUND(J27/SUM($J$27:$J$31),3)*100”后回車,再將公式拖動填充其他人數(shù)比例計算單元格即可。
(4)參加考試人數(shù)和缺考人數(shù)統(tǒng)計
本文使用公式“="期末實際參加考核("&SUM(J27:J31)&" )人""計算取得;“缺考”人數(shù)也可以有多種計算方法,本文使用COUNTIF函數(shù)直接從本表“備注”欄中的缺考信息計數(shù)取得,圖3的I34單元中輸入“="期末缺考("&COUNTIF(H6:H34,"缺考")+COUNTIF(P6:P25,"缺考")&")人"”。直接從本表中引用單元格進行統(tǒng)計計算,雖然函數(shù)復(fù)雜,但好處是后面復(fù)制制作其他班級成績表時,這些公式不需要修改參數(shù)。
至些,數(shù)據(jù)計算完成,期末總評表自動生成,復(fù)制三張,用同樣方法將對應(yīng)班級的基礎(chǔ)信息和原始成績調(diào)用過來,其他項目不需要改動,數(shù)據(jù)自動更新,這樣多個班級的數(shù)據(jù)全部處理完成。
4數(shù)據(jù)的保護
本模板使用了大量公式和函數(shù)計算,之間的調(diào)用關(guān)系較復(fù)雜,為了以防任課老師對函數(shù)公式的誤修改,造成數(shù)據(jù)錯誤,要啟用Excel的保護功能[2]。方案是:四張“總評成績”表通過 “審閱”菜單下的“保護工作表”設(shè)置默認參數(shù)保護,不允許任何操作; 而“參數(shù)及分析”工作表進行部分數(shù)據(jù)保護,即允許班級基本信息數(shù)據(jù)區(qū)域可以輸入新的內(nèi)容,其他項目不可動,工作表保護前注意需要將這些區(qū)域的“單元格格式設(shè)置”中的“鎖定”取消。另外可將此文檔保存為Excel模板文件,使用時直接新建調(diào)用該模板即可。
5結(jié)束語
本模板充分考慮到任課教師一個學(xué)期所帶班級多,所教授課數(shù)量多的情況,使用一個Excel文件即可進行多個班級不同課程的成績處理,改變了原版中一個文件只能處理一個班級成績的情況,本模板只需要教師輸入各班級最初始的成績及基礎(chǔ)數(shù)據(jù),期末總評成績表就會全部自動生成。該模板在本校的成績處理工作中已廣泛成功推廣,解決了煩瑣的重復(fù)數(shù)據(jù)處理問題,大大提高了教師學(xué)期成績分析統(tǒng)計工作的效率。
參考文獻:
[1]李盛蘭,吳慶祥.學(xué)生成績管理中Excel函數(shù)的應(yīng)用技術(shù)研究[J].數(shù)字技術(shù)與應(yīng)用,2017(5):239.
[2]金龍海,姜楠.Excel函數(shù)在統(tǒng)計學(xué)期成績中的應(yīng)用[J].中國新通信,2017,19(20):133-134.
【通聯(lián)編輯:代影】