20.3　利用数据透视表制作常用费用分析表

示例20.3　多年度制造费用差异对比分析表

图20-14所示展示了某公司制造费用数据列表，该数据列表中的数据是从财务软件系统中导出的，记录了该公司2010〜2011年度制造费用实际发生额数据。

img443a

图20-14　制造费用数据列表

面对上千行的跨年度数据进行分析，首先需要创建动态的数据透视表来满足动态数据分析的要求，并通过对相同字段在数据透视表中设置不同的数据显示方式来求得实际发生额数据不同年度的占比和差异，最后对数据透视表应用条件格式，突出显示差异异常的数据作为重点关注的项目，具体操作步骤如下。

步　骤1img01

为“制造费用”数据源定义动态名称，如图20-15所示。

Data=OFFSET(制造费用！$A$1,,,COUNTA(制造费用！$A：$A), COUNTA（制造费用！$1：$1））

img443b

图20-15　为“制造费用”数据源定义动态名称

步　骤2img01

以名称“Data”为数据源在“差异分析”工作表中创建如图20-16所示的数据透视表。

步　骤3img01

对数据透视表“科目名称”字段进行手工分组，以归纳费用属性，可控费用属于可以控制并加以重点关注的费用项目，不可控费用属于必须支出无法控制的费用，如图20-17所示。

步　骤4img01

将【数据透视表字段列表】中的“求和项：借方”字段连续移动至【∑数值】区域两次，如图20-18所示。

img444a

图20-16　创建数据透视表

img444b

图20-17　对数据透视表进行分组

img444c

图20-18　布局数据透视表

步　骤5img01

将数据透视表中“求和项：借方”、“求和项：借方2”、“求和项：借方3”字段的名称依次更改为“金额”、“占比”和“差异率%”，如图20-19所示。

img445a

图20-19　更改数据透视表字段名称

步　骤6img01

将“占比”字段在数据透视表中的“值显示方式”设置为“列汇总的百分比”，“差异”字段设置为“差异百分比”，如图20-20所示。

img445b

图20-20　设置数据透视表字段的数据显示方式

步　骤7img01

消除数据透视表“值区域”中的错误值“#DIV/0!”，删除数据透视表的“行总计”。

步　骤8img01

在数据透视表外部的BW列开始，添加总计标题，设置求和公式，如图20-21所示。

BW8={SUM(GETPIVOTDATA(“借方”，$A$4，“年”，2010，”月”，TEXT(COLUMN(A:L)，”00”)，”科目名称”，B8））}

BX8=BW8/$BW$29

BZ8={SUM(GETPIVOTDATA(“借方”，$A$4，”年”，2011，”月”，TEXT(COLUMN(A:L)，”00”)，”科目名称”，B8))}

CA8=BZ8/$BZ$29

CB8=IFERROR((BZ8-BW8)/BW8,"")

BW20=SUM(BW8:BW19)

BZ20=SUM(BZ8:BZ19)

BW28=SUM(BW21:BW27)

BZ28=SUM(BZ21:BZ27)

BW29=SUM(BW20,BW28)

BZ29=SUM(BZ20,BZ28)

img446a

图20-21　设置求和公式

步　骤9img01

在“差异分析”工作表内对所有显示为“差异率%”值的单元格应用条件格式，将差异率大于10%或小于-10%的数据突出显示，作为重点关注的项目，如图20-22所示。

img446b

图20-22　对数据透视表应用条件格式

步　骤10img01

依次隐藏数据透视表中每个月里“2010”字段的“差异率％”项，同时对数据透视表进行美化，最后效果如图20-23所示。

img447a

图20-23　多年度制造费用差异对比分析表

本例通过对数据透视表中的相同字段设置不同的数据显示方式来求得不同年度费用实际发生额的占比和差异率，并通过在数据透视表内应用条件格式，突出显示差异异常的数据作为决策者重点关注的项目，从而完成最终的多年度制造费用差异对比分析。