实验48 Excel 的综合应用.docx
《实验48 Excel 的综合应用.docx》由会员分享，可在线阅读，更多相关《实验48 Excel 的综合应用.docx（6页珍藏版）》请在冰豆网上搜索。
实验48Excel的综合应用
实验4-9Excel2020的综合应用
【实验目的】
1）把握Excel2010的各个知识点。
【相关知识】
【实验内容】
1）成绩分析
小李是北京某政法学院教务处的工作人员，法律系提交了2021级4个法律专业教学班的期末成绩单，为更好地把握各个教学班学习的整体情形，教务处领导要求她制作成绩分析表，供学院领导把握宏观情形。
请依照已有数据，帮忙小李完成2021级法律专业学生期末成绩分析表的制作。
具体要求如下：
1 打开“年级期末成绩分析.xlsx”文件。
2 在“2021级法律”工作表最右边依次插入“总分”、“平均分”、“年级排名”列；将工作表的第一行归并为一个单元格，并设置黑体、16磅，使其成为工作表的题目。
对班级成绩区套用带题目行的“表样式中等深浅15”的表格格式。
设置所有列的对齐方式为居中，其中排名为整数，其他成绩的数值保留1位小数。
3 在“2021级法律”工作表中，利用公式别离计算“总分”、“平均分”、“年级排名”列的值。
对学生成绩不合格（小于60）的单元格套用格式突出显示为“黄色（标准色）填充色红色（标准色）文本”。
提示：
“黄色（标准色）填充色红色（标准色）文本”
默许的设置格式中没有，需要进行自概念。
表格工具→设计→样式→条件格式→突出显示单元格规那么→小于，在“小于”对话框设置“自概念格式”，在“填充”选项卡中设置背景色为黄色（标准色），在“字体”选项卡中设置字体颜色为红色（标准色）。
4 在“2021级法律”工作表中，利用公式依照学生的学号将其班级的名称填入“班级”列，规那么为：
学号的第三位为专业代码、第四位为班级序号，即：
01
法律一班
02
法律二班
03
法律三班
04
法律四班
提示：
利用MID+IF函数。
5 依照“2021级法律”工作表，创建一个数据透视表，放置于表名为“班级平均分”的新工作表中，工作表的标签颜色设置为红色。
要求数据透视表中依照英语、体育、运算机、近代史、法制史、刑法、民法、法律英语、立法法的顺序统计各班各科成绩的平均分，其中行标签为班级。
为数据透视表格内容套用带题目行的“数据透视表样式中等深浅15”的表格格式，所有列的对齐方式设为居中，成绩的数值保留1位小数。
提示：
工作表标签设置为红色：
右键点击工作表标签选择“工作表标签颜色”。
6 在“班级平均分”工作表中，针对各课程的班级平均分创建二维的簇状柱形图，其中水平簇标签为班级，图例项为课程名称，并将图表放置在表格下方的A10：
H30区域中。
7 保留工作簿。
2）计算工资
小李是东方公司的会计，利用自己所学的办公软件进行记账治理，为节省时刻，同时又确保记账的准确性，她利用Excel编制了2021年3月员工工资表。
请你依照以下要求帮忙小李对该工资表进行整理和分析。
1 打开“东方公司2021年3月员工工资表.xlsx”。
2 通过归并单元格，将表名“东方公司2021年3月员工工资表”放于整个表的上端，居中，并调整字体为黑体、14。
3 在“序号”列别离填入1到15，将其数据格式设置为数值，保留0位小数，居中。
4 将“基础工资”（含）往右各列设置为会计专用格式、保留2位小数、无货币符号。
5 调整表格各列宽度，使得各列内容能够显示，并设置居中对齐。
设置纸张大小为A4、横向，整个工作表需调整在1个打印页内。
6 参考“工资薪金所得税利率”工作表信息，利用IF函数计算“应交个人所得税”列（提示：
应交个人所得税=应纳税所得额*对应税率-对应速算扣除数）。
7 利用公式计算“实发工资列”，公式为：
实发工资=应付工资合计-扣除社保-应交个人所得税。
8 复制工作表“2021年3月”，将副本放置到原表的右边，并命名为“分类汇总”。
9 在“分类汇总”工作表中通过度类汇总功能求出各部门“应付工资合计”、“实发工资”的和，每组数据不分页。
=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）考分统计（此题涉及函数操作较为复杂，可选做）：
滨海市对重点中学组织了一场物理统考，并生成了所有考生和每一个题目的得分。
市教委要求小罗教师依照已有数据，统计分析各学校及班级的考试情形。
请依照已有数据，统计分析各学校及班级的考试情形，帮忙小罗完成此项工作。
具体要求如下：
10 打开“滨海市2021年春高二物理统考情形分析.xlsx”文件。
11 利用“成绩单”、“小分统计”、“分值表”工作表中的数据，完成“按班级汇总”和“按学校汇总”工作表中相应空白列的数值计算。
具体提示如下：
i.“考试学生数”列必需利用公式计算，“平均分”列由“成绩单”工作表数据计算得出。
ii.“分值表”工作表中给出了本次考试各题的类型及分值（备注：
本次考试一共50小题，其中1~40为客观题，41~50为主观题）。
iii.“小分统计”工作表中包括了各班级每一道小题的平均分，通过其能够计算出各班级的“客观题平均分”和“主观题平均分”（备注：
由于系统生成每题平均分时已经进行了四舍五入操作，因此通过其计算“客观题平均分”和“主观题平均分”之和时，可能与依照“成绩单”工作表的计算结果存在必然误差）。
iv.利用公式计算“按学校汇总”工作表中的“客观题平均分”和“主观题平均分”。
计算方式为：
每一个学校的所有班级相应平均分乘以对应班级人数，相加后再除以该校的总考生数。
v.计算“按学校汇总”工作表中的每题得分率，即：
每一个学校所有学生在该题上的得分之和除以该校总考生数，再除以该题的分值。
vi.所有工作表中“考试学生数”、“最高分”、“最低分”
显示为整数；各类平均分显示为数值格式，并保留2位小数；各题得分率显示为百分比数据格式，并保留2位小数。
12 新建“按学校汇总2”工作表，将“按学校汇总”工作表中所有单元格数值转置复制到新工作表中。
提示：
选择性粘贴（粘贴：
数值；运算：
转置）
13 将“按学校汇总2”工作表中的内容套用表格样式为“表样式中等深浅12”；将得分率低于80%的单元格标记为“浅红填充色深红色文本”格式，将介于80%和90%之间的单元格标记为“黄填充色深黄色文本”格式。
提示：
开始→样式功能区→套用表格样式和条件格式
14 保留“滨海市2021年春高二物理统考情形分析.xlsx”文件。
提示：
“按班级汇总”工作表中
“考试学生数”列利用函数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组合键确信。
“平均分”利用函数AVERAGEIFS
F2单元格输入公式：
=AVERAGEIFS（成绩单!
$D$2:
$D$950,成绩单!
$A$2:
$A$950,按班级汇总!
$A2,成绩单!
$B$2:
$B$950,按班级汇总!
$B2）
“主观题平均分”和“客观题平均分”利用函数SUM
G2单元格输入公式：
=SUM（小分统计!
$C2:
$AP2）
H2单元格输入公式：
=SUM（小分统计!
$AQ2:
$AZ2）
注意：
公式中哪些要利用绝对地址，哪些要利用相对地址。
“考试学生数”、“最高分”、“最低分”显示为整数；各类平均分显示为数值格式，并保留2位小数。
提示：
“按学校汇总”工作表中
“考试学生数”列利用函数COUNTIFS
举例：
“按班级汇总”工作表中B2单元格的计算条件为“学校”为“滨海市第一中学”的“考试学生数”，
B2单元格输入公式：
=COUNTIFS（成绩单!
$A$2:
$A$950,A2）
“最高分”利用函数MAX+IF
C2单元格输入公式：
=MAX（IF（（成绩单!
$A$2:
$A$950=按学校汇总!
$A2）,成绩单!
$D$2:
$D$950,0））
按Ctrl+Shift+Enter组合键确信。
“最低分”利用函数MIN+IF
D2单元格输入公式：
=MIN（IF（（成绩单!
$A$2:
$A$950=按学校汇总!
$A2）,成绩单!
$D$2:
$D$950,100））
按Ctrl+Shift+Enter组合键确信。
“平均分”利用函数AVERAGEIFS
E2单元格输入公式：
=AVERAGEIFS（成绩单!
$D$2:
$D$950,成绩单!
$A$2:
$A$950,按学校汇总!
$A2）
“主观题平均分”和“客观题平均分”利用函数SUM+IF
F2单元格输入公式：
=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+IF
H2单元格输入公式：
=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高级应用上机考试题库”，题目中的红色提示为方便学生平常练习利用，正式考试中可不能显现
升级会员 