提高教务工作效率的Excel公式设计

李淑军（广州市交通高级技工学校 广东 广州 510170）

提高教务工作效率的Excel公式设计

李淑军
（广州市交通高级技工学校 广东 广州 510170）

介绍了Excel公式在教务工作中的应用。同时介绍了Excel函数在统计分析成绩、统计工作量、计算课酬中的运用，重点以不同工作表中信息的比对、教学日志格式转换的Excel公式设计为例，提出善于运用Excel函数、巧妙设计Excel公式可以简化教务工作，大大提高教务工作效率。

教务工作效率；Excel公式设计

教务工作涉及成绩管理、工作量统计、课酬计算、教学日志管理等等，需要统计大量的数据，编制大量的教务文件。目前，许多学校教务管理都在信息化管理平台进行，教务工作效率有了很大提高，但仍有大量数据统计、教务表格不能由教务管理系统完成。此外，系统导出的文件格式不是标准格式，需要进行格式转换。如果人工处理此类教务工作，由于数据量大，必然花费大量时间。

Excel是一种电子表格软件，具有很强的表格内和表格间计算功能，具有强大的数据库管理功能，能对大量数据进行排序、分类、统计和查询等处理。若能设计出实用的Excel计算公式，由于公式可复制或使用填充柄填充，数据修改后，Excel会按公式自动重新计算，大量统计和文件格式转换可快速完成，教务工作效率可得到很大提高。下面介绍几项常规教务工作用到的Excel公式设计。

成绩统计分析、工作量统计、课酬计算的Excel公式设计

在成绩统计分析中，总分、平均分、最高分、最低分是基本数据指标，通过函数SUM（）、AVERAGE（）、MAX（）、MIN（）可轻松计算得到。运用条件计数函数COUNTIF（）可统计及格人数、不及格人数、成绩优秀或良好人数，如要统计某科及格人数只需在单元格中输入公式：=COUNTIF（B2:B40,”>=60”），此处“B2: B40”为某科成绩单元格区域，其余依此类推。

统计工作量和课酬时，上课次数由COUNT（）函数计算获得，乘以课时系数即得到课时数，课时数和课酬标准相乘得到课酬。假设课酬统计工作表中D2单元格为课时数，在E2中输入课酬标准，F2用来存放课酬，那么在F2单元格中输入公式：= D2*E2或输入函数：=PRODUCT(D2:E2)即可计算出课酬。

上述统计数据的计算由单个函数即可解决，简单易学，计算机应用教程或一级MS Office教程都有详细讲解，本文不再赘述。

不同工作表中信息比对的Excel公式设计

毕业生名单审核时，为确保毕业生名单准确无误，常需将毕业生名单和学籍管理系统导出的学生名单进行比对，检查两份名单有无出入。如果人数较少，将两份表中的名单按同样的方式排序，再将一份表中的名单复制到另一份表中，逐个对照，就能完成比对。但如果毕业生有数千名之多，当两份表中名单顺序不一致，学生人数不一致时，这种方法显然不是一种有效的方法。下面介绍用Excel查找与引用函数解决这一问题的公式设计。

学籍管理系统导出的名单放在“系统中名单”工作表中（如表1所示），毕业生名单放在“毕业生”工作表中（如表2所示），只要检查“毕业生”工作表中学生名单能否在“系统中名单”工作表中查找到，就能核查出两份表中名单有无出入。

具体操作是：在“毕业生”工作表D1、E1、F1单元格中分别输入“系统序号”、“系统年级”、“系统班级”，在D2单元格中输入公式：=INDEX(系统中名单!$A$2:$A$3000,MATCH($C2,系统中名单!$C$2:$C$3000,0))。该公式含义是：先用匹配函数MATCH（）查找出“毕业生”工作表C2单元格学生在“系统中名单”工作表中对应的顺序，以此顺序确定索引行，再用索引函数INDEX（）确定索引列A列与索引行交叉的单元格位置，将该位置内容引用到D2单元格中。公式中参数“C2”为匹配内容，“系统中名单!$C$2:$C$3000”为匹配范围，“系统中名单!$A$2:$A $3000”为索引列范围，参数“0”表示准确匹配。考虑后面的操作需用到公式复制或填充柄，单元格引用为绝对引用，公式中必须使用绝对地址。将D2单元格中公式填充到E2、F2中，E2公式中索引列A改为E，F2公式中索引列改为F，对应公式分别为：=INDEX(系统中名单!$E$2:$E$3000,MATCH($C2,系统中名单!$C$2:$C$3000,0))和=INDEX(系统中名单!$F$2:$F$3000, MATCH($C2,系统中名单!$C$2:$C$3000,0))， 然后选中D2：F2区域，往下拖动填充柄填充到D2901：F2901（毕业生假设为2900人）。结果如表3所示。

表1 “系统中名单”工作表

表2 “毕业生”工作表

表3 比对结果表

从表中可以看出，“王六”对应的结果为“#N/A”，说明在系统中查无此人，经查后发现是输入错误，应为“王五”。单元格区域D2：F2501计算完毕后，以“系统班级”作为关键字进行排序，含“#N/A”的行集中在一起，有问题的毕业生名单一目了然，数千甚至上万名毕业生的名单比对瞬间就可完成。

教学日志格式转换的Excel公式设计

目前，教学日志一般由任课教师本人填入教务管理系统，系统导出的教学日志是按周次、课次排序但班级没有分开的数万条记录组合在一起的数据库（如图1所示）。此种教学日志不便于存档管理和检查审阅。因此，教学日志由系统导出后，必须转换成班级标准日志格式（如图2所示）。

图1 由教务管理系统导出的教学日志格式图

图2 班级周教学日志格式图

教学日志格式转换的一般方法是：先对数据库进行自动筛选，得到某个班级一个学期的记录后，按周次、星期、节次将科目、授课内容、课堂情况、作业情况、考勤情况、备注、作业的相关内容逐项复制粘贴到“课堂教学日志”中。一个学校班级数多达数十个甚至上百个，每班每学期有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))}。因为要根据对应周次、对应星期和对应节次引用相关内容，因此先用匹配函数MATCH（）根据上述条件确定引用行，由于涉及周次、星期、节次三个限定条件，而且条件之间是逻辑与的关系，因此公式中选用连接符“&”。单元格引用中“班级!$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—），女，工学硕士，湖南郴州人，广州市交通高级技工学校讲师，研究方向为环境工程。