第3章 Excel典型试题解析Word格式.docx
《第3章 Excel典型试题解析Word格式.docx》由会员分享,可在线阅读,更多相关《第3章 Excel典型试题解析Word格式.docx(57页珍藏版)》请在冰豆网上搜索。
3.步骤1:
单击B16单元格,切换到“开始”选项卡,单击“编辑”选项组的【Σ自动求和】按钮的下拉箭头,在弹出的下拉菜单中选择“平均值(A)”,如图36所示,B16单元格会有公式生成,如图37所示,检查求平均值区域正确后,按回车确认(也可以直接在B16单元格输入“=AVERAGE(B4:
B15)”),拖曳B16单元格的填充柄至K16,完成自动填充。
图36“Σ自动求和”的下拉箭头
图37生成求平均值公式
选择区域B16:
K16,在该区域中单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”(如图38所示),打开“设置单元格格式”对话框:
在“数字”选项卡中,选择“分类”为“数值”;
设置“小数位数”为“2”,如图39所示。
图38单元格右键菜单
图39设置小数位数
4.切换到Sheet1,选择区域A3:
A15,单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”,打开“设置单元格格式”对话框:
在“对齐”选项卡中,设置水平和垂直对齐方式,如图310所示;
选择L3:
L15区域重复以上操作。
图310“对齐”选项卡
5.步骤1:
切换到Sheet2,单击A17单元格,输入“平均情况”;
再单击B17单元格,切换到“公式”选项卡,单击“函数库”选项组的【插入函数】按钮(或者直接单击编辑栏的【插入函数】按钮
,可以直接插入函数),如图311所示。
图311插入函数
在“插入函数”对话框中选择的“IF”函数(一般可以在“常用函数”类别中找到),单击【确定】按钮(如图312所示),打开IF函数参数对话框。
图312选择“IF”函数
步骤3:
在“函数参数”对话框的第一行输入“B16>
5”,第二行输入“高”,第三行输入“低”(注意:
在输入后会自动添加西文的双引号)如图313所示。
单击【确定】按钮。
拖曳B17单元格的填充柄至K17,完成自动填充。
图313“IF”函数参数设置
6.鼠标单击屏幕下方【插入工作表】按钮,如图314所示,在Sheet2工作表后添加Sheet3。
切换到Sheet1,选中第3行到第15行,单击鼠标右键,选择“复制”命令;
再切换到Sheet3,单击A1单元格,然后单击鼠标右键,选择“粘贴”命令。
图314【插入工作表】按钮
7.步骤1:
选中Sheet3工作表中的B2:
K13单元格区域,切换到“开始”选项卡,单击“样式”选项组的【条件格式】按钮,选择“突出显示单元格规则”→“小于”命令(如图315所示)。
图315【条件格式】按钮
在“小于”对话框中的文本框中,输入“1”;
在“设置为”的下拉框选择“自定义格式”(如图316所示),打开“设置单元格格式”对话框,在该对话框的“填充”选项卡中,单击【其他颜色】按钮(如图317所示),打开“颜色”对话框。
图316“条件格式”“小于”对话框
图317“自定义格式”对话框
在“颜色”对话框中的“自定义”选项卡下,选择“颜色模式”为“RGB”;
红、绿、蓝颜色分别设置为100、255、100,如图318所示。
连续单击【确定】按钮。
图318背景色设置
步骤4:
继续选中B2:
K13单元格区域,切换到“开始”选项卡,单击“样式”选项组的【条件格式】按钮,选择“突出显示单元格规则”→“大于”命令,打开“大于”对话框,在文本框中输入“7”,“设置为”选择“自定义格式”(如图319所示),打开“设置单元格格式”对话框。
图319“条件格式”“大于”对话框
步骤5:
在“设置单元格格式”对话框的“字体”选项卡中,设置“颜色”为“红色”,“字形”为“加粗”,如图320所示,确定所做设置。
单击快速访问工具栏的【保存】按钮保存结果。
图320字体设置
3.1.2全年销售量统计表
1.求出Sheet1表中每项产品全年平均销售量,并填入“平均”行相应单元格中(小数取2位);
2.将Sheet1复制到Sheet2,求出Sheet2中每月总销售量并填入“总销售量”列相应单元格中;
3.将Sheet2表内容按总销售量降序排列(不包括平均数);
4.将Sheet1表套用表格格式为“表样式中等深浅3”(不包括文字“2012年全年销售量统计表”);
5.在Sheet2工作表总销量右侧增加一列,在Q3填入“超过85的产品数”,并统计各月销量超过85(含85)的产品品种数填入Q列相应单元格。
6.在Sheet2工作表后添加工作表Sheet3,将Sheet2的A3:
A15及P3:
P15的单元格内容复制到Sheet3。
7.对Sheet3工作表,对月份采用自定义序列“一月”、“二月”、……次序排序。
8.对Sheet3中数据,产生二维簇状柱形图。
其中“一月”、“二月”等为水平(分类)轴标签。
“总销售量”为图例项,并要求添加对数趋势线。
图表位置置于D1:
K14区域。
1.参考3.1.1中的题3。
2.步骤1:
参考3.1.1中的题2。
参考3.1.1中的题1。
选择Sheet2区域A3:
P15,切换到“数据”选项卡,单击“排序和筛选”选项组的【排序】按钮(如图321所示),打开“排序”对话框。
图321【排序】按钮
在“排序”对话框中:
设置“主要关键字”为“总销售量”,“排序依据”为“数值”,“次序”为“降序”,如图322所示,单击【确定】按钮。
图322排序对话框
4.切换到Sheet1,选中A2:
P16区域,切换到“开始”选项卡,单击“样式”选项组的【套用表格格式】下拉箭头,在弹出的下拉列表框中选择“表样式中等深浅3”,如图323所示;
在显示的“套用表格式”对话框中,检查“表数据的来源”区域是否正确,单击【确定】按钮,如图324所示。
图323“套用表格格式”命令
图324“套用表格式”对话框
切换到Sheet2,选中Q3:
Q16区域,切换到“开始”选项卡,单击“字体”选项组的【边框】下拉箭头,在弹出的下拉菜单中选择“所有框线”,如图325所示。
图325给单元格添加边框
在Q3单元格输入“超过85的产品数”;
单击Q4单元格,单击编辑栏的【插入函数】
,插入“统计”函数中的COUNTIF函数,并设置参数如图326所示。
拖曳Q4单元格的填充柄至Q15,完成自动填充。
图326COUNTIF函数参数设置
6.步骤1:
鼠标单击屏幕下方【插入工作表】按钮
,会在Sheet2工作表后添加Sheet3。
切换到Sheet2,选中A3:
A15,按下Ctrl同时选中P3:
P15,在选中区域单击鼠标右键,选择“复制”命令;
切换到Sheet3,单击A1单元格,然后单击鼠标右键,选择“粘贴”命令。
选择Sheet3区域A1:
B13,切换到“数据”选项卡,单击“排序和筛选”选项组的【排序】按钮,在“排序”对话框:
设置“主要关键字”为“月份”,“排序依据”为“数值”,“次序”为“自定义序列”,如图327所示。
图327“排序”对话框
在“自定义序列”中选择“一月,二月,三月……”序列,如图328所示,单击【确定】按钮返回到“排序”对话框,在单击【确定】完成设置。
图328“自定义序列”对话框
8.步骤1:
在Sheet3中,单击A1:
B13区域的任意一个单元格,切换到“插入”选项卡,单击“图表”选项组的【柱形图】下拉箭头,在弹出的下拉列表中选择“二维柱形图”中的“簇状柱形图”,如图329所示,生成图表。
(提示:
此时同时打开了“‘图表工具’设计”选项卡、“‘图表工具’布局”选项卡、“‘图表工具’格式”选项卡。
)
图329“图表”工具栏
单击“‘图表工具’布局”选项卡的【趋势线】下拉箭头(如图330所示),在弹出的下拉列表中选择“其他趋势线选项”(如图331所示),打开“设置趋势线格式”对话框。
图330“图表工具”栏
图331“趋势线”选项
在“设置趋势线格式”对话框中:
设置“趋势线选项”为“对数”,如图332所示。
图332“设置趋势线格式”对话框
单击【关闭】按钮,生成如图333所示图表;
选中图表后,拖曳鼠标将图表位置调整到区域D1:
K14。
图333图表“总销售量”完成图
3.1.3出勤表
1.求出Sheet1表中每班本周平均每天缺勤人数(小数取1位)并填入相应单元格中;
(本周平均缺勤人数=本周缺勤总数/5)
2.求出Sheet1表中每天实际出勤人数并填入相应单元格中;
3.在Sheet1表后插入Sheet2,将Sheet1表复制到Sheet2,并重命名Sheet2为“考勤表”;
4.在Sheet1表的第1行前插入标题行“年级周考勤表”,设置为“隶书,字号22,加粗、合并A1至M1单元格及水平居中”。
5.Sheet1的最后增加一行,班级列中文字为“缺勤班数”,利用公式在该行每天的“本日缺勤人数”中统计有缺勤的班级数(例如C104的统计值为71……)。
6.在“考勤表”工作表后添加工作表Sheet2,将Sheet1的第2行(“星期一”等所在行)和第104行(“缺勤班数”所在行)复制到Sheet2。
7.对Sheet2:
删除B、D、F、H、J、L、M列,为周一到周五的缺勤班级数制作三维簇状柱形图,要求:
(1)以星期一、星期二等为水平(分类)轴标签;
(2)以“缺勤班数”为图例项;
(3)图表标题使用“缺勤班数统计”(不包括引号);
(4)删除网格线;
(5)设置坐标轴选项使其最小值为0.0;
(6)将图表放置于A4:
F15的区域。
在Sheet1中,单击M3单元格,输入“=(C3+E3+G3+I3+K3)/5”,按回车确定。
参考3.1.1中的题3的“步骤2”,设置M3的小数位数。
双击M3单元格的填充柄,填充该列的数据。
2.在Sheet1中,考虑到:
“实际出勤人数”=“应出勤人数”-“本日缺勤人数”,单击D3单元格,输入“=B3-C3”,确定,双击D3单元格的填充柄填充该列的数据;
采用相同方法,单击F3单元格,输入“=B3-E3”,确定,自动填充F4:
F102;
单击H3单元格,输入“=B3-G3”,确定,自动填充H4:
H102;
单击J3单元格,输入“=B3-I3”,确定,自动填充J4:
J102;
单击L3单元格,输入“=B3-K3”,确定,自动填充L4:
L102。
3.鼠标单击屏幕下方【插入工作表】按钮
,在Sheet1工作表后添加Sheet2,复制Sheet1内所有内容,到Sheet2,鼠标右键单击Sheet2标签,在弹出的快捷菜单中选择“重命名”,输入“考勤表”,按回车确定。
4.步骤1:
在Sheet1中,在行号1处,单击鼠标右键,选择“插入”命令,如图334所示,完成插入一行。
图334插入行
单击A1单元格,输入“年级周考勤表”。
选中区域A1:
M1,单击鼠标右键,选择“设置单元格格式”,打开“设置单元格格式”对话框,在“字体”选项卡中设置字体为“隶书”,字号为“22”,字形为“加粗”,如图335所示。
图335“字体”选项卡
选择“对齐”选项卡,合并单元格,设置水平对齐为“居中”,如图336所示,单击【确定】按钮。
图336“对齐”选项卡
在Sheet1的最后一行后面,选中A103:
M103区域,切换到“开始”选项卡,单击“字体”选项组的【边框】按钮的下拉选项,选择“所有框线”。
在A103单元格,输入“缺勤班数”,单击C104单元格,单击编辑栏的【插入函数】按钮
,插入COUNTIF函数,设置“函数参数”对话框参数如图337所示。
复制C103单元格,粘贴到E103单元格、G103单元格、I103单元格、K103单元格。
图337COUNTIF函数“统计有缺勤的班级数”参数设定
,在“考勤表”工作表后添加工作表,命名为Sheet2。
选中工作表Sheet1的第2行,按“CTRL”键的同时选中第104行,单击鼠标右键,选择“复制”命令;
切换到Sheet2,单击A1单元格,然后单击鼠标右键,选择“粘贴”命令。
在工作表Sheet2中,同时选中B、D、F、H、J、L、M列,单击鼠标右键,选择“删除”命令,结果如图338所示。
图338Sheet2工作表的数据
鼠标单击任一有数据的单元格,切换到“插入”选项卡,单击“图表”选项组的【柱形图】按钮,在弹出的下拉列表中选择“三维柱形图”中的“簇状柱形图”。
在生成的图表中,检查水平(分类)轴标签为“星期一、星期二等”,“缺勤班数”为图例项;
单击图表标题,将其改为“缺勤班数统计”;
在网格线上单击鼠标右键,选择“删除”,如图339所示。
图339删除网格线
鼠标双击纵坐标数值,弹出“设置坐标轴格式”对话框,设置“最小值”为“固定”,文本框中输入“0.0”,如图340所示。
图340“设置坐标轴格式”对话框
单击【关闭】按钮。
选中图片后拖曳鼠标,将图表位置调整到区域A4:
F15,完成效果如图341所示。
图341完成的“缺勤班数统计”图表
3.1.4电线库存表
1.在Sheet1表后插入工作表Sheet2和Sheet3,并将Sheet1复制到Sheet2和Sheet3中;
2.将Sheet2第2、4、6、8、10行以及A列和C列删除;
3.在Sheet3第E列的第一个单元格输入“总价”,并求出对应行相应总价,保留两位小数;
(总价=库存量*单价)
4.对Sheet3表设置套用表格格式为“表样式浅色15”格式,各单元格内容水平对齐方式为“居中”,各列数据以“自动调整列宽”方式显示,各行数据以“自动调整行高”方式显示。
5.在Sheet3中利用公式统计单价>
25的货物的总价和,并放入G2单元格。
6.在Sheet3工作表后添加工作表Sheet4,将Sheet1的A到D列复制到Sheet4。
7.在Sheet4中,以库存量为第一关键字(降序)、单价为第二关键字(升序)对数据进行排序。
1.参考3.1.3中的题3。
2.切换到Sheet2,同时选中第2、4、6、8、10行,单击鼠标右键,选择“删除”命令;
再同时选中第A列和C列,单击鼠标右键,选择“删除”命令。
切换到Sheet3,在E1单元格输入“总价”,单击E2单元格,输入公式“=C2*D2”,确定。
参考3.1.1中的题3的“步骤2”,双击E2单元格的填充柄,填充该列的数据。
参考3.1.2中的题4。
选中所有单元格,单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”,打开“设置单元格格式”对话框。
在其“对齐”选项卡中设置“水平对齐”为“居中”;
切换到“开始”选项卡,单击“单元格”选项组的【格式】按钮,在弹出的下拉菜单中,先选择“自动调整列宽”,再选择“自动调整行高”,如图342所示。
图342“格式”命令选项
在Sheet3中,单击G2单元格,单击编辑栏的【插入函数】按钮
,插入SUMIF函数,如图343所示,单击【确定按钮,打开SUMIF函数参数对话框。
图343插入SUMIF函数
设置“函数参数”对话框的参数如图344所示,单击【确定】按钮。
图344SUMIF函数参数设置
6.鼠标单击屏幕下方【插入工作表】按钮
,在Sheet3工作表后添加Sheet4,切换到Sheet1,选中A到D列,复制;
切换到Sheet4,在A1单元格点右键,选择“粘贴”。
7.在Sheet4中,单击有数据的任意一个单元格,切换到“数据”选项卡,单击“排序和筛选”选项组的【排序】按钮,打开“排序”对话框:
设置“主要关键字”为“库存量”,“排序依据”为“数值”,“次序”为“降序”;
继续单击“添加条件”按钮,设置“次要关键字”为“单价”,“排序依据”为“数值”,“次序”为“升序”,如图345所示。
单击【确定】完成排序。
图345“排序”对话框参数设置
3.1.5车间工资表
1.将Sheet1表中内容复制到Sheet2表,并将Sheet2更名为“工资表”;
2.求出“工资表”中“应发工资”和“实发工资”数据并填入相应单元格中
(应发工资=基本工资+岗位津贴+工龄津贴+奖励工资)
(实发工资=应发工资-应扣工资);
3.求出“工资表”中除“编号”和“姓名”外其他栏目的平均数(小数取2位),并填入相应单元格中;
4.将“工资表”中每个职工内容按应发工资升序排列(不包括平均数所在行),并将应发工资最低的职工的所有内容的字体颜色用蓝色表示;
5.在“工资表”中利用公式统计4000≤实发工资≤4100的人数,并放入K2单元格,并设置K2单元格格式为“常规”。
6.在“工资表”后添加工作表Sheet2,将“工资表”中A列到I列的内容复制到Sheet2。
7.对Sheet2工作表启用筛选,筛选出姓“李”或姓“陈”的、且基本工资大于等于3100的数据行。
其中筛选出姓“李”或姓“陈”,要求采用自定义筛选方式。
1.复制Sheet1内所有内容到Sheet2;
右键单击Sheet2标签,选择“重命名”,输入“工资表”,按回车确定。
2.切换到“工资表”,单击G3单元格,输入公式“=C3+D3+E3+F3”,按回车确定,拖曳G3单元格的填充柄至G102,完成自动填充;
单击I3单元格,输入公式“=G3-H3”,按回车确定,拖曳I3单元格的填充柄至I102,完成自动填充。
3.参考3.1.1中的题3。
在“工资表”中,选中A2:
I102区域,切换到“数据”选项卡,单击“排序和筛选”选项组的【排序】按钮,打开“排序”对话框:
设置“主要关键字”为“应发工资”,“排序依据”为“数值”,“次序”为“升序”,确定。
选择A3:
I3区域,单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”,打开“设置单元格格式”对话框:
在“字体”选项卡中,设置“颜色”为“蓝色”,如图346所示,单击【确定】按钮。
图346设置字体颜色
在“工资表”中,单击K2单元格,单击编辑栏的【插入函数】按钮
,插入函数COUNTIFS函数,如图347所示,单击【确定】按钮打开函数参数对话框。
图347插入COUNTIFS函数
在“函数参数”对话框中设置如图348所示的参数,单击【确定】按钮;
在K2单元格上单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”,打开“设置单元格格式”对话框,在“数字”选项卡中设置分类为“常规”,单击【确定】按钮完成设置。
图348COUNTIFS函数“统计4000≤实发工资≤4100的人数”参数设置
6.参考3.1.4中的题6。
在Sheet2中,单击任意一个有数据的单元格,切换到“数据”选项卡,单击“排序和筛选”选项组的【筛选】按钮,如图349所示。
(此时各字段均出现了筛选下拉按钮。
图349筛选命令
单击“姓名”列的筛选下拉按钮,在弹出的菜单中选择“文本筛选”下的“自定义筛选”(如图350所示),打开“自定义自动筛选”对话框。
图350对姓名选择“自定义筛选”
在“自定义自动筛选方式”对话框,设置参数如图351所示。
图351“自定义筛选”参数设置
单击“基本工资”列的筛选下拉按钮,在弹出的菜单中选择“数字筛选”下的“大于或等于”(如图352所示),打开“自定义自动筛选”对话框。
图352“基本工资”列筛选
在“自定义自动筛选方式”对话框设置参数如图353所示。
图353“基本工资”筛选参数设置
3.1.6仪器库存表
1.将“库存表”中除仪器名称仅为“万用表”的行外,全部复制到Sheet2中;
2.将Sheet2中名称仅为“电流表”和“压力表”的“库存”分别改为20和30,并重新计算“库存总价”(库存总价=库存*单价);
3.将“库存表”中“仪器名称”、“单价”和“库存”三列复制到Sheet3中,并将Sheet3设置套用表格格式为“表样式浅色10”格式;
4.将Sheet2表“库存总价”列宽调整为10,设置“进货日期”的列宽为“自动调整列宽”,并按“库存总价”降序排列;
5.在Sheet2中利用公式统计库存量小于10的仪器种类数,并把数据放入H2单元格。
6.在Sheet3工作表后添加工作表Sheet4,将Sheet2的A到F列复制到Sheet4。
7.对Sheet4进行高级筛选,筛出单价大于等于1000的或库存大于等于60的数据行。
在原有区域显示筛选结果,高级筛选的条件可以写在H和I列的任意区域)
1.将“库存表”中所有数据复制到Sheet2中;
在Sheet2中鼠标点在行号“4”上(“万用表”所在行),右键单击,在弹出的快捷菜单中选择“删除”命令。
2.在Sheet2中,单击单元格E2,输入20,单击单元格F2,输入公式“=D2*E2