Excel05第二章.doc

Excel在学生成绩管理中的应用 2 1、掌握数据格式的设置及数据有效性的设置 2、掌握使用函数对成绩表中的数据进行处理 数据格式的设置，数据有效性的设置，函数在成绩表中应用 数据有效性的设置，函数在成绩表中的应用 12 　 8　 29 课堂导入 提问：本学期末如果班主任让你做全班同学的成绩，你如何完成？ 新课 Excel在学生成绩管理中的应用 一、建立学生成绩统计表 1、设置数据格式 学号的输入应怎样实现？ 2、设置数据有效性 学生的成绩怎样控制在0-100之间为正确输入，如果超出这个范围，进行控制？ （1）在未输入数据之前进行有效性设置，然后输入数据 数据有效性是对单元格或单元格区域输入的数据从内容到数量上的限制。对于符合条件的数据，允许输入：对于不符合条件的数据，则禁止输入。这样就可以依靠系统检查数据的正确有效性，避免错误的数据录入。 选中要输入数据的区域→数据→有效性→设置→输入数据：进行相关的设置→出错警告：如果输入有错提示的信息。 数据有效性功能既可以对数值数据进行有效性设置，也可以对日期、时间、文本等各种类型的数据进行有效性设置。 预先设置，以保证输入数据的正确性，还可以用来检查已输入的数据，起到纠错的功能。 （2）对于已经输入的数据，设置有效性来进行圈释错误 选中数据区→数据→有效性→设置→工具→公式审核→显示“公式审核”工具栏→圈释无效数据 自动找出不合条件的数据，修改为正确数据后，圈释自动消失。 二、统计个人成绩和科目成绩 1、使用sum()函数计算总分： 2、使用average()函数计算平均分： 3、使用rank()函数计算平均分： 4、使用countif()函数统计平均分大于等于60分的学生个数，自己选择放在哪里。 优秀率： =Countif(学生成绩表！区域范围，条件设置)/CountA（学生成绩表！区域范围） 调整优秀率以百分比的格式显示。 4、排名次 第一个参数：输入要查找的值 第二个参数：指定排名范围 第三个参数：指定以降序排列还是升序排列 使用些函数时要注意第二个参数 必须以绝对引用的方式。 在公式中引用单元格时，有相对引用、绝对引用、混合引用和外部引用4种方式。当对公式进行复制时，相对引用的单元格会发生变化，而绝对引用的单元格将保持不变。通过单元格引用，可以在公式和函数中使用不同工作簿和不同工作表中的数据，或者在多个公式中使用同一个单元格的数据。 1．相对引用 “相对引用”是指在公式复制时，该地址相对于目标单元格在不断地发生变化，这种类型的地址由列标和行号表示。例如，单元格E2中的公式为“=SUM(B2:D2)”，当该公式被复制到E3、E4单元格时，目标单元格中的公式会相应变化为“=SUM(B3:D3)”、“=SUM(B4:D4)”。这是由于目标单元格的位置相对于源位置分别下移了一行和两行，导致参加运算的区域分别做了下移一行和两行的调整。 2．绝对引用 “绝对引用”是指在公式复制时，该地址不随目标单元格的变化而变化。绝对引用地址的表示方法是在引用地址的列标和行号前分别加上一个“$”符号。如$B$6、$C$6、$B$1：$B$9所示。这里的“$”符号就像是一把“锁”，锁定了引用地址，使它们在移动或复制时，不随目标单元格的变化而变化。 3．混合引用 “混合引用”是指在引用单元格地址时，一部分为相对引用地址，另一部分为绝对引用地址，例如$A1或A$1。如果“$”符号放在列标前，如$A1，则表示列的位置是“绝对不变”的，而行的位置将随目标单元格的变化而变化。反之，如果“$”符号放在行号前，如A$1，则表示行的位置是“绝对不变”的，而列的位置将随目标单元格的变化而变化。 4．外部引用（链接） 同一工作表中的单元格之间的引用被称做“内部引用”，而在Excel中引用同一工作簿中不同工作表中的单元格，或引用不同工作簿中的工作表的单元格被称做“外部引用”，也称为“链接”。 引用同一工作簿内不同工作表中的单元格格式为：“=工作表名!单元格地址”。例如“=Sheet2!A1+Sheet1!A4”表示将Sheet2中的A1单元格的数据与Sheet1中的A4单元格的数据相加，放入目标单元格中。 引用不同工作簿中工作表的单元格格式为：“=[工作簿名]工作表名!单元格地址”。例如“=[Book1]Sheet1!$A$1-[Book2]Sheet2!B1”表示将Book1工作簿的Sheet1工作表中的A1单元格的数据与Book2工作簿的Sheet2工作表中的B1单元格的数据相减，放入目标单元格中。 小结：通过本次课使同学们掌握使用图表统计分析数据，便于直观的比较数据。 作业： 1、新建一个工作簿命名为“座号姓名学生成绩管理表.xls”，打开第一章“座号姓名学生档案管理表.xls”，把学生的