Excel综合练习test40解答.docx
《Excel综合练习test40解答.docx》由会员分享，可在线阅读，更多相关《Excel综合练习test40解答.docx（13页珍藏版）》请在冰豆网上搜索。
Excel综合练习test40解答
Excel综合练习
操作要求：
对文档“Test4_0.xls”进行如下操作，操作完成后如【样张4-1】所示，同名保存。
1.在工作表Sheet1中有二个分公司，一个分公司下有三个分厂。
计算比例1，比例1的计算公式为：
各个分厂三月份利润占本分厂第一季度利润的比例，计算结果采用百分比格式，保留一位小数。
2.在工作表Sheet1中有二个分公司，一个分公司下有三个分厂，计算比例2，比例2的计算公式为：
各个分厂三月份利润占本分公司第一季度利润的比例，计算结果采用百分比格式，保留一位小数。
3.按【样张4-1】，在Sheet1工作表的Ｃ１２单元格处计算出上海分公司第一季度总利润的值，文字和计算结果在一个单元格中。
4.本工作簿中已将某个区域命名为“Data”，用公式计算出这个区域的平均利润值，放置在Sheet1工作表的G12单元格中。
5.在工作表Sheet1中将单元格Ｃ１０的批注复制到单元格Ｃ７处。
6.标题“ABC电气有限公司利润表”采用楷体，粗体，大小18磅，字体：
蓝色，下划双线，按样张居中，行高：
40。
表格中所有数字采用“Arial”字体，大小：
10，粗体。
在表格的左下角单元格Ｂ１２和右下角单元格Ｈ１２两个单元格加上小方格图案。
7.设置表格边框线，样张中粗线指最粗线，细线指最细线，其余框线为双线。
8.在Sheet1工作表的Ｉ６:
Ｉ１１区域中计算出每个分厂第一季度的利润值，使用条件格式，将Sheet１工作表中每个分厂的每个月利润值高于4000的利润设置为蓝色粗体，同时使用条件格式，将Sheet１工作表中每个月利润值占本分厂第一季度利润３０％以下时用绿色粗体显示。
9.将I列隐藏。
10.在页眉处输入你的学号，取消屏幕网格线，并在打印时取消网格线。
【样张4-1】
参考解答：
1.启动“MicrosoftExcel”程序，打开“D:
\Excel操作”文件夹中的Test4_0.xls文件。
在Sheet1工作表中选中G6单元格，在编辑栏中键入公式：
=F6/SUM（D6:
F6）。
选中G6单元格，单击格式工具栏中的“%”百分比格式按钮，并单击格式工具栏中的增加小数位数按钮，保留小数位一位。
选中G6单元格，使用填充柄拖动到单元格G11。
2．计算比例2的值。
在Sheet1工作表中选中H6单元格，在编辑栏中键入公式：
=F6/SUM（$D$6:
$F$8）。
选中H6单元格，单击格式工具栏中的“%”百分比格式按钮，并单击格式工具栏中的增加小数位数按钮，保留小数位一位。
选中H6单元格，使用填充柄拖动到单元格H8。
在Sheet1工作表中选中H9单元格，在编辑栏中键入公式：
=F9/SUM（$D$9:
$F$11）。
选中H9单元格，单击格式工具栏中的“%”百分比格式按钮，并单击格式工具栏中的增加小数位数按钮，保留小数位一位。
选中H9单元格，使用填充柄拖动到单元格H11。
3.在Sheet1工作表中选中C12单元格，在编辑栏中键入公式：
="上海分公司第一季度总利润="&SUM（D6:
F8）&"元"。
选中区域C12:
F12单元格，单击格式工具栏中“合并及居中”按钮。
4.在Sheet1工作表中选中G12单元格，在编辑栏中键入公式：
=AVERAGE（Data）。
5.在Sheet1工作表中选中C10单元格，单击格式工具栏中的“复制”按钮。
选中C7单元格，选择“编辑”菜单，选择“选择性粘贴”。
在“选择性粘贴”对话框中选中“批注”单选项，选择“确定”。
若C10处有虚框流动，按键。
6.按下列步骤操作：
在Sheet1工作表中选中B4单元格，使用剪贴板移到C4单元格。
选中C4单元格，选择“格式”菜单，选择“单元格”，选择“字体”选项卡，选择“楷体，加粗，18磅，蓝色，双下划线”。
选中区域C4:
F4单元格，单击格式工具栏中“合并及居中”按钮。
选中C4单元格，选择“格式”菜单，选择“单元格”，选择“对齐”选项卡，在水平对齐中选择“分散对齐”，在垂直对齐中选择“居中”。
选择“确定”按钮。
选择第4行，选择“格式”菜单，选择“行”，选择“行高”，在行高文本框中键入：
40。
选择区域D6:
H11，按Ctrl键同时选中G12，选择“格式”菜单，选择“单元格”，选择“字体”，选择“Arial”字体，大小10磅，加粗。
选择“确定”。
选中B12单元格，按Ctrl键同时选中H12，选择“格式”菜单，选择“单元格”，选择“图案”，选择图案中的小方格子图案。
选择“确定”。
7.按下列步骤操作：
在Sheet1工作表中选中区域C4:
F12单元格，选择“格式”菜单，选择“单元格”，选择“边框”选项卡，选择线型：
双线，选择“外边框”，选择“确定”。
选中区域B5:
H12单元格，选择“格式”菜单，选择“单元格”，选择“边框”选项卡，选择线型：
最粗线，选择“外边框”，选择“确定”。
选中区域B6:
H11单元格，选择“格式”菜单，选择“单元格”，选择“边框”选项卡，选择线型：
最粗线，选择“上边框”。
选择线型：
双线，选择“下边框”，选择“确定”。
选中区域C5:
F11单元格，选择“格式”菜单，选择“单元格”，选择“边框”选项卡，选择线型：
最细线，选择“中间垂直线”，选择“确定”。
8.按下列步骤操作：
在Sheet1工作表中的I6:
I11处计算出每个分厂第一季度的利润值。
选中D6:
F11单元格。
选择“格式”菜单，选择“条件格式”，选择“大于”，键入4000，选择“格式”，选择“加粗，蓝色”，选择“确定”。
选择“添加”按钮，选择“小于”，键入“=$I6*0.3”，选择“格式”，选择“加粗，绿色”，选择“确定”，如图4-1所示。
图4-1
9.选中“I”列，选择“格式”菜单，选择“列”，选择“隐藏”。
10.按下列步骤操作：
在Sheet1工作表中，选择“文件”菜单，选择“页面设置”，在页面设置对话框中，选择“页眉/页脚”选项卡，在页眉处输入你的学号。
选择“工作表”选项卡，取消打印的网格线。
选择“工具”菜单，选择“选项”，选择“视图”选项卡，取消网格线。
在Sheet1工作表的Ｉ６:
Ｉ１１区域中计算出每个分厂第一季度的利润值，计算公式：
I6=SUM（D6:
F6），拖动到I11单元格。
选中D6:
F11单元格。
选择“格式”菜单，选择“条件格式”，选择“小于”，键入“=$I6*0.3”，
二、Eecel图表操作
操作要求：
对文档“D:
\Excel操作\Test4_0.xls”进行如下操作，操作完成后如【样张4-2】和【样张4-3】所示，同名保存。
1.按【样张4-2】，根据Sheet2工作表中的数据，制作嵌入式图表（三维簇状柱形图），放置在Sheet1工作表中的B32:
H42处，整个图表字体大小：
10磅，按样张设置刻度，加上最高柱子的数据标记，按样张图表边框采用蓝色阴影、圆角最粗框线。
2.按【样张4-3】，添加“第５经营部”系列，调整系列次序和分类间距。
【样张4-2】
【样张4-3】
参考解答：
1.按下列步骤操作：
在Sheet2工作表中选中二个区域：
C4:
F5，C7:
F7。
如图4-2所示。
图4-2
选择工具栏上的“图表向导”按钮，出现图表向导4之1，选择“簇状柱形图”，选择“下一步”，出现图表向导4之2。
在图表向导4之2中，选择“系列”选项卡，在分类（X轴）标志处选择区域C3:
F3，如图4-3所示。
选择“下一步”。
图4-3
在图表向导4之3中，选择“标题”选项卡，输入标题“销售利润表”，输入分类X轴标题：
“年份”。
选中“网格线”中的主要网格线。
选择“下一步”，出现图表向导4之4。
在图表向导4之4中选择嵌入式图表所在的工作表：
Sheet1，选择“完成”。
如图4-4所示。
图4-4
在Sheet1工作表中将图表移动到B32处，并缩放到B32:
H42区域。
单击图表，选择字体大小：
10磅。
选择Y轴，选择“格式”菜单，选择“坐标轴”，选择“刻度”，主要刻度间隔：
20000。
选择“第4经营部”系列，选择最高的柱子，选择“格式”菜单，选择“数据点”，选择“数据标志”中的“显示值”，选择“确定”。
单击图表，选择“格式”菜单，选择“图表区域”，选择“图案”，选中：
阴影和圆角。
在边框中，选择最粗线，蓝色。
2.按下列步骤操作：
在Sheet2工作表中选择区域：
B8:
F8。
选择工具栏中的“复制”按钮。
单击Sheet1工作表中的图表。
选择工具栏中的“粘贴”按钮。
在Sheet1工作表中选择图表中的某个系列，选择“格式”菜单，选择“数据系列”，选择“系列次序”，选择“上移”或“下移”按钮。
如图4-5所示。
图4-5
选择“选项”选项卡，分类间距为0。
选择“确定”。
如图4-6所示。
图4-6
三、Ｅxcel列表操作
操作要求：
对文档“D:
\Excel操作\Test4_0.xls”进行如下操作。
操作完成后同名保存。
1.在Sheet1工作表中，对“ABC电气有限公司职工表”中的数据按职称的笔划升序排列，若职称相同按工资降序排列，若工资再相同按奖金降序排列。
排序好的数据复制到Sheet3工作表的B1处并加上最细边框线。
如【样张4-4】所示。
【样张4-4】
2.根据Sheet1工作表中“ABC电气有限公司职工表”的数据，筛选出职称为工程师和助工且年龄在30（含30岁）至40（含40岁）的职工记录，并加上框线细线。
筛选结果放置在Sheet3工作表中B19处并加上最细边框线。
如【样张4-5】所示。
【样张4-5】
3.根据Sheet1工作表中“ABC电气有限公司职工表”的数据，使用高级筛选，筛选出职称为工程师或年龄大于等于40岁的所有职工，加上细线框线。
筛选结果放置在Sheet3工作表中的B28处。
如【样张4-6】所示。
【样张4-6】
4.根据Sheet1工作表中“ABC电气有限公司职工表”的数据，按样张生成数据透视表，数据透视表放置在Sheet3工作表的单元格B45开始处。
如【样张4-7】所示。
【样张4-7】
5.在Sheet1工作表中对“ABC电气有限公司职工表”列表按性别统计每个职称的人数，只显示小计值（即第2级）。
如【样张4-8】所示。
【样张4-8】
参考解答：
1.按下列步骤操作：
在Sheet1工作表中，单击“ABC电气有限公司职工表”列表中的任意一个单元格，表示整个列表被选中，注意：
若“ABC电气有限公司职工表”不是一个列表或最后一行含有平均值、总计值等情况，必须选中整个要排序的区域，B15:
G30。
选择“数据”菜单，选择“排序”，如图4-10所示。
主要关键字选择“职称”，选择“选项”，如图4-8所示，选择“笔画排序”，选择“确定”；在图4-7中，次要关键字选择“工资”递减；第三关键字选择“奖金”递减。
选择“确定”。
图4-7图4-8
使用剪贴板将排序后的数据复制到Sheet3工作表的B1处。
2.按下列步骤操作：
在Sheet1工作表中，单击“ABC电气有限公司职工表”列表中的任意一个单元格，表示整个列表被选中，选择“数据”菜单中的“筛选”，选择“自动筛选”，字段处出现了筛选器。
选择“职称”字段的筛选器，选择“自定义”，在自定义对话框中选择等于“工程师”或等于“助工”。
如图4-9所示。
图4-9
选择“年龄”字段的筛选器，选择“自定义”，在自定义对话框中选择“大于或等于30”且“小于或等于40”。
如图4-10所示。
图4-10
在Sheet1工作表中选择筛选出来的数据复制到Sheet3工作表的B19处。
在Sheet1工作表中选择筛选出来的任意一个单元格，选择“数据”菜单，选择“筛选”选择“全部显示”。
为下次筛选作准备。
若取消自动筛选，选择“数据”菜单，选择“筛选”，选择“自动筛选”。
3.按下列步骤操作：
使用高级筛选前，必须在某个区域建立一个条件区域，建议新插入一个新工作表专门设设置条件区域。
选择“插入”菜单，选择“工作表”，插入了一个新工作表Sheet4。
在Sheet4工作表的任意区域，建立一个条件区域，如图4-11所示。
注意：
字段名必须写在同一行，即“职称”与“年龄”在同一行，条件写在同一行表示“与”运算；写在不同行表示“或”运算，图4-11表示“或”运算。
图4-11
在Sheet1工作表中，单击“ABC电气有限公司职工表”列表中的任意一个单元格，表示整个列表被选中，选择“数据”菜单中的“筛选”，选择“高级筛选”，如图4-12所示。
图4-12
在Sheet1工作表中选择筛选出来的数据复制到Sheet3工作表的B28处。
在Sheet1工作表中选择筛选出来的任意一个单元格，选择“数据”菜单，选择“筛选”选择“全部显示”表示取消原处高级筛选的结果。
4.按下列步骤操作：
在Sheet1工作表中，单击“ABC电气有限公司职工表”列表中的任意一个单元格（注：
若不是列表，必须全部选中）。
表示整个列表被选中。
选择“数据”菜单，选择“数据透视表”。
数据透视表向导操作。
如图4-13所示。
图4-13
双击“职称”按钮，在图4-14“数据透视表字段”对话框中隐藏“工人”和“技术员”，如图4-14所示。
图4-14
双击“工资”按钮，选择汇总方式中的均值。
选择“确定”，如图4-15所示。
同理，双击“奖金”按钮，选择汇总方式中的均值。
选择“确定”
图4-15
选择“下一步”，在数据透视表向导4之4中，选择“选项”按钮，在选项对话框中取消行总计和列总计。
如图4-16所示。
图4-16
选择现有工作表Sheet1中的B45，如图4-17所示。
图4-17
5.按下列步骤操作：
分类汇总必须“先排序，后汇总”，在Sheet1工作表中，单击“ABC电气有限公司职工表”列表中的任意一个单元格，表示整个列表被选中，选择“数据”菜单中的“排序”，排序主要关键字为“性别”升序（按拼音字母）。
在Sheet1工作表中，单击“ABC电气有限公司职工表”列表中的任意一个单元格，表示整个列表被选中，选择“数据”菜单，选择“分类汇总”，如图4-18所示。
选择“确定”。
图4-18
选择第2级。
升级会员 