多年的图表难题就这样解决了:底边水平对齐的气泡比较图

刘万祥ExcelPro
刘万祥ExcelPro
刘万祥ExcelPro
17
文章
0
评论
2020-04-1802:05:00 评论 1,395 1320字
摘要

如何用Excel制作底边水平对齐的气泡比较图,以及圆圈排列、外切对齐的气泡比较图。

在和学员朋友们一起学习的过程中,我经常也受益良多。例如,有个一直困扰我的问题,最近在学员朋友的灵感之下得到了解决,不亦快哉。

我们在商业杂志上,特别是现在的信息图中,有时会看到一种底边水平对齐的气泡比较图,看起来就像地平线上的一串小球,如下图。

多年的图表难题就这样解决了:底边水平对齐的气泡比较图

虽然说这种图表形式的比较并不很精确,不过也是一种形式吧,尤其当前信息图表大行其道,讲究吸引眼球的时候。所以我想过如何用Excel来绘制它。

我们可以使用气泡图制作出居中对齐的气泡比较图,比如之前仿制一财的咪咪图:)

多年的图表难题就这样解决了:底边水平对齐的气泡比较图

但你无法做出底边对齐的形式,遇到的问题是这样的--------

本来想,气泡的位置由 x、y 值决定,要实现底边对齐水平排列,x可按1、2、3...依次取值,y可按气泡的半径取值,圆的面积公式 s=pie*r2,那么根据气泡的面积(z值),可以反求出气泡的半径:

r=sqrt(s/pie),也就是

y=sqrt(z/pie)

但如果用这个 r 作为 y 值做气泡图,并不能实现底边对齐,而且相去甚远。这说明Excel绘制气泡图时面积并不精确与 x、y 值有关,而只与 z 值有关,不能这样去求 y 值。想想也是,x、y、z的量纲和单位都可能完全不一样,怎么能这样算呢。

一位学员朋友却想到,虽然它们的半径不能直接由面积直接求出来,但肯定还是有线性比例关系的:

r ~ sqrt(s),也就是

y ~ sqrt(z)

这一想法正是点醒本例做法的关键。

那么我们先给最大值的气泡"人为"设定一个 y 值,使其底边能与 x 轴相切。然后根据这个线性关系,按比例折算出其他气泡的 y 值来,应该就可以实现底边对齐了。如下图,

多年的图表难题就这样解决了:底边水平对齐的气泡比较图

为方便人为设定调整,我设置了一个单元格 I5,作为最大值气泡的 y 值,范例中,

I8:=SQRT(J8)/SQRT(MAX($J$8:$J$14))*$I$5

也就是说,当气泡为最大值时,y就是r,就是I5,其他气泡则根据z值平方根按比例折算得出。

通过滚动条调整I5的大小,可以方便地让气泡底边对齐。困扰我多年的问题终于解决了!我忍不住给那位学员发了几个红包表示感谢。

需要注意的是,这个 y 系数并不是固定的,它与图表的图表区大小、绘图区大小、气泡缩放比例等都有关系。如果改变这些设置,可以看到气泡又不能底边对齐了,这时候可以再继续调整I5的值,来使气泡底边对齐。

在范例模板里,我还增加了是否排序、是否显示标签、是否集中排列等功能选择,这些都比较好办了,动画演示如下。

多年的图表难题就这样解决了:底边水平对齐的气泡比较图

在集中排列的情况下,可以使用另一组气泡图来标示标签和引导线。

多年的图表难题就这样解决了:底边水平对齐的气泡比较图

同样的,下图的圆环排列、圆周相切的气泡比较图,也属于底边对齐的一种变化形式。

多年的图表难题就这样解决了:底边水平对齐的气泡比较图

那么实现方法也是一样的,把大圆圈作为地平线,按比例折算出气泡的小r后,加上大圆圈的R,去计算各气泡的x、y位置,我们就留给大家作为作业练习吧。我不知道网易的制图师是如何绘制这个图的,有人知道吗?

多年的图表难题就这样解决了:底边水平对齐的气泡比较图

如果气泡太小,数据标签不合适放在气泡中间,那么可以使用另外一组气泡图来定点显示数据标签,范例中我没有去做。

结束之前,再来看个例图,我觉得还很不错。

多年的图表难题就这样解决了:底边水平对齐的气泡比较图

经济学人前几天刚好也发过一个气泡图,大家觉得这种形式怎么样?

多年的图表难题就这样解决了:底边水平对齐的气泡比较图

本例初始思路由来自上海的学员朋友"步"贡献,特此致谢。

End.

作者:刘万祥ExcelPro

来源:微博

本文均已和作者授权,如转载请与作者联系。

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: