鱼缸式百分比信息图表，这样计算才正确

经常有读者拿类似下面的图表问我如何做。

老读者朋友会发现，我很少写这类新奇特图表的帖子，因为这种图表多由那些PPT模板制作者拿形状绘制，只是追求样式新颖花哨，不但无助于数据比较，还存在极大误导，是典型的垃圾图表。

网络上也有人写这种图表的 Excel 制作教程，一般使用柱形图+遮罩或者填充的方法来做，但大多数比例是错误的，鱼缸里水的多少并没有正确反映数据的大小，尤其在水面接近底部和顶部时，误导极大。

因此，尽管我们完全不建议使用这种图表，但还是写个正确做法的帖子，以免新人朋友被以讹传讹。

作图思路

使用柱形图+贴入形状的方法来会绘制鱼缸和水面，这是一种常用作图技巧。关键是作图数据准备，指标数据如何正确转换为鱼缸里水面的高度，使水的侧面积代表指标大小(因是平面图表，不必使用水的体积去代表)。

图中阴影部分面积，占圆的面积的比例，代表要反映的指标的大小。一般都是已知 h 求 s，有以下公式：

s = arccos(1-h)-(1-h)*sqrt(2h-h^2)

如果不理解这个公式，请自行搜索脑补。

但我们是已知阴影面积 s，求水面高 h 是多少，我的数学知识有限，没法从这个公式解出 h=f(s) 的公式，只好变通处理，根据这个公式做个对照表，按 s 来反查 h 的取值。如果有人能解出公式，还请教教我：)

数据准备

图中，

C列，h 从1% 变化到200%

D列，计算出阴影面积 s，D7：=ACOS(1-C7)-(1-C7)*SQRT(2*C7-C7^2)

E列，为这个 s 占圆的面积的比例，也就是业务指标百分比，E7：=D7/(PI()*1^2)

F列，为对应的正确水深，F7：=C7/2

这里E、F两列就构成一个 s~h 的关系对应表，供下面调用查找。

转换作图数据：

C列为业务指标，D列为转换后的数据，也就是柱形图的高度，水面的高度：

D7：=VLOOKUP(C7,计算反查!$E$7:$F$206,2,1)

注意最后参数为1，模糊近似查找方式。对照表是有限精度，结果也会略有误差，不过已经可以忽略，咱们画个图表而已。

E列为辅助数据，固定为100%，用来绘制鱼缸外圈。

作图步骤

1、用B、D、E数据做簇状柱形图。

2、用形状绘制一个圆圈，复制后贴入到辅助序列柱形图;把圆圈复制1份，设置填充色，复制后贴入到折算序列柱形图，填充方式选 层叠并伸展。

3、设置柱形图重叠比例100%，分类间距50%。这时看到变形的鱼缸图。

4、折算序列添加数据标签，指定为C列的原指标数据，并拉宽图表到正圆形。

如果是只做一个鱼缸，就可以使用一个辅助的饼图来保持正圆，但这里有多组鱼缸，所以只能手动调整，不过你可以按住shift后绘制一个正圆来验证。

5、其他格式化到如下样式。

对比下面直接用原数据做柱形图的图表，你就可以看到没有转换的误差了，小的偏小，大的偏大，有误差误导。

不过，即使转换为正确的数据和面积，我们还是难以从图表直观看出数据差异，因为水面高度和指标大小不是线性关系，这也是我们不推荐使用这种图表类型的原因。

近似简化

以上，因为无法直接写出 h=f(s) 的公式，需要使用一个对照表来转换数据，略有不便，我想能够怎么简化。

我把对照表的 s~h 数据做成图表，利用Excel的添加预测趋势线功能，模拟出它们之间的关系，得到如下公式，拟合度0.9999：

h = 3.7785 *s^5 – 9.4188 *s^4 + 9.0867 *s^3 – 4.2298 *s^2 + 1.7532 *s + 0.0157

这么复杂的方程，确实难以解出来。如果有数学学霸，还请告知正确的公式。

那么，回到前面数据组织的地方，可以写个简化的近似公式，直接得到转换后的数据，不再需要查找了：

F7：=3.7785*C7^5 – 9.4188*C7^4 + 9.0867*C7^3 – 4.2298*C7^2 + 1.7532*C7 + 0.0157

这样，数据转换的工作就简单了，把这个公式复制保存好，以后一个公式就可以搞定，虽然还是略有误差，不过不重要了。

知识点

本例作图技巧较简单，就是形状填充，主要难点在数据转换。做完这个图表，你可以练习到以下知识点：

1. 构图思路，柱形图+填充+重叠
2. 数学知识，复习高中数学，三角函数与几何，略难
3. vlookup，参数1，模糊近似查找
4. 贴入形状，填充方式：层叠并伸展
5. 重叠比例，前后关系
6. 标签工具，引用其他位置
7. 趋势线，多项式，拟合方程，拟合度R2

本文作者：刘万祥ExcelPro

来源：51CTO