提高教務工作效率的Excel公式設計

李淑軍（廣州市交通高級技工學校 廣東 廣州 510170）

提高教務工作效率的Excel公式設計

李淑軍
（廣州市交通高級技工學校 廣東 廣州 510170）

介紹了Excel公式在教務工作中的應用。同時介紹了Excel函數在統(tǒng)計分析成績、統(tǒng)計工作量、計算課酬中的運用，重點以不同工作表中信息的比對、教學日志格式轉換的Excel公式設計為例，提出善于運用Excel函數、巧妙設計Excel公式可以簡化教務工作，大大提高教務工作效率。

教務工作效率；Excel公式設計

教務工作涉及成績管理、工作量統(tǒng)計、課酬計算、教學日志管理等等，需要統(tǒng)計大量的數據，編制大量的教務文件。目前，許多學校教務管理都在信息化管理平臺進行，教務工作效率有了很大提高，但仍有大量數據統(tǒng)計、教務表格不能由教務管理系統(tǒng)完成。此外，系統(tǒng)導出的文件格式不是標準格式，需要進行格式轉換。如果人工處理此類教務工作，由于數據量大，必然花費大量時間。

Excel是一種電子表格軟件，具有很強的表格內和表格間計算功能，具有強大的數據庫管理功能，能對大量數據進行排序、分類、統(tǒng)計和查詢等處理。若能設計出實用的Excel計算公式，由于公式可復制或使用填充柄填充，數據修改后，Excel會按公式自動重新計算，大量統(tǒng)計和文件格式轉換可快速完成，教務工作效率可得到很大提高。下面介紹幾項常規(guī)教務工作用到的Excel公式設計。

成績統(tǒng)計分析、工作量統(tǒng)計、課酬計算的Excel公式設計

在成績統(tǒng)計分析中，總分、平均分、最高分、最低分是基本數據指標，通過函數SUM（）、AVERAGE（）、MAX（）、MIN（）可輕松計算得到。運用條件計數函數COUNTIF（）可統(tǒng)計及格人數、不及格人數、成績優(yōu)秀或良好人數，如要統(tǒng)計某科及格人數只需在單元格中輸入公式：=COUNTIF（B2:B40,”>=60”），此處“B2: B40”為某科成績單元格區(qū)域，其余依此類推。

統(tǒng)計工作量和課酬時，上課次數由COUNT（）函數計算獲得，乘以課時系數即得到課時數，課時數和課酬標準相乘得到課酬。假設課酬統(tǒng)計工作表中D2單元格為課時數，在E2中輸入課酬標準，F2用來存放課酬，那么在F2單元格中輸入公式：= D2*E2或輸入函數：=PRODUCT(D2:E2)即可計算出課酬。

上述統(tǒng)計數據的計算由單個函數即可解決，簡單易學，計算機應用教程或一級MS Office教程都有詳細講解，本文不再贅述。

不同工作表中信息比對的Excel公式設計

畢業(yè)生名單審核時，為確保畢業(yè)生名單準確無誤，常需將畢業(yè)生名單和學籍管理系統(tǒng)導出的學生名單進行比對，檢查兩份名單有無出入。如果人數較少，將兩份表中的名單按同樣的方式排序，再將一份表中的名單復制到另一份表中，逐個對照，就能完成比對。但如果畢業(yè)生有數千名之多，當兩份表中名單順序不一致，學生人數不一致時，這種方法顯然不是一種有效的方法。下面介紹用Excel查找與引用函數解決這一問題的公式設計。

學籍管理系統(tǒng)導出的名單放在“系統(tǒng)中名單”工作表中（如表1所示），畢業(yè)生名單放在“畢業(yè)生”工作表中（如表2所示），只要檢查“畢業(yè)生”工作表中學生名單能否在“系統(tǒng)中名單”工作表中查找到，就能核查出兩份表中名單有無出入。

