财经数据透视表的实际应用案例

利用数据透视表进行表格结构快速转换。可以应用数据透视表将二维表转换成一个数据清单，也可以把流水表转换成一个二维表，以方便不同场合的应用及查看。

1.二维表转化为数据清单

例3-5　把“二维表转化为数据清单.xlsx”工作簿中的二维表数据转化成数据清单，如图1-3-18所示。

图1-3-18　将二维表转化成数据清单

图3-1-19　选择商品销售数据区域

具体操作步骤如下。

（1）单击“数据透视表和数据透视图向导”图标，在打开的对话框中选中“多重合并计算数据区域”单选按钮，在所需创建的报表类型区域选中“创建数据透视表”单选按钮，单击“下一步”按钮；选中“创建单页字段”单选按钮，单击“下一步”按钮；在“选定区域”中输入“二维表转化为数据清单！$A$1：$G$16”，单击“添加”按钮，如图1-3-19所示，最后单击“完成”按钮，得到如图1-3-20所示的商品销售数据透视表。

（2）在“商品销售数据透视表”中双击最后的合计单元格，得到如图1-3-21形式的表格。

图1-3-20　商品销售数据透视表

图1-3-21　商品销售数据透视表

（3）对图1-3-21表格样式进行清除。选择图1-3-21所示表格中的数据，工具栏中出现“表格工具”选项卡，单击其下的“设计”选项卡，然后单击“表格样式”组中右下角的下拉按钮，展开“表格样式”对话框，单击“清除”按钮，即可清除1-3-21表格当前的样式。

（4）转换为普通区域。单击“表格工具”下的“设计”选项卡，单击“工具”组中的“转换为区域”按钮，打开“是否将表转化为普通区域”对话框，单击“是”按钮，即已经把数据透视表转化成了普通表格。删除表中的“页1”这一列，把行、列、值分别改成商品、月份、金额，即可将二维表转换为数据清单。

2.流水表转化为数据清单

例3-6　把“流水表转化成数据清单.xlsx”工作簿中的数据转化成数据清单，要求分公司一列，姓名一列，如图1-3-22所示。

图1-3-22　数据流水表变成数据清单

具体操作步骤如下。

（1）在原表中插入一列辅助列，可任意输入名字，如图1-3-23所示。

（2）单击“数据透视表和数据透视图向导”图标，在打开的对话框中选中“多重合并计算数据区域”单选按钮，在“所需创建的报表类型选项区域中”选中“创建数据透视表”单选按钮，单击“下一步”按钮；选中“创建单页字段”单选按钮，单击“下一步”按钮；将“选定区域”设置为$A$1：$E$17”，如图1-3-24所示，单击“添加”按钮，在生成的新工作表中得到数据透视表如图1-3-25所示。

图1-3-23　流水表新插入一列

图1-3-24　选择表格数据区域

图1-3-25　公司人事分布数据透视表(https://www.daowen.com)

（3）双击“公司人事分布数据透视表”最后的合计单元格，结果图1-3-26所示。参照例3-5中的第（4）步，将表转换成普通区域。最后，删去得到的普通表格中名为“页1”的最后一列，删去表格中名为“行”的第一列。

（4）选择空值删除大量空格。选中“B”列，按“Ctrl+G”组合键，打开如图1-3-27所示的“定位”对话框，单击“定位条件”按钮，在打开的对话框中选择“空值”，然后单击“确定”按钮，则B列所有空格被选中。选中空白单元格，单击鼠标右键，在弹出的快捷菜单中选择“删除”，打开“删除”对话框，选中“整行”单选按钮，单击“确定”按钮，如图1-3-28所示。

（5）进行自定义排序。单击“数据”选项卡下的“排序”按钮，打开“排序”对话框；设置“主要关键字”为“列”，设置“次序”为“升序”，选中“数据包含标题”复选按钮，然后单击“确定”按钮，如图1-3-29所示。在已排序的表中更改标题“列”名为“分公司”，“值”名为“姓名”，得到如图1-3-30所示分公司人员数据清单。

图1-3-26　公司人事分布数据透视表合计结果

图1-3-27　“定位”对话框

图1-3-28　删除空格单元列

3.添加自定义计算字段

用户可以在数据透视表中添加计算字段，以便得到新的字段和分析指标。计算字段是指在数据透视表中创建公式，对现有字段进行计算得到一个新的字段。计算字段即现有字段之间的计算结果。

例3-7　制作如图1-3-31所示的添加自定义计算字段的数据透视表。要求利用基础表中的数据，按地区和性质计算毛利、毛利率、完成率三个新字段。具体操作步骤如下。

图1-3-29　自定义排序

图1-3-30　分公司人员数据清单

图1-3-31　自定义计算新字段

（1）在新工作表中创建销售数据基础数据透视表，如图1-3-32所示。

（2）单击“数据透视表工具”下的“分析”选项卡，然后单击“计算”组下的“字段、项目和集”下拉按钮，选择“计算字段”，打开“插入计算字段”对话框。

（3）在“插入计算字段”对话框中的“名称”栏中输入新字段名，在“公式”栏中输入公式，单击“添加”按钮，此时在字段栏则出现新的字段。如图1-3-33所示添加“毛利”新字段，输入公式“＝实际销售额-销售成本”。使用同样的方法插入“毛利率”“完成率”字段。毛利率与完成率的计算公式分别为：“毛利率＝round（毛利/实际销售金额，4）”；“完成率＝实际销售金额/本月指标”。上述设置完成后即可得到添加新字段后的数据透视表。

图1-3-32　销售数据基础数据透视表

图1-3-33　添加毛利字段

提示：在“公式”栏中可以插入部分函数使用。如用四舍五入函数对毛利率保留四位小数点计算等。

（4）调整数据透视表格式。单击“数据透视表工具”下的“设计”选项卡，单击“布局”组中的“分类汇总”按钮；打开“分类汇总”对话框，选择“在组的底部显示所有分类汇总”复选按钮，则可显示汇总项。在“数据透视表样式”下拉列表中选择“清除”，即清除数据透视表的当前样式，最后得到以表格形式显示的结果，如图1-3-34所示。

图1-3-34　添加新字段后的数据透视表