李曉云

摘要： Excel電子表格軟件以其強(qiáng)大的函數(shù)和數(shù)據(jù)分析工具以及VBA程序等功能，廣泛應(yīng)用于信息應(yīng)用的各個(gè)領(lǐng)域中，該文運(yùn)用Excel數(shù)組、函數(shù)等功能，創(chuàng)建了一個(gè)學(xué)期末總評(píng)成績處理模板，大大提高了任課教師學(xué)期末處理成績的效率，具有很好的實(shí)用價(jià)值。

關(guān)鍵詞：成績處理;Excel函數(shù)、數(shù)組;Excel模板

中圖分類號(hào)：G642 ? ? ? ?文獻(xiàn)標(biāo)識(shí)碼：A

文章編號(hào)：1009-3044（2020）25-0128-02

學(xué)期末任課教師所帶課程的成績處理是一項(xiàng)重要的基礎(chǔ)工作，特別是目前高職院校，基本上為過程性評(píng)價(jià)，評(píng)價(jià)項(xiàng)目多，如果不使用ExceL函數(shù)的高級(jí)處理功能，工作量將會(huì)非常煩瑣。就我校目前的情況，有許多課程是合班上課，班級(jí)人數(shù)眾多，而且目前仍有一些任課教師在信息化應(yīng)用技術(shù)上水平參差不齊，出現(xiàn) “手工”計(jì)算成績分析數(shù)據(jù)的情況，工作效率低，且容易出錯(cuò)。

Excel強(qiáng)大的數(shù)組、函數(shù)功能及靈活的數(shù)據(jù)調(diào)用方式可以輕松地進(jìn)行各類數(shù)據(jù)的統(tǒng)計(jì)與邏輯處理，本文創(chuàng)建了一個(gè)自動(dòng)化處理期末成績的模板，任課教師只需要輸入班級(jí)名稱、課程名稱及各評(píng)分項(xiàng)目成績的基礎(chǔ)數(shù)據(jù)，成績單及所有評(píng)價(jià)指標(biāo)數(shù)據(jù)自動(dòng)生成，簡單易用，在本校中推廣應(yīng)用良好，大大提高了任課教師處理成績的工作效率。

1錄入班級(jí)初始成績表

本過程的主要目的是以最簡單的形式組織綜合評(píng)價(jià)中各項(xiàng)目的基礎(chǔ)分?jǐn)?shù)據(jù)，表格結(jié)構(gòu)盡量簡單，數(shù)據(jù)的正確性方面進(jìn)行功能糾錯(cuò)。此表用于教師輸入班級(jí)每個(gè)同學(xué)的各項(xiàng)評(píng)分?jǐn)?shù)值。

（1）建立初始成績表結(jié)構(gòu)

創(chuàng)建一個(gè)班級(jí)工作簿文件，將sheet1工作表命名為“初始成績X1班”，數(shù)據(jù)表結(jié)構(gòu)有“學(xué)號(hào)、姓名、平時(shí)、期中、期末”五個(gè)字段，評(píng)價(jià)項(xiàng)目可根據(jù)課程評(píng)價(jià)標(biāo)準(zhǔn)自行調(diào)整。本模板的評(píng)價(jià)體系為：平時(shí)成績30分，期中100分，期末100分，總評(píng)=平時(shí)+期中*30%+期末*40%。

（2）設(shè)置數(shù)據(jù)有效性并輸入成績

該工作表要錄入每個(gè)同學(xué)的初始成績數(shù)據(jù)，工作量較大， 為了保證數(shù)據(jù)在輸入過程中邏輯上的正確性，對(duì)成績數(shù)據(jù)區(qū)域進(jìn)行 “有效性”的糾錯(cuò)設(shè)置，類型為“數(shù)值型”，范圍為0至100，當(dāng)超出范圍時(shí)給予出錯(cuò)提示?；诒拘＝處熑握n情況，復(fù)制三張工作表，分別重命名為各班級(jí)名稱。

