Excel数据分析与决策案例：使用各种图表做数据分析

在商业中使用分析功能的一个重要组成部分就完成数据的可视化，从可视化中解决和呈现问题和必备的技能。

1、制作组合图表

我们通常会绘制实际销量和目标销量，为了将二者在一个图表区分，需要通过组合图表来呈现数据，其中的两个系列分别使用不同的显示格式。组合图制作方式如下所示：

（1）选中数据区域，在功能区中选中“插入”-“折线图”。

（2）在折线图中将光标移到目标系列中的任意一点，选中target销售系列线。

（3）右击并选择“更改图表类型”，选择柱形图，即可做成下图。

但是，如果你想要绘制的两个系列的数据规模差异很大，如果使用相同的垂直轴会导致其中的一个系列小到看不见，这种情况下就需要使用次坐标轴。下面阐述详细的步骤：

（1）选中工作表中的D7:F16区域，单击插入选项卡，单击图表区域中的折线图。

（2）在图表中选择Revenue系列线，并右击选中更改系列数据类型，选中柱形图，即可生成下图所示的双轴图。

现在可以轻松地看到销量与收益如何密切地一起变动了。

2、制作动态图标标签

图表中的数据来自表格，对表格数据的更改会引起图表数据的更改，因此需要设置谁知更改的图表标签。

假设公司要求规划某种新产品的销售，如果一年内达到2000单位，则该公司收支相抵。在表格中，第一年销量在D2单元格和年增长率在D3单元格。收支平衡的目标在D1单元格。使用“根据所选内容创建”功能，将D1:D3中的数据命名为C1:C3区域中的名称，步骤如下：

可以基于初始销量和销量增长率持续将年销量绘制成图表。随着这些假设的变化，图表标题和纵轴图例也需要反映出这些更改，操作如下：

（1）在单元格F5中输入第一年的销量的公式=Year1sales。将公式=F5*(1+anngrowth)从F6单元格复制到F7:F15，生成从第2年到第11年的销量。

（2）想要查找收支平衡的年度，将公式=IF(F6>=target,"yes","no")从G5单元格复制到G6:G15以确定本年度是否获得了收支平衡。

要创建依赖于假定的初始销量和年增长率的动态标签，关键是将图表标题和系列图例与公式相关联。

现在需要确定第一个收支平衡年。这就要使用到MATCH函数。在E19单元中输入公式=MATCH("yes",G5:G15,0)会返回达到盈亏平衡目标的第一年（这里是第五年）。在D22单元格中的几个函数用于创建将成为动态图表标题的文本字符串，在D22单元格输入如下公式：=IFERROR("盈利的年份"&TEXT(E19,"0"),"不会盈利")。

如果E19单元格不返回一个错误，那么D22单元格中的公式就会返回文本字符串“不会盈利”。

还需要为包含年增率的销售数据创建标题，在D23中输入="销售额年增长率="&TEXT(anngrowth,"0.0")&")"。下面就可以创建动态标签图了。

（1）从图表中选中数据源E4:F15单元格。

（2）在插入选项卡中选中X-Y散点图，在图表工具中选中“图表标题”，将光标放在公式栏中输入等号（=），指向D22中的图表标题，按下enter即可。

（3）要创建系列标题，右击绘制的任意一个数据点，选中选中数据，单击编辑，在选中系列名称。

（4）输入一个等号，指向D23中的标题，按下enter即可。

3、添加新数据时自动更新

下面的数据包含了2000年-2011年间每月新房开工数量。在插入选项卡中，创建x-y散点图。

如果在当前的D和E列加入新数据，图表并不会自动合并新数据。需要进行如下的操作：

（1）选中数据（D2:E141）按下CTRL+T快捷键使他变成表格。

（2）插入散点图。

（3）在第142行和143行添加数据，如下图所示，图表会自动更新以包含新的数据。

4、加入图表以表示业绩排名

当在一堆数据中，很难一眼发现谁的业绩好坏，如果使用明显的图表来表示，就能更直观观察数据的反应的情况了。

为了创建反映每月每个销售人员业绩变动的向上、向下和平行箭头，需要执行以下步骤：

（1）通过公式=RANK(E6,E$6:E$20,0)，从J6单元格复制到J6:N20，创建每个月销售人员的业绩排名。

（2）接下来通过将公式=IF(L6<K6,"h",IF(L6>K6,"i","g"))从O6单元格复制到O6:R20来创建各箭头对应的h,I和g。

（3）将6:R20中的字体改为Wingdings3。

5、在图表中使用复选框控制数据

经常需要在单个图表中绘制多个系列，但是这可能会导致图表看起来混乱，这时候使用复选框来控制数据的呈现与否很有利于数据的呈现。

这里使用的是便利店中巧克力、DVD、杂志、苏打水和热狗的每周销售额。按照上述的内容，很容易就可以创建单个图表中的5条曲线来呈现数据，然而很凌乱。

下面使用复选框来控制呈现数据。

（1）在开发工具中选中插入，插入最顶行左边的第3个复选框。

（2）在工作表中释放鼠标后，会看到一个绘图工具，按住鼠标左键来调整复选框大小。在选择的时候，复选框仅将TRUE或FALSE放在所选择的单元格中，用于切换功能。

（3）将鼠标移到复选框，直到看到指示器，由此选择复选框控制的单元格。

（4）右击并选择复选框设置对话框，选择F3单元格，作为单元格链接。

（5）此时当选择该复选框时，F3单元格会显示TRUE，未选中时候显示FALSE。

用类似的方式创建控制G3、H3、I3和J3单元格的4个复选框，分别用于控制DVD、杂志、苏打水和热狗。

（6）创建好复选框后，将公式=IF(F$3,F6,NA())从O6单元格复制到06:S35，确保只有复选框被选中的系列的数据才会被绘制出来。比如，选中巧克力复选框时，F3单元格显示为TRUE，因此O6单元格直接挑选F6单元格中的第一周巧克力的实际销售额，如果取消选中巧克力复选框，F3单元为FALSE，O6单元格显示#N/A，图表什么也不会显示。

最终效果如下图所示：

---

我会在知识星球里面分享300个excel数据分析案例，目录：