不可思议,一个函数竟然能玩出10种可视化图表!数据可视化
工具
不可思议,一个函数竟然能玩出10种可视化图表!数据可视化
案例
不可思议,一个函数竟然能玩出10种可视化图表!数据可视化
可视化
不可思议,一个函数竟然能玩出10种可视化图表!数据可视化
商业智能
不可思议,一个函数竟然能玩出10种可视化图表!数据可视化
Excel

很久以前,我们就介绍过一个小技巧,使用 REPT 函数来做单元格里的条形图,也可以叫数据条。相比 Excel 本身的条件格式 - 数据条,它有很多优点,所以即使你的 Office 版本已经升级到了的 2016 版,这个小技巧仍然值得学会和使用。


1、REPT 函数原理介绍

REPT 应该是单词 repeat(重复)的缩写,它对指定的字符重复指定的次数,返回一个字符串。例如,


公式:=REPT("A",5) 结果:AAAAA

公式:=REPT("★",5) 结果:★★★★★


这就是 REPT 函数的设计功能。微软的开发者可能没想到,我们接下来的玩法可能会把这个函数玩坏。


2、单元格里的数据条


如果我们写公式:

=REPT("|",50)


其中这个小竖线,就是回车键上面的那个符号,按住 Shift 键输入。现在得到50条稀疏的小竖线。

如果我们给单元格设置 playbill 字体,就得到了无缝隙的单元格里的数据条!


运用到实际工作中,假如我们有如下的表格,



不可思议,一个函数竟然能玩出10种可视化图表!数据可视化

在 D6 单元格写公式:


=REPT("|",C6/MAX($C$6:$C$10)*200)


即根据指标数值大小按比例折算重复的次数,较大的数值重复200次(可自定)。这样不论你的指标为何数量级都可以适应。

然后设置为 playbill 字体,8磅,灰色,得到非常舒服的数据条。


这种数据条,不像 Excel 本身的数据条占满了整个单元格的垂直方向,大小合适,从而更清晰。

你可以通过单元格的字体大小、颜色来设置出各种风格,非常方便。例如,要突出强调某行的数据条,直接设置为红色即可,而这是内置数据条做不到的。


我把这个方法简记为【REPT("|",n)+playbill】,方便记忆和运用。


通常,介绍用 REPT 函数做数据条的方法,一般就是到这里为止。下面我们打开脑洞,玩出更多形式。


3、倾斜字体的数据条

既然是文本字符,我们设置字体为倾斜,会有什么效果?刀片一样的数据条,锋利,很有个性!



不可思议,一个函数竟然能玩出10种可视化图表!数据可视化

4、使用斜杠的数据条

说到倾斜,我们重复字符的时候,如果不使用小竖线,而使用斜杠符号 / ,会得到什么呢?就是这种风格了。


不可思议,一个函数竟然能玩出10种可视化图表!数据可视化

不要以为这是我在瞎想,我曾在国内以图表闻名的《第一财经周刊》上看到这样的图表,你不觉得它就是使用 REPT 函数制作的吗?



不可思议,一个函数竟然能玩出10种可视化图表!数据可视化

你可以这样想象,左边的实心条形图,就是 REPT("|",n)+playbill,右边的斜线条形图,就是 REPT("/",n) 嘛。看看我们模拟的图,是不是这样呢?(注:实际上一财的编辑怎样制作我们并不知道,但显然这样可以非常简单地做出来。)


不可思议,一个函数竟然能玩出10种可视化图表!数据可视化

5、单元格里的柱形图

既然可以倾斜,那能不能竖起来呢?我在《华尔街日报》上看到过这样一个图表案例,这些嵌入在表格里的柱形图,如果用图表来做显然很麻烦。怎样才能简单方便地实现呢?



不可思议,一个函数竟然能玩出10种可视化图表!数据可视化

我还是用 REPT("|",n)+playbill,做出单元格里的数据条,然后单元格格式里设置文本的方向为 90 度,就得到了下面的柱形图!


不可思议,一个函数竟然能玩出10种可视化图表!数据可视化

6、单元格里的漏斗图

单元格文本除了字体设置,还可以设置对齐方向。如果我们把这些单元格里的文本设置 居中对齐,立即就得到了一个漏斗图!


不可思议,一个函数竟然能玩出10种可视化图表!数据可视化