2創(chuàng)建基礎(chǔ)信息及統(tǒng)計(jì)數(shù)據(jù)表

將sheet2工作表命名為“基礎(chǔ)信息及統(tǒng)計(jì)數(shù)據(jù)”，此表結(jié)構(gòu)包括班級(jí)基本信息和分?jǐn)?shù)段等統(tǒng)計(jì)信息，前五項(xiàng)基礎(chǔ)信息項(xiàng)由任課教師根據(jù)各班級(jí)信息手工輸入，后面的統(tǒng)計(jì)數(shù)據(jù)項(xiàng)由函數(shù)計(jì)算取得。

（1）計(jì)算期末成績的“最高分、最低分，平均分”

分別運(yùn)用MAN、MIN和AVERAGE函數(shù)求出“期末”成績的最高分、最低分及平均分，函數(shù)示例：“=MAX（初始成績X1班！E：E）”“=MIN（初始成績X1班！E：E）”“=ROUND（AVERAGE（初始成績X1班！E：E），2）”，使用拖充柄將公式復(fù)制到其他行，并對(duì)應(yīng)修改公式中的班級(jí)名稱。

（2）計(jì)算期末成績的各“分?jǐn)?shù)段”數(shù)據(jù)

“分?jǐn)?shù)段”人數(shù)統(tǒng)計(jì)，本文使用了FREQUENCY數(shù)組函數(shù)功能。按期末成績劃分為5個(gè)分?jǐn)?shù)段：“90分以上”“80～89分”“70～79分”“60～69分”和 “60分以下”，將分段點(diǎn)分別設(shè)置為99、89、79、69和59，圖1中B10：C15區(qū)域，再運(yùn)用FREQUENCY函數(shù)分別對(duì)各班級(jí)期末成績按分段點(diǎn)返回頻率分布值，即為各分?jǐn)?shù)段的人數(shù)，函數(shù)示例：“{=FREQUENCY（初始成績X1班！E：E，C14：C18）}”，注意EXCEL數(shù)組轉(zhuǎn)換要使用特定的組合鍵“SHIFT+CTRL+ENTER”，如圖1中D11：G15區(qū)域。因 FREQUENCY函數(shù)只能進(jìn)行列向求頻率值[1]，所以最后還要將這組數(shù)據(jù)引用到圖2中J2：N5的橫向數(shù)據(jù)清單對(duì)應(yīng)區(qū)域。

3制作期末總評(píng)成績模板

“期末總評(píng)成績”表是每個(gè)教師學(xué)期末要上交存檔的學(xué)生總評(píng)成績單，主要包括三個(gè)組成部分，表頭信息區(qū)，成績?cè)u(píng)分區(qū)、總評(píng)成績分析區(qū)，表結(jié)構(gòu)如圖2。

3.1 表頭和成績區(qū)數(shù)據(jù)處理

（1）制作期末總評(píng)成績表結(jié)構(gòu)調(diào)入初始數(shù)據(jù)

插入新工作表重命名為“期末總評(píng)成績X1班”，制作學(xué)校統(tǒng)一的班級(jí)期末總成績表格。

以“=”號(hào)開頭公式引用的方式，將表頭中的學(xué)年、班級(jí)、課程名稱等標(biāo)識(shí)信息從“參數(shù)與分析”表中依次調(diào)入;同樣將“平時(shí)、期中、期末”三項(xiàng)原始數(shù)據(jù)從對(duì)應(yīng)班級(jí)初始成績工作表中調(diào)用過來，調(diào)用公式為“=IF（初始成績X1班！B2=""，""，初始成績X1班！B2）”，將公式拖充復(fù)制到左中兩邊的所有數(shù)據(jù)行，這種調(diào)用一旦原始數(shù)據(jù)發(fā)生改動(dòng)，調(diào)用表中數(shù)據(jù)會(huì)自動(dòng)更新保持一致?！翱荚?查”選項(xiàng)可以使用IF函數(shù)根據(jù)考試性質(zhì)參數(shù)值返回“ü”信息，函數(shù)公式：“=IF（參數(shù)及統(tǒng)計(jì)！D2="考試"，"√"，""）”。

