真题练习19-Excel电子表格-全国计算机等级考试一级计算机基础及MS Office应用考试【汪老师的个人课堂】

视频解析

真题练习19-Excel电子表格_哔哩哔哩_bilibili

题库下载

全国计算机等级考试题库下载（用电脑下载安装）：请点击

题目要求

第19套

小赵是一名参加工作不久的大学生。他习惯使用Excel表格来记录每月的个人开支情况，在2013年底，小赵将每个月各类支出的明细数据录入了文件名为“Excel素材.xlsx”的Excel工作簿文档中。请你根据下列要求帮助小赵对明细表进行整理和分析：

1．在考生文件夹下，将“Excel素材.xlsx”文件另存为“Excel.xlsx”（“.xlsx”为扩展名），后续操作均基于此文件，否则不得分。

2．在工作表“小赵的美好生活”的第一行添加表标题“小赵2013年开支明细表”，并通过合并单元格，放于整个表的上端、居中。

3．将工作表应用一种主题，并增大字号，适当加大行高列宽，设置居中对齐方式，除表标题“小赵2013年开支明细表”外，为工作表分别增加恰当的边框和底纹以使工作表更加美观。

4．将每月各类支出及总支出对应的单元格数据类型都设为“货币”类型，无小数、有人民币货币符号。

5．通过函数计算每个月的总支出、各个类别月均支出、每月平均总支出；并按每个月总支出升序对工作表进行排序。

6．利用“条件格式”功能：将月单项开支金额中大于1000元的数据所在单元格以不同的字体颜色与填充颜色突出显示；将月总支出额中大于月均总支出110%的数据所在单元格以另一种颜色显示，所用颜色深浅以不遮挡数据为宜。

7．在“年月”与“服装服饰”列之间插入新列“季度”，数据根据月份由函数生成，例如：1至3月对应“1季度”、4至6月对应“2季度”……

8．复制工作表“小赵的美好生活”，将副本放置到原表右侧；改变该副本表标签的颜色，并重命名为“按季度汇总”；

9．通过分类汇总功能，按季度升序求出每个季度各类开支的月均支出金额。

10．以分类汇总结果为基础，创建一个带数据标记的折线图，以季度为系列对各分类的季度平均支出进行比较，给每类的“最高季度月均支出值”添加数据标签，并将该图表放置在一个名为“图表”的新工作表中。

文字解析

第19组

1．【解析】在文件夹下打开素材文件“Excel素材.xlsx”，在“文件”选项卡下单击“另存为”按钮，打开“另存为”对话框，选择文件存放位置为考生文件夹，“保存类型”为“Excel工作簿（*.xlsx）”，“文件名”为“Excel.xlsx”，单击“保存”按钮关闭对话框。

2．【解析】打开“小赵的美好生活”工作表，在A1单元格输入“小赵2013年开支明细表”，选中A1:M1单元格区域，在“开始”|“对齐方式”分组中单击“合并后居中”按钮，完成单元格的合并与居中。

3．【解析】选中任意单元格，在“页面布局”选项卡下“主题”分组中单击“主题”下拉菜单，在下拉列表选项中选择任意一种主题样式即可。

选中工作表所有内容区域，在“开始”|“字体”分组中“字号”选框内调大字号。

选中工作表所有内容区域，在“开始”|“单元格”分组中单击“格式”下拉按钮，在下拉列表选项中选择“列宽”，打开“列宽”对话框，在“列宽”中输入适当的值，单击“确定”按钮。

选中除标题外的单元格数据区域，在“开始”|“字体”分组中单击右下角的启动器，打开“设置单元格格式”对话框。

在“边框”分组“线条”选项列表中任意选择一种线条样式和颜色，在“边框”单击四周框线及其单元格内部框线，在预览框中查看效果；在“设置单元格格式”对话框中“填充”选项卡下任意选择一种背景颜色，单击“确定”按钮。

4．【解析】选中每月各类支出及总支出对应的单元格数据，右键单击鼠标，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，在“数字”选项卡下选择“货币”，小数位数为0，在“货币符号（国家/地区）”中选择人民币符号，单击“确定”按钮。

5．【解析】（1）在M3单元格内输入公式“=SUM(B3:L3)”，然后按<Enter>键完成一月份总支出的计算。

选中M3单元格，将光标定位在M3单元格的右下角，光标会变成细黑十字状的智能填充句柄，一直拖动到M14单元格完成其他月份总支出的计算。

（2）在N2单元格内输入文字“月均总支出”，在N3单元格内输入公式“=AVERAGE(b3:L3)” ，然后按<Enter>键完成一月份总支出的计算。

选中N3单元格，将光标定位在N3单元格的右下角，光标会变成细黑十字状的智能填充句柄，一直拖动到N14单元格完成其他月份总支出的计算，并将此列设置与其他列相同的边框和底纹。

（3）在B15单元格内输入带函数公式“=AVERAGE(B3:B14)”，然后按<Enter>键完成“服装服饰月均开销”的计算。

选中B15单元格，将光标定位在B15单元格的右下角，光标会变成细黑十字状的智能填充句柄，一直拖动到M15单元格完成其他支出月均开销和每月平均总支出。

