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

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

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

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

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

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

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

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

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

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

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

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

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

學籍管理系統(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))。該公式含義是：先用匹配函數(shù)MATCH（）查找出“畢業(yè)生”工作表C2單元格學生在“系統(tǒng)中名單”工作表中對應的順序，以此順序確定索引行，再用索引函數(shù)INDEX（）確定索引列A列與索引行交叉的單元格位置，將該位置內(nèi)容引用到D2單元格中。公式中參數(shù)“C2”為匹配內(nèi)容，“系統(tǒng)中名單!$C$2:$C$3000”為匹配范圍，“系統(tǒng)中名單!$A$2:$A $3000”為索引列范圍，參數(shù)“0”表示準確匹配?？紤]后面的操作需用到公式復制或填充柄，單元格引用為絕對引用，公式中必須使用絕對地址。將D2單元格中公式填充到E2、F2中，E2公式中索引列A改為E，F(xiàn)2公式中索引列改為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)中查無此人，經(jīng)查后發(fā)現(xiàn)是輸入錯誤，應為“王五”。單元格區(qū)域D2：F2501計算完畢后，以“系統(tǒng)班級”作為關鍵字進行排序，含“#N/A”的行集中在一起，有問題的畢業(yè)生名單一目了然，數(shù)千甚至上萬名畢業(yè)生的名單比對瞬間就可完成。

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

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

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

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

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

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

建立“班級”工作表 將自動篩選結果復制粘貼到“班級”教學日志工作表中（如圖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，這兩個公式能夠根據(jù)每學期第1周起止時間和周次自動計算周起止時間?？紤]到后面操作中要復制這兩個公式，此處引用單元格使用絕對地址。

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

計算第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èi)容會根據(jù)周次自動計算，個別單元格中出現(xiàn)“#N/A”表示沒有教學日志，刪除即可。至此整個班級1～19周的教學日志就全部計算完成了。從此操作過程顯而易見，有了第1周的計算模板后，其他周次的教學日志只要2個步驟就可完成。

計算另一個班級的教學日志 在數(shù)據(jù)庫中自動篩選出該班級的記錄，復制粘貼到“班級”工作表中。此時工作表1～19單元格中的內(nèi)容會根據(jù)更新的工作表中的記錄自動重新計算；然后選中工作表1～19作為工作組，在C3單元格中輸入班級名稱，至此另一個班級的教學日志即全部計算完成，另存為一個文檔即可?？梢钥闯觯辛说?個班級的計算模板后，其他班級19周的教學日志只要4個簡單的步驟就可完成，非?？旖莘奖?。

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

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

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

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

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

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

G717

A

1672-5727（2011）08-0165-02

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