（2）計(jì)算期中、期末折合成績和總評(píng)成績

根據(jù)規(guī)定的折合比例，在“期中折合”和“期末折合”的第一個(gè)數(shù)據(jù)行中分別輸入“=IF（C6=""，""，ROUND（C6*30%，0））”和“=IF（E6=""，""，ROUND（E6*40%，0））”函數(shù)公式，復(fù)制到其數(shù)據(jù)行;“總評(píng)”成績?yōu)椤捌綍r(shí)+期中折合+期末折合”，但要判斷如果期中未考，則總評(píng)成績按“平時(shí)+期末*70%”計(jì)算，如果期末未考，則總評(píng)成績?yōu)?，使用兩級(jí)IF嵌套函數(shù)“=IF（A6<>""，IF（AND（C6=""，E6<>""），ROUND（B6+E6*70%，0），IF（E6=""，""，B6+D6+F6）），""）”返回最后結(jié)果，拖動(dòng)拖充柄復(fù)制到其他數(shù)據(jù)行，完成成績計(jì)算。

（3）進(jìn)行缺考標(biāo)識(shí)和不及格標(biāo)識(shí)

根據(jù)總評(píng)成績數(shù)據(jù)值用IF函數(shù)判斷，總評(píng)無成績者為“缺考”，在“備注”欄輸入公式“=IF（AND（G6=""，A6<>""），"缺考"，""）”，復(fù)制到其他數(shù)據(jù)行，標(biāo)識(shí)出所有缺考的學(xué)生行。

成績表中需要將總評(píng)成績不及格的數(shù)據(jù)做出突出標(biāo)點(diǎn)，以便觀察，具體步驟是：（1）選中工作表“總評(píng)”數(shù)據(jù)區(qū)域;（2）“開始”菜單找到“條件格式”單擊;（3）選中“突出顯示單元格規(guī)則小于” 圖標(biāo)，對(duì)話框中輸入60并設(shè)置“文本紅色”，單擊“確認(rèn)”按鈕。

3.2 總評(píng)成績分析區(qū)域數(shù)據(jù)處理

（1）班級(jí)平均分計(jì)算

班級(jí)平均分的數(shù)據(jù)源是本表左右兩欄中折算出來的G列和O列中的總評(píng)成績，在分析區(qū)域中J32單元格中輸入“=ROUND（AVERAGE（G6：G34，O6：O25），2）”， ROUND函數(shù)對(duì)數(shù)值四舍五入，保留2小數(shù)位數(shù)。

（2） “分?jǐn)?shù)段”人數(shù)統(tǒng)計(jì)

各“分?jǐn)?shù)段”人數(shù)的統(tǒng)計(jì)仍使用上述所說的FRFREQUENCY數(shù)組函數(shù)，但注意成績數(shù)據(jù)是本表的“總評(píng)”成績列，“分段點(diǎn)”參數(shù)值在“參數(shù)及分析”工作表中提取，在圖3中選定J27：J31單元格區(qū)域， 輸入公式“=FREQUENCY （高職2013級(jí)某班XX課成績表！ F：F，G2：G6）”，然后按“Ctrl+Shift+Enter”轉(zhuǎn)換為數(shù)組的形式，即可相應(yīng)單元格直接取得各分類段的人數(shù)結(jié)果。

（3）各“分?jǐn)?shù)段”人數(shù)所占比例統(tǒng)計(jì)

