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

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

[专栏] / 挖课机 [作者] / 刘万祥

这个非常简单,但运用形式可以非常多的技巧,你一定没有见过。

>> 点击查看相关课程 :经济学人的动态图表与交互设计

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

1、REPT 函数原理介绍

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

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

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

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

2、单元格里的数据条

如果我们写公式:

=REPT("|",50)

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

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

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

图片

在 D6 单元格写公式:

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

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

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

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

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

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

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

3、倾斜字体的数据条

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

图片

4、使用斜杠的数据条

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

图片

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

图片

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

图片

5、单元格里的柱形图

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

图片

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

图片

6、单元格里的漏斗图

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

图片

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

7、单元格里的蝴蝶图

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

图片

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

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

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

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

8、单元格里的甘特图

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

图片

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倍。

这里H7是一个调节系数,我用来控制进度图的长度合适。

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

9、单元格里的瀑布图

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

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

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

图片

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

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

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

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

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

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

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

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

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

图片

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

图片

我们使用的公式是:

=REPT("●",B6*100)

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

***

你看,一个再简单不过的函数和字符,只要我们创意足够,竟然可以玩出这么多花样,很多还是高级图表类型,但实现又是如此简单。

我们在云课堂的商业图表系列课程,都不是 Excel 的常规用法,都是原创、独创的用法,一般书上没有的,讲究专业有效,还要简单实用,人人可以学会、可以运用。

友情提示9月23日~30日,网易举行秋季促销活动,“打折+抵券+赠书”3重优惠,史上最大优惠力度,欢迎大家趁活动加入,记得先领优惠券。

图片

(点击看大图)

新课购买:经济学人的动态图表与交互设计 >>

以上案例的 Excel 源文件,可在微信公众号iamExcelPro发送消息“REPT”来获取下载。

如果在这篇贴子的灵感之下,关于 REPT 函数你有了更有意思的玩法,不要忘了告诉我哈。