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

拼搏现实的明天。 2021-12-15 09:21 1066阅读 0赞

我们常会看到罗兰贝格的这个价值体系图，这个图是怎么做的呢？ 我们尝试用excel试试看

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70][]

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

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

![20190704183753689.png][]

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

**以下先介绍2003下的做法。**

做法示意图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 1][]

1、准备梯度变化的颜色

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 2][]

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

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

《[图表之道][Link 1]》的读者应该都知道，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

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

![20190704183842563.png][]

3、生成曲面图

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 3][]

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

4、增加梯度

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

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 4][]

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

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

这时图表如下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 5][]

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

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

5、去掉边框线

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

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

![20190704183947910.png][]

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

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 6][]

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

**2007下的做法：**

1、前述第1步，相同。

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

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

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 7][]

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

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

Sub no3dshade()

ActiveChart.ChartGroups(1).Has3DShading = 0

End Sub

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

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

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

**替代的方法**

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 8][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 9][]

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70]: /images/20211214/432dc64ddbca41ada1cd38ae41d7167e.png
[20190704183753689.png]: /images/20211214/6b7c836795fe4076b5f9b76029700d45.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 1]: /images/20211214/fd00dd81c90d42b788a621e7ed2ade52.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 2]: /images/20211214/9a317dd339df4bb7bd4d0fd762d4a40a.png
[Link 1]: http://excelpro.blog.sohu.com/#tp_861253cce8a
[20190704183842563.png]: /images/20211214/3f1d4e3405894db59ed38d3addd048fa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 3]: /images/20211214/482fbb8f3e2f40738735559689510b04.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 4]: /images/20211214/2002494dcdad456c87f90e97f14b86db.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 5]: /images/20211214/e26b063b3c1e457f91b6ce014fc5a4be.png
[20190704183947910.png]: /images/20211214/6fe960b0f9e6449fb84368d679c9ab9a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 6]: /images/20211214/1ef77d57488a4178a65210e4fe82b1fc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 7]: /images/20211214/b989a285730a45238669b6c2fe0a4723.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 8]: /images/20211214/59704cdef1fe40749926d1976c5d688f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjA1Njc0NQ_size_16_color_FFFFFF_t_70 9]: /images/20211214/64e0ba1575ba4402b48521ce3677033e.png