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

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

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

老读者朋友会发现,我很少写这类新奇特图表的帖子,因为这种图表多由那些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
上一篇:Serverless在SaaS领域的最佳实践


下一篇:关于php操作windows计划任务管理