Excel自定义任意图表的通用模式

Excel作图通常使用自带图表或第三方插件图表。自带图表种类比较少，仅仅包含柱形图、条形图、折线图等基础图形，且功能有限。优秀的第三方插件图表是很好的扩充。

但是，这两种作图方式都无法解决一个问题-任性，即结合业务实际需求随心所欲的制图。 本文尝试给这种“任性”的需求一个解决方案，并且该方案是轻量级的，仅仅使用Excel本身的功能即可实现花式制图，无需额外任何安装。

例如，可以制作填充任何图形的条形图：

可以定制一个带有条件格式的迷你环形图，并且安装数据变化实时更新：

可以定制一个四象限方块图，按照指标达成状况将所有店铺分为四个区域：

熟悉我公众号的读者可能觉得以上图表似曾相识。没错，这些样例都是我之前在Power BI中使用DAX+SVG自定义的。现在，在Excel中也可以轻易的实现这种自定义，不过需要的是VBA+SVG（一点VBA都不懂的读者先不要吓跑，都是套路操作，仅仅需要少量代码）。

使用SVG的方式在Power BI和Excel自定义图表底层原理一致，都是利用了SVG图片的矢量性。图表是位置、大小、形状、颜色、亮度、方向和文本的组合，SVG的原理正好满足图表的所有属性需求。

但是二者又有明显的区别。Power BI有个优点，度量值可以标记为图像URL。这意味着，一个图片的网址可以在Power BI中直接显示为图片。也就是说，SVG+DAX生成的度量值也可以直接显示为图片，并且图片随着模型数据的变化而变化。

但是，Excel没有这样的功能（期待未来可以有）。因此，需要采取一种迂回的方式：

将Excel中需要制图的数据嵌入SVG，将该SVG图片导出Excel，接着再导回Excel的指定位置。数据更新后，自动删除当前SVG图片，再次生成新的SVG导入。以上动画中或者以下视频中，读者看到数据变化图表自动更新其实是一种错觉，因为电脑运行速度太快，本质上是将数据变化前的图片自动删除并用新的图片覆盖。以下分步骤解说。

1. 数据嵌入SVG

数据的提取有两种模式，一种是指定位置数据，比方上图中环形图的A2单元格，四象限方块图中的四个单元格。

还有一种是数据多少不固定，不选中数据时可以使用For to语句从头到尾遍历，选中指定数据制图时可以使用For each语句。

数据嵌入SVG的方式与Power BI雷同，比如针对四象限方块图，定义SVG的环节如下：

这与Power BI中的代码几乎是一样的：

无论是提取数据，还是重复生成图表，For循环都是最佳选择。圆点条形图的例子中，内层的For语句，按照数据大小生成相应数量的圆；外侧的For对选中的每一行数据执行内层的For。

2. 导出导入SVG图片

VBA的前半段生成SVG图片后，通过以下简单语句导出为一个叫wu的SVG文件：

Open "D:\wu.svg" For Output As #1
Print #1, SVG
Close #1

接着，将该文件导回Excel，如果是放在单元格的迷你图，本公众号前期分享过导入Excel图片的代码，读者可自行搜索；如果是放在工作表指定位置的大图，以下一句代码即可：

ActiveSheet.Pictures.Insert ("D:\wu.svg")

3. 自动刷新

刷新图表有两种方式，一种是指定一个按钮，点击刷新：

另外可以自动刷新，新建一个新的宏，如下所示，借助Worksheet_Change事件，当工作表数据有变更自动促发VBA的运行。该代码有两个模块，首先删除当前工作表中的所有图片（代码前期分享过，可公众号搜索），接着调用四象限方块图这个宏。

Private Sub Worksheet_Change(ByVal Target As Range)
  删除图片代码
  Call 四象限方块图
End Sub

以上即是完整的Excel自定义图表流程。你需要的最少知识是：VBA中For循环的使用方法及SVG基础图形的编码规则。这些知识可以在网上很容易搜索到。

VBA+SVG在Excel制图的优势比DAX+SVG在Power BI中非常明显：首先，VBA中的For 语句可以很方便的循环，DAX需要新建虚拟索引；其次，Excel单元格具有灵活性，在Power BI中建的SVG迷你图必须是正方形，且大小不能超过150*150像素，Excel完全没有这个限制。

常规状态下还是建议使用内置或者第三方插件图表。如果你喜欢折腾，或者有业务需求，但市面上实在没有图表可以满足，不妨试试本文的方案。需要说明的是，无论在Power BI还是Excel中，并不是所有SVG标签都可以得到支持，希望微软后续在这方面做些努力。