题目要求
第37套
财务朱明慧正在对本公司近三年的银行流水账进行整理。按照下列要求帮助她完成相关数据的计算、统计和分析工作：
1．将考生文件夹下的工作簿文档“Excel素材.xlsx”另存为“ExceL.xlsx”（“.xlsx”为文件扩展名），之后所有的操作均基于此文件，否则不得分。操作过程中，不可以随意改变原工作表素材数据的顺序。
2．将以Tab符分隔的文本文件“2018年银行流水.txt”中的数据导入到在工作表“银行交易明细”数据的最下方（自单元格A437开始），并删除与外部数据的连接。
3．在工作表“银行交易明细”中，按照下列要求对数据表进行完善：
①将第A列“交易日期”中的数据转换为类似“2016年01月15日”格式的日期（要求月、日均显示为两位），并居中显示。
②在“账户余额”列中自D5单元格开始，通过公式“本行余额=上行余额+本行收入-本行支出”计算每行的余额。
③设置收入、支出、账户余额3列数据的数字格式为数值、保留两位小数、使用千位分隔符。
④将A1单元格中的标题在数据区域内跨列居中；为自A3开始的数据区域套用一个表格格式。
⑤在最下方添加汇总行，分别计算收入和支出总额，其他列不进行任何计算。
⑥适当调整数据区域的字体、字号、列宽，并将数据区域以外的所有空白行列隐藏。
⑦将前3行锁定，令其总是可见。
4．以工作表“银行交易明细”为数据源，在工作表“销售情况统计”中完成以下各项统计分析工作：
①按照工作表“银行交易明细”F列“收支项目”中的分类，统计各年各季度的“销售收入”及“釆购成本”，并填入相应单元格中，其中2016年第1、2季度和2018年第3、4季度的收支均为0。
②按照公式“毛利润=销售收入-釆购成本”计算各年各季度的毛利润填入F列中。
③按照公式“毛利率=毛利润/销售收入”计算各年各季度的毛利率填入G列中，要求毛利率数据精确显示为保留为两位小数的百分比格式，如果出现错误值则不显示。
5．以计算完成的工作表“销售情况统计”为数据源，参照样例文档“图表示例.jpg”中效果、在数据源右侧插入一个反映三年销售收入与毛利率关系的图表，要求下列内容与示例一致：
①图表标题位置与内容。
②横纵坐标轴的格式、刻度格式、刻度显示单位。
③数据标签位置、格式及其内容。
④数据点颜色应该不同。
⑤图例位置及内容。
⑥适当改变图表样式。
6．以工作表“银行交易明细”为数据源，参照样例文档“透视图示例.jpg”所示、自新工作表“费用支出统计”的A3单元格开始生成数据透视表，要求如下：
①要求统计各年度每个月的员工支出项目的总和，包含行、列合计和分年度合计。
②行、列标题内容及格式、排列顺序均应与示例相同。
③设置数据区域的数字格式为保留二位小数、使用千位分隔符的数值。
④适当改变透视表样式，调整列宽、字体和字号。
⑤所有空单元格均显示为0。
文字解析
第37组
1．【解析】步骤1：在考生文件夹中双击打开“Excel素材.xlsx”文件。单击“文件”|“另存为”命令，在打开的“另存为”对话框中；
步骤2：将单击“浏览”按钮，打开的“另存为”对话框中，文件名称中的“Excel素材”改成“Excel”，在保存类型中选中“Excel工作簿（*.xlsx）”，单击“保存”按钮。
2．【解析】步骤1：选中A437单元格，单击“数据”|“获取外部数据”分组中的“自文本”按钮，打开“导入文本文件”对话框，找到考生文件夹下的“2018年银行流水.txt”，单击“导入”按钮，打开“文本导入向导”对话框；
步骤2：选中“分隔符号”按钮，选中“数据包含标题”复选框，单击“下一步”按钮，选中“Tab键”复选框，单击“下一步”按钮；
步骤3：单击完成按钮，打开“导入数据”对话框，在数据的放置位置中选中“现有工作表”按钮，在位置中选中“A437”单元格，单击“确定”按钮；
步骤4：单击“数据”|“连接”分组中的“连接”按钮，打开“工作簿连接”对话框，选中“2018年银行流水”，单击“删除”按钮，单击“确定”按钮，单击“关闭”按钮；
步骤5：选中437行的标题内容，单击“开始”|“单元格”分组中的“删除”按钮，删除这一行标题。
3．【解析】①步骤1：选中A4:A655单元格区域，单击“数据”|“数据工具”分组中的“分列”按钮，打开“文本分列向导”对话框，选中“固定宽度”按钮，单击“下一步”按钮，再次单一下一步按钮，选中“列数据格式”中的“日期”，在日期后面选中“YMD”，单击“完成”按钮；
步骤2：单击“开始”|“数字”分组中的对话框启动器，打开“设置单元格格式”对话框，在分类中选中“自定义”，在类型中输入“yyyy"年"mm"月"dd"日"”，单击“确定”按钮；
步骤3：单击“开始”|“对齐方式”分组中的“居中”按钮。
②在D5单元格输入公式“=N(D4)+N(B5)-N(C5)”，按回车键，然后双击智能填充柄完成其他行余额计算。
③选中B4:D655单元格区域，单击“开始”|“数字”分组中的对话框启动器，打开“设置单元格格式”对话框，在分类中选中“数值”，在小数位中输入“2”，选中“使用千位分隔符”复选框，单击“确定”按钮。
④步骤1：选中A1:F1单元格区域，在选中区域中单击鼠标右键，在弹出菜单中单击“设置单元格格式”命令，打开“设置单元格格式”对话框；
步骤2：单击“对齐”选项卡，在水平对齐中选中“跨列居中”，单击“确定”按钮。
⑤在A656单元格中输入“汇总”，在B656单元格中输入公式“=SUM(B4:B655)”，按回车键完成收入列汇总，拖动智能填充柄到C656单元格，完成支出列汇总。
⑥步骤1：选中数据区域，在“开始”|“字体”分组中调整字号和字体；
步骤2：单击“开始”|“单元格”分组中的“格式”下拉按钮，在列表中分别单击“自动调整行”和“自动调整列”命令；
步骤3：选中G列后按<Ctrl+Shift+向右箭头>（选中所有空表列），在选中区域单击鼠标右键，单击“隐藏”命令；
步骤4：选中657行后按<Ctrl+Shift+向下箭头>（选中所有空表列），在选中区域单击鼠标右键，单击“隐藏”命令；
⑦选中A4单元格，单击“视图”|“窗口”分组中的“冻结窗格”下拉按钮，在列表中单击“冻结窗格”命令。
4．【解析】①步骤1：在D3单元格中输入公式“=SUMIFS(银行交易明细!$B$4:$B$655,银行交易明细!$F$4:$F$655,D$2,银行交易明细!$A$4:$A$655,"<2016-4-1")”，按回车键完成计算；
步骤2：在D4单元格中输入公式“=SUMIFS(银行交易明细!$B$4:$B$655,银行交易明细!$F$4:$F$655,D$2,银行交易明细!$A$4:$A$655,"<2016-7-1",银行交易明细!$A$4:$A$655,">2016-3-31")”，按回车键完成计算；
步骤3：在D5单元格中输入公式“=SUMIFS(银行交易明细!$B$4:$B$655,银行交易明细!$F$4:$F$655,D$2,银行交易明细!$A$4:$A$655,"<2016-10-1",银行交易明细!$A$4:$A$655,">=2016-7-1")”，按回车键完成计算；
步骤4:在D6单元格中输入公式“=SUMIFS(银行交易明细!$B$4:$B$655,银行交易明细!$F$4:$F$655,D$2,银行交易明细!$A$4:$A$655,"<2017-1-1",银行交易明细!$A$4:$A$655,">=2016-10-1")”，按回车键完成计算；
步骤5：在E3单元格中输入公式“=SUMIFS(银行交易明细!$C$4:$C$655,银行交易明细!$F$4:$F$655,E$2,银行交易明细!$A$4:$A$655,"<2016-4-1")，按回车键完成计算；
步骤6：在E4单元格中输入公式=SUMIFS(银行交易明细!$C$4:$C$655,银行交易明细!$F$4:$F$655,E$2,银行交易明细!$A$4:$A$655,"<2016-7-1",银行交易明细!$A$4:$A$655,">2016-3-31")，按回车键完成计算；
步骤7：在E5单元格中输入公式=SUMIFS(银行交易明细!$C$4:$C$655,银行交易明细!$F$4:$F$655,E$2,银行交易明细!$A$4:$A$655,"<2016-10-1",银行交易明细!$A$4:$A$655,">=2016-7-1")，按回车键完成计算；
步骤8：在E6单元格中输入公式“=SUMIFS(银行交易明细!$C$4:$C$655,银行交易明细!$F$4:$F$655,E$2,银行交易明细!$A$4:$A$655,"<2017-1-1",银行交易明细!$A$4:$A$655,">=2016-10-1")”，按回车键完成计算；
②步骤1：在F3单元格中输入公式“=D3-E3”，按回车键完成计算，拖动智能填充柄到F6单元格，完成其他行计算；
步骤2：选中D7到F7单元格，单击“开始”|“编辑”分组中的“求和”按钮，完成2016年总计行计算。
③步骤1：在G3单元格“=IF(D3=0,"",F3/D3)”，拖动只能填充柄到G6单元格完成其他行计算；
步骤2：釆用同样的方法计算2017年和2018年的值；
步骤3：在D18单元格中输入“=SUM(D17,D12,D7)”，然后按回车键，拖动智能填充柄到F18单元格中。
5．【解析】步骤1：选中“C7:D7”、“C12:D12”和“C17:D17”单元格区域，单击“插入”|“图表”分组中的“插入散点图”下拉按钮，在列表中选中“三维气泡图”。
①步骤1：在图表标题中将内容修改为“销售收入/毛利率对比”；
步骤2：如果默认位置不是在图表上方，那么可以单击“图表工具”|“设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在列表中选中“图表标题”级联菜单中的“图表上方”。
②步骤1：选中纵坐标轴，单击“图表工具”|“格式”|“当前所选内容”分组中的“设置所选内容格式”按钮，打开“设置坐标轴格式”窗格，在坐标轴选项中的边界中设置最小值为“-1.0E6”、最大值设置为“7.0E6”，设置显示单位为“千”、选中“在图表上显示单位标签”复选框，设置刻度线中的主刻度线类型为“外部”；
步骤2：选中“横坐标轴”，在“设置坐标轴格式”窗格中设置“标签”为“无”。
③步骤1：单击“图表工具”|“设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在列表中选中“数据标签”级联菜单中的“其他数据标签选项”命令，打开“设置数据标签格式”窗格；
步骤2：在标签选项中选中“Y值”复选框，在标签位置中选中“靠上”，在数字中设置格式代码为“销售收入_0.00”，单击“添加”按钮。
④步骤1：选中2016年总计数据图，单击“图表工具”|“格式”|“形状样式”分组中的“形状填充”下拉按钮，在列表中选中“蓝色,个性色1”；
步骤2：选中2017年总计数据图，单击“图表工具”|“格式”|“形状样式”分组中的“形状填充”下拉按钮，在列表中选中“橙色,个性色2”；
步骤3：选中2018年总计数据图，单击“图表工具”|“格式”|“形状样式”分组中的“形状填充”下拉按钮，在列表中选中“灰色,个性色3”。
⑤单击“图表工具”|“设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在列表中选中“图例”级联菜单中的“底部”命令。
⑥单击“图表工具”|“设计”|“图表样式”分组中的其他按钮，在列表中选中一种样式（例如：样式7）。
6．【解析】①步骤1：选中“银行交易明细”工作表中的A3:F655单元格区域，单击“插入”|“表格”分组中的“数据透视表”按钮，打开“创建数据透视表”对话框，在选择放置数据透视表的位置中选中新工作表，单击“确定”按钮；
步骤2：将新插入的工作表命名为“费用支出统计”；
步骤3：拖动“交易日期”到“行”中，删除自动显示在“行”中的“季度”字段，拖动“收支项目”到“列”中；
步骤4：选中列标签中的“个人所得税”、“工资及奖金”、“社保、”和“住房公积金”，在选择的单元格上单击鼠标右键，在弹出菜单中单击“筛选”级联菜单中的“仅保留所选项目”命令；
步骤5：单击“数据透视表工具”|“设计”|“布局”分组中的“报表布局”下拉按钮，在列表中单击“以表格形式显示”命令。
步骤6：在“个人所得税”单元格上单击鼠标右键，然后单击“移动”级联菜单中的“将‘个人所得税’移至末尾”；
步骤7：拖动“支出”到“值”中。
②步骤1：双击A3单元格将内容修改为“支出金额”，双击C3单元格将内容修改为“支出项目”，双击A4单元格将内容修改为“年度”，双击B4单元格将内容修改为“月份”，选中G4单元格在地址栏中修改内容为“为员工支出总计”；
步骤2：在“行”中的“年”字段上单击下拉箭头，在列表中单击“字段设置”，打开“字段设置”对话框，选中“布局和打印”选项卡，选中“以表格形式显示项目标签”按钮，单击“确定”按钮；
③选中C5:G40单元格区域，单击“开始”|“数字”分组中的对话框启动器，打开“设置单元格格式”对话框，在分类中选中“数值”，在小数位数中输入“2”，选中“使用千位分隔符”复选框，单击“确定”按钮。
④步骤1：选中透视表，单击“数据透视表工具”|“设计”|“数据透视表样式”分组中的其他按钮，在列表中选中一种样式（例如：浅蓝，数据透视表样式浅色9）；
步骤2：适当调整表中字体和字号大小，调整列宽至表数据正常显示。
⑤步骤1：在数据透视表中单击鼠标右键，在弹出菜单中单击“数据透视表选项”命令，打开“数据透视表选项”对话框；
步骤2：选中“布局和格式”|“格式”中的“对于空单元格，显示”复选框，在输入框中输入“0”，单击“确定”按钮。
保存并关闭excel文档。
视频讲解
真题练习37-Excel电子表格_哔哩哔哩_bilibili
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