具體操作是：在“畢業(yè)生”工作表D1、E1、F1單元格中分別輸入“系統(tǒng)序號”、“系統(tǒng)年級”、“系統(tǒng)班級”，在D2單元格中輸入公式：=INDEX(系統(tǒng)中名單!$A$2:$A$3000,MATCH($C2,系統(tǒng)中名單!$C$2:$C$3000,0))。該公式含義是：先用匹配函數MATCH（）查找出“畢業(yè)生”工作表C2單元格學生在“系統(tǒng)中名單”工作表中對應的順序，以此順序確定索引行，再用索引函數INDEX（）確定索引列A列與索引行交叉的單元格位置，將該位置內容引用到D2單元格中。公式中參數“C2”為匹配內容，“系統(tǒng)中名單!$C$2:$C$3000”為匹配范圍，“系統(tǒng)中名單!$A$2:$A $3000”為索引列范圍，參數“0”表示準確匹配。考慮后面的操作需用到公式復制或填充柄，單元格引用為絕對引用，公式中必須使用絕對地址。將D2單元格中公式填充到E2、F2中，E2公式中索引列A改為E，F2公式中索引列改為F，對應公式分別為：=INDEX(系統(tǒng)中名單!$E$2:$E$3000,MATCH($C2,系統(tǒng)中名單!$C$2:$C$3000,0))和=INDEX(系統(tǒng)中名單!$F$2:$F$3000, MATCH($C2,系統(tǒng)中名單!$C$2:$C$3000,0))， 然后選中D2：F2區(qū)域，往下拖動填充柄填充到D2901：F2901（畢業(yè)生假設為2900人）。結果如表3所示。

表1 “系統(tǒng)中名單”工作表

表2 “畢業(yè)生”工作表

表3 比對結果表

從表中可以看出，“王六”對應的結果為“#N/A”，說明在系統(tǒng)中查無此人，經查后發(fā)現是輸入錯誤，應為“王五”。單元格區(qū)域D2：F2501計算完畢后，以“系統(tǒng)班級”作為關鍵字進行排序，含“#N/A”的行集中在一起，有問題的畢業(yè)生名單一目了然，數千甚至上萬名畢業(yè)生的名單比對瞬間就可完成。

教學日志格式轉換的Excel公式設計

目前，教學日志一般由任課教師本人填入教務管理系統(tǒng)，系統(tǒng)導出的教學日志是按周次、課次排序但班級沒有分開的數萬條記錄組合在一起的數據庫（如圖1所示）。此種教學日志不便于存檔管理和檢查審閱。因此，教學日志由系統(tǒng)導出后，必須轉換成班級標準日志格式（如圖2所示）。

圖1 由教務管理系統(tǒng)導出的教學日志格式圖

圖2 班級周教學日志格式圖

教學日志格式轉換的一般方法是：先對數據庫進行自動篩選，得到某個班級一個學期的記錄后，按周次、星期、節(jié)次將科目、授課內容、課堂情況、作業(yè)情況、考勤情況、備注、作業(yè)的相關內容逐項復制粘貼到“課堂教學日志”中。一個學校班級數多達數十個甚至上百個，每班每學期有19周的周教學日志，數據庫記錄多達數萬條，逐項復制粘貼工作量非常大，耗時非常多。如果能設計一個Excel公式，自動引用數據庫中相關內容，由于計算機處理速度會非常快，數十個班的教學日志格式轉換很快就能完成。下面介紹一種新型的運用Excel公式進行教學日志格式轉換的操作。

自動篩選 由于一個學校一學期的教學日志記錄可能多達數萬條，為簡化計算量，先按班級進行自動篩選，得到某個班級一學期教學日志，日志記錄一般五百條左右。

建立“班級”工作表 將自動篩選結果復制粘貼到“班級”教學日志工作表中（如圖3所示）。在“班級”工作表L1單元格中輸入開學日期（如2009年9月7日），在M1單元格中輸入第1周結束日期（如2009年9月11日）。

圖3 班級教學日志工作表示意圖

編輯第1周課堂教學日志 建立工作表“1”，編輯標準格式課堂教學日志，在C2單元格中輸入周次“1”。

