实验4-8 Excel 2010的综合应用.doc

《实验4-8 Excel 2010的综合应用.doc》由会员分享，可在线阅读，更多相关《实验4-8 Excel 2010的综合应用.doc（6页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、如有侵权，请联系网站删除，仅供学习与交流实验4-8 Excel 2010的综合应用【精品文档】第 6 页实验4-9 Excel 2010的综合应用【实验目的】1) 掌握Excel 2010的各个知识点。【相关知识】【实验内容】1) 成绩分析小李是北京某政法学院教务处的工作人员，法律系提交了2012级4个法律专业教学班的期末成绩单，为更好地掌握各个教学班学习的整体情况，教务处领导要求她制作成绩分析表，供学院领导掌握宏观情况。请根据已有数据，帮助小李完成2012级法律专业学生期末成绩分析表的制作。具体要求如下： 打开“年级期末成绩分析.xlsx”文件。 在“2012级法律”工作表最右侧依次插入“总

2、分”、“平均分”、“年级排名”列；将工作表的第一行合并为一个单元格，并设置黑体、16磅，使其成为工作表的标题。对班级成绩区套用带标题行的“表样式中等深浅15”的表格格式。设置所有列的对齐方式为居中，其中排名为整数，其他成绩的数值保留1位小数。 在“2012级法律”工作表中，利用公式分别计算“总分”、“平均分”、“年级排名”列的值。对学生成绩不及格（小于60）的单元格套用格式突出显示为“黄色（标准色）填充色红色（标准色）文本”。提示：“黄色（标准色）填充色红色（标准色）文本”默认的设置格式中没有，需要进行自定义。l 表格工具设计样式条件格式突出显示单元格规则小于，在“小于”对话框设置“自定义格式

3、”，在“填充”选项卡中设置背景色为黄色（标准色），在“字体”选项卡中设置字体颜色为红色（标准色）。 在“2012级法律”工作表中，利用公式根据学生的学号将其班级的名称填入“班级”列，规则为：学号的第三位为专业代码、第四位为班级序号，即：01法律一班02法律二班03法律三班04法律四班提示：使用MID+IF函数。 根据“2012级法律”工作表，创建一个数据透视表，放置于表名为“班级平均分”的新工作表中，工作表的标签颜色设置为红色。要求数据透视表中按照英语、体育、计算机、近代史、法制史、刑法、民法、法律英语、立法法的顺序统计各班各科成绩的平均分，其中行标签为班级。为数据透视表格内容套用带标题行的“

4、数据透视表样式中等深浅15”的表格格式，所有列的对齐方式设为居中，成绩的数值保留1位小数。提示：工作表标签设置为红色：右键点击工作表标签选择“工作表标签颜色”。 在“班级平均分”工作表中，针对各课程的班级平均分创建二维的簇状柱形图，其中水平簇标签为班级，图例项为课程名称，并将图表放置在表格下方的A10：H30区域中。 保存工作簿。2) 计算工资小李是东方公司的会计，利用自己所学的办公软件进行记账管理，为节省时间，同时又确保记账的准确性，她使用Excel编制了2014年3月员工工资表。请你根据下列要求帮助小李对该工资表进行整理和分析。 打开“东方公司2014年3月员工工资表.xlsx”。 通过合

5、并单元格，将表名“东方公司2014年3月员工工资表”放于整个表的上端，居中，并调整字体为黑体、14。 在“序号”列分别填入1到15，将其数据格式设置为数值，保留0位小数，居中。 将“基础工资”（含）往右各列设置为会计专用格式、保留2位小数、无货币符号。 调整表格各列宽度，使得各列内容能够显示，并设置居中对齐。设置纸张大小为A4、横向，整个工作表需调整在1个打印页内。 参考“工资薪金所得税利率”工作表信息，利用IF函数计算“应交个人所得税”列（提示：应交个人所得税=应纳税所得额*对应税率-对应速算扣除数）。 利用公式计算“实发工资列”，公式为：实发工资=应付工资合计-扣除社保-应交个人所得税。