（4）选中A3:M14单元格区域，在“数据”|“排序和筛选”分组下单击“排序”按钮，打开“排序”对话框，在“主要关键字”中选择“总支出”、“排序依据”选择“数值”、“次序”为“升序”，单击“确定”按钮。

6．【解析】（1）选中工作表中B3:L14单元格区域，在“开始”|“样式”分组中单击“条件格式”下拉按钮，在下拉列表选项中选择“突出显示单元格规则”“其它规则”，打开“新建格式规则”对话框。

在“选择规则类型”中选择“只为包含以下内容的单元格设置格式”，在“编辑规则说明”中依次选择“单元格值”、“大于”，在最后一个输入框中输入数值1000，单击“格式”按钮，打开“设置单元格格式”对话框。

在“字体”选项卡下为字体设置一种醒目的颜色，在“填充”选项卡下为背景设置一种不遮挡数据的颜色，依次单击“确定”按钮。

（2）选中N3:N14单元格区域。在“开始”|“样式”分组中单击“条件格式”下拉按钮，在下拉列表选项里面选择“突出显示单元格规则”中的“大于”命令，打开“大于”对话框。

在“为大于以下值的单元格设置格式”内输入“=$M$15*1.1”。在“设置为”中选择“自定义格式”，打开“设置单元格格式”对话框，在“填充”选项卡下“背景色”中选择一种颜色，要求颜色深浅不遮挡原数据，依次单击“确定”按钮。

7．【解析】（1）将光标定位在“服装服饰”列任一单元格，单击鼠标右键，在弹出的快捷菜单中选择“插入”命令。

打开“插入”对话框，选择“整列”，单击“确定”按钮。在B2单元格中输入文字“季度”。

（2）在B3单元格内输入带函数公式“=LOOKUP(MONTH(A3),{1;4;7;10},{"1";"2";"3";"4"}&"季度")”，然后按<Enter>键。

选中B3单元格，将光标定位在B3单元格的右下角，光标会变成细黑十字状的智能填充句柄，一直拖动到M14单元格。

8．【解析】（1）在工作表“小赵的美好生活”表名称上单击鼠标右键，在弹出的快捷菜单中选择“移动或复制”命令，打开“移动或复制工作表”对话框，在“下列选定工作表之前”选项中选择“移至最后”，勾选“建立副本”，单击“确定”按钮。

（2）在工作表“小赵的美好生活（2）”表名称上单击鼠标右键，在弹出的快捷菜单中选择“重命名”，然后输入文字“按季度汇总”，按<Enter>键。

（3）在工作表“按季度汇总”表名称上单击鼠标右键，在弹出的快捷菜单中选择“工作表标签颜色”命令，在颜色列表中选择任意一种颜色。

（4）打开“按季度汇总”工作表，选中“月均开销”行，单击鼠标右键，在弹出的快捷菜单中，选择“删除”命令，即可删除该行。

9．【解析】（1）选中“按季度汇总”工作表的所有内容区域，在“数据”选项卡下“排序和筛选”分组中单击“排序”按钮，打开“排序”对话框，选择“主要关键字”为“季度”、“排序依据”为“数值”、“次序”为“升序”，单击“确定”按钮。

（2）在“数据”选项卡下“分级显示”分组中单击“分类汇总”按钮，打开“分类汇总”对话框。

在“分类字段”中选择“季度”，在“汇总方式”中选择“平均值”，在“选定汇总项中”勾选“服装服饰”、“饮食”、“水电气房租”、“交通”、“通信”、“阅读培训”、“社交应酬”、“医疗保健”、“休闲旅游”、“个人兴趣”、“公益活动”，然后勾选“替换当前分类汇总”和“汇总结果显示在数据下方”，单击“确定”按钮。

10．【解析】（1）单击工作表下方的“新工作表”按钮，插入一个新的工作表，在表名称上单击鼠标右键，在弹出的快捷菜单中选择“重命名”命令，输入文字“折线图”，然后按<Enter>键。

（2）选中“按季度汇总”工作表的B2:M18单元格，在“插入”选项卡下“图表”分组中，单击“插入折线图或面积图”按钮，在下拉列表选项中选择“带数据标记的折线图”，选中折线图适当放大。选中折线图在“图标工具/设计”选项卡下“数据”分组中单击“切换行/列”按钮可设置水平轴标签为各类开支。

（3）选中折线图，在“图标工具-设计”选项卡下“数据”分组中单击“选择数据”按钮。

打开“选择数据源”对话框，在“图例项”列表框中选中除季度平均值外的字段，然后单击“删除”按钮，最后单击“确定”按钮。

（4）对比折线图，选择“服装服饰”开支的最高季度月均支出值，在“图表工具/设计”选项卡下“图表布局”分组中单击“添加图表元素”下拉按钮，在下拉列表选项中选择“数据标签/其他数据标签选项”。

打开“设置数据标签格式”窗口，标签选项内勾选“系列名称”“类别名称”和“值”选项，在“标签位置”中选择适当的位置，单击“关闭”按钮。

同理，设置其它开支的数据标签。

（5）折线图完成后，选中折线图，按下<Ctrl+X>组合键剪切，到“折线图”工作表内按下<Ctrl+V>组合键粘贴，适当调整折线图的大小和位置。