精选实验4-8-Excel-2024的综合应用2.doc

该【精选实验4-8-Excel-2024的综合应用2 】是由【朱老师】上传分享，文档一共【5】页，该文档可以免费在线阅读，需要了解更多关于【精选实验4-8-Excel-2024的综合应用2 】的内容，可以使用淘豆网的站内搜索功能，选择自己适合的文档，以下文字是截取该文章内的部分文字，如需要获得完整电子版，请下载此文档到您的设备，方便您编辑和打印。实验4-9Excel2024的综合应用【实验目的】掌握Excel2024的各个知识点。【相关知识】【实验内容】成绩分析小李是北京某政法学院教务处的工作人员,法律系提交了2024级4个法律专业教学班的期末成绩单,为更好地掌握各个教学班学****的整体情况,教务处领导要求她制作成绩分析表,供学院领导掌握宏观情况。请根据已有数据,帮助小李完成2024级法律专业学生期末成绩分析表的制作。具体要求如下:翻开“〞文件。在“2024级法律〞工作表最右侧依次插入“总分〞、“平均分〞、“年级排名〞列;将工作表的第一行合并为一个单元格,并设置黑体、16磅,使其成为工作表的标题。对班级成绩区套用带标题行的“表样式中等深浅15〞的表格格式。设置所有列的对齐方式为居中,其中排名为整数,其他成绩的数值保存1位小数。在“2024级法律〞工作表中,利用公式分别计算“总分〞、“平均分〞、“年级排名〞列的值。对学生成绩不及格〔小于60〕的单元格套用格式突出显示为“黄色〔标准色〕填充色红色〔标准色〕文本〞。提示:“黄色〔标准色〕填充色红色〔标准色〕文本〞默认的设置格式中没有,需要进行自定义。表格工具→设计→样式→条件格式→突出显示单元格规那么→小于,在“小于〞对话框设置“自定义格式〞,在“填充〞选项卡中设置背景色为黄色〔标准色〕,在“字体〞选项卡中设置字体颜色为红色〔标准色〕。在“2024级法律〞工作表中,利用公式根据学生的学号将其班级的名称填入“班级〞列,规那么为:学号的第三位为专业代码、第四位为班级序号,即:01法律一班02法律二班03法律三班04法律四班提示:使用MID+IF函数。根据“2024级法律〞工作表,创立一个数据***表,放置于表名为“班级平均分〞的新工作表中,工作表的标签颜色设置为红色。要求数据***表中按照英语、体育、计算机、近代史、法制史、刑法、民法、法律英语、立法法的顺序统计各班各科成绩的平均分,其中行标签为班级。为数据***表格内容套用带标题行的“数据***表样式中等深浅15〞的表格格式,所有列的对齐方式设为居中,成绩的数值保存1位小数。提示:工作表标签设置为红色:右键点击工作表标签选择“工作表标签颜色〞。在“班级平均分〞工作表中,针对各课程的班级平均分创立二维的簇状柱形图,其中水平簇标签为班级,图例项为课程名称,并将图表放置在表格下方的A10:H30区域中。保存工作簿。计算工资小李是东方公司的会计,利用自己所学的办公软件进行记账管理,为节省时间,同时又确保记账的准确性,她使用Excel编制了2024年3月员工工资表。请你根据以下要求帮助小李对该工资表进行整理和分析。翻开“〞。通过合并单元格,将表名“东方公司2024年3月员工工资表〞放于整个表的上端,居中,并调整字体为黑体、14。在“序号〞列分别填入1到15,将其数据格式设置为数值,保存0位小数,居中。将“根底工资〞〔含〕往右各列设置为会计专用格式、保存2位小数、无货币符号。调整表格各列宽度,使得各列内容能够显示,并设置居中对齐。设置纸张大小为A4、横向,整个工作表需调整在1个打印页内。参考“工资薪金所得税利率〞工作表信息,利用IF函数计算“应交个人所得税〞列〔提示:应交个人所得税=应纳税所得额*对应税率-对应速算扣除数〕。利用公式计算“实发工资列〞,公式为:实发工资=应付工资合计-扣除社保-应交个人所得税。复制工作表“2024年3月〞,将副本放置到原表的右侧,并命名为“分类汇总〞。在“分类汇总〞工作表中通过分类汇总功能求出各部门“应付工资合计〞、“实发工资〞的和,每组数据不分页。=IF(K3<=1500,K3*sl!$B$2-sl!$C$2,IF(K3<=4500,K3*sl!$B$3-sl!$C$3,IF(K3<=9000,K3*sl!$B$4-sl!$C$4,IF(K3<=35000,K3*sl!$B$5-sl!$C$5,IF(K3<=55000,K3*sl!$B$6-sl!$C$6,IF(K3<=80000,K3*sl!$B$7-sl!$C$7,K3*sl!$B$8-sl!$C$8))))))考分统计〔此题涉及函数操作较为复杂,可选做〕:滨海市对重点中学组织了一场物理统考,并生成了所有考生和每一个题目的得分。市教委要求小罗教师根据已有数据,统计分析各学校及班级的考试情况。请根据已有数据,统计分析各学校及班级的考试情况,帮助小罗完成此项工作。具体要求如下:翻开“〞文件。利用“成绩单〞、“小分统计〞、“分值表〞工作表中的数据,完成“按班级汇总〞和“按学校汇总〞工作表中相应空白列的数值计算。具体提示如下:“考试学生数〞列必须利用公式计算,“平均分〞列由“成绩单〞工作表数据计算得出。“分值表〞工作表中给出了本次考试各题的类型及分值〔备注:本次考试一共50小题,其中1~40为客观题,41~50为主观题〕。“小分统计〞工作表中包含了各班级每一道小题的平均分,通过其可以计算出各班级的“客观题平均分〞和“主观题平均分〞〔备注:由于系统生成每题平均分时已经进行了四舍五入操作,因此通过其计算“客观题平均分〞和“主观题平均分〞之和时,可能与根据“成绩单〞工作表的计算结果存在一定误差〕。利用公式计算“按学校汇总〞工作表中的“客观题平均分〞和“主观题平均分〞。计算方法为:每个学校的所有班级相应平均分乘以对应班级人数,相加后再除以该校的总考生数。计算“按学校汇总〞工作表中的每题得分率,即:每个学校所有学生在该题上的得分之和除以该校总考生数,再除以该题的分值。所有工作表中“考试学生数〞、“最高分〞、“最低分〞显示为整数;各类平均分显示为数值格式,并保存2位小数;各题得分率显示为百分比数据格式,并保存2位小数。新建“按学校汇总2〞工作表,将“按学校汇总〞工作表中所有单元格数值转置复制到新工作表中。提示:选择性粘贴〔粘贴:数值;运算:转置〕将“按学校汇总2〞工作表中的内容套用表格样式为“表样式中等深浅12〞;将得分率低于80%的单元格标记为“浅红填充色深红色文本〞格式,将介于80%和90%之间的单元格标记为“黄填充色深黄色文本〞格式。提示:开始→样式功能区→套用表格样式和条件格式保存“〞文件。提示:“按班级汇总〞工作表中“考试学生数〞列使用函数COUNTIFS举例:“按班级汇总〞工作表中C2单元格的计算条件为“学校〞为“滨海市第一中学〞且“班号〞为“1〞的“考试学生数〞,C2单元格输入公式:=COUNTIFS(成绩单!$A$2:$A$950,按班级汇总!$A2,成绩单!$B$2:$B$950,按班级汇总!$B2)“最高分〞使用函数MAX+IF举例:“按班级汇总〞工作表中D2单元格的计算条件为“学校〞为“滨海市第一中学〞且“班号〞为“1〞的“最高分〞,D2单元格输入公式:=MAX(IF((成绩单!$A$2:$A$950=按班级汇总!$A10)*(成绩单!$B$2:$B$950=按班级汇总!$B10),成绩单!$D$2:$D$950,0))最后按Ctrl+Shift+Enter组合键确定。说明:(成绩单!$A$2:$A$950=按班级汇总!$A2)*(成绩单!$B$2:$B$950=按班级汇总!$B2)相当于对“学校〞为“滨海市第一中学〞且“班号〞为“1〞条件的判定,成立结果为“1〞,否那么为“0〞。成绩单!$D$2:D$950)那么“学校〞为“滨海市第一中学〞且“班号〞为“1〞的学生保存原成绩,其余学生成绩计算结果为0。在以上成绩中找最大值,可以得到要求的结果。最后一个乘法涉及到数组相乘,需要用Ctrl+Shift+Enter组合键确认。“最低分〞使用函数MIN+IF举例:“按班级汇总〞工作表中E2单元格的计算条件为“学校〞为“滨海市第一中学〞且“班号〞为“1〞的“最低分〞,E2单元格输入公式:=MIN(IF((成绩单!$A$2:$A$950=按班级汇总!$A10)*(成绩单!$B$2:$B$950=按班级汇总!$B10),成绩单!$D$2:$D$950,100))最后按Ctrl+Shift+Enter组合键确定。“平均分〞使用函数AVERAGEIFSF2单元格输入公式:=AVERAGEIFS(成绩单!$D$2:$D$950,成绩单!$A$2:$A$950,按班级汇总!$A2,成绩单!$B$2:$B$950,按班级汇总!$B2)“主观题平均分〞和“客观题平均分〞使用函数SUMG2单元格输入公式:=SUM(小分统计!$C2:$AP2)H2单元格输入公式:=SUM(小分统计!$AQ2:$AZ2) 注意:公式中哪些要使用绝对地址,哪些要使用相对地址。“考试学生数〞、“最高分〞、“最低分〞显示为整数;各类平均分显示为数值格式,并保存2位小数。提示:“按学校汇总〞工作表中“考试学生数〞列使用函数COUNTIFS举例:“按班级汇总〞工作表中B2单元格的计算条件为“学校〞为“滨海市第一中学〞的“考试学生数〞,B2单元格输入公式:=COUNTIFS(成绩单!$A$2:$A$950,A2)“最高分〞使用函数MAX+IFC2单元格输入公式:=MAX(IF((成绩单!$A$2:$A$950=按学校汇总!$A2),成绩单!$D$2:$D$950,0))按Ctrl+Shift+Enter组合键确定。“最低分〞使用函数MIN+IFD2单元格输入公式:=MIN(IF((成绩单!$A$2:$A$950=按学校汇总!$A2),成绩单!$D$2:$D$950,100))按Ctrl+Shift+Enter组合键确定。“平均分〞使用函数AVERAGEIFSE2单元格输入公式:=AVERAGEIFS(成绩单!$D$2:$D$950,成绩单!$A$2:$A$950,按学校汇总!$A2)“主观题平均分〞和“客观题平均分〞使用函数SUM+IFF2单元格输入公式:=SUM(IF(按班级汇总!$A$2:$A$33=按学校汇总!$A2,按班级汇总!$C$2:$C$33*按班级汇总!G$2:G$33,0))/$B2按Ctrl+Shift+Enter组合键确定。G2单元格输入公式:=SUM(IF(按班级汇总!$A$2:$A$33=按学校汇总!$A2,按班级汇总!$C$2:$C$33*按班级汇总!H$2:H$33,0))/$B2按Ctrl+Shift+Enter组合键确定。“【1】得分率〞使用函数SUM+IFH2单元格输入公式:=SUM(IF(小分统计!$A$2:$A$33=按学校汇总!$A2,小分统计!C$2:C$33*按班级汇总!$C$2:$C$33,0))/$B2/分值表!B$3按Ctrl+Shift+Enter组合键确定。使用填充句柄填充H2:BE5区域,完成所有得分率的计算。所有工作表中“考试学生数〞、“最高分〞、“最低分〞显示为整数;各类平均分显示为数值格式,并保存2位小数;各题得分率显示为百分比数据格式,并保存2位小数。说明:本次实验的****题均来自于“全国计算机等级考试:二级MSOffice高级应用上机考试题库〞,题目中的红色提示为方便学生平时练****使用,正式考试中不会出现红色提示,由考生根据给定的素材和题目要求自行判断。【实验思考】