6、复制工作表“2014年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) 3) 考分统计

7、（本题涉及函数操作较为复杂，可选做）：滨海市对重点中学组织了一场物理统考，并生成了所有考生和每一个题目的得分。市教委要求小罗教师根据已有数据，统计分析各学校及班级的考试情况。请根据已有数据，统计分析各学校及班级的考试情况，帮助小罗完成此项工作。具体要求如下： 打开“滨海市2015年春高二物理统考情况分析.xlsx”文件。 利用“成绩单”、“小分统计”、“分值表”工作表中的数据，完成“按班级汇总”和“按学校汇总”工作表中相应空白列的数值计算。具体提示如下：i. “考试学生数”列必须利用公式计算，“平均分”列由“成绩单”工作表数据计算得出。ii. “分值表”工作表中给出了本次考试各题的类型及分值（

8、备注：本次考试一共50小题，其中140为客观题，4150为主观题）。iii. “小分统计”工作表中包含了各班级每一道小题的平均分，通过其可以计算出各班级的“客观题平均分”和“主观题平均分”（备注：由于系统生成每题平均分时已经进行了四舍五入操作，因此通过其计算“客观题平均分”和“主观题平均分”之和时，可能与根据“成绩单”工作表的计算结果存在一定误差）。iv. 利用公式计算“按学校汇总”工作表中的“客观题平均分”和“主观题平均分”。计算方法为：每个学校的所有班级相应平均分乘以对应班级人数，相加后再除以该校的总考生数。v. 计算“按学校汇总”工作表中的每题得分率，即：每个学校所有学生在该题上的得分之

9、和除以该校总考生数，再除以该题的分值。vi. 所有工作表中“考试学生数”、“最高分”、“最低分”显示为整数；各类平均分显示为数值格式，并保留2位小数；各题得分率显示为百分比数据格式，并保留2位小数。 新建“按学校汇总2”工作表，将“按学校汇总”工作表中所有单元格数值转置复制到新工作表中。提示：选择性粘贴（粘贴：数值；运算：转置） 将“按学校汇总2”工作表中的内容套用表格样式为“表样式中等深浅12”；将得分率低于80%的单元格标记为“浅红填充色深红色文本”格式，将介于80%和90%之间的单元格标记为“黄填充色深黄色文本”格式。提示：开始样式功能区套用表格样式和条件格式 保存“滨海市2015年春高

10、二物理统考情况分析.xlsx”文件。提示：“按班级汇总”工作表中l “考试学生数”列使用函数COUNTIFSn 举例：“按班级汇总”工作表中C2单元格的计算条件为“学校”为“滨海市第一中学”且“班号”为“1”的“考试学生数”，n C2单元格输入公式：=COUNTIFS(成绩单!$A$2:$A$950,按班级汇总!$A2,成绩单!$B$2:$B$950,按班级汇总!$B2)l “最高分”使用函数MAX+IFn 举例：“按班级汇总”工作表中D2单元格的计算条件为“学校”为“滨海市第一中学”且“班号”为“1”的“最高分”，n D2单元格输入公式：=MAX(IF(成绩单!$A$2:$A$950=按班级

11、汇总!$A10)*(成绩单!$B$2:$B$950=按班级汇总!$B10),成绩单!$D$2:$D$950,0)n 最后按Ctrl+Shift+Enter组合键确定。n 说明：u (成绩单!$A$2:$A$950=按班级汇总!$A2)*(成绩单!$B$2:$B$950=按班级汇总!$B2)相当于对“学校”为“滨海市第一中学”且“班号”为“1”条件的判定，成立结果为“1”，否则为“0”。u 成绩单!$D$2:D$950)则“学校”为“滨海市第一中学”且“班号”为“1”的学生保留原成绩，其余学生成绩计算结果为0。在以上成绩中找最大值，可以得到要求的结果。u 最后一个乘法涉及到数组相乘，需要用Ctr

