第14章 费用使用情况分析

1、ExcelExcel数据透视表数据透视表入门与实战入门与实战清华大学出版社清华大学出版社第十四章第十四章费用使用情况分析本章要点定义单元格名称创建动态的数据透视表添加计算字段更改标签字段名称更改报表样式和布局将没有超支数据底纹取消使用VBA禁止字段操作保护数据源结构不被更改共享数据透视表上传OneDrive备份共享学习目标学习目标在本章中，我们将通过使用名称、设置数据格式、添加字段和套用数据透视表样式、条件规则和VBA代码等知识来制作一个费用使用情况分析的数据透视表，帮助用户更好的、更灵活的使用基础知识来制作出完整报表。同时，达到举一反三的目的。14.1 案例制作效果和思路在分析金额的使用情况

2、时，主要是查看超支或没有用的金额数据，从而分析出金额的配置合理性，如图14-1所示是制作的费用明细分析报表的部分效果。如图14-2所示是制作该案例的大体操作思路。14.1 案例制作效果和思路14.1 案例制作效果和思路如图14-2所示是制作该案例的大体操作思路。14.2 创建动态的数据透视表我们要对费用使用情况进行分析，首先需要创建一个数据透视表，而且这个数据透视表需要是动态的，然后对其进行结构布局和格式设置，所以，其中要使用到定义名称、套用表格样式和字段添加等相关知识。14.2.1 定义单元格名称要实时动态分析费用使用情况，就需使用到名称来获取已有和新添加的数据，其具体操作如下。14.2.1

3、 定义单元格名称14.2.2 创建动态的数据透视表单元格名称定义后，我们就可以根据名称来创建动态数据透视表，其具体操作如下。14.2.2 创建动态的数据透视表14.2.2 创建动态的数据透视表14.2.3 添加计算字段在实际中费用的使用情况，一定程度上可以从预算和实际开支之间的差额体现出来，所以，我们需要手动添加“超/省金额”字段，具体操作如下。14.2.3 添加计算字段14.2.4 更改标签字段名称我们的费用字段都只有一列，所以没有多列进行求和的操作，同时，添加字段的计算方式是减法，所以，在数据透视表的标题行中包含“求和项:”字样不太符合实际，可将其取消掉，具体操作如下。14.3 设置数据透

4、视表显示样式创建的数据透视表，虽然整体结构已显示完成，但看起来还是不够美观和专业，这时，还需要对其进行整体样式、布局结构以及数据字体字号的依次设置。14.3.1 更改报表样式和布局要对数据透视表进行美化和结构设置，这里主要从3方面入手，一是套用表格样式；二是取消数据透视表中分类汇总行；三是对数据透视表字体字号以及数字显示方式进行设置，其具体操作如下。14.3.1 更改报表样式和布局14.3.1 更改报表样式和布局14.3.1 更改报表样式和布局14.3.2 使用颜色来展示超值程度由于每个部门在实际的费用中会因为项目的不同而出现多出或少用的情况，这时为了更好的显示超预算情况，我们可以使用颜色来进

5、行区分，从而帮助我们在下次预算时能估算充分，具体操作如下。14.3.2 使用颜色来展示超值程度14.3.2 使用颜色来展示超值程度14.3.3 将没有超支数据底纹取消在三色刻度颜色换分数据范围时，明显将一些没有超支或低于预算的数据单元格划分为与预算持平的颜色，为了更好的分析数据，我们将其底纹取消，具体操作如下。14.3.3 将没有超支数据底纹取消14.4 保护数据透视表由于我们的数据透视表要进行共享，让其他相关人员也能查看到相应数据和分析结果，同时，要保证数据透视表的结构完整和数据安全，这时，可以对其进行相应的保护。14.4.1 使用VBA禁止字段操作数据结构确定后，为防止其他人员对字段进行添

6、加或减少，所以要对字段设置保护，禁止他人添加或删除字段操作，具体操作如下。14.4.1 使用VBA禁止字段操作14.4.1 使用VBA禁止字段操作14.4.1 使用VBA禁止字段操作14.4.1 使用VBA禁止字段操作14.4.1 使用VBA禁止字段操作14.4.2 保护数据源结构不被更改数据透视表的结构和分析结果很大程度上取决于数据源，因此，要对数据透视表进行保护，还需从数据源入手，但本例中数据源允许用户输入费用最新数据，这时，我们就可以通过设置可编辑区来解决，具体操作如下。14.4.2 保护数据源结构不被更改14.4.2 保护数据源结构不被更改14.4.2 保护数据源结构不被更改14.4.

7、2 保护数据源结构不被更改14.4.2 保护数据源结构不被更改14.5 共享和备份数据透视表我们要让局域网中的用户都能查看和添加数据，就需要将其共享。为了保证数据最初的样式和安全，我们可以在OneDrive上进行备份，同时让远方的人员能对数据透视表的数据进行查看和操作。14.5.1 共享数据透视表要让数据透视表共享到局域网中，首先需要对工作簿本身进行共享，然后才能在局域网中进行共享，下面我们就将工作簿进行共享，具体操作如下。14.5.1 共享数据透视表14.5.2 上传OneDrive备份共享为了防止工作簿的意外丢失，我们可以将其上传到OneDrive中，同时，也方便远方的人员查看和添加数据等，具体操作如下。14.5.2 上传OneDrive备份共享14.5.2 上传OneDrive备份共享14.6 案例制作总结和答疑在本章中，我们制作的费用使用情况数据