当前位置: 首页 > 业界动态 > 技术实现 > 本文


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




发布时间: 2017-3-16 10:07:11  
百分比信息图表

  作者:刘万祥ExcelPro,《Excel图表之道》作者


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


  老读者朋友会发现,我很少写这类新奇特图表的帖子,因为这种图表多由那些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


  End.


  来源:36大数据(36dsj.com)

阅读:1125次
推荐阅读:

版权所有 © 2011-2017 南京云创大数据科技股份有限公司(股票代码:835305), 保留一切权利。(苏ICP备11060547号-1)  
云创大数据-专业的云存储、大数据、云计算产品供应商