王明是某在线销售数码产品公司的管理人员,于2017年初随机抽取了100名网站注册会员,准备使用Excel分析他们上一年度的消费情况。根据下列要求,帮助他运用已有的数据完成这项工作。1.在考生文件夹下,...
1.在考生文件夹下,将“Excel_素材.xlsx”文件另存为名为“Excel.xlsm”(“xlsm”为扩展名)的Excel启用宏的工作簿,后续操作均基于此文件,否则不得分。
2.在“客户资料”工作表中,完成下列任务:
①将数据区域A1:F101转换为表,将表的名称修改为“客户资料”,并取消隔行底纹的效果。
②将B列中所有的“M”替换为“男”,所有的“F”替换为“女”。
③修改C列中日期的格式,要求格式如“80年5月9日”(年份只显示后两位)。
④在D列中,计算每位顾客到2017年1月1日止的年龄,规则为每到下一个生日,计1岁。
⑤在E列中,计算每位顾客到2017年1月1日止所处的年龄段,年龄段的划分标准位于“按年龄和性别”工作表的A列中。(注意:不要改变顾客编号的默认排序,可使用中间表格进行计算)
⑥在F列中计算每位顾客2016年全年消费金额,各季度的消费情况位于“2016年消费”工作表中,将F列的计算结果修改为货币格式,保留0位小数。(注意:为便于计算,可修改“2016年消费”工作表的结构)
⑦为B列中的数据区域添加数据有效性,以便仅可在其中输入数据“男”或“女”,如果输入其他内容,则弹出样式为“停止”的出错警告,错误信息为“仅可输入中文!”。
⑧录制名为“最小年龄”宏,以便可以对选定单元格区域中数值最小的10项应用“浅红填充色深红色文本”的“项目选取规则”条件格式,将宏指定到快捷键“Ctrl+Shift+U”,并对D列中的数值应用此宏。
⑨为表格中的数据添加条件格式,将年消费金额最低的15位顾客所在的整行记录的文本颜色设置为绿色(注意:如果该顾客属于年龄最小的10位顾客,则年龄数值应保持为深红色文本)。
3.在“按年龄和性别”工作表中,完成下列任务:
①根据“客户资料”工作表中已完成的数据,在B列、C列和D列中分别计算各年龄段男顾客人数、女顾客人数、顾客总人数,并在表格底部进行求和汇总。
②创建名为“表格标题”的自定义单元格样式,为其设置适合的单元格填充颜色和字体颜色,并应用于表格的标题行(首行);为表格的数据区域(2-12行)应用一种恰当的主题单元格样式;为表格的汇总行(末行)应用“汇总”样式。
4.根据“按年龄和性别”工作表中的数据创建图表,显示各年龄段不同性别的顾客人数,并将图表移动到名为“各年龄段人数”的新的图表工作表中,按照考生文件夹下的“各年龄段顾客.png”完成效果图片,修改图表的下列元素:
5.隐藏“2016年消费”工作表,将“各年龄段人数”工作表置于所有工作表最右侧。
6.设置“客户资料”工作表,以便在打印的时候,该工作表的第一行会自动出现在每一页的顶部。
7.为所有可见工作表添加自定义页眉和页脚,在页眉正中显示工作表的名称,在页脚正中显示页码和页数,格式为“页码 of 总页数”,例如“1 of 5”,当工作表名称或者数据发生变化时,页眉和页脚内容应可以自动更新。
参考解析: 1.【解题步骤】
步骤1:打开考生文件夹下的“Excel_素材.xlsx”文件。
步骤2:单击【文件】菜单下“另存为”命令,弹出“另存为”对话框,保存位置选择考生文件夹,将“文件名”修改为“Excel”;将“保存类型”选择为“Excel启用宏的工作簿”,单击“保存”按钮。
2.【解题步骤】
步骤1:切换到“客户资料”工作表,选中数据区域A1:F101,单击【插入】选项卡下【表格】功能组中的“表格”按钮,在弹出的对话框中直接单击“确定”按钮,如下图所示;在【表格工具/设计】选项卡下【属性】功能组中,输入表名称“客户资料”,输入完成后按键盘上的Enter键完成输入;在【表格样式选项】功能组中取消勾选“镶边行”复选框。
步骤2:选中B列中的B2:B101数据区域,单击【开始】选项卡下【编辑】功能组中的“查找和选择”按钮,在下拉列表中选择“替换”,弹出“查找和替换”对话框,在“查找内容”中输入“M”,在“替换为”中输入“男”,单击“全部替换”按钮,此时将选中区域中所有“M”替换为“男”。继续在对话框的“查找内容”中输入“F”,在“替换为”中输入“女”,单击“全部替换”按钮,最后关闭“查找和替换”对话框。
步骤3:选中C列中的C2:C101数据区域,单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”命令,弹出“设置单元格格式”对话框,在对话框的“分类”列表框中选择“自定义”,在右侧的“类型”文本框中将类型修改为“yy年m月d日”,如下图所示,最后单击“确定”按钮。
步骤4:选中D2单元格,输入公式“=DATEDIF([@生日],"2017-1-1","y")”,输入完成后按键盘上的Enter键结束输入,公式自动填充到D101单元格。
步骤5:复制“按年龄和性别”工作表中的A列内容到“客户资料”工作表的I1:I12区域(此处区域可以任意指定),在H1单元格中输入“年龄”,按照如下图所示输入相应的数据;然后在E2单元格中输入公式“=VLOOKUP(D2,$H$1:$I$12,2,1)”,输入完成后按键盘上的Enter键结束输入,公式自动填充到E101单元格。
步骤6:选中“2016年消费”工作表中A列中的所有合并单元格,单击【开始】选项卡下【对齐方式】功能组中的“合并后居中”按钮,在下拉列表中选择“取消单元格合并”,将所有合并的单元格取消合并;单击【开始】选项卡下【编辑】功能组中的“查找和选择”按钮,在下拉列表中选择“定位条件”命令,在弹出的对话框中选择“空值”,单击“确定”按钮,将所有空白单元格全部选中;在“编辑栏”中输入公式“=A2”,输入完成后按键盘上的Ctrl+Enter组合键进行填充(此操作可以让空白单元格自动填充上面的数据),结果如下图所示。然后在“客户资料”工作表的F2单元格中输入公式“=SUMIF('2016年消费'!$A$2:$A$364,A2,'2016年消费'!$C$2:$C$364)”,输入完成后按键盘上的Enter键结束输入,公式自动填充到F101单元格。最后选中F列中的数据,单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”,在弹出的对话框的“数字”选项卡下,在“分类”列表框中选择“货币”,并设置“小数位数”为0,单击“确定”按钮。
步骤7:选择B列数据区域(B2:B101),单击【数据】选项卡下【数据工具】功能组中的“数据有效性”按钮,在下拉列表中选择“数据有效性”,在弹出的对话框中,将“有效性条件”组中的“允许”设置为“序列”,在“来源”中输入“男,女”,如下图所示;切换到“出错警告”选项卡,在“错误信息”文本框中输入“仅可输入中文!”,单击“确定”按钮。
步骤8:单击【视图】选项卡下【宏】功能组中的“宏”按钮,在下拉列表中选择“录制宏”,弹出“录制新宏”对话框,在“宏名”文本框中输入“最小年龄”;在“快捷键”文本框中同时按下键盘上的Shift键和U键(小写英文状态下),为宏指定快捷键,如下图所示;单击【开始】选项卡下【样式】功能组中的“条件格式”按钮,在下拉列表中选择“项目选取规则/值最小的10项”,在弹出的对话框中采用默认设置,直接单击“确定”按钮,然后单击【视图】选项卡下【宏】功能组中的“宏”按钮,在下拉列表中选择“停止录制”;最后,选中D列中的数据区域,单击【宏】功能组中的“宏”按钮,在下拉列表中选择“查看宏”,在弹出的对话框中单击“执行”按钮。
步骤9:选中A2:F101单元格区域,单击【开始】选项卡下【样式】功能组中的“条件格式”按钮,在下拉列表中选择“管理规则”按钮,弹出“条件格式规则管理器”对话框,在对话框中单击“新建规则”按钮,弹出“新建格式规则”对话框,在“选择规则类型”列表框中选择“使用公式确定要设置格式的单元格”,输入公式“=RANK($F2,$F$2:$F$101,1)<16”,单击下方的“格式”按钮,弹出“设置单元格格式”对话框,在对话框中将字体颜色设置为“标准色/绿色”,单击“确定”按钮。返回“条件格式规则管理器”对话框后选中第2个规则(“后10个”),单击上移按钮,使其成为第1个规则,如下图所示,然后单击“确定”按钮。
3.【解题步骤】
步骤1:切换到“按年龄和性别”工作表,在B2单元格中输入公式“=COUNTIFS(客户资料[性别],"男",客户资料[年龄段],按年龄和性别!A2)”,拖动填充句柄填充到B12单元格;在C2单元格中输入公式“=COUNTIFS(客户资料[性别],"女",客户资料[年龄段],按年龄和性别!A2)”,拖动填充句柄填充到C12单元格;在D2单元格输入公式“=B2+C2”,拖动填充句柄填充到D12单元格;在B13单元格中输入公式“=SUM(B2:B12)”,使用填充句柄填充到D13单元格。
步骤2:单击【开始】选项卡下【样式】功能组中的“单元格样式”按钮,在下拉列表中选择“新建单元格样式”,弹出“样式”对话框,在对话框的“样式名”中输入“表格标题”,单击“格式”按钮,在弹出的“设置单元格格式”对话框中设置单元格填充颜色和字体颜色(此处可以自行设置),单击“确定”按钮;选中表格标题行,单击【样式】功能组中的单元格样式”按钮,在下拉列表中选择“表格标题”样式。选中A2:D12数据区域,单击【样式】功能组中的“单元格样式”按钮,在下拉列表框中选择“主题单元格样式”下的任意一种样式;选中表格的最后一行,单击【样式】功能组中的“单元格样式”按钮,在下拉列表框中选择“汇总”样式。
4.【解题步骤】
步骤1:参考考生文件夹下的“各年龄段顾客.png”示例效果,选中“按年龄和性别”工作表中的A1:C12数据区域,单击【插入】选项卡下【图表】功能组中的“柱形图/堆积柱形图”,在工作表中插入一个柱形图表对象。
步骤2:选中图表对象,单击【图表工具/设计】选项卡下【位置】功能组中的“移动图表”按钮,在弹出的对话框中选择“新工作表”,输入工作表名“各年龄段人数”,单击“确定”按钮。
步骤3:选中图表对象,选择【图表工具/设计】选项卡下【图表样式】功能组中的“样式7”;切换到【布局】选项卡,单击【坐标轴】功能组中的“坐标轴”按钮,在下拉列表中选择“主要纵坐标轴/其他主要纵坐标轴选项”,弹出“设置坐标轴格式”对话框,选中左侧列表框中的“线条颜色”,设置“线条颜色”为“无线条”,单击“关闭”按钮。
步骤4:选中图表对象,单击【布局】选项卡下【坐标轴】功能组中的“坐标轴”按钮,在下拉列表中选择“主要横坐标轴/其他主要横坐标轴选项”,弹出“设置坐标轴格式”对话框,在“坐标轴选项”中将“主要刻度线类型”设置为“无”,单击“关闭”按钮。
步骤5:选中图表对象,单击【布局】选项卡下【坐标轴】功能组中的“网格线”按钮,在下拉列表中选择“主要横网格线/无”。
步骤6:选中图表对象,单击【布局】选项卡下【标签】功能组中的“图例”按钮,在下拉列表中选择“在底部显示图例”。
步骤7:选中图表对象,单击【布局】选项卡下【分析】功能组中的“折线”按钮,在下拉列表中选择“系列线”;在图表绘图区中单击选中“系列线”并右击,在弹出的快捷菜单中选择“设置系列线格式”,弹出“设置系列线格式”对话框,在左侧列表框中选中“线型”,在右侧“短划线类型”下拉列表中选中“短划线”,如下图所示,单击“关闭”按钮。
步骤8:单击【插入】选项卡下【文本】功能组中的“文本框”按钮,在下拉列表中选择“横排文本框”,在图表上方绘制一个文本框对象,输入文本“各年龄段顾客人数”,设置文本大小为“24”,字体为“微软雅黑”,颜色为“白色 背景1”,填充颜色为“标准色/深蓝色”;选中该文本框对象,向上拖动使其与图表顶端对齐(向上拖不动时证明已经与图表顶端对齐);单击图表对象,在【格式】选项卡下【大小】功能组中查看图表的宽度为25.85厘米,再选中文本框对象,在【绘图工具/格式】选项卡下【大小】功能组中设置宽度为“25.85厘米”;单击【开始】选项卡下【对齐方式】功能组中的“垂直居中”和“文本左对齐”按钮;适当缩小绘图区大小,使其不被标题文本框遮挡。
5.【解题步骤】
步骤1:鼠标右键单击“2016年消费”工作表标签名,在弹出的快捷菜单中选择“隐藏”。
步骤2:鼠标左键拖动工作表“各年龄段人数”,放置于所有工作表的右侧。
6.【解题步骤】
步骤:选中“客户资料”工作表,单击【页面布局】选项卡下【页面设置】功能组中的“打印标题”按钮,在弹出的“页面设置”对话框中,设置“顶端标题行”为“$1:$1”,单击“确定”按钮。
7.【解题步骤】
步骤1:在“客户资料”工作表中,单击【页面布局】选项卡下【页面设置】功能组右下角的对话框启动器按钮,在弹出的“页面设置”对话框中选择“页眉/页脚”选项卡,在“页眉”下拉列表中选择“客户资料”;单击“自定义页脚”按钮,在弹出的“页脚”对话框中将光标定位在“中”文本框中,单击对话框中的“插入页码”按钮,再输入文本“of”,然后单击对话框中的“插入页数”按钮,如下图所示,最后单击“确定”按钮关闭所有对话框。
步骤2:按照同样的方法为“按年龄和性别”和“各年龄段人数”工作表分别设置页眉和页脚。
步骤3:单击快速访问工具栏中的“保存”按钮,然后关闭文档。