99%的人都不会做的Excel截断柱状图

Step By Step

关键词
Excel | 柱状图 | 打断
全文约1500字,建议阅读后操作一遍

文不如表,表不如图。

Excel很大的一个优势是,能够很方便地制作各种数据图。
但,有时我们制作的柱状图,会出现这样的情况:

例:和马云、王思聪对比年收入(本文数据纯属虚构,不要对号入座)


图一:柱状图数据悬殊过大

咦,雷哥收入这么高,为什么在图上看起来这么少呢?
这是因为,我等草民与马云、王思聪之辈的收入差距太大,放在同一个图表中比较,简直是找虐。

这时,使用打断纵坐标轴的柱形图了能完美解决这一“尴尬”。

图二:这样的打断柱状图,是不是舒服多了

一、制作打断柱状图之前,我们先对图形进行分析

▼打断柱状图的特点

①打断纵坐标(y)轴以后,y轴的刻度是不变的,只是柱形图的高度变小;
②坐标轴数值以及柱状图标签值都是真实数值;
③不能手画(不能手工使用Excel的形状功能,画一个打断柱状图,这种耍赖的做法不在本文讨论范围内)。

▼应该从哪里打断?

确认应该从哪里打断数据,非常关键.

打断柱状图的本质,是抽走数据中的某一段数据,画上打断标示,让数据不连续的显示,从而降低柱状图的高度。

如下图三所示的区域,原则上都是可以抽走的数据区域。
这个区域怎么来的呢?

图三:打断柱状图打断点的确定方法

设定不需要打断那组数据的最大值①,需要打断那组数据的最小值②;
那么①②之间的数值都是可以抽走的部分

实际应用中,我们通常会取一个整数,比如本例中,星爷将50-500之间的数据抽走。也就是说直接从50跳到500(见图二)。

二、Step By Step,打断柱状图制作步骤

如上文的示例,以马云、王思聪、雷哥、星爷、小刘的年收入作为数据源,制作打断柱状图。

图四:柱状图原始数据

▼Step1、确定数值缩小的倍数,构造新数据列

很明显,我们需要打断马云和王思聪两位的数值,反应到年薪值,就是需要将年薪值缩小固定比例。

如图五,我们需要确定比较大的数据到底需要缩小到什么程度,然后取一个系数,这里选取5.(因为雷哥、星爷、小刘的数据无需缩小,所以系数为1)

图五:构造新数据列

▼Step2、使用新数据列绘制柱状图

使用新的数据绘制柱状图,这时各个数值所代表的柱子的高度协调了(如果觉得差距还是大,可以调整Step1中的系数)。


图六:使用新数据列插入柱状图

这样调整之后,会发现,坐标轴上的数值也被缩小了5倍,需要修正坐标轴数值。

▼Step3、确定打断后的数值在Y轴上的实际值

y轴数值被缩小了,必须修正坐标值,但是Excel不允许自定义坐标轴标签,我们需要通过散点图来模拟Y坐标轴刻度。
(Tips:根据散点图的特性,通常可以用来模拟坐标轴,在其他的图表中也有用到)

那么散点图的数据如何设置呢?


图七:构造散点图数据

解释一下,这里的y、x是用来生成散点图的数据,y1是用于修改散点图标签的数据。

y值就是图六中Y坐标的数值。

y1=ya,即将柱状图缩小的系数再进行放大。*

50-500就是打断后抽走的数据,所以50以上系数为5,50以下系数为1

使用新数据插入散点图↓↓↓(注意,是使用x、y两列数据)

图八:生成散点图

Tips:将散点图绘制在次坐标,如果次坐标的值与柱坐标不一样,需要修改为与柱坐标完全一样,这样能够确保散点图刚好落在y轴刻度线上。

▼Step4.修改坐标轴标签值

①隐藏次坐标轴的刻度线和标签,并隐藏主坐标轴y轴标签


图9:隐藏坐标轴刻度线和标签

②修改散点图标签为y1值

我们可以自定义对散点图数值标签进行修改,改为y轴缩小前的数值(即图7中y1的值),从而达到以假乱真的效果。


图10:修改散点图标签值

为使给数据系列添加(编辑)标签更加方便,可以使用Excel的免费插件——XY Chart Labeler。

③隐藏散点图标记

为了使散点图模拟的坐标轴更加逼真,需要将散点图标记隐藏掉。

图11:隐藏散点图标记

完成的效果是这样的,还需要添加打断线。
图12:打断柱状图初效果

▼Step5、添加打断线标志,对图表进行适当美化
图13:添加打断标示、美化图表

Tips:打断线的添加方法

  1. 绘制一个白色填充的矩形框,并复制

  2. 选中柱状图,按Ctrl+V进行粘贴

  3. 调节矩形框的位置和角度即可

使用这样的方法添加的元素,和图表是融为一体的。

总的来看,使用散点图模拟y轴,是本例中的突破口,而将数据先缩小绘制出合适比例的柱状图、再将y轴数值修改为原始比例是本例的核心。

赶快操作一遍,向同事嘚瑟一下吧。

The End。

Copyright © 2016 安伟星. All Rights Reserved.


我是安伟星(星爷)
Excel发烧友微软
Office认证大师
领英专栏作者

关注我,也许不能带来额外财富
但是一定会让你看起来很酷

文章均为原创,如需转载,请私信获取授权。

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 155,770评论 4 358
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 66,116评论 1 286
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 105,656评论 0 237
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 43,372评论 0 201
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 51,704评论 3 285
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 40,166评论 1 204
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,535评论 2 306
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 30,259评论 0 193
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,882评论 1 236
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,218评论 2 239
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,760评论 1 255
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 28,126评论 2 249
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,667评论 3 228
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,935评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,644评论 0 192
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 35,171评论 2 265
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 35,119评论 2 258

推荐阅读更多精彩内容

  • 一、实验目的 学习使用 weka 中的常用分类器,完成数据分类任务。 二、实验内容 了解 weka 中 explo...
    yigoh阅读 8,177评论 5 4
  • 图表思维是数据分析思维中的最简单和最基本的思维,主要是通过图形和表格对数据进行转化。 文字有局限 看到这段话,你会...
    keeya阅读 5,492评论 1 21
  • 一周500字 | 0112 | No.2 | 他的故乡,也是她的异乡 我没怎么读过张爱玲的作品,只算大概知道她写过...
    用力生活的小寞阅读 277评论 0 1
  • 越来越多的体验以及别人的灌输,得出的一个结论就是,修行是逆人性的。 首先说说逆人性。违背你懒惰的天赋,去做一些你精...
    Tempoo阅读 268评论 0 0
  • 事情是这样的,有时候我想给 border 加点动效,但是无奈 css 本身的border 的动画不能满足我的需要,...
    jackPan阅读 1,613评论 2 6