视频讲解
真题练习46-Excel电子表格_哔哩哔哩_bilibili
题库下载
题目要求
第46组
林明德是某在线销售摄影器材企业的管理人员，于2017年初随机抽取了100名网站注册会员，准备使用Excel分析他们上一 年度的消费情况。根据下列要求，帮助他运用已有的数据完成这项工作。
1．打开考生文件夹下的素材文件Excel.xlsx（“.xlsx”为扩展名），后续操作均基于此文件，否则不得分。
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列中的数据区域添加数据验证规则，以便仅可在其中输入数据“男”或“女”，如果输入其他内容，则弹出样式为“停止”的出错警告，错误信息为“仅可输入中文！”。
⑧ 为表格中的数据添加条件格式，将D列中年龄最小的10位顾客的年龄用标准红色显示，年消费金额最低的15位顾客所在的整行记录的文本颜色设置为标准绿色（注意：如果该顾客属于年龄最小的10位顾客，则年龄数值应保持为标准红色文本）。
3．在“按年龄和性别”工作表中，完成下列任务：
① 根据“客户资料”工作表中已完成的数据，在B列、C列和D列中分别计算各年龄段男顾客人数、女顾客人数、顾客总人数，并在表格底部进行求和汇总。
② 创建名为“表格标题”的自定义单元格样式，为其设置适当的单元格填充颜色和字体颜色，并应用于表格的标题行（首行）；为表格的数据区域（2-12行）应用一种恰当的主题单元格样式；为表格的汇总行（末行）应用“汇总”样式。
4．根据“按年龄和性别”工作表中的数据创建图表，显示各年龄段不同性别的顾客人数，并将图表移动到名为“各年龄段人数”的新的图表工作表中，参照考生文件夹下的Epic01.png完成效果图片，修改图表的元素，要求不显示垂直轴线条，不显示水平轴刻度线以及网格线，图例位于图表底部，系列线使用短划线样式，图表色彩使用单色区域中的第5种，图表标题为嵌入图表的文本框、与图表顶端对齐、水平方向与图表等宽、文字垂直方向中部对齐。
5．隐藏“2016年消费”工作表，将“各年龄段人数”工作表置于所有工作表最右侧。
6．设置“客户资料”工作表，以便在打印的时候，该工作表的第一行会自动出现在每一页的顶部。
7．为所有可见工作表添加自定义页眉和页脚，在页眉正中显示工作表的名称，在页脚正中显示页码和页数，格式为“页码 of 总页数”，例如“1 of 5”，当工作表名称或者数据发生变化时，页眉和页脚内容应可以自动更新。
文字解析
第46组
1．【解析】在考生文件夹中双击打开“Excel.xlsx”文件钮。
2．【解析】①步骤1：打开“客户资料”工作表，将光标定位于“A1:F101”区域内，按<Ctrl+A>选中“A1:F101”区域单元格；
步骤2：单击“开始”|“样式”分组中的“套用表格格式”下拉按钮，在下拉列表中，选中一个表格样式（例如：表样式浅色1），选中“表包含标题”复选框，单击“确定”按钮；
步骤3：在“设计”|“属性”分组中将表名称改为“客户资料”，取消“表格样式选项”分组中的“镶边行”复选框。
②单击“性别”列筛选按钮，将选项“F”取消选中，单击“确定”按钮，将B2单元格中内容改成“男”，选中B2，并按<Ctrl+C>组合键复制，选中剩余的所有B列单元跟，然后按<Ctrl+V>组合键粘贴；采用同样的方法将所有的“F”替换为“女”。
③步骤1：选中C列单元格，单击“开始”|“数字”分组中的对话框启动器，打开“设置单元格格式”对话框；
步骤2：在分类中选中“自定义”，在类型中输入日期格式“yy"年"m"月"d"日"”，单击“确定”按钮。
④步骤1：将光标定位于D2单元格，输入“=DATEDIF(,,)”，然后单击“插入函数”按钮，此时打开“参数函数”对话框；
步骤2：在第一个参数中输入“[@生日]”，在第二个参数中选中“C2”单元格，此时会显示出“”2017-1-1””，在第三个参数中输入“Y”，单击“确定”按钮，即可计算出每位顾客到2017年1月1日止的年龄。
知识拓展：DATEDIF函数
DATEDIF函数是Excel隐藏函数，在帮助和插入函数中没有。功能是：返回两个日期之间的年\月\日间隔数。常使用DATEDIF函数计算两日期之差。
语法：DATEDIF(start_date,end_date,unit)
其中：第一个参数（Start_date），是一个日期，表示时间段内的第一个日期或起始日期。；第二个参数（End_date），是一个日期，表示时间段内的最后一个日期或结束日期；第三个参数（Unit），是所需信息的返回类型。
第三个参数（Unit）返回值有6种：“Y”表示时间段中的整年数；“M”表示时间段中的整月数；“D”表示时间段中的天数；“MD”表示起始日期与结束日期的同月间隔天数，忽略日期中的月份和年份；“YD”表示起始日期与结束日期的同年间隔天数，忽略日期中的年份；“YM”表示起始日期与结束日期的间隔月数，忽略日期中年份。
备注：起始日期必须在1900年之后，结束日期必须大于起始日期。
⑤步骤1：此题我们可以先在“按年龄和性别”工作表中新建一个年龄最小值和年龄段对应表，其中年龄最小值取年龄段中最小值（例如：30岁以下取值0，30-34岁,取值30，后面的以此类推），这里我们建立在F1到G12区域内；
步骤2：返回客户资料工作表，选中E2单元格，单击“插入函数”按钮，打开“插入函数”对话框。在选择类别中选中“查找与引用”，在选择函数中选中“VLOOKUP”，单击“确定”按钮，打开“函数参数”对话框；
步骤3：在第一个参数中选中“D2”，此时参数内容变成“[@年龄]”；在第二个参数中选中“按年龄和性别”工作表中的“F1:G12”单元格区域（注意：要将单元格引用改成绝对引用）；在第三个参数中输入“2”表示从第2列获取满足条件内容；在第四个参数中输入“TRUE”或不输入（表示精确查找）。单击“确定”按钮。
⑥步骤1：选中“2016年消费”工作表中的A列单元格，单击“开始”|“对齐方式”分组中的“合并后居中”下拉按钮，在下拉列表中单击“取消单元格合并”命令；
步骤2：按<Ctrl+G>组合键，打开“定位”对话框，单击“定位条件”按钮，打开“定位条件”对话框，选中“空值”按钮，单击“确定”按钮；
步骤3：在编辑栏中输入“=A2”，按<Ctrl+Enter>组合键；
步骤4：返回“客户资料”工作表，选中F2单元格，单击“插入函数”按钮，打开“插入函数”对话框。在选择类别中选中“查找与引用”，在选择函数中选中“SUMIFS”，单击“确定”按钮，打开“函数参数”对话框；
步骤5：第一个参数是要求和引用的区域，这里就是“2016年消费”工作表中的C列，因此选中“2016年消费”工作表中的C列；
步骤6：第二个参数是要查找的条件区域，这里就是“2016年消费”工作表中的A列，因此选中“2016年消费”工作表中的A列；
步骤7：第三个参数是查找条件内容，这里就是“客户资料”工作表中的“A2”单元格，因此输入“A2”，单击“确定”按钮；
步骤8：选中F列，单击“开始”|“数字”分组中的对话框启动器，打开“设置单元格格式”对话框，在“数字”选项卡中，选中分类中的“货币”，小数位数中设置为“0”，单击“确定”按钮。
⑦步骤1：选中B列数据区域，单击“数据”|“数据工具”分组中的“数据验证”下拉按钮，在下拉列表中单击“数据验证”命令，打开“数据验证”对话框；
步骤2：在“设置”选项卡中的“允许”中选中“序列”，在“来源”中输入“男,女”（注意：中间的逗号是半角逗号）；
步骤3：在“出错警告”选项卡中，设置样式为“停止”、错误信息为“仅可输入中文！”，单击“确定”按钮。
⑧步骤1：单击“开始”|“样式”分组中的“条件格式”下拉按钮，在下拉列表中单击“最前/最后规则”命令中的“最后10项”，打开“最后10项”对话框，在“设置为”中选中“自定义格式”命令，打开“设置单元格格式”对话框，在“字体”选项卡中选中“颜色”为标准色中的“红色”，单击“确定”按钮，再次单击“确定”按钮；
步骤2：选定“客户资料”工作表中的数据区域，单击“开始”|“样式”分组中的“条件格式”下拉按钮，在下拉列表中单击“新建规则”命令，打开“新建格式规则”对话框；
步骤3：在“选中规则类型”中选“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式”中输入“=RANK($F2,$F$2:$F$101,1)<16”；
步骤4：单击“格式”按钮，打开“设置单元格格式”对话框，在“字体”分组中设置字体颜色为标准色中的“绿色”，单击“确定”按钮，再次单击“确定”按钮；
步骤5：调整当前表中的两个规则顺序，将“后10个”规则设置为第一个规则，单击“确定”按钮。
3．【解析】①步骤1：将光标定位于“按年龄和性别”工作表中的“B2”单元格中，单击“插入函数”按钮，打开“插入函数”对话框；
步骤2：在“或选择类别”中选中“统计”，在“选择函数”中选中“COUNTIFS”函数，单击“确定”按钮，打开“函数参数”对话框；
步骤3：COUNTIFS函数可以有一组或多组条件，一组条件两个参数；第一个参数是要为特定条件计算的单元格区域，本题中是要统计年龄段内的人数，因此这里应选客户资料工作表中的“年龄段”列（即：客户资料[年龄段]）；第二个参数是单元格统计的范围，可以是数字、表达式或文本形式的条件，本题中是按A列的条件来统计（即：A2）；
步骤4：本题还有第二个条件就是性别为男的顾客人数，因此还要添加第二组条件；第二组中第一个参数应该选择客户资料工作表中的“性别”列（即：客户资料[性别]）；第二个参数可以直接写成“男”；
步骤5：单击“确定”按钮即可求出B2单元格中男顾客人数，双击B2单元格智能填充柄，可求出B列其他单元格男顾客人数；
步骤6：同理女顾客人数，公式为：=COUNTIFS(客户资料[年龄段],A2,客户资料[性别],"女")；
步骤7：顾客总人数等于男顾客人数加上女顾客人数，公式为：=SUM(B2:C2)，双击D2单元格智能填充柄，可求出D列其他单元格顾客总人数；
步骤8：将合计行的3个数字0删除，选中B2:D13单元格区域，单击“开始”|“编辑”分组中的“求和”按钮，即可求出这3列各自的合计人数。
②步骤1：单击“开始”|“样式”分组中的其他按钮，在下拉列表中单击“新建单元格样式”，打开“样式”对话框；
步骤2：将样式名改为“表格标题”，单击“格式”按钮，打开“设置单元格格式”对话框；在“字体”选项卡中设置一种字体颜色（例如：白色）；在“填充”选项卡中设置一种填充颜色，单击“确定”按钮，单击“样式”对话框中的“确定”按钮；
步骤3：选中A1:D1单元格区域，单击“开始”|“样式”分组中的“表格标题”样式；
步骤4：选中A2:D12单元格区域，单击“开始”|“样式”分组中的一种主题单元格样式（例如：着色5）；
步骤5：选中A13:D13单元格区域，单击“开始”|“样式”分组中的“汇总”样式。
4．【解析】步骤1：选中A1:C12单元格区域，单击“插入”|“图表”分组中的“柱形图”下拉按钮中的“堆积柱形图”命令；
步骤2：在“设计”|“图表样式”分组中选中“样式1”样式；
步骤3：选中纵坐标，单击鼠标右键，在弹出菜单中单击“设置坐标轴格式”命令，打开“设置坐标轴格式”窗口，选中“线条”选项卡中的“无线条”按钮，单击“关闭”按钮；
步骤4：选中横坐标，单击鼠标右键，在弹出菜单中单击“设置坐标轴格式”命令，打开“设置坐标轴格式”窗口，选在“刻度线”选项卡中设置“主刻度线类型”为“无”，单击“关闭”按钮；
步骤5：选中横“网格线”，单击鼠标右键，选中“删除”命令；
步骤6：单击“图表工具”|“图表设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在下拉列表中选中“图例”级联菜单中的“底部”
步骤7：单击“图表工具”|“图表设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在下拉列表中选中“线条”级联菜单中的“系列线”命令，选中“系列线”，单击鼠标右键，在弹出的菜单中选择“设置系列线格式”，打开“设置系列线格式”对话框，在“线型”中选中“短划线类型”中的“短划线”，单击“关闭”按钮；
步骤8：单击“图表工具”|“图表设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在下拉列表中选中“图表标题”级联菜单中的无”命令；
步骤9：单击“图表工具”|“格式”|“插入形状”分组中的“横排文本框”，在图表中单击鼠标，插入一个文本框，输入“各年龄段顾客人数”，拖动文本框至图表左上方对齐，并调整文本框宽度和图表一样宽，适当设置文本框高度；
步骤10：在文本框上单击鼠标右键，然后单击“设置对象格式”命令，打开“设置形状格式”对话框，在填充中选中“纯色填充”，设置填充颜色为“深蓝”，单击“关闭”按钮。选中文本框，单击“开始”|“字体”分组中的“字体”颜色下拉按钮，选中其中的“白色”，设置适当的字号（例如：24号）；
步骤11：往下拖动图表的绘图区上边框线，使之完整显示而不被文本框覆盖；
步骤12：单击“图表工具”|“图表设计”|“位置”分组中的“移动图表”按钮，打开“移动图表”对话框，选中“新工作表”按钮，将名称设置为“各年龄段人数”，单击“确定”按钮。
5．【解析】步骤1：在“2016年消费”工作表标签上单击鼠标右键，在弹出菜单中选中“隐藏”命令；
步骤2：在“各年龄段人数”工作表标签上按住鼠标左键，然后拖动到最右侧再松开鼠标，即可将它置于所有工作表最右侧。
6．【解析】步骤1：打开“客户资料”工作表，单击“页面布局”|“页面设置”分组中的“打印标题”按钮，打开“页面设置”对话框；
步骤2：单击“顶端标题行”后面的选择按钮，选中第一行（此时显示：$1:$1），单击“确定”按钮。
7．【解析】步骤1：选中“客户资料”工作表，单击“页面布局”|“页面设置”分组中对话框启动器，打开“页面设置”对话框；
步骤2：单击“页眉/页脚”选项卡，单击“自定义页眉”按钮，打开“页眉”对话框，将光标定位在“中”输入框中，单击“插入数据表名称
”按钮，单击“确定”按钮；
步骤3：单击“自定义页脚”按钮，打开“页脚”对话框，将光标定位在“中”输入框中，单击“插入页码
”按钮，按空格键，然后输入“of”，再按空格键，单击“插入页数
”按钮，单击“确定”按钮，再次单击“确定”按钮；
步骤4：采用以上相同方法为“按年龄和性别”和“各年龄段人数”工作表设置页眉和页脚。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
