设置主页 | 收藏本站 Excel学习网-公益型的EXCEL在线学习网站,助您轻松办公!

利用Excel进行同比分析报表

2014年05月02日 14:44 发布来源:Excel网

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

Excel实例表

图1

利用Excel进行同比分析报表

图2

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

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

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

利用SQL豫剧连接Excel工作表数据

图3

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

插入Excel计算字段

图4

Excel工作表

图5

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

修改Excel字段

图6

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

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

Excel中插入计算字段

图7

Excel工作表

图8

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

Excel数据格式化百分比

图9

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

    例如1月份“收入”、“成本”和“费用”的同比增长率分别是-33.38%、-37.27%和-36.45%.其计算结果是-33.38%-(-37.27%)-(-36.45%)-40.34%.因此,自定义计算字段“利润”的同比增长率指标没有任何实际意义。

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

利用Excel进行同比分析报表

图10

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

利用Excel进行同比分析报表

图11

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

原创文章如转载请注明:转载自Excel学习网 [ http://www.excelcn.com/]
需要保留本文链接地址:http://www.excelcn.com/shujufenxi/538.html
分享到:
网站地图 | XML地图 | 免责声明 | 关于我们 | Excel学习网:优秀的EXCEL在线学习和资源分享网站。
版权所有: CopyRight © 2013-2018 www.excelcn.com All Rights Reserved.
豫ICP备12002644号