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

提高教务工作效率Excel公式设计 　　摘要：介绍了Excel公式在教务工作中的应用。同时介绍了Excel函数在统计分析成绩、统计工作量、计算课酬中的运用，重点以不同工作表中信息的比对、教学日志格式转换的Excel公式设计为例，提出善于运用Excel函数、巧妙设计Excel公式可以简化教务工作，大大提高教务工作效率。 　　关键词：教务工作效率；Excel公式设计 　　中图分类号：G717文献标识码：A文章编号：1672-5727（2011）08-0165-02 　　 　　教务工作涉及成绩管理、工作量统计、课酬计算、教学日志管理等等，需要统计大量的数据，编制大量的教务文件。目前，许多学校教务管理都在信息化管理平台进行，教务工作效率有了很大提高，但仍有大量数据统计、教务表格不能由教务管理系统完成。此外，系统导出的文件格式不是标准格式，需要进行格式转换。如果人工处理此类教务工作，由于数据量大，必然花费大量时间。 　　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所示。 　　从表中可以看出，“王六”对应的结果为“#N/A”，说明在系统中查无此人，经查后发现是输入