C14-【计算机二级】Excel操作题(14)统计分析减免税政策清单

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

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

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

• 考点1：移动工作表

②更改工作表标签颜色和名字

③取消显示工作表的网格线

或者在视图选项卡下取消勾选网格线

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

①为工作表“政策目录”的数据列表区域A3:I641套用一个表格格式

• 考点2：套用表格格式

②将其字号设为9磅

• 考点3：设置字体格式

③F:I列设为自动换行、A:E列数据垂直水平均居中对齐

• 考点4：设置段落格式

Ⅰ、自动换行

Ⅱ、垂直水平均居中对齐

④将工作表“代码”中第2行的标题格式应用到工作表“政策目录”单元格A1中的标题，并令其在整个数据列表上方合并居中

• 考点5：格式刷

此时已经应用上了“合并后居中”

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

• 考点6：设置单元格格式

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

• 考点7：VLOOKUP函数和MID函数嵌套

①提取出“减免性质代码”中对应的代码

Ⅰ、收入种类

Ⅱ、减免政策大类

Ⅲ、减免政策小类

②参照工作表“代码”中的代码与分类的对应关系，获取相关分类信息

剩余两列数据第一个参数都是复制刚才第①步中提取的代码，第二个参数则是更改对应的查询列

匹配完成

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

①在工作表“政策目录”的F和G列之间插入一个空白列，列标题为“年份”

②通过F列信息获取政策发布年份，将其填写到“年份”列所对应的单元格中，并以“2012年”形式显示。如果F列“政策名称”中没有年份信息，则“年份”列所对应的单元格显示为空。

• 考点8：FIND函数和MID函数嵌套

①查找代表年份的字符从第几个单元格开始

经观察，发现没有年份都是显示在“〔〕”当中的，因此需要用FIND函数找到“〔”的位置，紧跟着的四个字符就是年份

查找完毕

②提取年份

更改显示方式

如果F列“政策名称”中没有年份信息，则“年份”列所对应的单元格显示为空

• 考点9：定位条件

自动调整列宽

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

①显示隐藏的工作表

• 考点10：取消隐藏工作表

②复制“说明”工作表中的内容

借助记事本，将内容复制为纯文本内容

将纯文本内容作为工作表“政策目录”标题行“减免税政策目录及代码”的批注

③更改字体颜色

• 考点11：更改批注字体颜色

批注文本的更改需要在快捷菜单栏中设置批注格式处进行修改

④隐藏该批注

• 考点12：隐藏批注

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

• 考点13：冻结窗格

要求保持第1~3行、第A:E列在滚动阅读数据时总是可见，点击F4单元格，在视图选项卡下点击冻结窗格

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

数据透视示例1

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

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

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

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

• 考点14：创建数据透视表

按照题目要求，将“减免政策大类”拖拽到筛选，“年份”拖拽到列，“收入种类”和“减免政策小类”拖拽到行，“减免政策小类”拖拽到值。

筛选2006-2015年的数据

“增值税”下细类折叠

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

• 考点15：给数据透视表排序

给数据透视表排序不同于给一般的表格排序，在开始选项卡下的排序按钮不可用

应用刚才设置好的排序序列

修改标题名称

• 考点16：分别以减免政策大类的类名名称作为工作表的表名

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

示例图

仅勾选2006-2015年

对年份从左到右进行降序排序

政策数量按“总计”列自上而下由高到低排序

只显示数量总计前10的收入种类

修改 标题名称

生成数据透视图

Ctrl+S快速保存