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

用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/ 制作难度：★★★★

实用性：★★★★

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