视频讲解

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

题库下载

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

题目要求

小刘接到上级指派整理有关减免税政策清单，按照下列要求帮助小刘完成相关的整理、统计和分析工作。

1．打开考生文件夹下的素材文档Excel.xlsx（“.xlsx”为文件扩展名），后续操作均基于此文件，否则不得分。操作过程中，不得随意改变工作表中数据的顺序。

2．将工作簿“代码对应.xlsx”中的工作表Sheet1插入到Excel.xlsx中工作表“政策目录”的右侧，将Sheet1重命名为“代码”，更改标签颜色为标准蓝色，不显示该工作表网格线。

3．为工作表“政策目录”的数据列表区域A3:I641套用一个表格格式，将其字号设为9磅，其中的F:I列设为自动换行、A:E列数据垂直水平均居中对齐。将工作表“代码”中第2行的标题格式应用到工作表“政策目录”单元格A1中的标题，并令其在整个数据列表上方合并居中。

4．在工作表“政策目录”的“序号”列中输入顺序数字序号1、2、3……，并以数值型的001、002、003……单元格格式显示。

5．参照工作表“代码”中的代码与分类的对应关系，获取相关分类信息并填入工作表“政策目录”的C、D、E三列。其中“减免性质代码”从左至右其位数与分类项目的对应关系如下：

减免性质代码	项目名称
第1、2位	收入种类
第3、4位	减免政策大类
第5、6位	减免政策小类

6．在工作表“政策目录”的F和G列之间插入一个空白列，列标题为“年份”。在F列“政策名称”中，相关政策的发布年份信息包含在括号“〔〕”中，如“财税〔2012〕75号”中的“2012”即为年份。通过F列信息获取政策发布年份，将其填写到“年份”列所对应的单元格中，并以“2012年”形式显示。如果F列“政策名称”中没有年份信息，则“年份”列所对应的单元格显示为空。自动调整“年份”列至合适的列宽。

7．显示隐藏的工作表“说明”，将其中的全部内容作为工作表“政策目录”标题行“减免税政策目录及代码”的批注，设置批注字体颜色为绿色，并隐藏该批注。

8．设置工作表“政策目录”窗口视图，保持第1～3行、第A:E列在滚动阅读数据时总是可见。

9．参照示例文档“数据透视示例1.png”，为工作表“政策目录”中的每类“减免政策大类”生成结构相同的数据透视表，每张数据透视表的数据均自A3单元格开始，要求如下：

① 分别以减免政策大类的类名名称作为工作表的表名。

② 表中包含2006～2015（含）十年间每类“收入种类”下按“减免政策小类”细分的减免政策数量，并将其中的“增值税”下细类折叠。

③ 按工作表“代码”中“对应的收入种类”所示顺序对数据透视表进行排序。

④ 依据示例文档中所示，分别修改行列标签名称。

10．参照示例文档“数据透视示例2.png”，创建名为“数据透视总表”的数据透视表，该数据透视表位于A3单元格。在该数据透视表中列示2006～2015（含）十年间按“收入种类”划分的减免政策数量，年份自左向右从高到低排序，政策数量按“总计”列自上而下由高到低排序，且只显示数量总计前10的收入种类。在此透视表基础上生成数据透视图，比较各类收入的政策数量。

文字解析

第23组

1．【解析】打开考生文件夹下的“Excel.xlsx”文件。

2．【解析】步骤1：进入考生文件夹打开“代码对应.xlsx”工作表，在Sheet1工作表上单击右键，在弹出的菜单中选择“移动或复制工作表”命令，打开“移动和复制工作表”对话框，在“工作簿”下选择“Excel.xlsx”，在“下列选定工作表之前”中选择“移至最后”，单击“确定”按钮。

步骤2：进入“Excel.xlsx”工作簿，在“Sheet1”工作表上单击右键，在弹出的菜单中选择“重命名”命令，将工作表名称改为“代码”。

