文|仟櫻雪
BIG DATA不僅是口號(hào)，如今90%的基礎(chǔ)數(shù)據(jù)分析崗位都會(huì)準(zhǔn)備Excel的機(jī)試，每每掛掉無(wú)數(shù)人，
作為初入職場(chǎng)的freshman，在面對(duì)一份份依據(jù)條件顯示單元格的設(shè)置難題時(shí)，難免抓耳撓腮；
而作為久經(jīng)職場(chǎng)的老鳥(niǎo)們，甚至簡(jiǎn)單的認(rèn)為條件格式，就是標(biāo)注顯示重復(fù)值，難免一葉障目。
條件格式：將數(shù)據(jù)依據(jù)某些條件，進(jìn)行快速的標(biāo)注顯示，例如字體設(shè)置、字號(hào)調(diào)整、字體顏色、填充顏色等進(jìn)行特殊標(biāo)注顯示。
Excel條件格式，將數(shù)據(jù)單元格或數(shù)組進(jìn)行了智能著色突出顯示，通過(guò)添加強(qiáng)調(diào)、預(yù)警、分類(lèi)、標(biāo)注等效果讓數(shù)據(jù)分析愈發(fā)自動(dòng)化和智能化。
Excel條件格式的使用，是辦公必備技能智能化數(shù)據(jù)分析的一大捷徑，主要的精髓如下：
神技1：特殊值獨(dú)特顯示
神技2：獨(dú)特顯示特殊值
神技3：自定義顯示特殊值
神技4：可視化顯示特殊值
一、特殊顯示獨(dú)特值
1、標(biāo)注顯示重復(fù)值
例如：顯示各平臺(tái)日銷(xiāo)售報(bào)表中，品類(lèi)重復(fù)的記錄：
操作：選中“品類(lèi)”所在的G列，點(diǎn)擊“開(kāi)始”--“條件格式”--“突出顯示單元格”--“重復(fù)值”；
說(shuō)明：設(shè)置，重復(fù)值的顯示格式為“淺紅色填充，文本深紅色填充”特殊顯示，也可自定義顯示顏色；
結(jié)果：“品類(lèi)”中存在重復(fù)的記錄則會(huì)被特殊標(biāo)注顯示。
2、根據(jù)數(shù)值條件，標(biāo)注顯示特殊值
例如：顯示各平臺(tái)日銷(xiāo)售報(bào)表中，銷(xiāo)量大于5件的產(chǎn)品記錄：
操作：選中“數(shù)量”所在的I列，點(diǎn)擊“開(kāi)始”--“條件格式”--“新建格式規(guī)則”--“只為包含以下內(nèi)容的單元格設(shè)置格式”；
說(shuō)明：設(shè)置“只為滿足以下條件的單元格設(shè)置格式”區(qū)域，單元格值是“大于”5的條件，預(yù)覽設(shè)置為橙色顯示；
結(jié)果：“銷(xiāo)量”中大于5件的記錄則會(huì)特殊顯示為橙色。
3、根據(jù)文本條件，標(biāo)注顯示特殊值
例如：各平臺(tái)日銷(xiāo)售報(bào)表中，顯示天貓平臺(tái)的產(chǎn)品記錄：
方法1：選中平臺(tái)所在區(qū)域，在“條件格式區(qū)域”，直接在“突出顯示單元格規(guī)則”下的快捷設(shè)置選項(xiàng)中，選擇“文本包含”欄進(jìn)行設(shè)置；
結(jié)果為：
說(shuō)明：設(shè)置“為包含以下文本的單元格設(shè)置格式”區(qū)域，單元格文本為“天貓平臺(tái)”，預(yù)覽設(shè)置為“黃色填充，深黃色顯示文本內(nèi)容”顯示；
結(jié)果：“平臺(tái)”中包含“天貓平臺(tái)”文本的記錄被標(biāo)注顯示。
方法2：在“條件格式區(qū)域”，直接在“新建規(guī)則”下，重新設(shè)置：
操作：選中“平臺(tái)”所在的C列，點(diǎn)擊“開(kāi)始”--“條件格式”--“新建格式規(guī)則”--“只為包含以下內(nèi)容的單元格設(shè)置格式”--“特殊文本”；
說(shuō)明：設(shè)置“只為滿足以下條件的單元格設(shè)置格式”區(qū)域，單元格值是“特殊文本”的條件，，文本內(nèi)容包含“天貓平臺(tái)”，字樣，預(yù)覽設(shè)置為深黃色顯示；
結(jié)果：“平臺(tái)”中“天貓平臺(tái)”的記錄則會(huì)特殊顯示為深黃色。
4、根據(jù)日期條件，標(biāo)注顯示特殊值
例如：各平臺(tái)日銷(xiāo)售報(bào)表中，顯示本月的產(chǎn)品記錄：
方法1:：選中設(shè)置區(qū)域，在“條件格式區(qū)域”，直接在“突出顯示單元格規(guī)則”下的快捷設(shè)置選項(xiàng)中，選擇“發(fā)生日期”欄進(jìn)行設(shè)置；
結(jié)果為：
說(shuō)明：設(shè)置“為包含以下文本的單元格設(shè)置格式”區(qū)域，單元格周期為“本月”，預(yù)覽設(shè)置為“淺紅色填充，深紅色文本內(nèi)容”顯示；
結(jié)果：“日期”中本月（2017年10月）的日期的銷(xiāo)售記錄被標(biāo)注顯示為深紅色。
方法2：在“條件格式區(qū)域”，直接在“新建規(guī)則”下，重新設(shè)置：
操作：選中“日期”所在的A列，點(diǎn)擊“開(kāi)始”--“條件格式”--“新建格式規(guī)則”--“只為包含以下內(nèi)容的單元格設(shè)置格式”--“發(fā)生日期”；
說(shuō)明：設(shè)置“只為滿足以下條件的單元格設(shè)置格式”區(qū)域，單元格值是“發(fā)生日期”的條件，“本月””，預(yù)覽設(shè)置為淺藍(lán)色顯示；
結(jié)果：“日期”中本月（2017年10月）的記錄則會(huì)特殊顯示為淺藍(lán)色。
5、根據(jù)空值，標(biāo)注顯示特殊位置
例如：各平臺(tái)日銷(xiāo)售報(bào)表中，顯示缺失值，即空白的單元格：
操作：選中數(shù)據(jù)源區(qū)域，點(diǎn)擊“開(kāi)始”--“條件格式”--“新建格式規(guī)則”--“只為包含以下內(nèi)容的單元格設(shè)置格式”--“空值”；
說(shuō)明：設(shè)置“只為滿足以下條件的單元格設(shè)置格式”區(qū)域，單元格值是“空值”的條件，預(yù)覽設(shè)置為深紅色顯示；
結(jié)果：數(shù)據(jù)區(qū)域中的空白單元格，則會(huì)特殊顯示為深紅色。
6、根據(jù)報(bào)錯(cuò)，標(biāo)注顯示特殊位置
例如：各平臺(tái)日銷(xiāo)售報(bào)表中，顯示亂碼或者公式報(bào)錯(cuò)，標(biāo)注顯示定位：
操作：選中數(shù)據(jù)源，點(diǎn)擊“開(kāi)始”--“條件格式”--“新建格式規(guī)則”--“只為包含以下內(nèi)容的單元格設(shè)置格式”--“錯(cuò)誤”；
說(shuō)明：設(shè)置“只為滿足以下條件的單元格設(shè)置格式”區(qū)域，單元格值是“錯(cuò)誤”的條件，預(yù)覽設(shè)置為淺藍(lán)色顯示；
結(jié)果：數(shù)據(jù)區(qū)域中的“報(bào)錯(cuò)”單元格，則會(huì)特殊顯示為淺藍(lán)色。
結(jié)果為：
二、獨(dú)特值特殊顯示
1、數(shù)據(jù)的Max、Min值特殊標(biāo)記顯示
例如：各平臺(tái)日銷(xiāo)售報(bào)表中，顯示收入，最大值和最小值，標(biāo)注顯示定位：
操作1：選中數(shù)據(jù)源中“收入”所在H列，點(diǎn)擊“開(kāi)始”--“條件格式”--“新建格式規(guī)則”--“只為包含以下內(nèi)容的單元格設(shè)置格式”--“單元格值”；
說(shuō)明：設(shè)置“只為滿足以下條件的單元格設(shè)置格式”區(qū)域，單元格值條件為“等于”，輸入“=MAX($H$2:$H$18)”，預(yù)覽設(shè)置為深紅色顯示；
操作2：選中數(shù)據(jù)源中“收入”所在H列，點(diǎn)擊“開(kāi)始”--“條件格式”--“管理規(guī)則”--“新建規(guī)則”--“只為包含以下內(nèi)容的單元格設(shè)置格式”--“單元格值”；
說(shuō)明：設(shè)置“只為滿足以下條件的單元格設(shè)置格式”區(qū)域，單元格值條件為“等于”，輸入“=MIN($H$2:$H$18)”，預(yù)覽設(shè)置為深藍(lán)色顯示；
結(jié)果：數(shù)據(jù)區(qū)域H列的最大值、最小值的單元格，則會(huì)特殊顯示為深紅色和深藍(lán)色。
結(jié)果顯示：
2、數(shù)據(jù)的TOPn、LASTn值特殊標(biāo)記顯示
例如：各平臺(tái)日銷(xiāo)售報(bào)表中，顯示收入的TOP3、LAST3記錄，進(jìn)行標(biāo)注顯示定位：
操作1：選中數(shù)據(jù)源中“收入”所在H列，點(diǎn)擊“開(kāi)始”--“條件格式”--“新建格式規(guī)則”--“對(duì)排名靠前或靠后的數(shù)值設(shè)置格式”--“最高”；
說(shuō)明：設(shè)置“對(duì)以下排列的數(shù)值設(shè)置格式”區(qū)域，單元格值是“最高”且為3項(xiàng)的條件，預(yù)覽設(shè)置為橘色顯示；
操作2：選中數(shù)據(jù)源中“收入”所在H列，點(diǎn)擊“開(kāi)始”--“條件格式”--“管理規(guī)則”--“新建規(guī)則”--“對(duì)排名靠前或靠后的數(shù)值設(shè)置格式”--“最低”；
說(shuō)明：設(shè)置“對(duì)以下排列的數(shù)值設(shè)置格式”區(qū)域，單元格值是“最低”且為3項(xiàng)的條件，預(yù)覽設(shè)置為淺灰色顯示；
結(jié)果：數(shù)據(jù)區(qū)域中的“TOP3”條記錄單元格，則會(huì)特殊顯示為橘色，“LAST3”條記錄被標(biāo)記為淺灰色。
結(jié)果為：
3、數(shù)據(jù)的前n%、后n%的數(shù)值特殊標(biāo)記顯示
備注：數(shù)據(jù)的百分比區(qū)域，可以按照需求進(jìn)行調(diào)整。
例如：各平臺(tái)日銷(xiāo)售報(bào)表中，顯示收入的前10%、后10%的數(shù)據(jù)記錄，進(jìn)行標(biāo)注顯示定位：
操作1：選中設(shè)置區(qū)域，在“條件格式區(qū)域”，直接在“最前/最后規(guī)則”下的快捷設(shè)置選項(xiàng)中，選擇“前10%”欄進(jìn)行設(shè)置；
說(shuō)明：設(shè)置“為值最大的那些單元格設(shè)置格式”，單元格百分比區(qū)間為10%”，預(yù)覽設(shè)置為“淺紅色填充，深紅色文本內(nèi)容”顯示；
操作2：選中數(shù)據(jù)源中“收入”所在H列，點(diǎn)擊“開(kāi)始”--“條件格式”--“管理規(guī)則”--“新建規(guī)則”--“對(duì)排名靠前或靠后的數(shù)值設(shè)置格式”--“最低”；
說(shuō)明：設(shè)置“對(duì)以下排列的數(shù)值設(shè)置格式”區(qū)域，單元格值是“最低”且為10項(xiàng)的條件，且勾選“所選范圍的百分比”，預(yù)覽設(shè)置為淺黃色顯示；
結(jié)果：數(shù)據(jù)區(qū)域中的前10%和后10%，則會(huì)特殊顯示為淺紅色、淺黃色。
4、數(shù)據(jù)的高于均值、低于均值的數(shù)值特殊標(biāo)記顯示
例如：各平臺(tái)日銷(xiāo)售報(bào)表中，顯示高于“收入”平均值的數(shù)據(jù)記錄以及低于平均值的數(shù)據(jù)記錄，進(jìn)行標(biāo)注顯示定位：
操作1：選中設(shè)置區(qū)域，在“條件格式區(qū)域”，直接在“最前/最后規(guī)則”下的快捷設(shè)置選項(xiàng)中，選擇“高于平均值”欄進(jìn)行設(shè)置；
說(shuō)明：設(shè)置“為高于平均值的那些單元格設(shè)置格式”，預(yù)覽設(shè)置為“淺紅色填充，深紅色文本內(nèi)容”顯示；
操作2：選中數(shù)據(jù)源中“收入”所在H列，點(diǎn)擊“開(kāi)始”--“條件格式”--“管理規(guī)則”--“新建規(guī)則”--“僅對(duì)高于或低于平均值的數(shù)值設(shè)置格式”--“低于”；
說(shuō)明：設(shè)置“為滿足以下條件的值設(shè)置格式”，條件是“低于”，預(yù)覽設(shè)置為淺黃色顯示；
結(jié)果：數(shù)據(jù)區(qū)域中高于“收入”的平均值的數(shù)據(jù)都被填充為淺紅色，低于收入平均值的“收入”數(shù)據(jù)被填充為黃色。
結(jié)果為：
三、自定義顯示特殊值
1、數(shù)據(jù)高亮顯示，自動(dòng)預(yù)警
例如：各平臺(tái)日銷(xiāo)售報(bào)表中，需自動(dòng)預(yù)警最近一周的且是天貓平臺(tái)的銷(xiāo)售記錄數(shù)據(jù)：
操作：選中整個(gè)數(shù)據(jù)源，點(diǎn)擊“開(kāi)始”--“條件格式”--“新建格式規(guī)則”--“使用公式確定要設(shè)置的單元格“=($A2<today()-7)*($C2="天貓平臺(tái)")”；
說(shuō)明：設(shè)置“只為符合此公式的值設(shè)置格式”區(qū)域，公式為“=（$A2<today()-7）*($C2="天貓平臺(tái)")”，預(yù)覽顯示為淺黃色；
結(jié)果：數(shù)據(jù)區(qū)域中，滿足銷(xiāo)售日期為近一周且天貓平臺(tái)的銷(xiāo)售記錄淺黃色標(biāo)注顯示。
2、數(shù)據(jù)間隔條紋，自動(dòng)生成
例如：各平臺(tái)日銷(xiāo)售報(bào)表中，需隔行顯示：
操作：選中整個(gè)數(shù)據(jù)源，點(diǎn)擊“開(kāi)始”--“條件格式”--“新建格式規(guī)則”--“使用公式確定要設(shè)置的單元格”--“=(MOD(ROW($A1),2)=1”；
說(shuō)明：設(shè)置“只為符合此公式的值設(shè)置格式”區(qū)域，公式為“=(MOD(ROW($A1),2)=1”，預(yù)覽設(shè)置為淺灰色顯示，使用MOD函數(shù)對(duì)行號(hào)進(jìn)行計(jì)算，行號(hào)為單設(shè)置淺灰色，行號(hào)為雙不設(shè)置，即可生成間隔條紋；
3、數(shù)據(jù)整行記錄，自動(dòng)標(biāo)識(shí)
例如：顯示各平臺(tái)日銷(xiāo)售報(bào)表中，“阿里”平臺(tái)的且是“山西”區(qū)域的產(chǎn)品銷(xiāo)售記錄：
操作：選中整個(gè)數(shù)據(jù)源，點(diǎn)擊“開(kāi)始”--“條件格式”--“新建格式規(guī)則”--“使用公式確定要設(shè)置的單元格”--“=($C2=$N$1)*($D2=$N$2)”；
說(shuō)明：設(shè)置“只為符合此公式的值設(shè)置格式”區(qū)域，公式為“=($C2=$N$1)*($D2=$N$2)”，預(yù)覽設(shè)置為淺黃色顯示；
結(jié)果：數(shù)據(jù)區(qū)域中，滿足N1單元格為“阿里”且N2單元格為“山西”的條件的記錄，整行都顯示為黃色。
注意：條件改變，高亮標(biāo)注顯示的數(shù)據(jù)，會(huì)因隨著條件更改記錄的顯示。
4、數(shù)據(jù)隔列，錯(cuò)列顯示
例如：各平臺(tái)日銷(xiāo)售報(bào)表中，收入大于100的產(chǎn)品名稱“標(biāo)黃色”顯示，收入數(shù)據(jù)無(wú)需標(biāo)注顯示：
操作：選中整個(gè)數(shù)據(jù)源中“產(chǎn)品名稱”所在的F列，點(diǎn)擊“開(kāi)始”--“條件格式”--“新建格式規(guī)則”--“使用公式確定要設(shè)置的單元格”--“=$H2>100”；
說(shuō)明：設(shè)置“只為符合此公式的值設(shè)置格式”區(qū)域，公式為“=$H>100”，預(yù)覽設(shè)置為淺黃色顯示；
結(jié)果：數(shù)據(jù)區(qū)域中，收入大于100的產(chǎn)品名稱自動(dòng)標(biāo)記為黃色填充的底色
四、數(shù)據(jù)可視化顯示特殊值
數(shù)據(jù)分析僅是基礎(chǔ)，高階的進(jìn)化便是可視化分析。
Excel的數(shù)據(jù)條件格式的可視化，主要應(yīng)用于三個(gè)方面：數(shù)據(jù)條、色階、圖標(biāo)集。
1、數(shù)據(jù)條
數(shù)據(jù)條：主要是將數(shù)據(jù)按照一列數(shù)據(jù)中的最大數(shù)據(jù)作為默認(rèn)100%的填充，剩余單元格數(shù)據(jù)以此按照占比顯示長(zhǎng)短。
例如：各平臺(tái)日銷(xiāo)售報(bào)表中，各產(chǎn)品的毛利進(jìn)行數(shù)據(jù)條顯示：
操作：選中整個(gè)數(shù)據(jù)源中毛利所在L列，點(diǎn)擊“開(kāi)始”--“條件格式”--“新建格式規(guī)則”--“基于各自值設(shè)置所有單元格的格式”--“數(shù)據(jù)條”，且勾選“僅顯示數(shù)據(jù)條”，避免數(shù)據(jù)和圖的混亂，影響視圖；
說(shuō)明：設(shè)置條形圖的外觀“正值”為藍(lán)色實(shí)心邊框+藍(lán)色漸變填充的柱形圖；
結(jié)果：毛利列中正值的毛利為向右的藍(lán)色柱形圖，負(fù)值的毛利為向左的紅色柱形圖
備注：可以根據(jù)“負(fù)值和坐標(biāo)軸”進(jìn)行設(shè)置負(fù)值的軸列數(shù)據(jù)顯示設(shè)置。
2、色階
色階：主要是根據(jù)單元格數(shù)值的大小，進(jìn)行顏色深淺分類(lèi)的標(biāo)識(shí)。
常見(jiàn)的色階主要有兩種：雙色刻度、三色刻度。
例如：各平臺(tái)日銷(xiāo)售報(bào)表中，各平臺(tái)的收入按照雙色刻度顯示：
操作：選中整個(gè)數(shù)據(jù)源中收入所在H列，點(diǎn)擊“開(kāi)始”--“條件格式”--“新建格式規(guī)則”--“基于各自值設(shè)置所有單元格的格式”--“雙色刻度”；
說(shuō)明：設(shè)置最小值的顏色漸變?yōu)椤吧铋偕?，最大值的顏色漸變?yōu)椤皽\黃色”；
結(jié)果：收入中值越高，顏色越淺淡，收入越低，顏色越深沉。
備注：三色刻度類(lèi)似雙色刻度的顏色深淺顯示，只是多了一個(gè)顏色的漸變。
3、圖標(biāo)集
圖標(biāo)集：是利用圖標(biāo)，例如表情、三角形、圓形等對(duì)數(shù)據(jù)進(jìn)行分類(lèi)標(biāo)識(shí)顯示。
例如：各平臺(tái)日銷(xiāo)售報(bào)表中，各平臺(tái)的收入按照分類(lèi)進(jìn)行顯示，大于500的用綠色旗子標(biāo)識(shí)，100-500的用黃色旗子標(biāo)識(shí)，0-100的用紅色旗子標(biāo)識(shí)：
操作：選中整個(gè)數(shù)據(jù)源中收入所在H列，點(diǎn)擊“開(kāi)始”--“條件格式”--“新建格式規(guī)則”--“基于各自值設(shè)置所有單元格的格式”--“圖標(biāo)集”--“選擇三角旗子”標(biāo)識(shí)；
說(shuō)明：根據(jù)需求的條件設(shè)置每個(gè)顏色的旗幟對(duì)應(yīng)的數(shù)字區(qū)間，而不是百分比或者小數(shù)數(shù)據(jù)類(lèi)型，其中大于等于500為綠色旗子，100-500為黃色旗子，0-100為紅色旗子；
結(jié)果：每一個(gè)收入的數(shù)據(jù)前面均貼上了一個(gè)分類(lèi)的旗幟標(biāo)簽。
備注：數(shù)據(jù)類(lèi)型和圖標(biāo)類(lèi)型均可根據(jù)需求設(shè)置，比如百分比區(qū)間、小數(shù)區(qū)間等。
Excel條件格式，作為簡(jiǎn)化的數(shù)據(jù)可視化捷徑，讓數(shù)據(jù)分析愈發(fā)的智能和簡(jiǎn)便化，作為辦公神技之一，值得學(xué)習(xí)?。?！
（注：2017.10.30,廢柴日記，后續(xù)更新）
