实验48Excel的综合应用.docx

实验48Excel的综合应用.docx

《实验48Excel的综合应用.docx》由会员分享，可在线阅读，更多相关《实验48Excel的综合应用.docx（8页珍藏版）》请在冰豆网上搜索。

实验48Excel的综合应用

1　

2　

3　

4　

01

法律一班

02

法律二班

03

法律三班

04

法律四班

提示：

使用MID+IF函数。

5　根据“2012级法律”工作表，创建一个数据透视表，放置于表名为“班级平均分”的新工作表中，工作表的标签颜色设置为红色。

要求数据透视表中按照英语、体育、计算机、近代史、法制史、刑法、民法、法律英语、立法法的顺序统计各班各科成绩的平均分，其中行标签为班级。

为数据透视表格内容套用带标题行的“数据透视表样式中等深浅15”的表格格式，所有列的对齐方式设为居中，成绩的数值保留1位小数。

提示：

工作表标签设置为红色：

右键点击工作表标签选择“工作表标签颜色”。

6　在“班级平均分”工作表中，针对各课程的班级平均分创建二维的簇状柱形图，其中水平簇标签为班级，图例项为课程名称，并将图表放置在表格下方的A10：

H30区域中。

7　保存工作簿。

1）计算工资

小李是东方公司的会计，利用自己所学的办公软件进行记账管理，为节省时间，同时又确保记账的准确性，她使用Excel编制了2014年3月员工工资表。

请你根据下列要求帮助小李对该工资表进行整理和分析。

1　打开“东方公司2014年3月员工工资表.xlsx”。

2　通过合并单元格，将表名“东方公司2014年3月员工工资表”放于整个表的上端，居中，并调整字体为黑体、14。

3　在“序号”列分别填入1到15，将其数据格式设置为数值，保留0位小数，居中。

4　将“基础工资”（含）往右各列设置为会计专用格式、保留2位小数、无货币符号。

5　调整表格各列宽度，使得各列内容能够显示，并设置居中对齐。

设置纸张大小为A4、横向，整个工作表需调整在1个打印页内。

6　参考“工资薪金所得税利率”工作表信息，利用IF函数计算“应交个人所得税”列（提示：

应交个人所得税=应纳税所得额*对应税率-对应速算扣除数）。

7　利用公式计算“实发工资列”，公式为：

实发工资=应付工资合计-扣除社保-应交个人所得税。

8　复制工作表“2014年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））））））

10　

2）考分统计（本题涉及函数操作较为复杂，可选做）：

滨海市对重点中学组织了一场物理统考，并生成了所有考生和每一个题目的得分。

市教委要求小罗教师根据已有数据，统计分析各学校及班级的考试情况。

请根据已有数据，统计分析各学校及班级的考试情况，帮助小罗完成此项工作。

具体要求如下：

11　打开“滨海市2015年春高二物理统考情况分析.xlsx”文件。

12　利用“成绩单”、“小分统计”、“分值表”工作表中的数据，完成“按班级汇总”和“按学校汇总”工作表中相应空白列的数值计算。

具体提示如下：

i.“考试学生数”列必须利用公式计算，“平均分”列由“成绩单”工作表数据计算得出。

ii.“分值表”工作表中给出了本次考试各题的类型及分值（备注：

本次考试一共50小题，其中1~40为客观题，41~50为主观题）。

iii.“小分统计”工作表中包含了各班级每一道小题的平均分，通过其可以计算出各班级的“客观题平均分”和“主观题平均分”（备注：

由于系统生成每题平均分时已经进行了四舍五入操作，因此通过其计算“客观题平均分”和“主观题平均分”之和时，可能与根据“成绩单”工作表的计算结果存在一定误差）。

iv.利用公式计算“按学校汇总”工作表中的“客观题平均分”和“主观题平均分”。

计算方法为：

每个学校的所有班级相应平均分乘以对应班级人数，相加后再除以该校的总考生数。

v.计算“按学校汇总”工作表中的每题得分率，即：

每个学校所有学生在该题上的得分之和除以该校总考生数，再除以该题的分值。

vi.所有工作表中“考试学生数”、“最高分”、“最低分”显示为整数；各类平均分显示为数值格式，并保留2位小数；各题得分率显示为百分比数据格式，并保留2位小数。

13　新建“按学校汇总2”工作表，将“按学校汇总”工作表中所有单元格数值转置复制到新工作表中。

提示：

选择性粘贴（粘贴：

数值；运算：

转置）

14　将“按学校汇总2”工作表中的内容套用表格样式为“表样式中等深浅12”；将得分率低于80%的单元格标记为“浅红填充色深红色文本”格式，将介于80%和90%之间的单元格标记为“黄填充色深黄色文本”格式。

提示：

开始→样式功能区→套用表格样式和条件格式

15　保存“滨海市2015年春高二物理统考情况分析.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高级应用上机考试题库”，题目中的红色提示为方便学生平时练习使用，正式考试中不会出现红色提示，由考生根据给定的素材和题目要求自行判断。

【实验思考】