12、l+Shift+Enter组合键确认。l “最低分”使用函数MIN+IFn 举例：“按班级汇总”工作表中E2单元格的计算条件为“学校”为“滨海市第一中学”且“班号”为“1”的“最低分”，n E2单元格输入公式：=MIN(IF(成绩单!$A$2:$A$950=按班级汇总!$A10)*(成绩单!$B$2:$B$950=按班级汇总!$B10),成绩单!$D$2:$D$950,100)n 最后按Ctrl+Shift+Enter组合键确定。l “平均分”使用函数AVERAGEIFSn F2单元格输入公式：=AVERAGEIFS(成绩单!$D$2:$D$950,成绩单!$A$2:$A$950,按班级汇总!

13、$A2,成绩单!$B$2:$B$950,按班级汇总!$B2)l “主观题平均分”和“客观题平均分”使用函数SUMn G2单元格输入公式：=SUM(小分统计!$C2:$AP2)n H2单元格输入公式：=SUM(小分统计!$AQ2:$AZ2)注意：公式中哪些要使用绝对地址，哪些要使用相对地址。l “考试学生数”、“最高分”、“最低分”显示为整数；各类平均分显示为数值格式，并保留2位小数。提示：“按学校汇总”工作表中l “考试学生数”列使用函数COUNTIFSn 举例：“按班级汇总”工作表中B2单元格的计算条件为“学校”为“滨海市第一中学”的“考试学生数”，n B2单元格输入公式：=COUNTIFS

14、(成绩单!$A$2:$A$950,A2)l “最高分”使用函数MAX+IFn C2单元格输入公式：=MAX(IF(成绩单!$A$2:$A$950=按学校汇总!$A2),成绩单!$D$2:$D$950,0)n 按Ctrl+Shift+Enter组合键确定。l “最低分”使用函数MIN+IFn D2单元格输入公式：=MIN(IF(成绩单!$A$2:$A$950=按学校汇总!$A2),成绩单!$D$2:$D$950,100)n 按Ctrl+Shift+Enter组合键确定。l “平均分”使用函数AVERAGEIFSn E2单元格输入公式：=AVERAGEIFS(成绩单!$D$2:$D$950,成绩单

15、!$A$2:$A$950,按学校汇总!$A2)l “主观题平均分”和“客观题平均分”使用函数SUM+IFn F2单元格输入公式：=SUM(IF(按班级汇总!$A$2:$A$33=按学校汇总!$A2,按班级汇总!$C$2:$C$33*按班级汇总!G$2:G$33,0)/$B2n 按Ctrl+Shift+Enter组合键确定。n G2单元格输入公式：=SUM(IF(按班级汇总!$A$2:$A$33=按学校汇总!$A2,按班级汇总!$C$2:$C$33*按班级汇总!H$2:H$33,0)/$B2n 按Ctrl+Shift+Enter组合键确定。l “【1】得分率”使用函数SUM+IFn H2单元格输

16、入公式：=SUM(IF(小分统计!$A$2:$A$33=按学校汇总!$A2,小分统计!C$2:C$33*按班级汇总!$C$2:$C$33,0)/$B2/分值表!B$3n 按Ctrl+Shift+Enter组合键确定。n 使用填充句柄填充H2：BE5区域，完成所有得分率的计算。l 所有工作表中“考试学生数”、“最高分”、“最低分”显示为整数；各类平均分显示为数值格式，并保留2位小数；各题得分率显示为百分比数据格式，并保留2位小数。说明：本次实验的习题均来自于“全国计算机等级考试：二级MS Office高级应用上机考试题库”，题目中的红色提示为方便学生平时练习使用，正式考试中不会出现红色提示，由考生根据给定的素材和题目要求自行判断。【实验思考】