EXCEL在煤礦安全理論考試成績統計中的應用

摘 要：Microsoft Excel是微軟公司的辦公軟件Microsoft office的組件之一，它可以進行各種數據的處理、統計分析和輔助決策操作。針對當前大量考試成績的統計分析工作日益繁復，利用該軟件的函數、模板和統計等功能處理成績數據，對提高工作的效率和準確性有著十分重要的現實意義。文章以山東唐口煤業有限公司為例，闡述如何運用Excel軟件對員工考試成績進行匯總統計，并形成分析模版。

關鍵詞：Excel；成績；統計分析；函數

引言

Excel是微軟辦公軟件Microsoft Office的組件之一，它是一款功能強大的電子表格處理軟件，廣泛應用于管理、統計、財經、金融等眾多行業領域。利用它可以制作表格、分析處理數據、創建圖表等。[1]近年來，隨著煤礦企業逐漸由勞動力密集型向技術密集型轉變，企業對高技能人才的需求越來越迫切。為了督促職工提升自身技能知識水平，企業對職工技能知識的各種考核也日漸增多，隨之而來的，對大量的各類成績的統計分析工作變得更加繁瑣復雜。目前，山東唐口煤業有限公司共有21個部門（單位）參加崗位技能知識考試，實際參加考試共計約2300人左右。每月月末，安培中心都要講上述人員的成績按照部門、工種和成績分布分別統計分析報送至相關部門。下面就如何運用Excel軟件對員工考試成績進行匯總統計作具體闡述。

1 準備工作

1.1 原成績表導出和錄入

目前，我礦職工考試使用的是在線考試平臺，考試結束后，教師可以根據需要將成績以Excel表格的形式導出，便于后續的成績匯總統計，該平臺的成績表導出后有以下字段：序號、編號、身份證號、姓名、班級、工種、成績、工區。教師可以直接利用該成績表的源數據進行統計和分析，省去了過往將筆答試卷成績一一錄入的繁瑣，有效的提高了成績統計效率，縮短了考核周期。

1.2 設計成績匯總表

新建一個工作簿，更改sheet1的表名為“成績匯總表”，在第一行逐列輸入“單位名稱”、“考試人數”、“成績分布”、“平均分”、“平均分與上次比較”、“違紀”、“名次”，然后在第二行成績分布下方插入列，并逐列輸入“100分”、“90分以上”、“80-90”、“80分以下”接下來合并除成績分布列之外的第一二行，效果如圖1所示。

1.3 設計成績明細表

在上述成績匯總表的同一工作簿中，更改sheet2的表名為“成績明細表”，在第一行逐列輸入“序號”、“單位”、“姓名”、“成績”、“身份證號”、“工種”、“備注”，然后將由在線考試平臺中導出的原成績表根據表頭信息逐列粘貼到“成績明細表”中。

2 匯總表函數設計

匯總表的成績統計主要包括：（1）統計相應單位的各分數段人數；（2）統計各分數段人數占總人數的比例；（3）統計各單位平均分和排名情況。由于“成績匯總表”工作表中的數據都是通過“成績明細表”工作表中的數據統計出來的，不需要做任何修改。為了防止使用者無意中修改其中的數據，必須將“成績匯總表”工作表中的所有單元格保護起來，不允許使用者修改。

2.1 成績分布函數設計

由于各單位的成績分布需要分別統計，所以用到了SUMPRODUCT函數[2]，具體公式設計如下：

100分：SUMPRODUCT（（成績明細表！$B：$B=成績匯總表！A3）*（成績明細表！$D：$D=100））

90～99分：SUMPRODUCT（（成績明細表！$B：$B=成績匯總表！A3）*（成績明細表！$D：$D>=90））-C3-I3

80～89分：SUMPRODUCT（（成績明細表！$B：$B=成績匯總表！A3）*（成績明細表！$D：$D>=80））-D3-C3-I3

