Excel条件格式可视化大法：基础版

点击下方名片关注和星标『可乐的数据分析之路』

👆点击关注｜设为星标｜干货速递👆

哈喽大家好，我是可乐

Excel中的条件格式也是一种可视化，关于条件格式的可视化的内容，我分为了两部分，一个是基础版，也就是条件格式最常用的功能，另一个是进阶版，这篇文章，我们先来说基础功能。

条件格式的基础用法节选自我的书《Excel数据处理与分析——数据思维+分析方法+场景应用》

1.突出显示单元格规则

这是第一种突出显示单元格的类型，通过名字已经可以很明确地知道它的功能了，可以是数值比较、文本包含、日期以及重复值的突出显示，还可以自定义规则（在后面的新建规则中会详细讲解）。

1) 大于/小于/介于/等于

这都是数值比较的突出显示单元格，如图5.1.1所示将销量在80到100件的单元格突出显示，选中该列数据，单击【开始】——【条件格式】——【突出显示单元格规则】——【介于】，在弹出的【介于】对话框中，设置介于80到100的数值单元格填充浅红，字体为深红颜色，这样，就得到图5.1.2的条件格式效果。

2) 文本包含

对于文本数据而言，可以使用【文本包含】来突出显示文本数据所在的单元格。如图5.1.3所示对品类包含“牙”字的单元格突出显示，单击【开始】——【条件格式】——【突出显示单元格规则】——【文本包含】，在弹出的【文本中包含】对话框中，设置包含“牙”字的单元格填充浅红，字体为深红颜色，这样，就得到图5.1.4的条件格式效果。

3) 发生日期

对于日期数据，可以使用【发生日期】来突出显示日期数据所在的单元格，日期可以选择天、周或月的值，这里的昨天、本周的日期都是针对正在编辑工作表这一天而言。

如图5.1.5所示对日期中上个月的单元格突出显示，单击【开始】——【条件格式】——【突出显示单元格规则】——【发生日期】。

如图5.1.6所示，在弹出的【发生日期】对话框中，设置日期为上个月的单元格填充浅红，字体为深红颜色，当前编辑工作表的日期是10月4日，因此上个月即9月，这样，就得到图5.1.7所示的条件格式效果。

4) 重复值

突出显示某一列数据中重复的值是应用较多的规则。如图5.1.8对销量一列数值相同的单元格突出显示，单击【开始】——【条件格式】——【突出显示单元格规则】——【重复值】，在弹出的【重复值】对话框中，设置重复的单元格填充浅红，字体为深红颜色，这样，就得到图5.1.9所示的条件格式效果。

2. 最前/最后规则

这也是一种突出显示单元格的类型，只不过是突出显示一列数据中最前或最后的N项。

1) 最前/后N项

可以显示一列数据中排名靠前的N项，或者排名靠后的N项，N可以自行设定。如图5.1.10所示将销量中排名靠前的10个值突出显示，单击【开始】——【条件格式】——【最前/最后规则】——【前10项】，在弹出的【前10项】对话框中，设置前10项的单元格填充浅红，字体为深红颜色，这样，就得到图5.1.11所示的条件格式效果。

2) 高/低于平均值

对于一列数据中高于平均值或低于平均值的数据进行突出显示。如图5.1.12所示将单价中高于平均值的值突出显示，单击【开始】——【条件格式】——【最前/最后规则】——【高于平均值】，在弹出的【高于平均值】对话框中，设置前10项的单元格填充浅红，字体为深红颜色，这样，就得到图5.1.13所示的条件格式效果。

3. 数据条

数据条是条件格式的第二种类型，单元格内的可视化，可以用来展示数值的大小，比起单纯的数据表有一目了然，清晰可读的优点。

如图5.1.14对销量一列制作数据条，单击【开始】——【条件格式】——【数据条】，插入一个蓝色渐变填充的数据条，在E列就可以看到数值越大数据条越长，数值越小，数据条越短，数值的大小被清楚地展示了出来。

4. 色阶

色阶同数据条一样，是用来展示数值大小的，对于不同的数值设置不同的颜色加以区分。如图5.1.15对人口自然增长率一列制作色阶，单击【开始】——【条件格式】——【色阶】，插入一个绿白红色阶，颜色越深，数值越小，可以清晰地比较不同的数值。

5. 图标集

图标集也是一种单元格内可视化的类型，用来展示数据的特征，比如大于0的值显示↑图标，小于0的值显示↓图标；又或者考察学生成绩时，及格的显示√图标，不及格的显示×图标。如图5.1.16所示的人口增幅数据，欲对增幅一列正数显示↑图标，负数显示↓图标。

