浅谈Excel函数在教学中的应用

智慧

摘 要：Excel是微软公司出品的Office系列办公软件，可以用来完成许多复杂的数据运算、数据分析和数据预测。在教学和教育管理工作中，我们可以利用Excel公式和函数功能帮助我们解决教学管理和成绩统计的各种问题。实现成绩自动分析统计，教学计划、考勤自动设置、自动管理，使教师避免大量复杂的数据运算和重复性劳动。

关键词：Excel函数；教学管理；统计

一、准工作日的设置

在Excel表格中，日期系列是一串连续的数字，但工作日期不包括周六、周日两天。如何显示出实际的工作天数呢？Excel可以帮助我们计算出“准工作日”。

a.启动Excel 2010，新建一个教师员工的工作簿。在sheet 1工作表的A2、B2单元格中分别输入列标题为“姓名”和“日期”，然后单击 “合并及居中”按钮将A2和A3两个单元格合并为1个单元格，然后输入第一个教师的姓名，按照以上方法输入其他教职工的姓名。

b.在单元格C1中输入“3-1”回车，单元格内容自动转成“3月1日”。用鼠标选中单元格C1，待出现实心“十”字的填充柄后，按住鼠标左键向右拖动到需要的位置，这样在3月1日后就形成了一个日期序列。

c.单击日期序列右下角的“自动填充选项”，在弹出来的菜单中点选“以工作日填充”项，单元格日期系列便自动删除星期六、星期日两天。

d.选中整个日期行，在Excel 2010版中单击“开始”—数字栏中的“自定义”下拉菜单—“其他数字格式”—“自定义”项，在类型框中删除“m”月留下“d”日，然后单击“确定”按钮。

二、宏按钮的设置

宏，译自英文单词Macro。宏是微软公司为其Office软件包设计的一个特殊功能，软件设计者为了让人们在使用软件进行工作时，避免一再地重复相同的动作而设计出来的一种工具，它利用简单的语法，把常用的动作写成宏，当在工作时，就可以直接利用事先编好的宏自动运行，去完成某项特定的任务，而不必再重复相同的动作，目的是让文档中的一些任务自动化，很多不会编程的人都可以使用。在考勤表中，只要我们录制宏，输入now（）函数，当单击按钮时，就能显示当前的时间，实现宏按钮对时间的自动设置。

操作方法如下：

a.单击“视图→宏→录制宏”—“录制新宏”—输入“返回当前时间”—确定。

b.在单元格C3中输入公式“=now（）”，按回车键，然后单击“停止录

制”。

c.（Excel 2010版制作按钮的方法和其他版本不同）单击“文件”—“选项”—“自定义功能区”—“开发工具”—“确定”—“开发工具”按钮—“插入”—“表单控件”选择按钮。

d.在单元格B3中拖动鼠标绘制一个按钮。Excel将自动打开“指定宏”对话框，点选其中的“返回当前时间”宏，再单击“确定”。

e.制作和复制“上、下班”按钮宏。

三、录入时间的设置

录入时间的设定包括两个方面：

a.时间格式的设定：在单击“上班”“下班”按钮时在单元格中会返回“年份+时间”，其实我们只需要“8：00”这样的时间就可以了。方法：单击“开始”—数字窗—“常规”—“其他数字格式”—“数字”—“时间”—“13：30”—“确定”。

b.要将出勤记录及时由公式结果转换成具体的时间数值，否则当录入新的时间时，公式被重新计算后，其他时间将更改。方法：选中表中已录入的时间数据单元格，单击右键选择“复制”，点选“编辑→选择性粘贴”菜单项，在“选择性粘贴”对话框中选中“数值”项，然后单击“确定”。

四、考勤结果的统计

（1）统计单元格与姓名单元格相匹配。

在日期行之后的单元格中依次输入“迟到”“早退”“病假”“事假”等需要统计的项目。并将这几列中的单元格上下两两合并，使之对应于姓名行。否则就会把“下班”和“上班”分别统计为“迟到”和“早退”。

（2）统计的时间格式与录入考勤时间的格式相匹配。

在这里，我们使用COUNTIF函数自动统计考勤结果。COUNTIF函数是Microsoft Excel中对指定区域中符合指定条件的单元格计数的一个函数。

