视频讲解
真题练习47-Excel电子表格_哔哩哔哩_bilibili
题库下载
题目要求
第47组
许晓璐是某贸易企业的管理人员，正在分析企业2014~2016国外订货的情况。帮助她运用已有数据完成这项工作。
1．打开考生文件夹下的素材文件Excel.xlsx（“.xlsx”为扩展名），后续操作均基于此文件，否则不得分。
2．在“销售资料”工作表中完成下列任务：
① 将B列（“日期”列）中不规范的日期数据修改为Excel可识别的日期格式，并将数字格式设置为“January 1,2014”，适当调整列宽，将数据右对齐。
② 在C列（“客户编号”列）中，根据“销往地区”在“客户编号”前面添加地区代码，代码可在“地区代码”工作表中进行查询。
③ 在H列（“产品价格”列）中填入每种产品的价格，具体价格信息可在“产品信息”工作表中查询。
④ 在J列（“订购金额”列）中计算每个订单的金额，公式为“订购金额=产品价格×订购数量”，并调整为货币格式，货币符号为“$”，保留0位小数。
⑤ 修改E列（“销往国家”列）中数据验证设置的错误，以便根据D列“销往地区”的不同，在E列中通过下拉列表可以正确显示对应的国家，例如，在D2单元格中若为“北美洲”，则在E2单元格中数据验证所提供的下拉列表选项为“加拿大，美国，墨西哥”（“销往地区”和“销往国家”的对应关系可从“销往国家”工作表中查询，不能更改数据验证中的函数类型）。
⑥ 冻结工作表的首行。
3．参考样例效果图片Epic01.jpg，在“销售汇总”工作表中完成下列任务：
① 将A列中文本“销往地区”的文字方向改为竖排。
② 在C3:F6单元格区域中，使用SUMIFS函数计算销往不同地区各个类别商品的总金额，并调整为货币格式，货币符号为“$”，保留0位小数。
③ 在B8单元格中设置数据验证，以便可以通过下拉列表选择单元格中的数据，下拉列表项为“服饰配件，日用品，自行车款，自行车配件”，并将结果显示为“日用品”。
④ 定义新的名称“各类别销售汇总”，要求这个名称可根据B8单元格中数值的变化，动态引用该单元格中显示的产品类别对应的销往各个地区的销售数据。例如，当B8单元格中的数据修改为“自行车配件”时，名称引用的单元格区域为F3:F6。
⑤ 在C8:F20单元格区域中创建簇状柱形图，水平（分类）轴标签为各个销往地区的名称，图表的图例项（数据系列）的值来自名称“各类别销售汇总”，图表可根据B8单元格中数值的变化而动态显示不同产品类别的销售情况。取消网格线和图例，并根据样例效果设置数值轴的刻度。
⑥ 为C3:F6单元格区域仅设置一个条件格式规则：当B8单元格中所显示的产品类别发生变化时，相应产品类别的数据所在单元格格式也随之发生动态变化，单元格的填充颜色变为标准红色，字体颜色变为“白色,背景1”。
4．创建名为“销售情况报告”的新工作表，并置于所有工作表的左侧，在此工作表中完成下列任务：
① 将工作表的纸张方向修改为横向。
② 在单元格区域B11:M25中，插入布局为“表格列表”的SmartArt图形，并参照样例图片Epic02.jpg填入相应内容。
③ 为SmartArt图形中标题下方的5个形状添加超链接，使其可以分别链接到文档中相同名称工作表的A2单元格。
文字解析
第47组
1．【解析】在考生文件夹中双击打开“Excel.xlsx”文件。
2．【解析】① 步骤1：选中B2单元格，然后按<Ctrl+Shift+向下箭头>组合键，选中B列所有数据单元格；
步骤2：按<Ctrl+H>组合键，打开“查找和替换”对话框，在查找内容中输入“.”，在替换为中输入“-”，单击“全部替换”按钮，单击“确定”按钮，单击“关闭”按钮；
步骤3：选中B2单元格，按<Ctrl+Shift+向下箭头>组合键，选中B列所有数据单元格；按<Ctrl+1>组合键，打开“设置单元格格式”对话框；
步骤4：在“区域设置（国家/地区）”中选中“英语（美国）”，在类型中选中“March 14, 2012”，单击“确定”按钮；
步骤5：选中B列右侧边线，向右拉至适当位置，适当调整B列列宽，使B列文字全部显示；
步骤6：选中“开始”|“对齐方式”分组中的“右对齐”按钮。
② 步骤1：选中“地区代码”工作表，将A列复制到C列；
步骤2：返回到“销售资料”工作表，在C列后面插入一个空白列，将光标定位于D2单元格，单击“插入函数”按钮，打开“插入函数”对话框
步骤3：在“或选择类别”中选中“查找与引用”，在“选择函数”中选中VLOOKUP函数，单击“确定”按钮，打开“函数参数”对话框；
步骤4：在第一个参数中选中E2单元格，在第二个参数中选中“地区代码”工作表中，选中B2:C5区域（注意要绝对引用），在第三个参数中输入“2”，在第4个参数中输入“0”，单击“确定”按钮；
步骤5：在D2单元格公式最后，加上链接符号“&”和C2，即最后公式为“=VLOOKUP(E2,地区代码!$B$2:$C$5,2,0)&C2”，双击D2单元格智能填充柄，完成其他客户编号填写；
步骤6：在D列数据选中的情况下，按<Ctrl+C>复制，然后选中“C2”单元格，单击“开始”选项卡中“剪贴板”分组中的“粘贴”下拉按钮，在下拉列表中选中“值”粘贴按钮；
步骤7：选中D列，直接删除D列。
③ 步骤1：选中H2单元格，单击“插入函数”按钮，打开“插入函数”对话框，在选择类别中选中“查找与引用”，在选择函数中选中VLOOKUP函数，单击“确定”按钮，打开“函数参数”对话框；
步骤2：在第一个参数中选中G2单元格，在第二个参数中选中“产品信息”工作表中，选中B2:C26区域（注意要绝对引用），在第三个参数中输入“2”，在第4个参数中输入0，单击“确定”按钮；
步骤3：双击H2单元格智能填充柄，完成其他客户编号填写。
④ 步骤1：将光标定位于J2单元格，输入计算公式“=H2*I2”，双击J2单元格智能填充柄，完成其他客户编号填写；
步骤2：按组合键<Ctrl+1>，打开“设置单元格格式”对话框，选中分类中的“货币”、小数位数设置为“0”、货币符号中选中“$”符号，单击“确定”按钮。
⑤ 步骤1：选中E2单元格，按<Ctrl+Shift+向下箭头>选中E列所有数据；
步骤2：单击“数据”|“数据工具”分组中的“数据验证”下拉按钮，在列表中单击“数据验证”命令，打开“数据验证”对话框；
步骤3：将“来源”中的D2单元格绝对引用改成相对引用，（即“=INDIRECT(D2)”），单击“确定”按钮。
⑥ 单击“视图”|“窗口”分组中的“冻结窗格”下拉按钮，在列表中单击“冻结首行”命令。
3．【解析】① 选中“销售汇总”工作表中的“A3”单元格，单击“开始”|“对齐方式”分组中的“方向”下拉按钮，在下拉列表中选中“竖排文字”。
② 步骤1：选中“C3”单元格，单击“插入函数”按钮，打开“插入函数”对话框，在选择类别中选中“数学与三角函数”，在选择函数中选中“SUMIFS”函数，单击“确定”按钮，打开“函数参数”对话框；
步骤2：第一个参数是要求和的单元格区域，这里选中销售资料中的J2:J117单元格区域，并设置绝对引用“销售资料!$J$2:$J$117”；
步骤3：第二个参数是要查找满足第一个条件的区域，这里选中销往地区，即D2:D117单元格区域，并设置绝对引用“销售资料!$D$2:$D$117”；
步骤4：第三个参数是第一个条件指定的值，这里选中“销售汇总”表中的B3单元格“$B3”，注意这里要绝对引用B列（方便一会往右使用智能填充）；
步骤5：第四个参数是要查找满足第二个条件的区域，这里选中商品类别区域即F2:F117单元格区域，并设置绝对引用“销售资料!$F$2:$F$117”；
步骤6：第五个参数是第二个条件指定的值，这里选中“销售汇总”表中的C2单元格“C$2”，注意这里要绝对引用第2行（方便一会往下使用智能填充），单击“确定”按钮。
步骤7：检查下货币符号和小数位，已经是题目要求的，就不用改动了；
步骤8：拖动智能填充柄到F3单元格，双击智能填充柄，完成其他行填充。
③ 步骤1：选中B8单元格，单击“数据”|“数据工具”分组中“数据验证”下拉按钮，在下拉列表中选中“数据验证”命令，打开“数据验证”对话框；
步骤2：在“允许”中选中“序列”，在来源中选中“C2:F2”单元格区域，单击“确定”按钮；
步骤3：在B8单元格后面的下拉按钮中选中“日用品”。
④ 步骤1：选中“C2:F6”单元格区域，单击“公式”|“定义的名称”分组中的“根据所选内容创建”按钮，打开“以选定区域创建名称”对话框，选中“首行”复选框，单击“确定”按钮；
步骤2：单击“公式”|“定义的名称”分组中的“定义名称”按钮，打开“新建名称”对话框；
步骤3：在名称中输入“各类别销售汇总”，在引用位置中输入“=indirect(销售汇总!$B$8)”，单击“确定”按钮。
小提示：INDIRECT函数是返回由文本字符串指定的引用。
此函数立即对引用进行计算，并显示其内容。
⑤ 步骤1：单击“插入”|“图表”分组中的“柱形图”下拉按钮，在下拉列表中的选中“簇状柱形图”；
步骤2：单击“图表设计”|“数据”分组中的“选择数据”按钮，打开“选择数据源”对话框，删除“系列1”，单击“添加”按钮，打开“编辑数据系列”对话框；
步骤3：在系列名称中选中“B8”单元格，在系列值中输入“=销售汇总!各类别销售汇总”，单击“确定”按钮；
步骤4：单击“水平(分类)轴标签”中的“编辑”按钮，在轴标签区域中选中B3:B6单元格区域，单击“确定”按钮，再次单击“确定”按钮；
步骤5：选中“网格线”，按删除键<Del>删除网格线；
步骤6：选中“图例”，按删除键<Del>删除网格线；
步骤7：选中纵坐标轴，单击“格式”|“当前所选内容”分组中的“设置所选内容格式”按钮，打开“设置坐标轴格式”窗口；
步骤8：在“坐标轴选项”中设置“单位”中的“大”为“300000”，单击“关闭”按钮；
步骤9：拖动图表左上角到C8单元格内，拖动图表右下角，修改图表大小，使其在F20单元格内。
⑥ 步骤1：选中C3:F6单元格，单击“开始”|“样式”分组中的“条件格式”下拉按钮，在下拉列表中选择“新建规则”命令，打开“新建格式规则”对话框；
步骤2：选中“使用公式确定要设置格式的单元格”，在“为符合次公式的值设置格式”中输入公式“=$B$8=C$2”，单击“格式”按钮，打开“设置单元格格式”对话框；
步骤3：在“字体”选项卡中选中颜色中的“白色,背景 1”，单击“填充”选项卡，选中标准色中的“红色”，单击“确定”按钮；
步骤4：单击“确定”按钮。
4．【解析】单击“新工作表”按钮，将工作表名称改为“销售情况报告”；拖动该工作表到最左侧。
① 单击“页面布局”|“页面设置”分组中的“纸张方向”下拉按钮，在列表中选中“横向”。
② 步骤1：单击“插入”|“插图”分组中的“SmartArt”按钮，打开“选中SmartArt图形”对话框，在左边选中“列表”，在右侧找到并选中“表格列表”图形，单击“确定”按钮；
步骤2：根据样例图片“Epic02.jpg”在最上方的输入框中输入“国外销售情况报告”，在下方形状中依次输入“销售资料”、“销往国家”、“产品信息”、“地区代码”、“销售汇总”；
步骤3：拖动SmartArt图形左上角到B11单元格内，拖动图形右下角缩放大小，使得右下角在“M25单元格内”。
③ 步骤1：选中“销售资料”形状，单击“插入”|“链接”分组中的“超链接”按钮，打开“插入超链接”对话框；
步骤2：选中“本文档中的位置”，选中单元格引用中的“销售资料”，在“请键入单元格引用”输入“A2”，单击“确定”按钮；
步骤3：采用同样的方法，为后面4个形状添加超链接。
保存并关闭Excel.xlsx。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