如图5.1.17所示，单击【开始】——【条件格式】——【图标集】——【其他规则】，因为这里需要自定义规则，所以已有的图标无法使用。

如图5.1.18所示，在弹出的【编辑格式规则】对话框中，图标样式选择3箭头图标，当值>0时显示向上绿色箭头，当值<=0且>=0，也就是等于0时，显示向右黄色箭头，当值<0时显示向下红色箭头，类型为数字，单击确定按钮，则可得到左边增幅图标的效果。

6. 新建规则

新建规则可以自定义条件和格式，即自行创建一条规则。在Excel条件格式中，有6种规则类型，可以满足几乎所有的情况。

1) 基于各自值设置所有单元格的格式

这条规则其实是数据条/色阶/图标集的一个自定义，即单元格内的可视化操作。如图5.1.19，对单价一列按数据升序颜色逐渐变浅的可视化操作，在【开始】——【条件格式】——【新建规则】中选择【基于各自值设置所有单元格的格式】，格式样式选择【双色刻度】，类型为最小值对应最低值，最大值对应最高值，单击确定，即可实现图示展示数据分布的效果。同样地，还可以在【格式样式】中对数据条、图标集进行自定义设置。

2) 只为包含以下内容的单元格设置格式

这条规则属于突出显示单元格的规则，相当于对突出显示单元格的自定义操作。如图5.1.20所示，在【开始】——【条件格式】——【新建规则】中选择【只为包含以下内容的单元格设置格式】，可以选择单元格值在特定区间、特定文本包含、日期在特定范围等条件。

如图5.1.21所示，对单价一列在15到30之间的单元格填充蓝色底色，选择“单元格值”，介于15到30，格式选择填充蓝色即可完成图示左边单价区域的条件格式效果。

3) 仅对排名靠前或靠后的数值设置格式

这条规则是对最前最后规则的一个自定义规则，也是对排名靠前或靠后的N项设置格式，对于N的值这条规则里可以自行设置。如图5.1.22所示，对单价排名靠前的前20项突出显示，则在【开始】——【条件格式】——【新建规则】中选择【仅对排名靠前或靠后的数值设置格式】，设置最高的20项内容填充黄色底色，字体倾斜加粗，即可得到图示的条件格式效果。

4) 仅对高于或低于平均值的数值设置格式

这条规则同样是对最前最后规则中高于低于平均值的一个自定义规则，如图5.1.23所示，可以选择的格式比较多，不局限于高于/低于平均值，还可以选择标准差高于/低于的格式。

如图5.1.24所示，对小于等于单价平均值的值突出显示，则在【开始】——【条件格式】——【新建规则】中选择【仅对高于或低于平均值的数值设置格式】，设置等于或低于平均值的格式为蓝色填充，填充效果为中心辐射，即可得到图示的条件格式效果。

5) 仅对唯一值或重复值设置格式

这条规则是对突出显示重复值的一个自定义，在这条规则中不仅能把重复值突出显示，还可以把剔除重复值后剩下的唯一值突出显示出来。如图5.1.25，将单价中不重复值突出显示，则在【开始】——【条件格式】——【新建规则】中选择【仅对唯一值或重复值设置格式】，格式设置为唯一值字体颜色为红色加粗，则可得到图示条件格式的效果。

6) 使用公式确定要设置格式的单元格

这条规则是条件格式里最难的规则，需要写出有效的公式，这个公式在写的时候需要注意写成逻辑真/假的关系，即只有在公式为真时，条件生效，格式出现；在公式为假时，条件不生效，无格式。如图5.1.26，要对销量大于等于95，且单价大于等于15的值突出显示，则在【开始】——【条件格式】——【新建规则】中选择【使用公式确定要设置格式的单元格】，公式为“=AND(F3>=15)”，AND表示“与”，这个公式的结果是一个逻辑真/假值，为真时设置填充绿色底色的格式，则可得到图示条件格式的效果。

以上就是条件格式可视化基础版的全部内容了

下一节内容是Excel条件格式的进阶版，主要是在熟练运用条件格式的基础上，进行一些好玩的操作，比如实现聚光灯效果、旋风图、用控件控制等。

以上内容摘自我的书《Excel数据处理与分析——数据思维+分析方法+场景应用》已上线，详细内容可点击->坚持写公众号3年，我的第一本书出版！主要讲解Excel之于数据分析、处理的作用。如有需要可直接点击下方链接购买。