参数：range要计算其中非空单元格数目的区域。参数：criteria以数字、表达式或文本形式定义的条件。

例如：a.单击“迟到”下面的单元格。

b.键入公式“=COUNTIF（C3：H3，>“2013/3/1 8：00”）”（因为计算机显示自动时间格式是2013/3/1 8：00），按回车键，I23单元格中就会出现选中员工所有迟于8：00上班的工作日天数。

C3：H3：第一个教职工“上班”的日期所占的单元格区域；

2013/3/1“8：00”：是教职工“上班”的日期和时间。

c.在单元格中输入公式“=COUNTIF（C4∶H4，“2013/3/1 17：00”）”，并按回车键，J23单元格中便会出现选中员工所有早于17：00下班的工作日天数。显示结果，病假函数的使用：COUNTIF（C3∶H4，“病假”），事假函数的使用：COUNTIF（C3∶H4，“事假”）。

五、保存和打开宏文件

（1）保存宏文件。

Excel如何保存宏文件和2003版本稍有不同。

方法：选择另存为—启用宏的Excel工作簿—文件名—确定。

（2）打开宏文件。我们都知道宏是一段程序代码，可以把一系列的操作自动连续完成。但由于宏是程序，容易被人用来写入恶意代码，成为有破坏性的病毒，所以excel默认是不允许打开宏的，在打开有宏的文档时就会受到提醒。通常打开时是将安全级别降到最低。endprint

（3）Excel 2003设置方法：

a.菜单—工具—宏—宏安全性

b.菜单—工具—选项—安全性—宏安全性

（4）Excel 2007设置方法：

Office按钮—Excel选项—信任中心—信任中心设置—宏设置—启用所有宏。

完成设置后，启动带宏的Excel表格再不会有提示了，最好在使用这种方法打开前，用杀毒软件进行检查，确保文件没有包含恶意的宏代码。

六、Excel在教学统计中的应用

学生成绩统计是教师经常要进行的工作，如果手工计算，不但效率低，工作量还大。如果利用Excel函数功能可以很好地解决教学工作中的各种问题。我们最常使用的函数除了SUM、SUMIF、AVERAGE、MAX、MIN之外，还有IF、COUNT、DCOUNT等。

在这里，主要介绍运用不同的函数如何根据分数来求出优、良、中、差所对应的A、B、C、D等级的方法。

（1）IF函数快速求出成绩的等级。

首先，我们要了解Excel里IF函数的参数和返回值的用法，第一个参数是条件，第二个参数是条件为真的返回值，第三个参数是条件为假的返回值；这样根据问题就可以用相应的公式来解决。例如：在成绩等级评价中，成绩在60分以下为“不合格”，设为D；60～80为“合格”，设为C；80～90为“良”，设为B；90～100为“优秀”，设为A。

上面的问题可以用下面的公式=IF（C2<60，“D”，IF（C2<80，“C”，IF（C2<90，“B”，“A”））），函数参数如图1所示。

得到所有学生的考试结果。效果如图2所示。

在IF函数运算中先判断最外层的条件，如果判断C2<60为假，在第二层条件继续判断，如果还为假再进入第三层IF语句，依次判断，直到符合条件。

（2）CHOOSE函数快速求出成绩的等级。

在Excel中，IF函数在Excel 2003版本中，最多允许7层嵌套，那么遇到嵌套过多的情况可以用Excel中的另一个函数解决，那就是CHOOSE函数。

下面我们来看看如何使用CHOOSE函数快速求出成绩的等级来。

具体操作方法：选中D2单元格，在“函数参数”中输入以下公式：

=CHOOSE（IF（D2>=90，1，IF（D2>=80，2，IF（D2>=60，3，4）））），“A”，“B”，“C”，“D”）

如果Index_num为1，函数CHOOSE返回Value1；如果Index_num为2，函数CHOOSE返回Value2；如果Index_num为3，函数CHOOSE返回Value3；依次类推。

CHOOSE函数与IF函数的区别是：

IF（）是判断第一个参数的逻辑值（是/否，只有这两个结果，第一个参数可以是计算式），“是”返回第二参数（或计算式的值），“否”返回第三个参数（或计算式的值）。

CHOOSE（）是按照序号（第一个参数）从一个数列（后面一串以逗号分隔的内容或单元格）中读取出对应的那个数。endprint