漏斗图常用来反映一个业务流程中各环节数据衰减变化的过程。当我们用图表来做漏斗图的时候,需要使用辅助序列占位的技巧,而用 REPT 函数做,竟然只需要一个居中对齐!


7、单元格里的蝴蝶图

蝴蝶图是一种左右对比的条形图,如果用图表来做,会比较麻烦。而如果在单元格做,使用 REPT 函数分别作出两列数据条,然后分别设置右对齐和左对齐,就可以得到了。


不可思议,一个函数竟然能玩出10种可视化图表!数据可视化

范例中,我们给条形图带上了指标数值,公式如下:

左侧:=C6&" " & REPT("|",C6/MAX($C$6:$D$13)*50),右对齐

右侧:=REPT("|",D6/MAX($C$6:$D$13)*50) & " "&D6,左对齐


由于同时使用了竖线和数字,需要找到一种合适的字体,使竖线和数字的显示都可以接受,范例文件里给出了几种字体的显示结果。


8、单元格里的甘特图

继续开脑洞,项目管理里的甘特图,我们也可以用 REPT("|",n) 方法来做,因为它也是单元格里的条形图。


不可思议,一个函数竟然能玩出10种可视化图表!数据可视化

F6 的公式:

=REPT(" ",(C6-MIN($C$6:$C$14))*$H$7) & REPT("|",1.5*(D6-C6+1)*$H$7)


前半截重复空格占位,重复次数为该行的开始日期-整个项目的最小开始日期。后半截重复小竖线绘制甘特图,重复次数为工期数*1.5。


为什么要乘1.5?因为在 Arial 字体、8磅大小下,一个空格的宽度(大约)是小竖线“|”的1.5倍,因此我们重复小竖线的时候就放大1.5倍。


对于简单的项目进度安排,如果不想使用 Project 或其他专门工具,那么像这样在 Excel 里用公式拉一下,就出来了甘特图,效果也是极好的。事实上,绝大多数项目计划可能都是在 Excel 而不是 Project 里进行的。


9、单元格里的瀑布图

上面的甘特图其实已经有了瀑布图的雏形了,如果再有往左的行,就是标准的嵌入单元格里的瀑布图了。

瀑布图是麦肯锡公司发明,用来反映从一个数据到另一个数据的变化过程。


Excel 2016 版本新增了瀑布图类型,不过仍然不能制作水平的瀑布图。使用嵌入表格的水平瀑布图来可视化损益表,是非常合适的,可以说是财务较佳实践,我们在《让你的表格会说话》课程里曾专门开设 1 章来介绍多种方法。



不可思议,一个函数竟然能玩出10种可视化图表!数据可视化

要 REPT 出这样的瀑布图,我们要准备1列辅助的占位数据,根据这个占位数据 REPT 空格来占位,根据指标数据 REPT 小竖线来绘制条形图。D6的公式:

=REPT(" ",G6*$C$19)&REPT("|",ABS(C6)*$C$19*1.5)


其中,小竖线的重复次数也要放大1.5倍,也要设置 Arial 字体、8磅大小。

由于指标数量级可能很大也可能很小,需要使用一个系数来折算出合理的重复次数。

红色的行,则是根据指标正负来设置条件格式字体颜色实现的。


这个案例略微有点难度,大家可以下载范例后仔细研究。本文最后有下载方式。


10、单元格里的圆点百分比图

前面两个例子,公式的写法略微有些烧脑了,本贴最后,我们用个简单点的形式来结尾。

在《向经济学人学图表》课程里,我们介绍过这样一款偏信息图表风格的案例:


不可思议,一个函数竟然能玩出10种可视化图表!数据可视化

课程里介绍了多个实现方法,其中之一,就是使用 REPT 函数。


我们使用的公式是:


=REPT("●",B6*100)


得到和百分比数字一样个数的的小黑点,然后我们设置单元格 自动换行,调整列宽,使每行刚好能容纳10个黑点,就得到了这样的信息图表!其中还需要些其他技巧,这里不细述,详见范例文件。


欢迎加入本站公开兴趣群

商业智能与数据分析群

兴趣范围包括各种让数据产生价值的办法,实际应用案例分享与讨论,分析工具,ETL工具,数据仓库,数据挖掘工具,报表系统等全方位知识

QQ群:418451831

分页:12
转载请注明
本文标题:不可思议,一个函数竟然能玩出10种可视化图表!数据可视化
本站链接:http://www.codesec.net/view/565700.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 商业智能 | 评论(0) | 阅读(60)