用Excel曲面图做等高线图（仿罗兰贝格消费者价值体系图）

用Excel曲面图做等高线图（仿罗兰贝格消费者价值体系图）

文 / ExcelPro的图表博客

    一位读者询问如何做这个网页中的图表。这个原图我以前曾看到过，是罗兰贝格在一份《图解消费者需求》报告中的图解，如下图。

    此图可能在市场研究领域用得比较多，但我并看不懂，所以此前也不曾留意。链接中的网友似乎是用R软件画的，今天这篇日志尝试了一下Excel的做法，因为不搞定它心里总觉得有事。

    Excel有一种曲面图类型，用来反映xyz三维数据，其中x、y为分类，z为数值。当选择俯视的平面图时，可以得到一个等高线图。但Excel2003和2007下默认的俯视曲面图效果如下图所示，这也是大家经常看到的，完全不是我们想要的样式，也无法看。

    但要命的是我们似乎无法找到调整设置的地方，来改变颜色、增加梯度、去掉边框线等，这是做等高线图的3个难点。

以下先介绍2003下的做法。

    做法示意图：

1、准备梯度变化的颜色

    罗兰贝格这个图表中，首要的特征是这种有梯度的颜色变化，Excel中是没有的，我们首先准备这个。

    利用2007的条件格式-色阶，生成如下一组蓝-白-红的21档渐变颜色，取出其RGB值。要自动获取色阶所产生颜色的RGB比较复杂，这里就不详细写了。

    这组颜色是模仿罗兰贝格的，在设置色阶的时候，一头一尾设置为例图中的蓝色和红色就可以了。在这个例子里，我用蓝色代表数值低（海洋色），红色代表数值高（热量色）。你也可根据需要反过来----感觉罗兰贝格的图就是相反的？

2、将颜色设置到颜色面板

    《图表之道》的读者应该都知道，Excel2003在作图时，默认将color从17开始的颜色分配给图表使用，并且根据序列多少顺序往后使用。因此我们提前将这20个颜色（不含当中的白色）设置到color(17)~color(36)，这样后面作图的时候就不需要再手动设置了。批量设置颜色面板的宏如下：

    Sub SetMyColor（）

        ActiveWorkbook.Colors(17) = RGB(0, 122, 163)

        ......

        ActiveWorkbook.Colors(36) = RGB(211, 31, 39)

    End Sub

    此时，文件中的颜色面板应该是这样的：

3、生成曲面图

    用x*y的10*10矩阵数据做一个曲面图，子类型选择俯视平面图，得到如下的图表。

    可以看到，默认已经使用了前面设置的颜色，比最初的默认样式好多了，很有等高线图的意思。但由于默认的图例只有5个项（每一个图例项是一个数值区间段），所以只有5个蓝色，红色没有出现。图中梯度也不明显，线条过于突兀。

4、增加梯度

    其实这种图也叫等高线图，是地理领域的专业图表形式，线越密，表示海拔约高。在商业上就是数值越大。

    如何增加梯度呢？这是格式化俯视曲面图的第2个难点----等高线或颜色梯度要通过设置数值轴的刻度单位来设置。

    曲面图有xyz三个方向，因为俯视的缘故，z轴也就是数值轴看不见了，但它应该依然在那里。只是如何选中它？《图表之道》中曾介绍如何选择难于选择或者无法选择到的图表元素，最后的手段就是通过图表工具栏下拉框，如下图。

    选中后，按Ctrl+1进入数值轴的格式化对话框。通过设置最小刻度、最大刻度、主要刻度单位，设出20个分档，使图表出现20个图例项。

    另外，选中图例，在其格式化对话框中有个 刻度 选项卡，也可以设置刻度。奇怪吧？可以这样理解，等高线图的图例就是数值轴的分档区间，所以设置可以在图例里设置。

    这时图表如下图：

    可以看到，增加梯度后，图表自动使用了我们前面准备的20档的梯次变化颜色，呈蓝-浅蓝-浅红-红变化。曲面图的第1个难点无形中已经化解。

    你也可以只设置10个档次，那么前面准备颜色面板时也只准备10档。

