Excel套题解析未来教育计算机二级Word下载.docx
《Excel套题解析未来教育计算机二级Word下载.docx》由会员分享,可在线阅读,更多相关《Excel套题解析未来教育计算机二级Word下载.docx(55页珍藏版)》请在冰豆网上搜索。
步骤4:
按步骤3的方法,设置"
的单元格区域D2:
D21为数值型,保留小数点后0位。
(2)
的C2单元格中输入"
=一季度销售情况表!
C2+'
二季度销售情况表'
!
C2"
键,选中C2单元格,将鼠标指针移动到该单元格右下角的填充柄上,当鼠标变为黑十字时,按住鼠标左键,拖动单元格填充柄到要填充的单元格中。
D2+'
D2"
中,选中单元格区域C2:
的E2单元格中输入"
=RANK(D2,$D$2:
$D$21,0)"
键,选中E2单元格,将鼠标指针移动到该单元格右下角的填充柄上,当鼠标变为黑十字时,按住鼠标左键,拖动单元格填充柄到要填充的单元格中。
(3)
中,单击【插入】选项卡下【表格】组中的【数据透视表】按钮,从弹出的下拉列表中选择"
数据透视表"
,弹出"
创建数据透视表"
对话框,设置"
表/区域"
为"
产品销售汇总图表!
$A$1:
$E$21"
,选择放置数据透视表的位置为现有工作表,"
位置"
$G$1"
数据透视字段列表"
任务窗格中拖动"
产品型号"
到行标签,拖动"
产品类别代码"
到列标签,拖动"
到数值。
单击"
自定义快速访问工具栏中"
的"
保存"
按钮,保存文件Excel.xlsx。
D21,单击【开始】选项卡下【数字】组中的对话框启动按钮,弹出"
按照步骤3同样的方法,设置"
D21,单击【开始】选项卡下【数字】组中的对话框启动器按钮,弹出"
中,单击【插入】选项卡下【表格】组中的【数据透视表】下拉按钮,从弹出的下拉列表中选择"
单击Excel左上角"
二、小李今年毕业后,在一家计算机图书销售公司担任市场部助理,主要的工作
职责是为部门经理提供销售信息的分析和汇总。
请你根据销售数据报表("
文件),按照如下要求完成统计和分析工作:
1.请对"
订单明细表"
工作表进行格式调整,通过套用表格格式方法将所有的销售记录调整为一致的外观格式,并将"
单价"
小计"
列所包含的单元格调整为"
会计专用"
(人民币)数字格式。
2.根据图书编号,请在"
工作表的"
图书名称"
列中,使用VLOOKUP函数完成图书名称的自动填充。
"
图书编号"
的对应关系在"
编号对照"
工作表中。
3.根据图书编号,请在"
列中,使用VLOOKUP函数完成图书单价的自动填充。
4.在"
列中,计算每笔订单的销售额。
5.根据"
工作表中的销售数据,统计所有订单的总销售金额,并将其填写在"
统计报告"
工作表的B3单元格中。
6.根据"
工作表中的销售数据,统计《MSOffice高级应用》图书在2012年的总销售额,并将其填写在"
工作表的B4单元格中。
7.根据"
工作表中的销售数据,统计隆华书店在2011年第3季度的总销售额,并将其填写在"
工作表的B5单元格中。
8.根据"
工作表中的销售数据,统计隆华书店在2011年的每月平均销售额(保留2位小数),并将其填写在"
工作表的B6单元格中。
9.保存"
文件。
1.解题步骤:
启动考生文件下的"
,打开"
工作表。
选中工作表中的A2:
H636,单击【开始】选项卡下【样式】组中的"
套用表格格式"
按钮,在弹出的下拉列表中选择一种表样式,此处我们选择"
表样式浅色10"
。
弹出"
套用表格式"
对话框。
保留默认设置后单击"
按钮即可。
按住Ctrl键,同时选中"
列,右击鼠标,在弹出的下拉列表中选择"
命令,继而弹出"
组中选择"
命令,然后单击"
货币符号(国家/地区)"
下拉列表选择"
CNY"
2.解题步骤:
工作表的E3单元格中输入"
=VLOOKUP(D3,编号对照!
$A$3:
$C$19,2,FALSE)"
,按"
键完成图书名称的自动填充。
3.解题步骤:
工作表的F3单元格中输入"
=VLOOKUP(D3,编号对照!
$C$19,3,FALSE)"
键完成单价的自动填充。
4.解题步骤:
工作表的H3单元格中输入"
=[@单价]*[@销量(本)]"
键完成小计的自动填充。
5.解题步骤:
工作表中的B3单元格输入"
=SUM(订单明细表!
H3:
H636)"
键后完成销售额的自动填充。
单击B4单元格右侧的"
自动更正选项"
按钮,选择"
撤销计算列"
6.解题步骤:
工作表中,单击"
日期"
单元格的下拉按钮,选择"
降序"
后单击"
切换至"
工作表,在B4单元格中输入"
=SUMPRODUCT(1*(订单明细表!
E3:
E262="
《MSOffice高级应用》"
),订单明细表!
H262)"
键确认。
7.解题步骤:
步骤:
工作表的B5单元格中输入"
C350:
C461="
隆华书店"
H350:
H461)"
8.解题步骤:
工作表的B6单元格中输入"
C263:
C636="
H263:
H636)/12"
键确认,然后设置该单元格格式保留2位小数。
9.解题步骤:
按钮即可完成"
文件的保存。
三、小蒋是一位中学教师,在教务处负责初一年级学生的成绩管理。
由于学校地处偏远地区,缺乏必要的教学设施,只有一台配置不太高的PC可以使用。
他在这台电脑中安装了MicrosoftOffice,决定通过Excel来管理学生成绩,以弥补学校缺少数据库管理系统的不足。
现在,第一学期期末考试刚刚结束,小蒋将初一年级三个班的成绩均录入了文件名为"
学生成绩单.xlsx"
的Excel工作簿文档中。
请你根据下列要求帮助小蒋老师对该成绩单进行整理和分析:
1.对工作表"
第一学期期末成绩"
中的数据列表进行格式化操作:
将第一列"
学号"
列设为文本,将所有成绩列设为保留两位小数的数值;
适当加大行高列宽,改变字体、字号,设置对齐方式,增加适当的边框和底纹以使工作表更加美观。
2.利用"
条件格式"
功能进行下列设置:
将语文、数学、英语三科中不低于110分的成绩所在的单元格以一种颜色填充,其他四科中高于95分的成绩以另一种字体颜色标出,所用颜色深浅以不遮挡数据为宜。
3.利用sum和average函数计算每一个学生的总分及平均成绩。
4.学号第3、4位代表学生所在的班级,例如:
120105"
代表12级1班5号。
请通过函数提取每个学生所在的班级并按下列对应关系填写在"
班级"
列中:
的3、4位对应班级
011班
022班
033班
5.复制工作表"
,将副本放置到原表之后;
改变该副本表标签的颜色,并重新命名,新表名需包含"
分类汇总"
字样。
6.通过分类汇总功能求出每个班各科的平均成绩,并将每组结果分页显示。
7.以分类汇总结果为基础,创建一个簇状柱形图,对每个班各科平均成绩进行比较,并将该图表放置在一个名为"
柱状分析图"
新工作表中。
打开考生文件夹下的"
选中"
所在的列,单击鼠标右键,在弹出的下拉列表中选择"
命令,弹出"
选项卡,在"
文本"
选中所有成绩列,单击鼠标右键,在弹出的下拉列表中选择"
对话框,切换至"
数字选项卡"
,在小数位数微调框中设置小数位数为"
2"
选中A1:
L19单元格,单击【开始】选项卡下【单元格】组中的"
格式"
下拉按钮,在弹出的的下拉列表中选择"
行高"
对话框,设置行高为"
15"
,设置完毕后单击“确定”按钮。
步骤5:
单击【开始】选项卡下【单元格】组中的"
列宽"
对话框,设置列宽为"
10"
步骤6:
单击鼠标右键,在弹出的快捷菜单中选择"
,在弹出的"
对话框中切换至"
字体"
下拉列表框中设置字体为"
幼圆"
,在“字号”下拉列表中设置字号为"
,单击“确定”按钮。
步骤7:
选中第一行单元格,在【开始】选项卡下的【字体】组中单击"
加粗"
按钮,从而设置字形为"
步骤8:
重新选中数据区域,按照同样的方式打开"
对齐"
选项卡下,在"
文本对齐方式"
组中设置"
水平对齐"
与"
垂直对齐"
都为"
居中"
步骤9:
边框"
预置"
选项中选择"
外边框"
内部"
选项。
步骤10:
再切换至"
填充"
背景色"
浅绿"
步骤11:
选中D2:
F19单元格区域,单击【开始】选项卡下【样式】组中的"
下拉按钮,选择"
突出显示单元格规则"
中的"
其他规则"
新建格式规则"
编辑规则说明"
选项下设置单元格值大于或等于110,然后单击"
选项卡下选择"
红色"
按钮,按同样的方法设置E2:
E19和F2:
F19单元格区域。
选中G2:
J19,按照上述同样方法,把单元格值大于95的字体颜色设置为红色,按同样的方法设置H2:
H19和I2:
I19单元格区域。
在K2单元格中输入"
=SUM(D2:
J2)"
键后该单元格值为"
629.50"
,拖动K2右下角的填充柄直至最下一行数据处,完成总分的填充。
在L2单元格中输入"
=AVERAGE(D2:
89.93"
,拖动L2右下角的填充柄直至最下一行数据处,完成平均分的填充。
在C2单元格中输入"
=LOOKUP(MID(A2,3,2),{"
01"
"
02"
03"
},{"
1班"
2班"
3班"
})"
,拖动C2右下角的填充柄直至最下一行数据处,完成班级的填充。
复制工作表"
,粘贴到Sheet2工作表中。
然后在副本的工作表名上单击鼠标右键,在弹出的快捷菜单"
工作表标签颜色"
的级联菜单中选择"
命令。
双击副本表名呈可编辑状态,重新命名为"
第一学期期末成绩分类汇总"
按照题意,首先对班级按升序进行排序,选中C2:
C19,单击【数据】选项卡下【排序和筛选】组中的"
升序"
排序提醒"
对话框,单单击"
扩展选定区域"
单选按钮。
排序"
按钮后即可完成设置。
选中D20单元格,单击【数据】选项卡下【分级显示】组中的"
分类字段"
组中的下拉按钮,选择"
选项,单击"
汇总方式"
平均值"
选项,在"
选定汇总项"
组中勾选"
语文"
数学"
英语"
生物"
地理"
历史"
政治"
复选框。
最后勾选"
每组数据分页"
选中每个班各科平均成绩所在的单元格,单击【插入】选项卡下【图表】组中"
柱形图"
按钮,在弹出的下拉列表中选择"
簇状柱形图"
右击图表区,在弹出的的快捷菜单中选择"
选择数据"
选择数据源"
对话框,选中"
图例项"
选项下的"
系列1"
编辑"
编辑数据系列"
系列名称"
文本框中输入"
然后单击"
按钮完成设置,按照同样方法编辑"
系列2"
系列3"
对话框中,选中"
水平(分类)轴标签"
下的"
1"
轴标签"
轴标签区域"
语文,数学,英语,生物,地理,历史,政治"
剪切该簇状柱形图到Sheet3,把Sheet3重命名为"
即可完成设置。
四、中国的人口发展形势非常严峻,为此国家统计局每10年进行一次全国人口普查,以掌握全国人口的增长速度及规模。
按照下列要求完成对第五次、第六次人口普查数据的统计分析:
(1)新建一个空白Excel文档,将工作表sheet1更名为"
第五次普查数据"
,将sheet2更名为"
第六次普查数据"
,将该文档以"
全国人口普查数据分析.xlsx"
为文件名进行保存。
(2)浏览网页"
第五次全国人口普查公报.htm"
,将其中的"
2000年第五次全国人口普查主要数据"
表格导入到工作表"
中;
浏览网页"
第六次全国人口普查公报.htm"
2010年第六次全国人口普查主要数据"
中(要求均从A1单元格开始导入,不得对两个工作表中的数据进行排序)。
(3)对两个工作表中的数据区域套用合适的表格样式,要求至少四周有边框、且偶数行有底纹,并将所有人口数列的数字格式设为带千分位分隔符的整数。
(4)将两个工作表内容合并,合并后的工作表放置在新工作表"
比较数据"
中(自A1单元格开始),且保持最左列仍为地区名称、A1单元格中的列标题为"
地区"
,对合并后的工作表适当的调整行高列宽、字体字号、
边框底纹等,使其便于阅读。
以"
为关键字对工作表"
进行升序排列。
(5)在合并后的工作表"
中的数据区域最右边依次增加"
人口增长数"
比重变化"
两列,计算这两列的值,并设置合适的格式。
其中:
人口增长数=2010年人口数-2000年人口数;
比重变化=2010年比重-2000年比重。
(6)打开工作簿"
统计指标.xlsx"
,将工作表"
统计数据"
插入到正在编辑的文档"
中工作表"
的右侧。
(7)在工作簿"
的工作表"
中的相应单元格内填入统计结果。
(8)基于工作表"
创建一个数据透视表,将其单独存放在一个名为"
透视分析"
的工作表中。
透视表中要求筛选出2010年人口数超过5000万的地区及其人口数、2010年所占比重、人口增长数,并按人
口数从多到少排序。
最后适当调整透视表中的数字格式。
(提示:
行标签为"
,数值项依次为2010年人口数、2010年比重、人口增长数)。
新建一个空白Excel文档,并将该文档命名为"
打开"
,双击工作表Sheet1的表名,在编辑状态下输入"
,双击工作表Sheet2的表名,在编辑状态下输入"
在考生文件夹下双击打开网页"
,在工作表"
中选中A1,单击【数据】选项卡下【获取外部数据】组中的"
自网站"
新建Web查询"
地址"
文本框中输入网页"
的地址,单击右侧的"
转到"
单击要选择的表旁边的带方框的黑色箭头,使黑色箭头变成对号,然后单击"
导入"
之后会弹出"
导入数据"
对话框,选择"
数据的放置位置"
现有工作表"
,在文本框中输入"
=$A$1"
按照上述方法浏览网页"
中。
在工作表"
中选中数据区域,在【开始】选项卡的【样式】组中单击"
下拉按钮,弹出下拉列表,按照题目要求至少四周有边框且偶数行有底纹,此处我们可选择"
表样式浅色16"
选中B列,单击【开始】选项卡下【数字】组中对话框启动器按钮,弹出"
选项卡的"
下选择"
,勾选"
使用千位分隔符"
复选框,然后单击"
按照上述方法对工作表"
套用合适的表格样式,要求至少四周有边框、且偶数行有底纹,此处我们可套用"
表样式浅色17"
,并将所有人口数列的数字格式设为带千分位分隔符的整数。
(4)
双击工作表sheet3的表名,在编辑状态下输入"
在该工作表的A1中输入"
,在【数据】选项卡的【数据工具】组中单击"
合并计算"
函数"
求和"
引用位置"
文本框中键入第一个区域"
第五次普查数据!
$C$34"
添加"
按钮,键入第二个区域"
第六次普查数据!
按钮,在"
标签位置"
下勾选"
首行"
复选框和"
最左列"
对合并后的工作表适当的调整行高列宽、字体字号、边框底纹等。
选中整个工作表,我们在【开始】选项卡下【单元格】组中单击"
下拉按钮,从弹出的下拉列表中选择"
自动调整行高"
自动调整列宽"
在【开始】选项卡下【字体】组中单击对话框启动器按钮,弹出"
黑体"
,字号为"
11"
选中数据区域,单击鼠标右键,从弹出的快捷菜单中选择"
对话框,单击"
选项卡,单击"
选中数据区域,在【开始】选项卡的【样式】组中单击"
下拉按钮,弹出下拉列表,此处我们可选择"
表样式浅色18"
选中数据区域的任一单元格,单击【数据】选项卡下【排序和筛选】组中的【排序】按钮,弹出"
主要关键字"
,"
次序"
升序