学生成绩统计与分析学生成绩统计与分析 内容提要案例设计建立学生名册表建立单科成绩表建立成绩总表学生成绩分析建立成绩分析统计表 案例分析本工作情境要建立本工作情境要建立5 5个表格：个表格：一是学生名册表，该表中，学生的学号，身份证号、邮编、一是学生名册表，该表中，学生的学号，身份证号、邮编、号码等数据，一部分前面以号码等数据，一部分前面以“ “0”0”开头，输入后会自开头，输入后会自动消失，需要将这些数据设置成文本型；动消失，需要将这些数据设置成文本型；二是学生各科成绩表，成绩数据在复制时容易出错，这是二是学生各科成绩表，成绩数据在复制时容易出错，这是由于数据是用公式运算出来的，需要使用由于数据是用公式运算出来的，需要使用“ “选择性粘贴选择性粘贴” ”来实现；来实现；三学生成绩总表，成绩数据需要在单科成绩中引用三学生成绩总表，成绩数据需要在单科成绩中引用 ，注意，注意数据表的引用方法；数据表的引用方法；四是学生成绩分析表的制作，表中的数据用公式或函数进四是学生成绩分析表的制作，表中的数据用公式或函数进行运算，用图表进行分析行运算，用图表进行分析五是制作学生成绩分析统计表，表中的数据需要从成绩分五是制作学生成绩分析统计表，表中的数据需要从成绩分析表中引用，注意数据的复制和引用。
析表中引用，注意数据的复制和引用参考样例 一、建立学生名册表并格式化能力目标：能力目标：会制作表格、格式化表格 讲和练启动Excel软件，认识Excel界面输入文字和数据特殊数据处理：学号、工号、编号、身份证号、邮编、号码等数据，一部分前面以“0”开头，输入后会自动消失，需要将这些数据设置成文本型 “格式”/“单元格”菜单，打开“单元格格式设置”对话框，设置表格格式 自主训练建立学生本班同学的学生名册表 二、建立单科成绩表能力目标：能力目标：掌握工作表的复制和粘贴掌握单个工作表中的数据运算 讲和练导入学生单科成绩表：工作表复制计算学生单科成绩：学会运用公式计算数据，数据的引用 成绩计算公式单科成绩计算方法：平时成绩占20%，作业占20%，期末考试占60%公式：=平时成绩×0.2+作业×0.2+期末考试×0.6 自主训练设计学生自已班级本学期所学各门课程的成绩，并进行数据运算 三、建立成绩总表能力目标：能力目标：掌握工作表的操作掌握单元格引用（绝对地址、相对地址）掌握数据公式和函数运算(rank等函数)、多表运算 讲和练建立成绩总表：选择性粘贴各单科成绩、计算每个人的总分和平均分、课程的最高分、最低分、平均分设置数据条件格式：不及格为红色学生排名次 相关函数函数函数功能功能Sum Sum 计算单元区域中所有数值的和计算单元区域中所有数值的和 Average Average 计算数值的算术平均值计算数值的算术平均值 Max Max 计算一组数值的最大值计算一组数值的最大值 Min Min 计算一组数值的最小值计算一组数值的最小值 Rank Rank 计算某数值在一列数字中相对于其计算某数值在一列数字中相对于其他数值的大小排位他数值的大小排位 自主训练将学生自已班级的各门课程成绩汇总，并进行数据运算。
四、建立成绩分析表能力目标：能力目标：掌握数据公式和函数运算(counta、countif等函数)、多表运算会制作图表(折线图)、能完成数据筛选操作 讲和练数据统计分析建立图表：图表向导 相关运算应考人数应考人数=COUNTA(E3:E33)=COUNTA(E3:E33)实考人数实考人数=COUNT(E3:E33)=COUNT(E3:E33)及格人数：及格人数：=COUNTIF(E3:E33,">=60")=COUNTIF(E3:E33,">=60")不及格人数：不及格人数：=COUNTIF(E3:E33,"<60")=COUNTIF(E3:E33,"<60")90-10090-100分：分：=COUNTIF(E3:E33,">=90")=COUNTIF(E3:E33,">=90")80-8980-89分：分：=COUNTIF(E3:E33,">=80")-I21=COUNTIF(E3:E33,">=80")-I2170-7970-79分：分：=COUNTIF(E3:E33,">=70")-I21-I22=COUNTIF(E3:E33,">=70")-I21-I2260-6960-69分：分：=COUNTIF(E3:E33,">=60")-I21-I22-I23=COUNTIF(E3:E33,">=60")-I21-I22-I2340-5940-59分：分：=COUNTIF(E3:E33,"<60")-I26=COUNTIF(E3:E33,"<60")-I260-390-39分：分：=COUNTIF(E3:E33,"<40")=COUNTIF(E3:E33,"<40") 自主训练为学生上一次课制作的班级学生成绩表进行成绩分析 五、建立成绩分析统计表能力目标：能力目标：掌握数据公式和函数运算(counta、countif等函数)、多表运算会制作图表(条形图、柱形图) 讲和练数据计算和图表制作数据计算和图表制作及格率及格率=1-(B13+B14)/B7=1-(B13+B14)/B7优秀率优秀率=B9/B7=B9/B7 自主训练丰富完善班级学生成绩分析表 课后作业1 1、根据天正公司员工名册、根据天正公司员工名册( (素材素材).doc).doc文档表格，文档表格，在在ExcelExcel中建立一个职工名册表，并将工作表命名中建立一个职工名册表，并将工作表命名为为“ “名册名册” ”，文件命名为，文件命名为“ “职工考勤表职工考勤表” ”。
2 2、将考勤表、将考勤表( (素材素材). ).xlsxls素材表复制到职工考勤表，素材表复制到职工考勤表，并把工作表分别命名为并把工作表分别命名为“ “1 1月考勤月考勤” ”、、“ “2 2月考勤月考勤” ”、、“ “3 3月考勤月考勤” ”3 3、制作、制作“ “一季度考勤表一季度考勤表” ”，表格由，表格由“ “员工编号、员工编号、姓名、姓名、1 1月加班天数、月加班天数、2 2月加班天数、月加班天数、3 3月加班天数月加班天数、一季度总加班天数、一季度总加班天数” ”组成，组成，3 3个月的加班天数是个月的加班天数是由素材表中的由素材表中的“ “加班天数加班天数- -请假天数请假天数” ”获得4 4、分别求出最高加班天数、最低加班天数、平均、分别求出最高加班天数、最低加班天数、平均加班天数加班天数 课后作业5 5、打开、打开“ “工资表工资表( (素材素材). ).xlsxls” ”素材表，获取职工素材表，获取职工基本工资，分别创建职工基本工资，分别创建职工1 1月、月、2 2月、月、3 3月工资表，月工资表，工资表分别包括员工编号、姓名、加班天数、基工资表分别包括员工编号、姓名、加班天数、基本工资、职务津贴、应发工资、个税、实发工资本工资、职务津贴、应发工资、个税、实发工资等内容。
等内容6 6、利用、利用if if函数，计算职务工资函数，计算职务工资职务职务 经理经理主任主任 工程师工程师其它职称工资其它职称工资 300030002500 20002500 2000150015007 7、加班天数、加班天数加班天数加班天数= =加班天数加班天数- -请假天数请假天数8 8、计算应发工资、计算应发工资应发工资应发工资= =基本工资基本工资+ +职务工资职务工资+ +加班天数加班天数* *基本工基本工资资/22/22 课后作业9、用if 函数计算个税个税=(基本工资-1600)*税率-扣除数n n条件条件税率税率 扣除数扣除数n n基本工资基本工资<=1600<=16000 00 0n n基本工资基本工资-1600<500-1600<5005%5%0 0n n500<500<基本工资基本工资-1600<2000-1600<200010%10% 2525n n2000<2000<基本工资基本工资-1600<5000-1600<500015%15% 125125n n5000<5000<基本工资基本工资-1600<20000-1600<20000 20%20% 37537510、计算实发工资实发工资=应发工资-个税 。