巧用Excel函數創建學期末總評成績模板

李曉云

摘要： Excel電子表格軟件以其強大的函數和數據分析工具以及VBA程序等功能，廣泛應用于信息應用的各個領域中，該文運用Excel數組、函數等功能，創建了一個學期末總評成績處理模板，大大提高了任課教師學期末處理成績的效率，具有很好的實用價值。

關鍵詞：成績處理;Excel函數、數組;Excel模板

中圖分類號：G642 ? ? ? ?文獻標識碼：A

文章編號：1009-3044（2020）25-0128-02

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

Excel強大的數組、函數功能及靈活的數據調用方式可以輕松地進行各類數據的統計與邏輯處理，本文創建了一個自動化處理期末成績的模板，任課教師只需要輸入班級名稱、課程名稱及各評分項目成績的基礎數據，成績單及所有評價指標數據自動生成，簡單易用，在本校中推廣應用良好，大大提高了任課教師處理成績的工作效率。

1錄入班級初始成績表

本過程的主要目的是以最簡單的形式組織綜合評價中各項目的基礎分數據，表格結構盡量簡單，數據的正確性方面進行功能糾錯。此表用于教師輸入班級每個同學的各項評分數值。

（1）建立初始成績表結構

創建一個班級工作簿文件，將sheet1工作表命名為“初始成績X1班”，數據表結構有“學號、姓名、平時、期中、期末”五個字段，評價項目可根據課程評價標準自行調整。本模板的評價體系為：平時成績30分，期中100分，期末100分，總評=平時+期中*30%+期末*40%。

（2）設置數據有效性并輸入成績

該工作表要錄入每個同學的初始成績數據，工作量較大， 為了保證數據在輸入過程中邏輯上的正確性，對成績數據區域進行 “有效性”的糾錯設置，類型為“數值型”，范圍為0至100，當超出范圍時給予出錯提示。基于本校教師任課情況，復制三張工作表，分別重命名為各班級名稱。

2創建基礎信息及統計數據表

將sheet2工作表命名為“基礎信息及統計數據”，此表結構包括班級基本信息和分數段等統計信息，前五項基礎信息項由任課教師根據各班級信息手工輸入，后面的統計數據項由函數計算取得。

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

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

（2）計算期末成績的各“分數段”數據

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

3制作期末總評成績模板

“期末總評成績”表是每個教師學期末要上交存檔的學生總評成績單，主要包括三個組成部分，表頭信息區，成績評分區、總評成績分析區，表結構如圖2。

3.1 表頭和成績區數據處理

（1）制作期末總評成績表結構調入初始數據

插入新工作表重命名為“期末總評成績X1班”，制作學校統一的班級期末總成績表格。

以“=”號開頭公式引用的方式，將表頭中的學年、班級、課程名稱等標識信息從“參數與分析”表中依次調入;同樣將“平時、期中、期末”三項原始數據從對應班級初始成績工作表中調用過來，調用公式為“=IF（初始成績X1班！B2=""，""，初始成績X1班！B2）”，將公式拖充復制到左中兩邊的所有數據行，這種調用一旦原始數據發生改動，調用表中數據會自動更新保持一致。“考試/查”選項可以使用IF函數根據考試性質參數值返回“ü”信息，函數公式：“=IF（參數及統計！D2="考試"，"√"，""）”。

（2）計算期中、期末折合成績和總評成績

根據規定的折合比例，在“期中折合”和“期末折合”的第一個數據行中分別輸入“=IF（C6=""，""，ROUND（C6*30%，0））”和“=IF（E6=""，""，ROUND（E6*40%，0））”函數公式，復制到其數據行;“總評”成績為“平時+期中折合+期末折合”，但要判斷如果期中未考，則總評成績按“平時+期末*70%”計算，如果期末未考，則總評成績為0，使用兩級IF嵌套函數“=IF（A6<>""，IF（AND（C6=""，E6<>""），ROUND（B6+E6*70%，0），IF（E6=""，""，B6+D6+F6）），""）”返回最后結果，拖動拖充柄復制到其他數據行，完成成績計算。

（3）進行缺考標識和不及格標識

根據總評成績數據值用IF函數判斷，總評無成績者為“缺考”，在“備注”欄輸入公式“=IF（AND（G6=""，A6<>""），"缺考"，""）”，復制到其他數據行，標識出所有缺考的學生行。

成績表中需要將總評成績不及格的數據做出突出標點，以便觀察，具體步驟是：（1）選中工作表“總評”數據區域;（2）“開始”菜單找到“條件格式”單擊;（3）選中“突出顯示單元格規則小于” 圖標，對話框中輸入60并設置“文本紅色”，單擊“確認”按鈕。

3.2 總評成績分析區域數據處理

（1）班級平均分計算

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

（2） “分數段”人數統計

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

（3）各“分數段”人數所占比例統計

統計學中單項占全體的結構比較分析法，便于總體上表明全班考試成績分布的基本情況，使用剛得出的各分數段的人數與總人數相除取得，如“90分以上”的人數比例值在圖3L27單元格中輸入 “=ROUND（J27/SUM（$J$27：$J$31），3）*100”后回車，再將公式拖動填充其他人數比例計算單元格即可。

（4）參加考試人數和缺考人數統計

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

至些，數據計算完成，期末總評表自動生成，復制三張，用同樣方法將對應班級的基礎信息和原始成績調用過來，其他項目不需要改動，數據自動更新，這樣多個班級的數據全部處理完成。

4數據的保護

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

5結束語

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

參考文獻：

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

[2]金龍海，姜楠.Excel函數在統計學期成績中的應用[J].中國新通信，2017，19（20）：133-134.

【通聯編輯：代影】