文|仟樱雪

BIG DATA不仅是口号，如今90%的基础数据分析岗位都会准备Excel的机试，每每挂掉无数人，

作为初入职场的freshman，在面对一份份依据条件显示单元格的设置难题时，难免抓耳挠腮；

而作为久经职场的老鸟们，甚至简单的认为条件格式，就是标注显示重复值，难免一叶障目。

条件格式：将数据依据某些条件，进行快速的标注显示，例如字体设置、字号调整、字体颜色、填充颜色等进行特殊标注显示。

Excel条件格式，将数据单元格或数组进行了智能着色突出显示，通过添加强调、预警、分类、标注等效果让数据分析愈发自动化和智能化。

Excel条件格式的使用，是办公必备技能智能化数据分析的一大捷径，主要的精髓如下：

神技1：特殊值独特显示

神技2：独特显示特殊值

神技3：自定义显示特殊值

神技4：可视化显示特殊值

一、特殊显示独特值

1、标注显示重复值

例如：显示各平台日销售报表中，品类重复的记录：

操作：选中“品类”所在的G列，点击“开始”--“条件格式”--“突出显示单元格”--“重复值”；

说明：设置，重复值的显示格式为“浅红色填充，文本深红色填充”特殊显示，也可自定义显示颜色；

结果：“品类”中存在重复的记录则会被特殊标注显示。

2、根据数值条件，标注显示特殊值

例如：显示各平台日销售报表中，销量大于5件的产品记录：

操作：选中“数量”所在的I列，点击“开始”--“条件格式”--“新建格式规则”--“只为包含以下内容的单元格设置格式”；

说明：设置“只为满足以下条件的单元格设置格式”区域，单元格值是“大于”5的条件，预览设置为橙色显示；

结果：“销量”中大于5件的记录则会特殊显示为橙色。

3、根据文本条件，标注显示特殊值

例如：各平台日销售报表中，显示天猫平台的产品记录：

方法1：选中平台所在区域，在“条件格式区域”，直接在“突出显示单元格规则”下的快捷设置选项中，选择“文本包含”栏进行设置；

结果为：

说明：设置“为包含以下文本的单元格设置格式”区域，单元格文本为“天猫平台”，预览设置为“黄色填充，深黄色显示文本内容”显示；

结果：“平台”中包含“天猫平台”文本的记录被标注显示。

方法2：在“条件格式区域”，直接在“新建规则”下，重新设置：

操作：选中“平台”所在的C列，点击“开始”--“条件格式”--“新建格式规则”--“只为包含以下内容的单元格设置格式”--“特殊文本”；

说明：设置“只为满足以下条件的单元格设置格式”区域，单元格值是“特殊文本”的条件，，文本内容包含“天猫平台”，字样，预览设置为深黄色显示；

结果：“平台”中“天猫平台”的记录则会特殊显示为深黄色。

4、根据日期条件，标注显示特殊值

例如：各平台日销售报表中，显示本月的产品记录：

方法1:：选中设置区域，在“条件格式区域”，直接在“突出显示单元格规则”下的快捷设置选项中，选择“发生日期”栏进行设置；

结果为：

说明：设置“为包含以下文本的单元格设置格式”区域，单元格周期为“本月”，预览设置为“浅红色填充，深红色文本内容”显示；

结果：“日期”中本月（2017年10月）的日期的销售记录被标注显示为深红色。

方法2：在“条件格式区域”，直接在“新建规则”下，重新设置：

操作：选中“日期”所在的A列，点击“开始”--“条件格式”--“新建格式规则”--“只为包含以下内容的单元格设置格式”--“发生日期”；

说明：设置“只为满足以下条件的单元格设置格式”区域，单元格值是“发生日期”的条件，“本月””，预览设置为浅蓝色显示；

结果：“日期”中本月（2017年10月）的记录则会特殊显示为浅蓝色。

5、根据空值，标注显示特殊位置

例如：各平台日销售报表中，显示缺失值，即空白的单元格：

操作：选中数据源区域，点击“开始”--“条件格式”--“新建格式规则”--“只为包含以下内容的单元格设置格式”--“空值”；

说明：设置“只为满足以下条件的单元格设置格式”区域，单元格值是“空值”的条件，预览设置为深红色显示；

结果：数据区域中的空白单元格，则会特殊显示为深红色。

6、根据报错，标注显示特殊位置

例如：各平台日销售报表中，显示乱码或者公式报错，标注显示定位：

操作：选中数据源，点击“开始”--“条件格式”--“新建格式规则”--“只为包含以下内容的单元格设置格式”--“错误”；

