利用Excel进行同比分析报表-英雄云拓展知识分享

    我们这节接着上节的对Excel数据透视表进行自定义内涵继续给大家讲授数据分析，下面的图1所示是某公司2009年和2010年上半年的销售数据，它们分别保存在工作表“2009年”和“2010年”中。现在要求制作一个利用Excel进行同比分析报表，以判断两年经营事迹的差别，并绘制图表辅助分析，如图2所示。由这个报表和图表，可以一目了然地知道哪些月份的收入、本钱、费用和利润出现了异常增长。

图1

图2

    举例来看，2010年5月份销售利润同比大幅度增长了788.93%,经过图表可以看出，本钱和费用也分别同期增长了100%以上，这么大的利润增长主要来自于销售收入的快捷增长（同期增长120%左右）。若在2010年能够对本钱和费用进行很好的控制，销售利润的增长就会更大。

    1、采取导入数据+SQL语句的方法将两个工作表数据汇总在一起，并制作数据透视表，如图3所示。这里的SQL语句为：

    select '2009年' as年份， *ftom[2009年$] union all select '2010年' as年份，*from[2010年$]

图3

    2、单击“数据透视表工具”|“选项”选项卡，在“工具”功能组中单击“公式”按钮，弹出下拉菜单，选择“计算字段”命令，打开“插入计算字段”对话框，在“名称”下拉列表框中输入名称“利润”，在“公式”文本框中输入计算公式“=收入-本钱-费用”，如图4所示，单击“肯定”按钮，为数据透视表添加一个计算字段“利润”，得到图5所示的数据透视表。

图4

图5

    3、修改字段名称，让报表看起来更加清楚，如图6所示。

图6

    4、由于要将2010年和2009年进行同比分析，而“2009年”和“2010年”是字段“年份”下的两个项目，所以这里要为字段“年份”添加自定义内涵计算项。

    单击字段“年份”，然后单击“数据透视表工具”|“选项”选项卡，在“工具”功能组中单击“公式”按钮，弹出下拉菜单，选择“计算项”命令，打开“在‘年份’中插入计算字段”对话框，然后在“名称”下拉列表框中输入名称“同期增长率”，在“公式”文本框中输入计算公式“=（'2010年'-'2009年'）/'2009年'”，如图7所示。单击“肯定”按钮，为数据透视表添加一个计算项“同期增长率”。得到如图8所示的数据透视表。

图7

图8

    5、设置4个同期增长率数据列的数字格式为百分比，便可得到一个同比分析报表如图9所示。

图9

    6、分别使用收入、本钱、费用和利润数据绘制图表，以便更加直观地表现两年的收入、本钱、费用和利润的变化。关于如何绘制这样的两轴折线图。注意，自定义内涵字段“利润”下的同期增长率是一个毛病的计算值，它其实不是2010年利润和2009年利润的比较结果，而是字段“收入”、“本钱”和“费用”3个字段的同期增长率的加减结果，由于计算字段“利润”的计算公式就是“=收入-本钱-费用”。

    例如1月份“收入”、“本钱”和“费用”的同期增长率分别是⑶3.38%、⑶7.27%和⑶6.45%.其计算结果是⑶3.38%-（⑶7.27%）-（⑶6.45％）⑷0.34%.因此，这种情况下，自定义内涵计算字段“利润”的同期增长率指标没有任何实际意义。

    为了解决这个问题，可以在数据透视表的右边使用公式计算利润的同期增长率，如图10所示。

图10

    将Excel数据透视表内的利润同期增长率字体色彩设置为白色，使之不可见，然后利用照相机将手工计算的数据列拍摄下来。放到数据透视表内的利润同期增长率数据列上，如图11所示。

图11

    利用Excel进行同比分析报表，这样我们就知道同期的增长或减少了，我们就能够进行两年或则是同一个季度的同期增长或进行其他的对照数据分析。