5、去掉边框线

    曲面图的每个梯度上都有黑色的边框线和网格线，影响了图表的外观和清晰度。但整个绘图区似乎无法选择，当然也无法格式化，如何去掉这个边框线呢？

    这是曲面图的第3个难点----你需要通过图例的图例项标示来设置曲面图的线条色和填充色。先选中图例，稍后，再选中第一个图例的图例项标示（前面的填色小方块），如下图。

    这时双击或Ctrl+1进入格式化对话框，可以设置线条色和填充色。设置无线条色，填充颜色前面已自动使用，这里无需设置了。设置后，会看到图表上对应发生变化。

    完成第一个图例项后，对其他图例项采用 选中->F4键，快速完成格式化。也可采用宏批量完成，这里不写了，可参见范例文件。

    现在图表如下图，基本已经是我们想要的样式了。

    由于本例中使用的是随机数据，所以做出的图表比较难看。若有真实的数据，效果可能会不一样。

2007下的做法：

    1、前述第1步，相同。

    2、前述第2步，不需要了。因为2007做图时不再使用56色的顺序了，而是使用新的颜色面板，这个面板我们只可以设置第1行的后6个。因此只好在第5步时再一一设置颜色。

    3、前述第3步，相同，生成曲面图。

    但问题是默认生成的曲面图是有三维阴影的，非常奇怪、难看，根本看不出等高的意思。如何取消三维阴影？藏得太深了，幸亏有Jon告诉了大家：

    选择某个图例项，在其设置镶边格式->三维格式->表面效果->材料，选择特殊效果->平面。真TM藏得深啊，我是无法找的。

    然后一一设置其他图例项。又是个体力活，可使用F4键重复。

    不过可以使用下面一句话搞定：

        Sub no3dshade()

              ActiveChart.ChartGroups(1).Has3DShading = 0

        End Sub

    4、前述第4步，相同，通过刻度设置增加梯度。

    5、前述第5步，需要。2007下倒是默认没有边框线，但需要设置每个图例项的填充色，按前述第1步准备的20档填色rgb一一设置。2007下无法再选择器那个小方块了，可以直接选个某个图例项，然后进行填充色格式化。

    显然这一步比较麻烦，而且由于2007对话框的啰嗦性，绝对是个重体力活，这一点上2007不如2003。你也可以通过宏来完成这个颜色设置，这里也不写了。

替代的方法

    其实，在2007以后新增了条件格式-色阶，可以直接对单元格进行heatmap显示，其效果与这种等高线图类似：

    当然，这样的表格虽然清晰，但没有咨询公司的“范”，考虑优化一下。将数据表设置自定义格式;;;隐藏数字显示。截图，用PS或其他软件对数据表区域进行“模糊”，也有两分接近罗兰贝格的效果了，如下图。

    所以，我甚至怀疑，罗兰贝格可能根本就不是用什么数量作图软件来做的，而是直接用Photoshop“画”出来的！显然这对PS人员来说并不难。从开头引用的那位网友用R软件+函数建模来画的复杂度看，更加深了这种猜测，因为难道罗兰贝格的咨询顾问都那么厉害？

    参考文献：感谢Jon peltier，http://peltiertech.com/WordPress/contour-and-surface-charts-in-excel-2007/

    制作难度：★★★★

    实用性：★★★★

    适用场景：市场研究，客户细分

范例文件下载：

    下载地址：新浪资料审核中。。。下载

     《Excel图表之道——如何制作专业有效的商务图表》，2010年最值得阅读的职场充电图书！购买链接：当当、卓越、京东、淘宝、互动网、电子社，更多信息可查看“图表之道”专栏。更多图表好书，尽在EP图表书架！