統(tǒng)計(jì)學(xué)中單項(xiàng)占全體的結(jié)構(gòu)比較分析法，便于總體上表明全班考試成績分布的基本情況，使用剛得出的各分?jǐn)?shù)段的人數(shù)與總?cè)藬?shù)相除取得，如“90分以上”的人數(shù)比例值在圖3L27單元格中輸入 “=ROUND（J27/SUM（$J$27：$J$31），3）*100”后回車，再將公式拖動(dòng)填充其他人數(shù)比例計(jì)算單元格即可。

（4）參加考試人數(shù)和缺考人數(shù)統(tǒng)計(jì)

本文使用公式“="期末實(shí)際參加考核（"&SUM（J27：J31）&" ）人""計(jì)算取得;“缺考”人數(shù)也可以有多種計(jì)算方法，本文使用COUNTIF函數(shù)直接從本表“備注”欄中的缺考信息計(jì)數(shù)取得，圖3的I34單元中輸入“="期末缺考（"&COUNTIF（H6：H34，"缺考"）+COUNTIF（P6：P25，"缺考"）&"）人"”。直接從本表中引用單元格進(jìn)行統(tǒng)計(jì)計(jì)算，雖然函數(shù)復(fù)雜，但好處是后面復(fù)制制作其他班級(jí)成績表時(shí)，這些公式不需要修改參數(shù)。

至些，數(shù)據(jù)計(jì)算完成，期末總評(píng)表自動(dòng)生成，復(fù)制三張，用同樣方法將對(duì)應(yīng)班級(jí)的基礎(chǔ)信息和原始成績調(diào)用過來，其他項(xiàng)目不需要改動(dòng)，數(shù)據(jù)自動(dòng)更新，這樣多個(gè)班級(jí)的數(shù)據(jù)全部處理完成。

4數(shù)據(jù)的保護(hù)

本模板使用了大量公式和函數(shù)計(jì)算，之間的調(diào)用關(guān)系較復(fù)雜，為了以防任課老師對(duì)函數(shù)公式的誤修改，造成數(shù)據(jù)錯(cuò)誤，要啟用Excel的保護(hù)功能[2]。方案是：四張“總評(píng)成績”表通過 “審閱”菜單下的“保護(hù)工作表”設(shè)置默認(rèn)參數(shù)保護(hù)，不允許任何操作; 而“參數(shù)及分析”工作表進(jìn)行部分?jǐn)?shù)據(jù)保護(hù)，即允許班級(jí)基本信息數(shù)據(jù)區(qū)域可以輸入新的內(nèi)容，其他項(xiàng)目不可動(dòng)，工作表保護(hù)前注意需要將這些區(qū)域的“單元格格式設(shè)置”中的“鎖定”取消。另外可將此文檔保存為Excel模板文件，使用時(shí)直接新建調(diào)用該模板即可。

5結(jié)束語

本模板充分考慮到任課教師一個(gè)學(xué)期所帶班級(jí)多，所教授課數(shù)量多的情況，使用一個(gè)Excel文件即可進(jìn)行多個(gè)班級(jí)不同課程的成績處理，改變了原版中一個(gè)文件只能處理一個(gè)班級(jí)成績的情況，本模板只需要教師輸入各班級(jí)最初始的成績及基礎(chǔ)數(shù)據(jù)，期末總評(píng)成績表就會(huì)全部自動(dòng)生成。該模板在本校的成績處理工作中已廣泛成功推廣，解決了煩瑣的重復(fù)數(shù)據(jù)處理問題，大大提高了教師學(xué)期成績分析統(tǒng)計(jì)工作的效率。

參考文獻(xiàn)：

[1]李盛蘭，吳慶祥.學(xué)生成績管理中Excel函數(shù)的應(yīng)用技術(shù)研究[J].數(shù)字技術(shù)與應(yīng)用，2017（5）：239.

[2]金龍海，姜楠.Excel函數(shù)在統(tǒng)計(jì)學(xué)期成績中的應(yīng)用[J].中國新通信，2017，19（20）：133-134.

【通聯(lián)編輯：代影】