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

關(guān)鍵詞：Excel；成績；統(tǒng)計分析；函數(shù)

引言

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

1 準(zhǔn)備工作

1.1 原成績表導(dǎo)出和錄入

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

1.2 設(shè)計成績匯總表

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

1.3 設(shè)計成績明細(xì)表

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

2 匯總表函數(shù)設(shè)計

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

2.1 成績分布函數(shù)設(shè)計

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

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

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

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

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

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

2.2 平均分函數(shù)設(shè)計

平均分的計算則是將“成績明細(xì)表”中相應(yīng)單位的全體成績?nèi)∑骄?，所用到的函?shù)是AVERAGEIF函數(shù)，具體公式為：AVERAGEIF（成績明細(xì)表！$B：$B，A3，成績明細(xì)表！$D：$D），上述公式中的A3，為當(dāng)前所計算平均分所對應(yīng)的“單位名稱”。將第一行編輯完成后，拖動填充柄至最后一個單位所在行。

2.3 違紀(jì)人數(shù)統(tǒng)計的函數(shù)設(shè)計

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

2.4 各單位成績排名函數(shù)設(shè)計

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

2.5 保護(hù)“成績表匯總表”工作表

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

3 結(jié)束語

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

參考文獻(xiàn)

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

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

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