欢迎关注公众号和知乎专栏：SHU说新语
今天来讲个轻松一点的场景，那就是条件格式。追求数据的可视化一直是数据分析的目的之一，做成图表无疑是最可视化的，但做图表一来麻烦，二来也会损失很多信息。Excel中，便有条件格式这个功能，能够基于表格本身，施加可视化，帮助大家更好的理解。本次分为应用场景、入口和初级功能、高级功能三块。
1应用场景
那什么是条件格式呢，相信能有兴趣读到这篇文章的人也是使用excel不少的同学，一定也看过类似下面的图。总结来看，条件格式的使用场景就两种：
- 重点突出某些对象：有时候想重点突出个别拥有某些特征的对象，如“高于平均值“，或者“某一特定分类“等，也可以用条件格式完成
- 可视化各个对象之间的属性差异：比如这10几个游戏的使用时长，分别是20-80分钟不等，直接看这些数字可能不够直观，但是专门做一张柱形图也是比较麻烦。因此通过内置的条件格式，便可直观显示出不同游戏这一数值的相对大小
2 使用入口与初级功能
条件格式的入口非常好找，就在“开始”tab下面的样式中，可以看到如下的界面，内置的这些基础规则非常简单易懂好上手，选中要使用条件格式的单元格区域，然后点击相应规则即可，我们只做简单梳理。
场景1：重点突出某些对象
针对第一种重点突出某些对象的使用场景，条件格式内置了两大类规则：“突出显示单元格规则”与“最前/最后规则”。
- 突出显示单元格规则
- 这一组的特点便是基于某些绝对的筛选规则，进行突出显示，比如点击“大于”规则后，会弹出以下窗口。左边的筛选条件可以是手动敲入的数字如“16”，也可以是引用的单元格，然后既然涉及引用，便又涉及到绝对引用与相对引用的关系。
-
- 绝对引用的时候，所有单元格都会去和L10这个单元格里的内容做比较（L10单元的内容是5），因此可以看出所有的日活这一列的单元格都变红了，因为都满足这个条件。
-
- 而如果设置为相对引用，那么其实单元格比较的是同一行的数字，可以看到只有日均活跃用户大于同行的最后一列数字时，才会标红。
- 最前最后规则
- 这一组主要是利用一些相对规则，来实现突出显示，比如显示最高的10%或10个数，显示高于平均值的数等，非常好理解，不做赘述
场景2：可视化对象间属性差异
针对第一种可视化各个对象之间的属性差异的使用场景，则内置了数据条、色阶和标志。
- 数据条便是用长度来区分数值的大小，色阶则是用颜色的深浅来区分，图标集则是用更灵活的图标来区分，分别看三个例子，同样的数据，可以用不同的方式区分，各有特点，大家根据可视化效果自己选择即可。
如果想对其进行更多关于格式等个性化设置，可以点击下面的“其他规则”，然后跳转到编辑规则界面中，设置诸如条形图的方向、填充颜色，更改最小值最大值等等
关于更改最小值与最大值，可以详细再解释一下，上图中的最小值与最大值可以更改类型
- 下图最左边一列：如果自动设定的化，那么自动会把区域里的最大值当做满格的长度处理，其他格子的长度按照与最长格子的百分比来显示，满意度相差不大的情况下，可以看到蓝条长度都差不多，难以区分
- 下图中间一列：如果我们认为15%左右的满意度太低了，蓝条的长度一定要短一些。那么就可以把最大值和最小值设定为0和1，这样蓝条长度便缩短为实际的百分比例，虽然各个单元格之间差别不大，但可以立即知道这个满意度不高
- 下图中最右一列：还有一种情况，如果想明显的显示出各个单元格的差别（虽然都是15%左右），那么可以把最小值和最大值的设定变为区域的最低值和最高值，即最小值会变成14.3%，对应蓝条长度为0，最大值会变成15.7%，对应蓝条长度为整个单元格宽度
3 高级功能-自定义规则
前面讲的东西都比较基础和傻瓜操作，如果想要更进一步的定制化你的单元格格式，便需要了解更多深层次的知识和灵活使用公式。
条件格式的本质其实是给一些单元格增加了一些显示规则，按照excel的原理，施加的规则一定会有个管理界面。这个界面还是在条件格式的功能tab下面便可以找到，分别是新建规则、清除规则和管理规则。
我们以一个稍微复杂一些的为例，解释一下。如下图，可以看到这个单元格所在的$J$10:$J$22区域，被添加了4个不同的突出显示规则。Excel的逻辑是优先应用最上面的逻辑，比如一个规则要让这个单元格变红，另外一个规则这个单元格不变，哪么谁在上面会优先执行哪个。
上面的应用于里面有个区域，表面这条规则对这些区域里的单元格适用。值得一提的是当复制一个具有条件格式的单元格或区域到另一数据区域时，全部粘贴或粘贴格式都会把条件格式的规则粘贴到新的区域中。
那从这里也看出，我们可以自己新建规则，点击新建规则，可以看到以下的规则类型。那么前5条其实比较眼熟，就是我们前面初级功能里讲到的类型，如果想添加这些类型的规则，不需要这么麻烦，直接在那些入口里添加即可。这里唯一需要讲的便是最后一条，使用公式确定要设置格式的单元格，这一条使得条件格式变的非常的自由和定制化。
我们用一个例子来说明好了，以下是本文初展示的数据，包含了一堆游戏和他们的一些属性。我们想做的事情是把活跃用户数大于100万的益智休闲类游戏所在的行突出显示。这个需求很正常吧，经常平时需要把一些重点的行标注出来等。普通的简单规则，只能把活跃用户所在的I列或二级分类所在的G列，根据单元格内容，突出显示出来，但无法应用到全行。
想实现这个功能，我们就需要用到使用公式确定要设置格式的单元格。
- 第一步：我们先选中我们想应用这个格式的区域，在这个例子中，因为我们是希望应用在D10:I22的区域中，当每一行满足条件时，该行D列：I列都能变格式
- 第二步：选择条件格式里的新建规则，并选择使用公式确定要设置格式的单元格
- 第三步：输入公式，这里的公式很值得注意，格式是先写一个"="，然后写上"公式表达式"，当公式表达式为True时，便会显示设定的格式，这里的例子便是如下，AND($G10="益智休闲",$I10>100)为公式表达式。有的同学可能不太理解这里绝对引用和相对引用的情况，那么来解释下。
-
- 首先，之所以选第10行，是因为第10行为区域里的第一行，我先判断第一个游戏的类别和活跃用户是否符合要求。
- 然后，之所以锁定列($G, $I) 是因为，以区域里第一行为例，所有的单元格（D10也好，E10也好，F10也好），如果想让他们显示新的格式，那么都要基于对G10和I10满足条件与否的判定。如果不锁定列，那么只有区域的第一列D10单元格会基于G10和I10的判定，E10单元格的格式就会基于H10和J10的判定（相对的往后移动一列），所以我一定要锁列
- 然后，之所以不锁定行，是因为区域里的第一行即表格的第10行的格式，要基于G10和I10的判定，第11行就要基于G11和I11的判定，是一定要移动的，所以要不锁定行，作为相对引用
- 第四步：选格式，如果选定区域的单元格满足了公式的要求，那么将呈现什么格式呢？我们可以在下方的格式中进行设置，比如我设置了填充色为粉色
- 最后，看结果如下，活跃用户大于100万的益智休闲游戏被标记成了粉色
关于使用公式进行条件格式的应用，大家自行多多练习尝试。如果发现格式不是自己想要的，那么检查以下几点：
- 打开规则管理，看看是否有多个条件格式规则，把想要的格式提到优先级最高，或者把其他的删掉
- 检查公式的绝对引用相对引用是否正确，可以变换下多试试，同时也避免写太复杂的公式，减少出错概率
综合来说，条件格式并不是一个复杂的功能，同时在应用场景上并不是数据分析核心必备的功能，更多作为锦上添花即可。
本系列之前文章请看：
工作中常用到的基础及中级excel技能总结系列 1 - 预先评估，如何根据应用场景合理的使用excelmp.weixin.qq.com
关注公众号：SHU说新语，前BCG咨询顾问，现字节互联网民工。分享生活学习和数据有关的书、数、术
2135
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
