实验4-8Excel2010地综合应用.docxVIP

WORD格式 专业资料整理 实验 4-9 Excel 2010的综合应用 【实验目的】 掌握 Excel 2010 的各个知识点。 【相关知识】 【实验内容】 成绩分析 小李是北京某政法学院教务处的工作人员，法律系提交了 2012 级 4 个法律专业教学班 的期末成绩单， 为更好地掌握各个教学班学习的整体情况， 教务处领导要求她制作成绩分析 表，供学院领导掌握宏观情况。请根据已有数据，帮助小李完成 2012 级法律专业学生期末 成绩分析表的制作。具体要求如下： ① 打开“年级期末成绩分析 .xlsx”文件。 ② 在“ 2012 级法律”工作表最右侧依次插入“总分” 、“平均分”、“年级排名”列； 将工作表的第一行合并为一个单元格，并设置黑体、 16 磅，使其成为工作表的标题。对班 级成绩区套用带标题行的“表样式中等深浅 15”的表格格式。设置所有列的对齐方式为居 中，其中排名为整数，其他成绩的数值保留 1 位小数。 ③ 在“ 2012 级法律”工作表中，利用公式分别计算“总分” 、“平均分”、“年级排名” 列的值。对学生成绩不及格（小于 60）的单元格套用格式突出显示为“黄色（标准色）填 充色红色（标准色）文本” 。 提示：“黄色（标准色）填充色红色（标准色）文本”默认的设置格式中没有，需要进 行自定义。 表格工具→设计→样式→条件格式→突出显示单元格规则→小于， 在“小 于”对话框设置“自定义格式” ，在“填充”选项卡中设置背景色为黄色 （标准色），在“字体”选项卡中设置字体颜色为红色（标准色） 。 ④ 在“ 2012 级法律”工作表中，利用公式根据学生的学号将其班级的名称填入“班 级”列，规则为：学号的第三位为专业代码、第四位为班级序号，即： 01 法律一班 02 法律二班 03 法律三班 04 法律四班 提示：使用 MID+IF 函数。 ⑤ 根据“ 2012 级法律”工作表，创建一个数据透视表，放置于表名为“班级平均分” 的新工作表中， 工作表的标签颜色设置为红色。 要求数据透视表中按照英语、 体育、计算机、 近代史、法制史、刑法、民法、法律英语、立法法的顺序统计各班各科成绩的平均分，其中 行标签为班级。为数据透视表格内容套用带标题行的“数据透视表样式中等深浅 15”的表 格格式，所有列的对齐方式设为居中，成绩的数值保留 1 位小数。 提示：工作表标签设置为红色：右键点击工作表标签选择“工作表标签颜色” 。 ⑥ 在“班级平均分”工作表中，针对各课程的班级平均分创建二维的簇状柱形图，其 中水平簇标签为班级，图例项为课程名称，并将图表放置在表格下方的 A10： H30 区域中。 ⑦ 保存工作簿。 计算工资 小李是东方公司的会计， 利用自己所学的办公软件进行记账管理，为节省时间， 同时又 确保记账的准确性，她使用 Excel 编制了 2014 年 3 月员工工资表。请你根据下列要求帮助 小李对该工资表进行整理和分析。 ① 打开“东方公司 2014 年 3 月员工工资表 .xlsx”。 ② 通过合并单元格， 将表名 “东方公司 2014 年 3 月员工工资表” 放于整个表的上端， 居中，并调整字体为黑体、 14。 ③ 在“序号”列分别填入 1 到 15，将其数据格式设置为数值，保留 0 位小数，居中。 ④ 将“基础工资” （含）往右各列设置为会计专用格式、保留 2 位小数、无货币符号。 ⑤ 调整表格各列宽度，使得各列内容能够显示，并设置居中对齐。设置纸张大小为 A4、横向，整个工作表需调整在 1 个打印页内。 ⑥ 参考“工资薪金所得税利率”工作表信息，利用 IF 函数计算“应交个人所得税” 列（提示：应交个人所得税 =应纳税所得额 * 对应税率 -对应速算扣除数） 。 ⑦ 利用公式计算“实发工资列” ，公式为：实发工资 =应付工资合计 -扣除社保 -应交个 人所得税。 ⑧ 复制工作表“ 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(K 3=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) 考分统计（本题涉及函数操作较为复杂，可选做） ： 滨海市对重点中学组织了一场物理统考， 并生成了所有考生和每一个题目的得分