资源描述
,LOGO,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,#,单击此处编辑母版标题样式,第,7,章,Excel,高级,应用,制作学生成绩统计分析,表,7.1.1,任务描述,在学校的教学管理过程中，对学生成绩的分析和统计是必不可少的。杨晓燕利用Excel2021提供函数、排序、筛选、分类汇总、数据透视表等工具对计算机根底课程成绩进行统计和分析，还可以利用图表和数据透视图更直观的显示统计结果。,本章,任务,制作学生成绩统计分析表,结果,图如下：,3,7.1.2,任务分解,任务思路,制作学生成绩统计分析表整体结构,用公式和函数完成计算,利用排序、筛选、分类汇总和数据透视表等统计成绩,利用图表和数据透视图显示统计结构,1.,制作,工作表整体结构,2.,使用,函数,3.,数据,有效性,4.,排序,7,.2,完成任务,5.,筛选,6,.,分类汇总,7,.,数据透视表,8.,图表,9.,数据透视图,在工作表标签上右击,6,1,制作工作表整体结构,其他方法：,双击工作表标签,单击工作表最右边标签,拖开工作表标签,Ctrl+拖开工作表标签,2,使用函数,函数,是系统预定义的特殊公式，通过使用一些称为参数的特定数值按特定顺序或结构执行计算。,函数的语法形式,函数名（参数,1,参数,2,),函数的输入,直接输入或使用命令,7,任务,:,计算总分,SUM,函数,主要功能,计算所有参数数值的和,使用格式,SUM(Number1,Number2),参数说明,Number1,、,Number2,代表需要计算的值，可以是具体的数值、引用的单元格（区域）、逻辑值等。,举例,=SUM(C2,：,E2),8,9,任务,:,计算总分,SUM,函数,任务,:,计算总评,IF,函数,主要功能,执行真假值判断，根据对指定条件进行逻辑评价的真假而返回不同的结果,使用格式,IF,(,logjical_test,value_if_true,value_if_false,),参数说明,logjical_test,为条件表达式,value_if_true,是条件表达式中的条件成立时的返回值,value_if_false,是条件表达式中的条件不成立时的返回值,举例,=IF(F2=60,合格,不合格,),10,11,任务,:,计算总评,IF,函数,任务,:,计算每人排名,RANK,函数,主要功能,返回一个数值在一组数值中的排位,使用格式,RANK,（,Number,，,Ref,，,Order,）,参数说明,Number,是指定的要排位的数值。,Ref,是引用的区域。,Order,是排位方式，排位方式默认的数值为,0,或省略，表示排位方式是降序排列；非零值表示的是升序排列方式；数值重复时排位相同。,举例,=RANK(F2,$F$2:$F$16),12,13,任务,:,计算每人排名,RANK,函数,任务,:,计算专业名称列,VLOOKUP,函数,主要功能,搜索指定区域内首列满足条件的元素，确定待检测单元格在区域中的行序号，再进一步返回选定单元格的值。,使用格式,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),参数说明,Lookup_value,为需要在数据表第一列中查找的数值；,Table_array,为需要在其中查找数据的数据表。,Col_index_num,为,table_array,中待返回的匹配值的列序号。,Range_lookup,为一逻辑值，指明函数,VLOOKUP,返回时是精确匹配,还是近似匹配。,举例,=VLOOKUP(A24,A2:H16,6),14,15,任务,:,计算专业名称列,VLOOKUP,函数,任务,:,统计总人数,COUNT,函数,主要功能,计算指定区域中,包含数字的单元格,以及参数列表中的数字个数。,使用格式,COUNT(value1,value2,.),参数说明,value1,value2,.,为包含或引用各种类型数据的参数,但只有数字类型的数据才被计算。,举例,=COUNT(C2:C16),16,17,任务,:,统计总人数,COUNT,函数,任务,:,计算及,格人数,COUNTIF,函数,主要功能,统计某个单元格区域中符合指定条件的单元格数目,使用格式,COUNT(,Range,Criteria,),参数说明,Range,代表要统计的单元格区域；,Criteria,表示指定的条件表达式。,注意,:,允许引用的单元格区域中有空白单元格出现。,举例,=COUNTIF(G2:G16,合格,),18,19,任务,:,计算及格率,COUNTIF,函数,任务,:,排名前,10,总分之和,SUMIF,函数,主要功能,对指定区域中符合指定条件的单元格求和,使用格式,SUMIF(Range,Criteria,sum_range,),参数说明,Range,代表要,进行条件判断的,单元格区域；,Criteria,表示指定的条件表达式,；,Sum_range,指定要求和的单元格区域,。,举例,=SUMIF(,计算机基础成绩,排名,=10,计算机基础成绩,总分,),20,21,任务,:,排名前,10,总分之和,SUMIF,函数,任务,:,从身份证号中提取出生日期,MID,函数,主要功能,从文本字符串中指定的起始位置起返回指定长度的字符。,使用格式,MID(text,start_num,num_chars),参数说明,text,文本字符串,start_num,起始位置,num_chars,指定长度,举例,=MID(B2,7,4),22,23,任务,:,从身份证号中提取出生日期,MID,函数,任务,:,计算年龄,YEAR,、,NOW,函数,主要功能,YEAR,函数,:,返回给定日期的年份,NOW,函数,:,返回日期时间格式的当前日期和时间,使用格式,YEAR(serial_number),NOW(),举例,=YEAR(C2),=NOW(),24,25,任务,:,计算年龄,YEAR,、,NOW,函数,任务,:,计算性别,IF、MOD,函数,主要功能,返回两个数相除的余数,使用格式,MOD(Number,Divisor,),参数说明,Number,是被除数,；,Divisor,是除数,。,举例,=IF(MOD(MID(B2,17,1),2)=1,男,女,),26,27,任务,:,计算性别,IF、MOD,函数,3,数据有效性,方法：,选定需设置数据有效性的单元格或区域数据数据工具数据有效性,28,4,排序,1简单排序,29,数据,排序和筛选,排序,2自定义排序,30,4,排序,任务：将学生成绩先按性别升序，再按所学专业为英语、音乐学、语文教育的顺序排序,5,筛选,筛,选能将数据清单中,不符合条件的记录隐藏,起来，只显示符合条件的记录，得到用户需要的记录的一个子集，从而帮助用户快速、准确地查找与显示有用数据,。,31,筛选结果不需要重新排列或移动就可以复制、查找、编辑、设置格式、制作图表和打印。,1自动筛选:数据排序和筛选筛选,32,5,筛选,2高级筛选：在数据列表以外的位置输入条件区域数据|排序和筛选|高级,33,5,筛选,任务：筛选“姓李的或“音乐学专业的学生成绩信息,6,分类汇总,作用,:,将数据列表以分类汇总的形式反映各类数据的情况。,前提,:,按某关键字段进行,分类汇总之前必须按该字段排序。,34,6,分类汇总,任务：,统计各专业人数和期末成绩、平时成绩、总分的平均值,方法：,数据分级显示,分类汇总,35,删除分类,汇总,36,6,分类汇总,单击,“,全部删除,”,7,数据透视表,实现,多,个字段进行分类并汇总,以,交,互式表,格,显示结果,37,任务：,统计各专业男、女生,人数,方法：,插入表格数据透视表,38,7,数据透视表,创立图表,方法：选中数据源插入图表,39,8,图表,任务：,将各专业男女生人数用柱形图展示,40,8,图表,8,图表,图表元素,41,图表区,绘图区,图例,垂直（值）轴,水平（类别）轴,图表标题,数据系列,编辑图表,移,动、复制、缩放和,删除,其它,编辑,42,8,图表,迷你图,43,8,图表,迷你图,迷你图是工作表单元格中的一个微型图表，可提供数据的直观表示。,可以显示一系列数值的趋势，也可以突出显示最大值和最小值,有折线图、柱形图、盈亏三种类型,放置在数据旁边的单元格中，可到达最正确效果。,44,8,图表,9,数据透视图,数据透视图建立在数据透视表的根底上，以图形方式展示数据透视表的数据,方法一 根据已有数据透视表创立数据透视图,45,9,数据透视图,方法二 直接从数据源生成数据透视图,46,7.2.9,知识拓展,常见错误及解决方法,切片器,47,任务总结,48,通过任务学习了Excel 2021常用函数的使用、图表的创立方法、利用数据有效性定义数据输入范围及利用排序、筛选、分类汇总、数据透视表和数据透视图对工作表进行统计和汇总等内容。,作业,实,训十,实,训十一,49,
展开阅读全文