为了体现企业经营策略变动所发生的效果,有时需要将相邻的年度之间的数据进行对比分析。例如,将本年度的销售收入、销售成本、销售成本率与上年度的销售收入、销售成本、销售成本率进行对比分析。

创建分析表格

首先创建年度分析表格,表格中列标志包含“销售收入”“销售成本”“销售成本率”的本年数和上年数,操作步骤如下所示。

步骤01:将工作表标签Sheet2更改为“销售收入与成本年度比较分析”,在该工作表中创建“销售收入与成本年度对比分析”表格,并输入已知的上年的“销售收入”“销售成本”数据,如图2-142所示。

注意:第7行为合计,指的是求和项,用SUM函数得出,下文含有合计项的可直接计算得出,不再重复过程。

Excel 销售收入与成本年度对比分析-Excel22

图2-142 新建表格并输入上年数据

步骤02:在单元格D4中输入公式“=C4/B4”,按下Enter键后,向下复制公式至单元格D7,得到如图2-143所示的数据。

Excel 销售收入与成本年度对比分析-Excel22

图2-143 计算上年销售成本率

步骤03:在单元格E4中输入公式“=数据表!O4”,单元格E5中输入公式“=数据表!O11”,单元格E6中输入公式“=数据表!O18”,得到的数据如图2-144所示。

Excel 销售收入与成本年度对比分析-Excel22

图2-144 引用本年销售收入

步骤04:在单元格F4中输入公式“=数据表!O5”,单元格F5中输入公式“=数据表!O12”,单元格F6中输入公式“=数据表!O19”,得到如图2-145所示的数据。

步骤05:在单元格G4中输入公式“=F4/E4”,计算销售成本率,按下Enter键后,向下复制公式至单元格G7,得到如图2-146所示的数据。

创建结构图表

创建结构图表能够帮助我们更好地分析数据。下面我们以创建销售收入结构图表为例,具体讲解一下创建结构图表的过程。

步骤01:选择单元格区域A4:B6,在主页面功能区中切换到“插入”选项卡下,在“图表”组中单击“饼图”下三角按钮,从展开的下拉列表中选择“三维饼图”,如图2-147所示。Excel创建的默认的三维饼图效果如图2-148所示。

Excel 销售收入与成本年度对比分析-Excel22

图2-145 引用本年销售成本

Excel 销售收入与成本年度对比分析-Excel22

图2-146 计算销售成本率

Excel 销售收入与成本年度对比分析-Excel22

图2-147 选择图表类型

Excel 销售收入与成本年度对比分析-Excel22

图2-148 图表默认效果

步骤02:在“图表工具–设计”选项卡中的“图表布局”组中选择带有图表标题、百分比数据标志和图例的布局样式,这里选择“布局6”,如图2-149所示。

Excel 销售收入与成本年度对比分析-Excel22

图2-149 更改图表布局

步骤03:更改图表布局后的图表效果如图2-150所示,从图表中的数据标志可以得知,在上年的销售收入结构中,A产品的销售收入占上年整个销售收入的9%,B产品占76%,C产品占15%。

创建本年与上年销售成本率比较图表

通过比较上年销售成本率与本年销售成本率的变化,可以得知企业在该年度的成本控制效果。那么怎么创建本年与上年销售成本率比较图表呢?具体操作如下。

步骤01:在主页面功能区中切换到“插入”选项卡下,在“图表”组中单击“折线图”下三角按钮,从展开的下拉列表中选择“带数据标记的折线图”子类型,如图2-151所示。

步骤02:在“图表工具–设计”选项卡的“数据”组中单击“选择数据”按钮,打开“选择数据源”对话框,单击“添加”按钮,如图2-152所示。

Excel 销售收入与成本年度对比分析-Excel22

图2-150 图表效果

Excel 销售收入与成本年度对比分析-Excel22

图2-151 选择图表类型

Excel 销售收入与成本年度对比分析-Excel22

图2-152 单击“添加”按钮

步骤03:打开“编辑数据系列”对话框,在“系列名称”框中直接输入“销售成本率(上年数)”,单击“系列值”单元格引用按钮,选择单元格区域D4:D6,然后单击“确定”按钮,如图2-153所示。

Excel 销售收入与成本年度对比分析-Excel22

图2-153 编辑数据系列

步骤04:返回“选择数据源”对话框,再次单击“添加”按钮打开“编辑数据系列”对话框。在“系列名称”框中输入“销售成本率(本年数)”,单击“系列值”单元格引用按钮,选择单元格区域G4:G6,然后单击“确定”按钮,如图2-154所示。

Excel 销售收入与成本年度对比分析-Excel22

图2-154 添加“销售成本率”系列

步骤05:返回“选择数据源”对话框,此时添加的系列会显示在“图例项(系列)”列表中。在“水平(分类)轴标签”区域中单击“编辑”按钮,如图2-155所示。

Excel 销售收入与成本年度对比分析-Excel22

图2-155 单击“编辑”按钮

步骤06:在打开的“轴标签”对话框中,单击“轴标签区域”单元格引用按钮,选择单元格区域A4:A6,然后单击“确定”按钮,如图2-156所示。

步骤07:更改轴标签后返回“选择数据源”对话框,单击“确定”按钮,如图2-157所示。

步骤08:图表最终效果如图2-158所示。从图表中可以明显看出,本年销售成本率低于上年的销售成本率,说明本年的成本控制比上一年做得好。

Excel 销售收入与成本年度对比分析-Excel22

图2-156 选择轴标签区域

Excel 销售收入与成本年度对比分析-Excel22

图2-157 单击“确定”按钮

Excel 销售收入与成本年度对比分析-Excel22

图2-158 图表最终效果