这两年来,数据可视化这个概念很热门,也是数据分析师必备的职业技能。
在企业中,老板这两年似乎也更重视分析分析了,无论是在人事部、财务部、销售部、商品部,或多或少都会要求下属进行一些数据分析工作。所以即使你不是数据分析师,也应该要具备一定的数据分析技能。不然怎么能满足老板的需求呢? (即使你的老板还没有这样的想法,上进的你也可提升自身技能,要么给老板好好看,要么给老板好看!~)
今天跟大家Excel里的【条件格式】功能,可以很方便地应用在日常报表的可视化中。
【条件格式】就是指在一定的条件下,单元格的显示格式。在Excel的【开始】选项卡,右边有个条件格式的图标,点击它就可以看到条件格式的菜单了。
我们先来看条件格式的【条件】,以Excel2007为例:
Excel默认内置有两大类条件,直接点选即可使用,建议大家可以打开一个有数据的表格,然后逐项点击尝试。
然后再来看条件格式的【格式】:
在满足一定的条件后,指定单元格可以显示哪些格式?
【数字】各种数字的格式都可以设置,常规、数字、货币、百分比、日期、时间……
【字体】设置单元格的字体,字体颜色
【边框】设置单元格的边框格式
【填充】设置单元格的填充格式
以上四类格式,可以单独,也可混合使用,任君选择。来看个小示范,以【值最大的10项】为例:
由以上示范可看到,使用单元格式的步骤为:
选定要设置的单元格区域
设定条件(上例为直接选定了内置的【值最大的10项】条件,然后手动改为3项)
设置格式,选择内置的条件,会有一些格式建议,当然也可以自定义格式。例子中是选择了红色填充,白色字体。
有条件,有格式后,指定的单元格的条件格式就生效了!前3大值的单元格就自动显示为红底白字,而且我更改数字的时候,格式也随之改变。
这个功能在我们日常的Excel报表中是非常实用的!内置的条件中,我用得比较多的是:
【重复值】:快速找到重复值
【前10项】:快速找到TOP10
想象一下,如果你看着一堆密密麻麻的数据表格,若要单凭肉眼从中找到重点,是非常费劲的,甚至多看几个就眼花缭乱了。
当然条件格式功能,还有更高阶一点的用法--【使用公式作为条件】
场景1:用表格给老板做报告,想美化一下格式,进行隔行填充颜色。
最笨的办法,当然是手动隔行填充,在行数比较少的情况下,当然无所谓。但当行数比较多时,你还用手动填充吗?如果你还手动的话,不好意思,我先下班走了……
这种情况下,条件格式就可以解决这个问题。好,问题来了,怎么设置【条件】?别急,跟着我来理思路。
首先我们要理解隔行填充,先确定是哪些行?要么是奇数行,要么是偶数行!以A1单元格为例,行号判定就用=ROW(A1),就可以显示行号数了;
接下来是怎么判定行号数的奇数还是偶数,可以用ISODD()判定奇数,ISEVEN()判定偶数就可以实现了!(当然还有其他办法可以判断,大家可以自行百度)
以A1单元格判定为例,输入=ISODD(ROW(A1)),如果返回是TRUE,就是奇数的行号了!
所以我们只要选定表格的所有单元格,新建条件格式规则,然后输入公式=ISODD(ROW(单元格))=TRUE,再设定格式为填充颜色,即可实现隔行填充,详细操作请看以下动图示范:
细看动图的朋友,可以发现有个小细节:输入公式时,G1默认是绝对引用的$G$1,因为我们要所有已选定的单元格都应用公式,所以要取消绝对引用(删除$),这样应用条件格式就是所有选定的单元格,这个细节要注意一下,否则会只是G1的条件格式生效!
场景2:一份天气数据,若是纯粹的表格方式,不够生动直观,如何可视化?
条件格式菜单中,还有【数据条】和【色阶】两个格式:
【数据条】:其实就是在单元格里的条形图,满足【条件】的单元格,会根据单元格里的数值大小,自动生成不同长短的数据条。
【色阶】:用不同的颜色过渡来表示单元格里数值的大小。
条件格式中内置有数据条和色阶的设定,直接选择即可使用,当然也可以进行自定义修改。
用数据条和色阶进行可视化,看看以下动图的示范吧:
最终出来的效果图如下:
这样就比纯粹地看数据表格更美观、直观了!
条件格式的高阶应用中,主要是【条件】的灵活运用,通过不同的公式来设置条件。例如在一些Excel管理模板,企业的分析仪表板(Dashboard)中,条件格式多用于预警提示的设定,例如同比上升了,就把字体显示为红色,同比下滑了就显示为绿色。
例如在黄成明老师的《零售店铺预测分析追踪模板》中,在【杜邦分析】表里,就应用了这个条件格式的功能:
在日常的Excel表格中,如果你能灵活运用条件格式,会提高受众读表的效率,也有美化的作用。看了那么多,赶紧玩起来吧,用条件格式处理一下你的数据表格,给你老板好看!