計算周起止時間 在“1”工作表E3單元格中輸入公式：=班級!$L$1+($C$2-1)*7，在H3單元格中輸入公式：=班級!$M$1+ ($C$2-1)*7，這兩個公式能夠根據每學期第1周起止時間和周次自動計算周起止時間。考慮到后面操作中要復制這兩個公式，此處引用單元格使用絕對地址。

引用數據庫記錄 引用數據庫記錄使用查找與引用函數。在工作表 “1”單元格C6中輸入公式：{=INDEX(班級!F$2:F $500,MATCH($C$2&"1"&LEFT($B6,1),班級!$C$2:$C$500&班級!$D$2:$D$500&班級!$E$2:$E$500,0))}。因為要根據對應周次、對應星期和對應節(jié)次引用相關內容，因此先用匹配函數MATCH（）根據上述條件確定引用行，由于涉及周次、星期、節(jié)次三個限定條件，而且條件之間是邏輯與的關系，因此公式中選用連接符“&”。單元格引用中“班級!$C$2:$C$500”、“班級!$D$2: $D$500”、“班級!$E$2:$E$500”表示匹配的數據范圍，公式中“班級!F$2:F$500”表示引用列范圍，參數“0”表示準確匹配。后面要將此公式復制粘貼到其他單元格，引用地址必須使用絕對地址。由于公式中涉及數組的匹配計算，因此輸入完公式后按Ctrl+Shift+Enter組合鍵鎖定數組公式。

計算第1周的教學日志 將C6單元格公式往下填充到C7：C19，將公式中表示星期一的1相應改為2，3，4，5；然后選中C6：C19，向右填充到D6：E19和I6：I19，將公式中引用的列范圍“班級!F$2:F$500”中的F相應改為G、H、I，這樣第1周的教學日志就計算完成了（如圖4所示）。

圖4 通過公式計算出來的班級周教學日志示意圖

計算整個班級的教學日志 新建工作表2，3，…，19，將工作表1全選、復制、粘貼到工作表2，3，…，19。在每張工作表表示周次的C2單元格中相應輸入2，3，…，19。單元格中內容會根據周次自動計算，個別單元格中出現“#N/A”表示沒有教學日志，刪除即可。至此整個班級1～19周的教學日志就全部計算完成了。從此操作過程顯而易見，有了第1周的計算模板后，其他周次的教學日志只要2個步驟就可完成。

計算另一個班級的教學日志 在數據庫中自動篩選出該班級的記錄，復制粘貼到“班級”工作表中。此時工作表1～19單元格中的內容會根據更新的工作表中的記錄自動重新計算；然后選中工作表1～19作為工作組，在C3單元格中輸入班級名稱，至此另一個班級的教學日志即全部計算完成，另存為一個文檔即可。可以看出，有了第1個班級的計算模板后，其他班級19周的教學日志只要4個簡單的步驟就可完成，非常快捷方便。

Excel提供了11類函數，每一類有若干個不同的函數，具有強大的計算功能和處理問題的能力。通過函數的組合和嵌套，函數的公式可以千變萬化。如果在教務工作中能善用Excel函數，巧妙設計Excel公式，教務工作可以得到極大簡化，工作效率可大大提高。

[1]教育部考試中心.一級MS Office教程[M].天津：南開大學出版社，2008：195-203.

[2]曹曉光.Excel函數在工程數據處理中的應用[J].山西建筑，2010，36（19）：367-367.

[3]李飛.Excel提高教學管理工作效率的探索與實踐[J].廣西廣播電視大學學報，2009，20（4）：38-40.

[4]胥家萍.巧用Excel編制工資報表[J].電腦學習，2010，2 （1）：112-114.

[5]武新華.Excel2007函數、公式范例應用[M].北京：清華大學出版社，2007:59-63.

G717

A

1672-5727（2011）08-0165-02

李淑軍（1971—），女，工學碩士，湖南郴州人，廣州市交通高級技工學校講師，研究方向為環(huán)境工程。