步骤3：在“代码”工作表上单击右键，在弹出的菜单中选择“工作表标签颜色”命令，将工作表标签颜色设置为标准“蓝色”。

步骤4：单击取消“视图”|“显示”分组中的“网格线”复选框。

3．【解析】步骤1：选中A3单元格，然后<Ctrl+A>组合键，选中A3:I641的区域，然后单击“开始”|“样式”分组中的“套用表格样式”下拉按钮，在下拉列表中选择“白色，表样式浅色1”样式，单击“确定”按钮；单击“开始”选项卡中“字体”分组中的字号下拉框，选中“9”。

步骤2：选中F列到I列，然后单击“对齐方式”分组中的“自动换行（）”按钮，完成自动换行设置；选中A列到E列，然后分别单击“对齐方式”分组中的“垂直居中”和“居中”按钮，完成数据垂直水平居中对齐。

步骤3：单击“代码”工作表中第2行的标题，选择“开始”选项卡中“剪贴板”分组中的“格式刷”按钮，然后单击“政策目录”工作表中的单元格A1。此时会自动将A1至H1合并为一个单元格并设置为“代码”中第2行的标题格式。

步骤4：在选中这个单元格情况下，单击“开始”选项卡中“对齐方式”分组中的“合并后居中”按钮，取消合并；然后选中A1单元格，再次单击“格式刷”，然后单击“I1”单元格，将I1格式设置为与A1单元格相同格式；再选中A1至I1单元格，单击“合并后居中”按钮。

4．【解析】步骤1：在A4单元格中输入“1”，在A5单元格中输入“2”，然后选中A4单元格和A5单元格，双击A5单元格右下方的填充句柄，填充完成剩余的单元格。

步骤2：选中A列并单击右键，在下拉列表中选择“设置单元格格式”命令，在对话框中的“数字”选项卡下选择“自定义”，在类型下面输入“000”，单击“确定”按钮完成数字格式设置。

5．【解析】在C4单元格中输入“=VLOOKUP(LEFT([@减免性质代码],2),代码!$B$5:$C$22,2,FALSE)”。

在D4中输入“=VLOOKUP(MID([@减免性质代码],3,2),代码!$E$5:$F$15,2,FALSE)”。

在E4中输入“=VLOOKUP(MID([@减免性质代码],5,2),代码!$H$5:$I$49,2,FALSE)”。

然后选中C4到E4，双击E4单元格右下角的填充句柄，完成填充。

6．【解析】步骤1：将鼠标选中G列，然后单击右键，在下拉列表中选择“插入”命令，在F和G列插入一个空白列。

步骤2：在G3单元格中输入“年份”，在G4单元格中输入“=IFERROR(TEXT(MID([@政策名称],FIND("〔",[@政策名称],1)+1,4),"0年"),"")”。

步骤3：再双击G4单元格右下角的填充句柄，完成填充。最后双击G和H列交界处，完成自动调整至合适列宽。

7．【解析】步骤1：在任意工作表标签上单击右键，在弹出的菜单中选择“取消隐藏”命令，在打开的“取消隐藏”对话框中，选中“说明”工作表，单击“确定”按钮，将隐藏的“说明”工作标显示出来。

步骤2：选中“政策目录”工作表中的标题“减免税政策目录及代码”，然后在“审阅”选项卡中，单击“批注”分组中的“新建批注”按钮，即可新建一个批注，然后将“说明”工作表的内容都复制到该批注中。

步骤3：选中批注中所有文字，然后单击右键，在下拉列表中选择“设置批注格式”命令，打开“设置批注格式”对话框，将字体颜色修改为“绿色”。

步骤4：单击“批注”选项卡中的“显示/隐藏批注”按钮，将批注隐藏。

8．【解析】选择F4单元格，单击“视图”|“窗口”分组中的“冻结窗格”下拉按钮，在下拉列表中选择“冻结拆分窗格”命令，这样设置之后第1～3行、第A:E列总是可见。