80分以下：SUMPRODUCT（（成績明細表！$B：$B=成績匯總表！A3）*（成績明細表！$D：$D<80））

上述公式中的A3，為當前所計算成績分布對應的“單位名稱”。將第一行編輯完成后，拖動填充柄至最后一個單位所在行。計算完各分數段人員占總人數比例后，可通過插入圖表的方式，直接繪制出其分布的餅狀圖，使成績的分布更直觀地顯示出來。

2.2 平均分函數設計

平均分的計算則是將“成績明細表”中相應單位的全體成績取平均值，所用到的函數是AVERAGEIF函數，具體公式為：AVERAGEIF（成績明細表！$B：$B，A3，成績明細表！$D：$D），上述公式中的A3，為當前所計算平均分所對應的“單位名稱”。將第一行編輯完成后，拖動填充柄至最后一個單位所在行。

2.3 違紀人數統計的函數設計

目前，有兩種情況被定義為考試違紀：一是正常出勤，無故不參加考試的；二是在考試中出現替考等違紀行為的。在成績統計時，上述兩種人員分別按照“缺考”和“替考”統計。在成績匯總表的違紀一欄按照單位分別統計其違紀人數，所用到的函數是SUMPRODUCT函數，具體公式為：SUMPRODUCT（（成績明細表！$B：$B=成績匯總表！A3）*（成績明細表！$D：$D=“替考”））+SUMPRODUCT（（成績明細表！$B：$B=成績匯總表！A3）*（成績明細表！$D：$D=“缺考”））。上述公式中的A3，為當前所計算違紀人數對應的“單位名稱”。將第一行編輯完成后，拖動填充柄至最后一個單位所在行。

2.4 各單位成績排名函數設計

各單位成績排名，是將所有單位的平均分，按照高低次序排名次，所用到的函數是RANK（number，ref，order），，其中有三個參數，第一個參數“number”為需要排序的某單位平均分所在的單元格；第二個參數“ref”為所有單位平均分所在的區域；第三個參數“order”是可選的，表示統計方式，若省寫或寫“0”，則成績高的名次靠前；如果寫“1”，則成績高的名次靠后。具體公式為：RANK（G3，$G$3：$G$23，0）。上述公式中的G3，為當前所計算排名對應單位的“平均分”。將第一行編輯完成后，拖動填充柄至最后一個單位所在行。

2.5 保護“成績表匯總表”工作表

為了便于“成績匯總表”的有效傳閱，防止傳閱過程中他人誤操作造成數據紊亂，因此需要將“成績匯總表”工作表保護起來，以Microsoft Excel 2010版為例，其具體步驟如下[3]：（1）選中該工作表的所有單元格；（2）在選中的單元格處右擊，選擇“設置單元格格式”菜單；（3）在彈出的對話框中選擇“保護”選項卡，并且勾選“鎖定”復選框，然后單擊“確定”按鈕；（4）在界面上方的“審閱”選項卡中，點擊“保護工作表”，然后在彈出的復選框中選擇需要保護的內容，然后輸入密碼，單擊“確認”按鈕。通過以上設置，使得只有取得保護密碼的使用者，才能對工作表內容進行修改，有效的確保了統計數據的安全性和準確性。

3 結束語

該模板經過在我礦日常成績統計中的應用，取得了很好的效果。教師只需按要求錄入成績數據，就可以在“成績匯總表”中得到相應的統計數據。這不僅有效的提高了成績統計工作的效率，還減少了無意造成的數據錯誤，對煤礦的安全培訓工作起到了良好的促進效果。

參考文獻

[1]ExcelHome.Excel實戰技巧精粹[M].北京：人民郵電出版社，2007.

[2]羽山博，吉川明廣，等.辦公寶典Excel 2003/2002/2000函數大全[M].北京：人民郵電出版社，2007.

[3]陳華福.最新統計電算化教程（Excel 2000在統計學中的應用）[M].北京：冶金工業出版社，2001：64.