说明：设置“只为满足以下条件的单元格设置格式”区域，单元格值是“错误”的条件，预览设置为浅蓝色显示；

结果：数据区域中的“报错”单元格，则会特殊显示为浅蓝色。

结果为：

二、独特值特殊显示

1、数据的Max、Min值特殊标记显示

例如：各平台日销售报表中，显示收入，最大值和最小值，标注显示定位：

操作1：选中数据源中“收入”所在H列，点击“开始”--“条件格式”--“新建格式规则”--“只为包含以下内容的单元格设置格式”--“单元格值”；

说明：设置“只为满足以下条件的单元格设置格式”区域，单元格值条件为“等于”，输入“=MAX($H$2:$H$18)”，预览设置为深红色显示；

操作2：选中数据源中“收入”所在H列，点击“开始”--“条件格式”--“管理规则”--“新建规则”--“只为包含以下内容的单元格设置格式”--“单元格值”；

说明：设置“只为满足以下条件的单元格设置格式”区域，单元格值条件为“等于”，输入“=MIN($H$2:$H$18)”，预览设置为深蓝色显示；

结果：数据区域H列的最大值、最小值的单元格，则会特殊显示为深红色和深蓝色。

结果显示：

2、数据的TOPn、LASTn值特殊标记显示

例如：各平台日销售报表中，显示收入的TOP3、LAST3记录，进行标注显示定位：

操作1：选中数据源中“收入”所在H列，点击“开始”--“条件格式”--“新建格式规则”--“对排名靠前或靠后的数值设置格式”--“最高”；

说明：设置“对以下排列的数值设置格式”区域，单元格值是“最高”且为3项的条件，预览设置为橘色显示；

操作2：选中数据源中“收入”所在H列，点击“开始”--“条件格式”--“管理规则”--“新建规则”--“对排名靠前或靠后的数值设置格式”--“最低”；

说明：设置“对以下排列的数值设置格式”区域，单元格值是“最低”且为3项的条件，预览设置为浅灰色显示；

结果：数据区域中的“TOP3”条记录单元格，则会特殊显示为橘色，“LAST3”条记录被标记为浅灰色。

结果为：

3、数据的前n%、后n%的数值特殊标记显示

备注：数据的百分比区域，可以按照需求进行调整。

例如：各平台日销售报表中，显示收入的前10%、后10%的数据记录，进行标注显示定位：

操作1：选中设置区域，在“条件格式区域”，直接在“最前/最后规则”下的快捷设置选项中，选择“前10%”栏进行设置；

说明：设置“为值最大的那些单元格设置格式”，单元格百分比区间为10%”，预览设置为“浅红色填充，深红色文本内容”显示；

操作2：选中数据源中“收入”所在H列，点击“开始”--“条件格式”--“管理规则”--“新建规则”--“对排名靠前或靠后的数值设置格式”--“最低”；

说明：设置“对以下排列的数值设置格式”区域，单元格值是“最低”且为10项的条件，且勾选“所选范围的百分比”，预览设置为浅黄色显示；

结果：数据区域中的前10%和后10%，则会特殊显示为浅红色、浅黄色。

4、数据的高于均值、低于均值的数值特殊标记显示

例如：各平台日销售报表中，显示高于“收入”平均值的数据记录以及低于平均值的数据记录，进行标注显示定位：

操作1：选中设置区域，在“条件格式区域”，直接在“最前/最后规则”下的快捷设置选项中，选择“高于平均值”栏进行设置；

说明：设置“为高于平均值的那些单元格设置格式”，预览设置为“浅红色填充，深红色文本内容”显示；

操作2：选中数据源中“收入”所在H列，点击“开始”--“条件格式”--“管理规则”--“新建规则”--“仅对高于或低于平均值的数值设置格式”--“低于”；

说明：设置“为满足以下条件的值设置格式”，条件是“低于”，预览设置为浅黄色显示；

结果：数据区域中高于“收入”的平均值的数据都被填充为浅红色，低于收入平均值的“收入”数据被填充为黄色。

结果为：

三、自定义显示特殊值

1、数据高亮显示，自动预警

例如：各平台日销售报表中，需自动预警最近一周的且是天猫平台的销售记录数据：

操作：选中整个数据源，点击“开始”--“条件格式”--“新建格式规则”--“使用公式确定要设置的单元格“=($A2<today()-7)*($C2="天猫平台")”；

说明：设置“只为符合此公式的值设置格式”区域，公式为“=（$A2<today()-7）*($C2="天猫平台")”，预览显示为浅黄色；

