用Excel制作不一样的分割图表

思考一个问题，我们在簇状柱形图上添加一条平均线是很容易实现的。那么如果我们想对优于平均线的部分填充为一种颜色，劣于平均线的部分填充为另一种对比颜色，这样的需求我们又该如何实现呢？

针对以上提出的问题，下面我们准备了一组作图数据源，说明一点本次的作图数据完全是模拟的，数据是使用RANDBETWEEN函数自动生成，该函数包括两个参数，其中参数1为bottom和参数2为top，其具体使用方法可进行百度查阅。

我们先来看下实现的最终效果图：

我们也可以对各省份添加数据标签，因考虑到作图美观问题，此处未添加说明。

下面来看下作图数据源：

其中E列的数据是由D列/C列生成。

再来看下具体的操作步骤：
步骤一

在N2单元格输入以下公式，向下拖动复制。
=RANK(E2,$E$2:$2:E$32)+COUNTIF(E$2:E2,E2)-1
公式的意思是对完成率做排名，完成率相同的占不同的名次，避免匹配L列省份时出现错误。

在L2单元格输入以下公式，向下拖动复制。
=INDEX($B$2:$2:B$32,MATCH(O2,$32,MATCH(O2,N$2:$2:N$32,))
公式的意思是根据MATCH函数定位完成率最高的位置ID，并将返回的位置ID传递给INDEX函数。
INDEX和MATCH函数的使用方法百度有详细介绍，此处不做详细讲解。

在M2单元格输入以下公式，向下拖动复制。
=VLOOKUP(L2,$B$2:$2:E$33,4,)
公式的意思是根据L列的省份匹配相对应的完成率。
VLOOKUP函数的使用方法百度有详细介绍，此处不做详细讲解。

步骤二

要使图表显示不同的颜色，最关键的地方是要先生成不同的数据系列，所以首先来构造辅助列。
在Q2单元格输入以下公式，向下拖动复制。
=MIN(M2,P2)
公式的意思是对比完成率与平均值，取最小的一个值。

在R2单元格输入以下公式，向下拖动复制。
=IF(M2>P2,M2-P2,NA())
公式的意思是对比完成率与平均值，如果完成率大于平均值，则显示两者之间的差值，否则返回错误值NA()，错误值NA()在图表中起占位的作用。

步骤三
选中L1:L32区域并同时按住CTRL键，继续选中P1:R32区域，，插入堆积柱形图。

得到以下图表

选中图表中任一柱子，右键更改系列图表类型，将平均值改成折线图，点击确定。

此时图表已经有了大致的轮廓，但是折线图两侧没到头，为让线条填充至绘图区，可以为折线图添加趋势线来解决这个问题。
单击选中折线图，右键选择添加趋势线，弹出设置趋势线格式对话框，设置趋势线趋势预测前推后推各0.5个周期。再设置趋势线的线型和宽度，颜色选择深红色，宽度大概为1.25磅，线型选择为短划线类型。
选中平均值线条，对其做同样的处理。得到的图表效果如下。

步骤四

趋势线添加好后，我们可以继续对图表进行美化，按住DELETE键删除图例。将柱子之间的间隙宽度设置为70%左右。将主要横坐标轴标签设置为微软雅黑8号字体，主要纵坐标轴标签设置为Arial8号字体。选中平均线的最后一个标记点，添加数据标签，去掉引导线，将标签拖动到平均线上方合适位置处。选中标签，在设置数据标签格式中，对数字标签自定义格式代码 “▼平均:” 0.00%，字体设置为微软雅黑，大小设置为9号，颜色为深红色。插入一个矩形文本框，将其放在图表的左上角位置处，颜色填充为红色。
得到的效果图如下。

最后单击图表区，设置填充颜色，OK了。

• 说明一点，本文的图表颜色主题取自经济学人颜色主题，可根据业务需求选择适合自己的一套颜色主题。