实例十公司日常费用分析

实例十公司日常费用分析

10.1案例简介

10.1.1 案例需求与展示

吴红是一家公司财务部的员工，每天要面对很多账务报销信息，对这些数据的统计、分析是她的工作之一。现在要根据公司1-3月份的日常费用明细表统计第一季度公司的财务报销情况，具体要求如下：

1）将各部门的日常费用情况单独生成一张表格。

2）统计各部门日常费用的平均值。

3）对各部门的日常费用情况按总费用的从高到低进行排序。效果如图10-1所示。

图10-1公司日常费用情况分析效果图

10.1.2知识技能目标

本案例涉及的知识点主要有：数据透视表的创建、数据透视表的编辑、数据透视表的值字段设置、数据透视表的筛选与排序；

知识技能目标：

掌握Excel中创建数据透视表。
掌握利用数据透视表的对数据进行计算分析。
掌握数据透表的美化。

10.2案例实现

10.2.1创建数据透视表

数据透视表是一种对大量数据快速汇总和建立交叉表的交互式表格，用户可以转换行以查看数据源的不同汇总结果，可以显示不同页面以筛选数据，还可以根据需要显示区域中的明细数据。

本实例中，创建数据透视表的操作步骤如下：

1）打开素材文件夹中的工作簿文件“日常费用明细表.xlsx”。

2）选中表格中的任一单元格，切换到“插入”选项卡，单击“表格”功能组中的“数据透视表”按钮，如图10-2所示。弹出“创建数据透视表”对话框。

图10-2 “数据透视表”按钮

3）保持默认的表/区域的值不变，选中“选择放置数据透视表的位置”栏中“新工作表”单选按钮，如图10-3所示。单击“确定”按钮，返回工作表，即可进入数据透视表的设计环境。

图10-3 “创建数据透视表”对话框

4）在“数据透视表字段”窗格中，将“选择要添加到报表的字段”列表框中的“费用类别”拖动到“列”字段，将“经办人”拖动到“行”字段，将“金额”拖动到“值”字段。如图10-4所示。即可实现数据透视表的创建，如图10-5所示。

图10-4 “数据透视表字段”窗格

图10-5 数据透视表创建完成后效果

【创建数据透视表】视频教程：

10.2.2添加报表筛选页字段

Excel提供了报表筛选页字段的功能，通过该功能用户可以在数据透视表中快速显示位于筛选器中的字段的所有信息，添加报表筛选页字段后生成的工作表会自动以字段信息命名，便于用户查看数据信息。本实例中，要将各部门的日常费用情况单独生成表格，可使用报表筛选字段的功能，操作步骤如下：

1）在“数据透视表字段”窗格中，将“部门”拖动到“筛选”字段。

2）选中数据透视表中的任一含有内容的单元格。切换到“数据透视表工具|分析”选项卡，在“数据透视表”功能组中单击“选项”箭头按钮，在下拉列表中选择“显示报表筛选页”命令，如图10-6所示。

图10-6 “显示报表筛选页”命令

3）弹出“显示报表筛选页”对话框，选择要显示的报表筛选字段“部门”，如图10-7所示。单击“确定”按钮，返回工作表中，Excel自动生成“行政部”、“客服部”、“生产部”、“维修部”、“销售部”五张工作表，如图10-8所示。切换至任意一张工作表，均可查看员工的报销费用。

图10-7 “显示报表筛选页”对话框

图10-8 报表筛选字段后效果图

10.2.3 增加计算项

Excel提供了创建计算项的功能，计算项是在已有的字段中插入新项，是通过对该字段现有的其他项计算后得到的。在选中数据透视表中某个字段标题或其下的项目时，可以使用“计算项”功能。需要注意的是，计算项只能应用于行、列字段，无法应用于数字区域。

本实例中需要在数据透视表中体现各部门的平均费用，可通过增加计算项实现。操作步骤如下：

1）在“数据透视表字段”窗格中，单击“行”字段列表中的“经办人”下拉按钮，从下拉列表中选择“删除字段”命令，如图10-9所示，将“经办人”字段从“行”字段的列表中移除。

图10-9 “删除字段”命令

2）将“部门”字段从“筛选”列表移至“行”列表中。

3）选中单元格F4，切换到“数据透视表工具|分析”选项卡，单击“计算”功能组的“字段、项目和集”下拉按钮，从下拉列表中选择“计算项”命令，如图10-10所示。

图10-10 “计算项”命令