结果：数据区域中，满足销售日期为近一周且天猫平台的销售记录浅黄色标注显示。

2、数据间隔条纹，自动生成

例如：各平台日销售报表中，需隔行显示：

操作：选中整个数据源，点击“开始”--“条件格式”--“新建格式规则”--“使用公式确定要设置的单元格”--“=(MOD(ROW($A1),2)=1”；

说明：设置“只为符合此公式的值设置格式”区域，公式为“=(MOD(ROW($A1),2)=1”，预览设置为浅灰色显示，使用MOD函数对行号进行计算，行号为单设置浅灰色，行号为双不设置，即可生成间隔条纹；

3、数据整行记录，自动标识

例如：显示各平台日销售报表中，“阿里”平台的且是“山西”区域的产品销售记录：

操作：选中整个数据源，点击“开始”--“条件格式”--“新建格式规则”--“使用公式确定要设置的单元格”--“=($C2=$N$1)*($D2=$N$2)”；

说明：设置“只为符合此公式的值设置格式”区域，公式为“=($C2=$N$1)*($D2=$N$2)”，预览设置为浅黄色显示；

结果：数据区域中，满足N1单元格为“阿里”且N2单元格为“山西”的条件的记录，整行都显示为黄色。

注意：条件改变，高亮标注显示的数据，会因随着条件更改记录的显示。

4、数据隔列，错列显示

例如：各平台日销售报表中，收入大于100的产品名称“标黄色”显示，收入数据无需标注显示：

操作：选中整个数据源中“产品名称”所在的F列，点击“开始”--“条件格式”--“新建格式规则”--“使用公式确定要设置的单元格”--“=$H2>100”；

说明：设置“只为符合此公式的值设置格式”区域，公式为“=$H>100”，预览设置为浅黄色显示；

结果：数据区域中，收入大于100的产品名称自动标记为黄色填充的底色

四、数据可视化显示特殊值

数据分析仅是基础，高阶的进化便是可视化分析。

Excel的数据条件格式的可视化，主要应用于三个方面：数据条、色阶、图标集。

1、数据条

数据条：主要是将数据按照一列数据中的最大数据作为默认100%的填充，剩余单元格数据以此按照占比显示长短。

例如：各平台日销售报表中，各产品的毛利进行数据条显示：

操作：选中整个数据源中毛利所在L列，点击“开始”--“条件格式”--“新建格式规则”--“基于各自值设置所有单元格的格式”--“数据条”，且勾选“仅显示数据条”，避免数据和图的混乱，影响视图；

说明：设置条形图的外观“正值”为蓝色实心边框+蓝色渐变填充的柱形图；

结果：毛利列中正值的毛利为向右的蓝色柱形图，负值的毛利为向左的红色柱形图

备注：可以根据“负值和坐标轴”进行设置负值的轴列数据显示设置。

2、色阶

色阶：主要是根据单元格数值的大小，进行颜色深浅分类的标识。

常见的色阶主要有两种：双色刻度、三色刻度。

例如：各平台日销售报表中，各平台的收入按照双色刻度显示：

操作：选中整个数据源中收入所在H列，点击“开始”--“条件格式”--“新建格式规则”--“基于各自值设置所有单元格的格式”--“双色刻度”；

说明：设置最小值的颜色渐变为“深橘色”，最大值的颜色渐变为“浅黄色”；

结果：收入中值越高，颜色越浅淡，收入越低，颜色越深沉。

备注：三色刻度类似双色刻度的颜色深浅显示，只是多了一个颜色的渐变。

3、图标集

图标集：是利用图标，例如表情、三角形、圆形等对数据进行分类标识显示。

例如：各平台日销售报表中，各平台的收入按照分类进行显示，大于500的用绿色旗子标识，100-500的用黄色旗子标识，0-100的用红色旗子标识：

操作：选中整个数据源中收入所在H列，点击“开始”--“条件格式”--“新建格式规则”--“基于各自值设置所有单元格的格式”--“图标集”--“选择三角旗子”标识；

说明：根据需求的条件设置每个颜色的旗帜对应的数字区间，而不是百分比或者小数数据类型，其中大于等于500为绿色旗子，100-500为黄色旗子，0-100为红色旗子；

结果：每一个收入的数据前面均贴上了一个分类的旗帜标签。

备注：数据类型和图标类型均可根据需求设置，比如百分比区间、小数区间等。

Excel条件格式，作为简化的数据可视化捷径，让数据分析愈发的智能和简便化，作为办公神技之一，值得学习！！！

（注：2017.10.30,废柴日记，后续更新）