专题课程
经常有读者拿类似下面的图表问我如何做。
老读者朋友会发现，我很少写这类新奇特图表的帖子，因为这种图表多由那些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
这样，数据转换的工作就简单了，把这个公式复制保存好，以后一个公式就可以搞定，虽然还是略有误差，不过不重要了。
知识点
本例作图技巧较简单，就是形状填充，主要难点在数据转换。做完这个图表，你可以练习到以下知识点：
构图思路，柱形图+填充+重叠
数学知识，复习高中数学，三角函数与几何，略难
vlookup，参数1，模糊近似查找
贴入形状，填充方式：层叠并伸展
重叠比例，前后关系
标签工具，引用其他位置
趋势线，多项式，拟合方程，拟合度R2
收藏(0)
赞 
粤公网安备44010602001432号