9．【解析】步骤1：选择“政策目录”中所有有数据的区域，然后单击“插入”|“表格”分组中的“数据透视表”按钮，单击“确定”按钮。

步骤2：将“数据透视表字段列表”中“减免政策大类”拖至“报表筛选”，将“收入种类”拖至行标签，将“减免政策小类”也拖入行标签，将“年份”拖入“列标签”，将“减免项目名称”拖入数值。

步骤3：单击“列标签”倒三角，将鼠标移至“标签筛选”上，选择级联菜单中的“介于”，在第一个文本框中输入“2006”，在第二个文本框中输入“2016”。设置包含2006～2015年。单击“增值税钱”前的“减号”，将其系类折叠。

步骤4：单击“代码”工作表，选中C5到C22单元格，单击“文件”|“选项”命令，打开“Excel选项”对话框，单击“高级”按钮，选择常规分组中“创建用于排序和填充序列的列表”后的“编辑自定义列表”按钮。

步骤5：在“自定义序列”对话框中单击“导入”按钮，然后再单击“确定”按钮，单击“确定”完成自定义序列导入。

步骤6：返回之前创建的数据透视表，单击“行标签”右侧的倒三角，选择下拉列表中的“其他排序选项”，打开“排序（收入种类）”对话框，选择“升序排列”。

步骤7：单击“其他选项”按钮，打开“其他排序选项（收入种类）”对话框。取消“每次更新报表时自动排序”，在“主关键字排序次序”下组合框中选择刚刚添加的排序规则。连续两次单击“确定”按钮，完成排序。

步骤8：将“计数项：减免项目名称”的名称修改为“政策数量”，将“列标签”修改为“年度”，将“行标签”修改为“分类”，完成数据透视表设置。

步骤9：将该数据透视表复制10份，在第1份数据透视表中的“（全部）”后倒三角中选择“促进区域发展”，并将该工作表的名称修改为“促进区域发展”。采用类似方法设置其他工作表。

9．【解析】步骤1：选择“政策目录”中所有数据区域，单击“插入”|“表格”分组中的“数据透视表”按钮，打开“创建数据透视表”对话框，单击“确定”完成数据透视表创建。

步骤2：在创建的新工作表标签上右击，选择“重命名”按钮，输入“数据透视总表”，将“年份”拖入列标签，将“收入种类”拖入“行标签”，将“政策名称”拖入“数值”。

步骤3：单击列标签后倒三角，将鼠标移至“标签筛选”上，在级联菜单中选择“介于”，打开“标签筛选（年份）”对话框。

在第一个文本框中输入“2006”，在第2个文本框中输入“2016”，单击“确定”按钮。完成显示年份在2006～2015年之间。

步骤4：继续单击列标签后倒三角，选择“其他排序选项”，打开“排序（年份）”对话框，单击“降序排列（Z到A）依据”，选择组合框中的“年份”，单击“确定”按钮。

完成年份从左到右从高到低排序。

步骤5：单击“行标签”后倒三角，将鼠标移至“值筛选”上，单击级联菜单中“前10项”单击“确定”按钮，筛选出前十的收入种类。

步骤6：继续单击“行标签”后倒三角，选择“其他排序选项”，打开“排序（收入种类）”对话框，选择“降序排列（Z到A）依据”选项，选择组合框中的“计数项：政策名称”，单击“确定”按钮。完成政策数量从上到下由高到低排序。

步骤7：单击“数据透视表工具”|“分析”|“工具”分组中“数据透视图”按钮，打开“插入图标对话框”，选择“柱形图”中的“堆积柱形图”，适当调整数据透视图的大小，单击选中“数据透视图工具”|“设计”选项卡中“图表样式”分组中的一种合适样式（例如：样式11），完成数据透视图生成和设置。

步骤8：保存并关闭“Excel.xlsx”文档。