EXCEL在教務管理中的綜合應用

楊維如

（健雄職業技術學院，江蘇 太倉 215400）

在日常的教務管理工作中會遇到大量的數據、繁雜的統計，無形中增加了許多工作量，這就要靠計算機來解決問題。office中的Excel是功能豐富和數據處理能力強大的電子表格軟件，具有數據篩選、排序、查詢、統計等功能,許多信息的存儲與處理都是通過Excel電子表格來實現的。Excel既容易掌握，又方便實用，是辦公自動化的好助手，下面就如何運用Excel來進行教務管理工作中成績管理、選課管理、數據透視表在考試沖突排查中的應用等幾方面作簡要介紹。

一、成績管理

在Excel中輸入如表1所示的學生成績表就要用到SUM、AVERAGE、COUNT、COUNTIF、RANK等函數。

新建一個Excel工作薄文件“學生成績表”，按表1形式輸入各基本數據項，其中每個學生的“總分”、“平均分”、“名次”三列中的數據是通過函數計算出來的，從“單科平均分”起以下各行的數據也是通過函數計算出來的。

在“秦毅”的總分即第二行第E列處也就是E2 單元格中輸入公式“=SUM（B2：D2）”，在 F2單元格中輸入公式“=AVERAGE（B2：D2）”，在G2單元格中輸入公式“=RANK(F2,$F$2:$F$51)”,Excel就可自動計算機出“秦毅”的總分、平均分、名次。要計算機其余各人的總分、平均分、名次只需用拖動的方法將E2、F2、G2單元格的分式復制到以下各單元格即可。

在大學英語單科平均分處也就是B52單元格中輸入公式“=AVERAGE (B2:B51)”,在B53單元格中輸入公式“=COUNTIF（B2：B51，“>=60”）/COUNT(B2:B51)”，在 B54 單元格中輸入公式“=COUNTIF（B2：B51，“<60”）/COUNT(B2:B51)”，在B55單元格中輸入公式“COUNTIF（B2：B51，“>=60”）”，在B57單元格中輸入公式“=COUNTIF（B2：B51）”，就可自動計算出“大學英語”的單科平均分、及格率、不及格率、及格人數、不及格人數、參加考試人數等數據。要計算另外幾門課程的平均分、及格率、不及格率、及格人數、不及格人數、參加考試人數只需將單元格 B52、B53、B54、B55、B56、B57 中的公式用拖動的方法復制到右邊的各單元格即可。

在表1用到的各個函數的格式與功能如下：

函數SUM功能是計算參數表中各數值的總和，如“SUM（B2：D2）“是計算從 B2 到 D2 單元格中的各項數據之和。

函數AVERAGE的功能是計算參數表各數值的平均值，如“AVERAGE（B2：D2）”是計算從B2到D2單元格中的各項數據的平均值。

函數RANK的功能是計算一個數據在一組數據中的排位，如“RANK($F$2:$F$51)”是計算F2單元格中的數據在從F2到F51共50單元格中的50個數據按升序排列時的位置。

函數COUNTIF的功能是計算某個區域中滿足某個條件的單元格的數目。如“COUNTIF（B2：B51，“>=60”）是計算從B2到B51共50個單元格中的50個數據中大于或等于60的數據個數。

函數COUNT的功能是計算參數表中的數字參數和含數字的單元格的個數。如“COUNT（B2：B51）是計算從B2到B51共50單元格中是數字的單元格的個數。

如果要計算大學英語學科的80分（含80分）到90分之間的人數應該怎樣寫公式呢？公式應該是“COUNTIF(B2:B51,“>=80”)-COUNTIF(B2:B51,“>90”)”

二、選課管理

我們學院2009級學生開設的公共藝術選修課對于每一個學生來說都得選，2009級學生有1490名，而到報名截止前一天，只有1380名學生選了，怎樣才能做到又準確又快捷地找出這110名沒有選課的學生呢？VLOOKUP函數就可以解決這個問題。

VLOOKUP函數是根據表格或數組的首列來查找指定的數據，并由此返回表格或數組當前行中指定列處的數據，它有如下功能：

（1）指定位置查找和引用數據；（2）表與表的核對；（3）利用模糊運算進行區間查詢。

語法格式：VLOOKUP（查找目標，查找區域，需要顯示的列，TRUE或FALSE）

公式說明：查找目標：實際上是指查找的依據 就是連接兩個表或多個表的共同項，表1（2009級所有學生）、表2（2009級公共選修課學生名單）中的學號；

查找區域：需要在其中查找數據的數據表區域，注意查找目標必須在查找區域的第1列。表2 2009級公共選修課學生名單中，學號必須在第1列，輸入的查找區域必須填寫絕對坐標可以寫成：'2009級公共選修課學生名單'!$A$2:$C$1381；

需要顯示的列：查找區域中待返回的列數，比如我們要返回表3公共選修課選課情況中選課情況，相對列數就為3；

TRUE或FALSE：TRUE代表為模糊查找，可以用1代替；FALSE為精確查找，可以用0代替，我們一般都選擇精確查找，設置為0。

這樣，我們為了精確查找沒有選課的學生名單，就在公共選修課選課情況表的第3列中設置公式為：“=VLOOKUP(A2,'2009級公共選修課學生名單'!$A$2:$C$1381,3,0)”，就出現了表3的情況，

表3第3列若出現“#N/A”情況的，則說明這個學生還沒有進行公共選修課的選課，這樣我們就可以準確地通知這些學生進行網上選課。

三、數據透視表在考試沖突排查中的應用

數據透視表是一種對大量數據快速匯總和建立交叉列表的交互式表格，可以轉換行和列來查看源數據的不同匯總結果，提供了一種從不同角度觀看數據清單的簡便方法。數據透視表透視出的數據經常是考務管理員分析問題、研究對策取得解決問題更好方案的有力依據。如學期初補考每門課程的補考學生數，這直接決定了補考試卷的印數；再如有學期末每自然班的考試課程數在及每課程參加考試的平行班數作參考，可確定出優先排考課程以及強力控制考試結束時間班級的范圍，得到這些數據憑分類匯總也是無法實現的，只有數據透視表才能做到。

考務管理員每學期最繁忙時就是期末考試之時，全校一兩百個在校班級，每個班級有若干門需考試的科目，而同一門課必須同時考試，同一個班級一個時間段即場次只能考一門，為排考很多高校購買了排考系統，然而一般功能比較健全的系統市場價格也不菲，價格低廉的系統功能又不健全，查不出沖突。其實Excel數據透視表本具有排查考試沖突的功能，但要合理的確定透視表的行字段、列字段和數據項才能實現這一功能。以考試時間沖突排查為例，以“班級”作為數據透視表的行字段，“場次”作為數據透視表的列字段，“課程名稱”作為數據透視表的數據項進行透視，即可得到每班在每場已安排的考試課程數，若均為“1”，則沒有沖突，若有“2”或“3”出現那么安排在這個場次中的課程需另換時間了。對考場以及監考教師沖突的排查同理可推，數據透視表可以快速統計監考老師場次。

以上這些都是我在平時的教務工作中積累下來的的Excel電子表格的巧妙應用，Excel為電腦應用基礎之中的基礎。希望大家能夠借助我以上的實踐經驗，對以后的學習和工作有所幫助。

[1]鄭麗.Excel若干使用技巧.電腦學習［J］,2000.1.

[2]熊傲.Excel2000全面掌握.中國大地出版社,2004.

[3]王國琴.Excel在日常教務管理中的應用.機械職業教育,2004.12.