实验4-8Excel2010的综合应用
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
实验4-9 Excel 2010的综合应用【实验目的】
1)掌握Excel 2010的各个知识点。
【相关知识】
【实验内容】
1)成绩分析
小李是北京某政法学院教务处的工作人员,法律系提交了2012级4个法律专业教学班
的期末成绩单,为更好地掌握各个教学班学习的整体情况,教务处领导要求她制作成绩分析
表,供学院领导掌握宏观情况。请根据已有数据,帮助小李完成2012级法律专业学生期末
成绩分析表的制作。具体要求如下:
①打开"年级期末成绩分析.xlsx”文件。
②在“ 2012级法律”工作表最右侧依次插入“总分” 、“平均分”、“年级排名”列;
将工作表的第一行合并为一个单元格,并设置黑体、16磅,使其成为工作表的标题。对班
级成绩区套用带标题行的“表样式中等深浅15”的表格格式。设置所有列的对齐方式为居
中,其中排名为整数,其他成绩的数值保留1位小数。
③在“ 2012级法律”工作表中,利用公式分别计算“总分”、“平均分”、“年级排名”
列的值。对学生成绩不及格(小于60)的单元格套用格式突出显示为“黄色(标准色)填
充色红色(标准色)文本”。
提示:“黄色(标准色)填充色红色(标准色)文本”默认的设置格式中没有,需要进行自定义。
表格工具T设计T样式T条件格式T突出显示单元格规则T小于,在“小
于”对话框设置“自定义格式”,在“填充”选项卡中设置背景色为黄色(标准
色),在“字体”选项卡中设置字体颜色为红色(标准色)。
④在“ 2012级法律”工作表中,利用公式根据学生的学号将其班级的名称填入“班级”列,规则为:学号的第三位为专业代码、第四位为班级序号,即:
提示:使用MID+IF函数。
⑤根据“ 2012级法律”工作表,创建一个数据透视表,放置于表名为“班级平均分” 的新工作表中,工作表的标签颜色设置为红色。要求数据透视表中按照英语、体育、计算机、近代史、法制史、刑法、民法、法律英语、立法法的顺序统计各班各科成绩的平均分,其中行标签为班级。为数
据透视表格内容套用带标题行的“数据透视表样式中等深浅15”的表格格式,所有列的对齐方式设为居中,成绩的数值保留1位小数。
提示:工作表标签设置为红色:右键点击工作表标签选择"工作表标签颜色”。
⑥在“班级平均分”工作表中,针对各课程的班级平均分创建二维的簇状柱形图,其
中水平簇标签为班级,图例项为课程名称,并将图表放置在表格下方的A10: H30区域中。
⑦保存工作簿。
2) 计算工资
小李是东方公司的会计,利用自己所学的办公软件进行记账管理,为节省时间,同时又
确保记账的准确性,她使用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(K3v=4500,K3*sl!$B$3-sl!$C$3,IF
(K3<=9000,K3*sl!$B$4-sl!$C$4,IF(K3v=35000,K3*sl!$B$5-sl!$C$5,IF(K
3<=55000,K3*sl!$B$6-sl!$C$6,IF(K3v=80000,K3*sl!$B$7-sl!$C$7,K3*sl !$B$8-sl!$C$8))))))
3) 考分统计(本题涉及函数操作较为复杂,可选做) :
滨海市对重点中学组织了一场物理统考,并生成了所有考生和每一个题目的得分。市教
委要求小罗教师根据已有数据,统计分析各学校及班级的考试情况。请根据已有数据,统计分析各学校及班级的考试情况,帮助小罗完成此项工作。具体要求如下:
⑩打开“滨海市2015年春高二物理统考情况分析.xlsx”文件。
11利用“成绩单”、“小分统计”、“分值表”工作表中的数据,完成“按班级汇总”和“按学校汇总”工作表中相应空白列的数值计算。具体提示如下:
i. “考试学生数”列必须利用公式计算,“平均分”列由“成绩单”工作表数据
计算得出。
ii. “分值表”工作表中给出了本次考试各题的类型及分值(备注:本次考试一共50小题,其中1~40为客观题,41~50为主观题)。
iii. “小分统计”工作表中包含了各班级每一道小题的平均分,通过其可以计算出各班级的“客观题平均分”和“主观题平均分”(备注:由于系统生成每题平
均分时已经进行了四舍五入操作,因此通过其计算“客观题平均分”和“主观
题平均分”之和时,可能与根据“成绩单”工作表的计算结果存在一定误差)
C
iv. 利用公式计算“按学校汇总”工作表中的“客观题平均分”和“主观题平均分”。
计算方法为:每个学校的所有班级相应平均分乘以对应班级人数,相加后再除
以该校的总考生数。
v. 计算“按学校汇总”工作表中的每题
得分率,即:每个学校所有学生在该题上的得分之和除以该校总考生数,再除以该题的分值。
Vi.所有工作表中“考试学生数”、“最高分”、“最低分”显示为整数;各类平均分显示为数值格式,并保留2位小数;各题得分率显示为百分比数据格式,并保
留2位小数。
12新建“按学校汇总2 ”工作表,将“按学校汇总”工作表中所有单元格数值转置复制到新工作表中。
提示:选择性粘贴(粘贴:数值;运算:转置)
13将“按学校汇总2”工作表中的内容套用表格样式为“表样式中等深浅12”;将得分率低于80%的单元格标记为“浅红填充色深红色文本”格式,将介于80%和90%之间的
单元格标记为“黄填充色深黄色文本”格式。
提示:开始T样式功能区T套用表格样式和条件格式
14 保存“滨海市2015年春高二物理统考情况分析.xlsx ”文件。提示:“按班级汇总”工作表
中
“考试学生数”列使用函数COUNTIFS
举例:“按班级汇总”工作表中C2单元格的计算条件为“学校”为“滨海
市第一中学”且“班号”为“1”的“考试学生数”,
“最高分”使用函数MAX+IF
举例:“按班级汇总”工作表中D2单元格的计算条件为“学校”为
“滨海市第一中学”且“班号”为“ 1 ”的“最高分”,
最后按Ctrl+Shift+Enter组合键确定。
说明:
(成绩单!$A$2:$A$950=按班级汇总!$A2)*(成绩
单!$B$2:$B$950= 按班级汇总!$B2)相当于对“学校”为“滨海市
第一中学”且“班号”为“ 1”条件的判定,成立结果为“ 1 ”,否
则为“ 0”。成绩单!$D$2:D$950)则“学校”为“滨海市第一中
学”且“班号”
为“1”的学生保留原成绩,其余学生成绩计算结果为0。在以
上成绩中找最大值,可以得到要求的结果。