4）在弹出的对话框中，设置名称为“平均费用”，在“公式”文本框中输入“=average(”，在“字段”列表框中选择“费用类别”，在“项”列表框中选择“办公费”，单击“插入项”按钮。

5）在“公式”显示的“办公费”后输入逗号，在“项”列表框中选择“差旅费”，单击“插入项”按钮。用同样的方法继续添加“交通费”项、“宣传费”项、“招待费”项。如图10-11所示。添加完成后，单击“确定”按钮，返回工作表，可看到添加的“平均费用”计算项，如图10-12所示。

图10-11 “在‘费用类别’中插入计算字段”对话框

图10-12 “平均费用”计算项添加完成效果图

【增加计算项】视频教程：

10.2.4 数据透视表的排序

在已完成设置的数据透视表中还可以招待排序命令。实例中要求对分析出的数据按“总计”金额的从高到低进行排序，具体操作如下：

1）将鼠标定位于数据透视表的任意单元格中，单击“行标签”按钮，在弹出的快捷菜单中选择“其他排序选项”命令，如图10-13所示。弹出“排序（部门）”对话框。

图10-13 “其他排序选项”命令图10-14 “排序（部门）”对话框

2）在“排序选项”栏中选择“降序排序（Z到A）依据”单选按钮，并从其下拉列表框中选择“求和项：金额”选项，如图10-14所示。

3）单击“确定”按钮，即可实现数据透视表中数据按“总计”金额从高到低的排序，效果如图10-15所示。

图10-15 “降序”排序后效果图

10.2.5 数据透视表的美化

为了增强数据透视表的视觉效果，用户可以对数据透视表进行样式选择、值字段设置等操作。具体操作如下：

1）将鼠标定位于数据透视表的任意单元格中，切换到“数据透视表工具|设计”选项卡，单击“数据透视表样式”功能组的“其他”按钮，从下拉列表中选择“浅绿，数据透视表样式浅色14”选项，如图10-16所示。

图10-16 “数据透视表样式”列表

2）此时可以在工作表中看到应用了指定数据透视表样式后的表格，如图10-17所示。

图10-17 应用数据透视表样式后效果图

3）在“数据透视表工具|设计”选项卡中，勾选“数据透视表样式选项”功能组中的“镶边列”复选框、“镶边行”复选框，如图10-18所示，实现对数据透视表中的行、列镶边效果。

图10-18 “数据透视表样式选项”功能组

4）双击单元格A4（即行标签单元格），修改其文本内容为“部门”，双击单元格B3（即列标签单元格），修改其文本内容为“费用”。

5）在“数据透视表字段”窗格中，单击“求和项：金额”箭头按钮，从弹出的快捷菜单中选择“值字段设置”命令，如图10-19所示。打开“值字段设置”对话框，如图10-20所示。

图10-19 “值字段设置”命令图10-20 “值字段设置”对话框

6）单击“数字格式”按钮，弹出“设置单元格格式”对话框，在“数字”选项卡中选择“数值”选项，设置“小数位数”为“2”，勾选“使用千位分隔符”，如图10-21所示。单击两次“确定”按钮，返回工作表，完成数据透视表中数值单元格的格式设置。

图10-21 “设置单元格格式”对话框

7）选中整个数据透视表，切换到“开始”选项卡，单击“对齐方式”功能组中的“居中”按钮，对齐表格中的数据。效果如图10-1所示。

8）单击“保存”按钮，完成实例的制作。

【美化数据透视表】视频教程：

10.3案例小结

本案例通过分析公司日常费用情况讲解了Excel中数据透视表的创建、数据透视表的值字段设置、数据透视表的数据排序等内容。在实际操作中大家还需要注意以下问题：

（1）数据透视表是从数据库中生成的动态总结报告，其中数据库可以是工作表中的，也可以是其他外部文件中的。数据透视表用一种特殊的方式显示一般工作表的数据，能够更加直观清晰地显示复杂的数据。

需要注意的是，并不是所有的数据都可以用于创建数据透视表，汇总的数据必须包含字段、数据记录和数据项。在创建数据透视表时一定要选择Excel能处理的数据库文件。

（2）在Excel2016中提供了“推荐的数据透视表”功能，此功能可以根据所选表格内容来列举不同字段布局的数据透视表，如图10-22所示。用户可以根据自己的实际需要来选择合适的数据透视表。

图10-22 “推荐的数据透视表”对话框

（3）在“数据透视表字段列表”窗格的下方有四个区域，名称分别为“筛选”、“列”、“行”、“值”。分别代表了数据透视表的四个区域。

对于数值字段，默认会进入“值”列表框中。对于文本字段默认会进入“行”列表框中。如需改变默认的归类，需要手工拖动字段。

（4）数据透视图是一个和数据透视表相链接的图表，它以图形的形式来展现数据透视表中的数据。数据透视图是一个交互式的图表，用户只需要改变数据透视图中的字段就可以实现不同数据的显示。当数据透视表中的数据发生变化时，数据透视图也将随之发生变化，数据透视图改变时，数据透视表也将随之发生变化。以本实例中的数据透视表数据为例，数据透视图的创建操作如下：

1）将鼠标定位于数据透视表的任意单元格中，切换到“数据透视表工具|分析”选项卡，在“工具”功能组中单击“数据透视图”按钮，如图10-23所示。