王明是某在线销售数码产品公司的管理人员，于2017年初随机抽取了100名网站注册会员，准备使用Excel分析他们上一年度的消费情况。根据下列要求，帮助他运用已有的数据完成这项工作。在“客户资料”工作表中，完成下列任务： ①将数据区域A1：F10

admin2019-02-11 18

问题王明是某在线销售数码产品公司的管理人员，于2017年初随机抽取了100名网站注册会员，准备使用Excel分析他们上一年度的消费情况。根据下列要求，帮助他运用已有的数据完成这项工作。
在“客户资料”工作表中，完成下列任务：
①将数据区域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位顾客，则年龄数值应保持为深红色文本)。

选项

答案步骤1：切换到“客户资料”工作表，选中数据区域A1：F101，单击【插入】选项卡下【表格】功能组中的“表格”按钮，在弹出的对话框中直接单击“确定”按钮，如图1．17所示；在【表格工具／设计】选项卡下【属性】功能组中，输入表名称“客户资料”，输入完成后按键盘上的Enter键完成输入；在【表格样式选项】功能组中取消勾选“镶边行”复选框。 [*] 步骤2：选中B列中的B2：B101数据区域，单击【开始】选项卡下【编辑】功能组中的“查找和选择”按钮，在下拉列表中选择“替换”，弹出“查找和替换”对话框，在“查找内容”中输入“M”，在“替换为”中输入“男”，单击“全部替换”按钮，此时将选中区域中所有“M”替换为“男”。继续在对话框的“查找内容”中输入“F”，在“替换为”中输入“女”，单击“全部替换”按钮，最后关闭“查找和替换”对话框。步骤3：选中C列中的C2：C101数据区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，弹出“设置单元格格式”对话框，在对话框的“分类”列表框中选择“自定义”，在右侧的“类型”文本框中将类型修改为“yy年m月d日”，如图1．18所示，最后单击“确定”按钮。 [*] 步骤4：选中D2单元格，输入公式“＝DATEDIF([@生日]，"2017－1－1"，"y")”，输入完成后按键盘上的Enter键结束输入，公式自动填充到D101单元格。步骤5：复制“按年龄和性别”工作表中的A列内容到“客户资料”工作表的I1：112区域(此处区域可以任意指定)，在H1单元格中输入“年龄”，按照如图1．19所示输入相应的数据；然后在E2单元格中输入公式“＝VLOOKUP(D2，$H$1：$I$12，2，1)”，输入完成后按键盘上的Enter键结束输入，公式自动填充到E101单元格。 [*] 步骤6：选中“2016年消费”工作表中A列中的所有合并单元格，单击【开始】选项卡下【对齐方式】功能组中的“合并后居中”按钮，在下拉列表中选择“取消单元格合并”，将所有合并的单元格取消合并；单击【开始】选项卡下【编辑】功能组中的“查找和选择”按钮，在下拉列表中选择“定位条件”命令，在弹出的对话框中选择“空值”，单击“确定”按钮，将所有空白单元格全部选中；在“编辑栏”中输入公式“＝A2”，输入完成后按键盘上的Ctrl＋Enter组合键进行填充(此操作可以让空白单元格自动填充上面的数据)，结果如图1．20所示。然后在“客户资料”工作表的F2单元格中输入公式“＝SUMIF(’2016年消费’!$A$2：$A$364，A2，’2016年消费’!$C$2：$C$364)”，输入完成后按键盘上的Enter键结束输入，公式自动填充到F101单元格。最后选中F列中的数据，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”，在弹出的对话框的“数字”选项卡下，在“分类”列表框中选择“货币”，并设置“小数位数”为0，单击“确定”按钮。 [*] 步骤7：选择B列数据区域(B2：B101)，单击【数据】选项卡下【数据工具】功能组中的“数据有效性”按钮，在下拉列表中选择“数据有效性”，在弹出的对话框中，将“有效性条件”组中的“允许”设置为“序列”，在“来源”中输入“男，女”，如图1．21所示；切换到“出错警告”选项卡，在“错误信息”文本框中输入“仅可输入中文!”，单击“确定”按钮。 [*] 步骤8：单击【视图】选项卡下【宏】功能组中的“宏”按钮，在下拉列表中选择“录制宏”，弹出“录制新宏”对话框，在“宏名”文本框中输入“最小年龄”；在“快捷键”文本框中同时按下键盘上的Shift键和U键(小写英文状态下)，为宏指定快捷键，如图1．22所示；单击【开始】选项卡下【样式】功能组中的“条件格式”按钮，在下拉列表中选择“项目选取规则／值最小的10项”，在弹出的对话框中采用默认设置，直接单击“确定”按钮，然后单击【视图】选项卡下【宏】功能组中的“宏”按钮，在下拉列表中选择“停止录制”；最后，选中D列中的数据区域，单击【宏】功能组中的“宏”按钮，在下拉列表中选择“查看宏”，在弹出的对话框中单击“执行”按钮。 [*] 步骤9：选中A2：F101单元格区域，单击【开始】选项卡下【样式】功能组中的“条件格式”按钮，在下拉列表中选择“管理规则”按钮，弹出“条件格式规则管理器”对话框，在对话框中单击“新建规则”按钮，弹出“新建格式规则”对话框，在“选择规则类型”列表框中选择“使用公式确定要设置格式的单元格”，输入公式“＝RANK($F2，$F$2：$F$101，1)＜16”，单击下方的“格式”按钮，弹出“设置单元格格式”对话框，在对话框中将字体颜色设置为“标准色／绿色”，单击“确定”按钮。返回“条件格式规则管理器”对话框后选中第2个规则(“后10个”)，单击上移按钮，使其成为第1个规则，如图1．23所示，然后单击“确定”按钮。 [*]

解析

转载请注明原文地址:https://kaotiyun.com/show/oJep777K

本试题收录于：二级MS Office高级应用题库NCRE全国计算机二级分类