文|仟櫻雪

BIG DATA不僅是口號贡这，如今90%的基礎(chǔ)數(shù)據(jù)分析崗位都會準(zhǔn)備Excel的機試弥姻，每每掛掉無數(shù)人，

作為初入職場的freshman眼滤，在面對一份份依據(jù)條件顯示單元格的設(shè)置難題時，難免抓耳撓腮历涝；

而作為久經(jīng)職場的老鳥們诅需，甚至簡單的認(rèn)為條件格式漾唉，就是標(biāo)注顯示重復(fù)值，難免一葉障目堰塌。

條件格式：將數(shù)據(jù)依據(jù)某些條件赵刑，進(jìn)行快速的標(biāo)注顯示，例如字體設(shè)置场刑、字號調(diào)整般此、字體顏色、填充顏色等進(jìn)行特殊標(biāo)注顯示牵现。

Excel條件格式铐懊，將數(shù)據(jù)單元格或數(shù)組進(jìn)行了智能著色突出顯示，通過添加強調(diào)瞎疼、預(yù)警科乎、分類、標(biāo)注等效果讓數(shù)據(jù)分析愈發(fā)自動化和智能化贼急。

Excel條件格式的使用喜喂，是辦公必備技能智能化數(shù)據(jù)分析的一大捷徑，主要的精髓如下：

神技1：特殊值獨特顯示

神技2：獨特顯示特殊值

神技3：自定義顯示特殊值

神技4：可視化顯示特殊值

一竿裂、特殊顯示獨特值

1玉吁、標(biāo)注顯示重復(fù)值

例如：顯示各平臺日銷售報表中，品類重復(fù)的記錄：

操作：選中“品類”所在的G列腻异，點擊“開始”--“條件格式”--“突出顯示單元格”--“重復(fù)值”进副；

說明：設(shè)置，重復(fù)值的顯示格式為“淺紅色填充悔常，文本深紅色填充”特殊顯示影斑，也可自定義顯示顏色；

結(jié)果：“品類”中存在重復(fù)的記錄則會被特殊標(biāo)注顯示机打。

2矫户、根據(jù)數(shù)值條件，標(biāo)注顯示特殊值

例如：顯示各平臺日銷售報表中残邀，銷量大于5件的產(chǎn)品記錄：

操作：選中“數(shù)量”所在的I列皆辽，點擊“開始”--“條件格式”--“新建格式規(guī)則”--“只為包含以下內(nèi)容的單元格設(shè)置格式”；

說明：設(shè)置“只為滿足以下條件的單元格設(shè)置格式”區(qū)域芥挣，單元格值是“大于”5的條件驱闷，預(yù)覽設(shè)置為橙色顯示；

結(jié)果：“銷量”中大于5件的記錄則會特殊顯示為橙色空免。

3空另、根據(jù)文本條件，標(biāo)注顯示特殊值

例如：各平臺日銷售報表中蹋砚，顯示天貓平臺的產(chǎn)品記錄：

方法1：選中平臺所在區(qū)域扼菠，在“條件格式區(qū)域”摄杂，直接在“突出顯示單元格規(guī)則”下的快捷設(shè)置選項中，選擇“文本包含”欄進(jìn)行設(shè)置循榆；

結(jié)果為：

說明：設(shè)置“為包含以下文本的單元格設(shè)置格式”區(qū)域匙姜，單元格文本為“天貓平臺”，預(yù)覽設(shè)置為“黃色填充冯痢，深黃色顯示文本內(nèi)容”顯示氮昧；

結(jié)果：“平臺”中包含“天貓平臺”文本的記錄被標(biāo)注顯示。

方法2：在“條件格式區(qū)域”浦楣，直接在“新建規(guī)則”下袖肥，重新設(shè)置：

操作：選中“平臺”所在的C列，點擊“開始”--“條件格式”--“新建格式規(guī)則”--“只為包含以下內(nèi)容的單元格設(shè)置格式”--“特殊文本”振劳；

說明：設(shè)置“只為滿足以下條件的單元格設(shè)置格式”區(qū)域椎组，單元格值是“特殊文本”的條件，历恐，文本內(nèi)容包含“天貓平臺”寸癌，字樣，預(yù)覽設(shè)置為深黃色顯示弱贼；

結(jié)果：“平臺”中“天貓平臺”的記錄則會特殊顯示為深黃色蒸苇。

4、根據(jù)日期條件吮旅，標(biāo)注顯示特殊值

例如：各平臺日銷售報表中溪烤，顯示本月的產(chǎn)品記錄：

方法1:：選中設(shè)置區(qū)域，在“條件格式區(qū)域”庇勃，直接在“突出顯示單元格規(guī)則”下的快捷設(shè)置選項中檬嘀，選擇“發(fā)生日期”欄進(jìn)行設(shè)置；

結(jié)果為：

說明：設(shè)置“為包含以下文本的單元格設(shè)置格式”區(qū)域责嚷，單元格周期為“本月”鸳兽，預(yù)覽設(shè)置為“淺紅色填充，深紅色文本內(nèi)容”顯示罕拂；

結(jié)果：“日期”中本月（2017年10月）的日期的銷售記錄被標(biāo)注顯示為深紅色揍异。

方法2：在“條件格式區(qū)域”，直接在“新建規(guī)則”下聂受，重新設(shè)置：

操作：選中“日期”所在的A列蒿秦，點擊“開始”--“條件格式”--“新建格式規(guī)則”--“只為包含以下內(nèi)容的單元格設(shè)置格式”--“發(fā)生日期”烤镐；

說明：設(shè)置“只為滿足以下條件的單元格設(shè)置格式”區(qū)域，單元格值是“發(fā)生日期”的條件，“本月””干签，預(yù)覽設(shè)置為淺藍(lán)色顯示；

結(jié)果：“日期”中本月（2017年10月）的記錄則會特殊顯示為淺藍(lán)色渡处。

5、根據(jù)空值祟辟，標(biāo)注顯示特殊位置

例如：各平臺日銷售報表中医瘫，顯示缺失值，即空白的單元格：

操作：選中數(shù)據(jù)源區(qū)域旧困，點擊“開始”--“條件格式”--“新建格式規(guī)則”--“只為包含以下內(nèi)容的單元格設(shè)置格式”--“空值”醇份；

說明：設(shè)置“只為滿足以下條件的單元格設(shè)置格式”區(qū)域，單元格值是“空值”的條件吼具，預(yù)覽設(shè)置為深紅色顯示僚纷；

結(jié)果：數(shù)據(jù)區(qū)域中的空白單元格，則會特殊顯示為深紅色拗盒。

6怖竭、根據(jù)報錯，標(biāo)注顯示特殊位置

例如：各平臺日銷售報表中陡蝇，顯示亂碼或者公式報錯痊臭，標(biāo)注顯示定位：

操作：選中數(shù)據(jù)源，點擊“開始”--“條件格式”--“新建格式規(guī)則”--“只為包含以下內(nèi)容的單元格設(shè)置格式”--“錯誤”登夫；

說明：設(shè)置“只為滿足以下條件的單元格設(shè)置格式”區(qū)域广匙，單元格值是“錯誤”的條件，預(yù)覽設(shè)置為淺藍(lán)色顯示恼策；

結(jié)果：數(shù)據(jù)區(qū)域中的“報錯”單元格艇潭，則會特殊顯示為淺藍(lán)色。

結(jié)果為：

二戏蔑、獨特值特殊顯示

1蹋凝、數(shù)據(jù)的Max、Min值特殊標(biāo)記顯示

例如：各平臺日銷售報表中总棵，顯示收入鳍寂，最大值和最小值，標(biāo)注顯示定位：

操作1：選中數(shù)據(jù)源中“收入”所在H列情龄，點擊“開始”--“條件格式”--“新建格式規(guī)則”--“只為包含以下內(nèi)容的單元格設(shè)置格式”--“單元格值”迄汛；

說明：設(shè)置“只為滿足以下條件的單元格設(shè)置格式”區(qū)域，單元格值條件為“等于”骤视，輸入“=MAX($H$2:$H$18)”鞍爱，預(yù)覽設(shè)置為深紅色顯示；

操作2：選中數(shù)據(jù)源中“收入”所在H列专酗，點擊“開始”--“條件格式”--“管理規(guī)則”--“新建規(guī)則”--“只為包含以下內(nèi)容的單元格設(shè)置格式”--“單元格值”睹逃；

說明：設(shè)置“只為滿足以下條件的單元格設(shè)置格式”區(qū)域，單元格值條件為“等于”，輸入“=MIN($H$2:$H$18)”沉填，預(yù)覽設(shè)置為深藍(lán)色顯示疗隶；

結(jié)果：數(shù)據(jù)區(qū)域H列的最大值、最小值的單元格翼闹，則會特殊顯示為深紅色和深藍(lán)色斑鼻。

結(jié)果顯示：

2、數(shù)據(jù)的TOPn猎荠、LASTn值特殊標(biāo)記顯示

例如：各平臺日銷售報表中坚弱，顯示收入的TOP3、LAST3記錄关摇，進(jìn)行標(biāo)注顯示定位：

操作1：選中數(shù)據(jù)源中“收入”所在H列史汗，點擊“開始”--“條件格式”--“新建格式規(guī)則”--“對排名靠前或靠后的數(shù)值設(shè)置格式”--“最高”；

說明：設(shè)置“對以下排列的數(shù)值設(shè)置格式”區(qū)域拒垃，單元格值是“最高”且為3項的條件停撞，預(yù)覽設(shè)置為橘色顯示；

操作2：選中數(shù)據(jù)源中“收入”所在H列悼瓮，點擊“開始”--“條件格式”--“管理規(guī)則”--“新建規(guī)則”--“對排名靠前或靠后的數(shù)值設(shè)置格式”--“最低”戈毒；

說明：設(shè)置“對以下排列的數(shù)值設(shè)置格式”區(qū)域，單元格值是“最低”且為3項的條件横堡，預(yù)覽設(shè)置為淺灰色顯示埋市；

結(jié)果：數(shù)據(jù)區(qū)域中的“TOP3”條記錄單元格，則會特殊顯示為橘色命贴，“LAST3”條記錄被標(biāo)記為淺灰色道宅。

結(jié)果為：

3、數(shù)據(jù)的前n%胸蛛、后n%的數(shù)值特殊標(biāo)記顯示

備注：數(shù)據(jù)的百分比區(qū)域污茵，可以按照需求進(jìn)行調(diào)整。

例如：各平臺日銷售報表中葬项，顯示收入的前10%泞当、后10%的數(shù)據(jù)記錄，進(jìn)行標(biāo)注顯示定位：

操作1：選中設(shè)置區(qū)域民珍，在“條件格式區(qū)域”襟士，直接在“最前/最后規(guī)則”下的快捷設(shè)置選項中，選擇“前10%”欄進(jìn)行設(shè)置嚷量；

說明：設(shè)置“為值最大的那些單元格設(shè)置格式”陋桂，單元格百分比區(qū)間為10%”，預(yù)覽設(shè)置為“淺紅色填充蝶溶，深紅色文本內(nèi)容”顯示嗜历；

操作2：選中數(shù)據(jù)源中“收入”所在H列，點擊“開始”--“條件格式”--“管理規(guī)則”--“新建規(guī)則”--“對排名靠前或靠后的數(shù)值設(shè)置格式”--“最低”；

說明：設(shè)置“對以下排列的數(shù)值設(shè)置格式”區(qū)域秸脱，單元格值是“最低”且為10項的條件落包，且勾選“所選范圍的百分比”部蛇，預(yù)覽設(shè)置為淺黃色顯示摊唇；

結(jié)果：數(shù)據(jù)區(qū)域中的前10%和后10%，則會特殊顯示為淺紅色涯鲁、淺黃色巷查。

4、數(shù)據(jù)的高于均值抹腿、低于均值的數(shù)值特殊標(biāo)記顯示

例如：各平臺日銷售報表中岛请，顯示高于“收入”平均值的數(shù)據(jù)記錄以及低于平均值的數(shù)據(jù)記錄，進(jìn)行標(biāo)注顯示定位：

操作1：選中設(shè)置區(qū)域警绩，在“條件格式區(qū)域”崇败，直接在“最前/最后規(guī)則”下的快捷設(shè)置選項中，選擇“高于平均值”欄進(jìn)行設(shè)置肩祥；

說明：設(shè)置“為高于平均值的那些單元格設(shè)置格式”后室，預(yù)覽設(shè)置為“淺紅色填充，深紅色文本內(nèi)容”顯示混狠；

操作2：選中數(shù)據(jù)源中“收入”所在H列岸霹，點擊“開始”--“條件格式”--“管理規(guī)則”--“新建規(guī)則”--“僅對高于或低于平均值的數(shù)值設(shè)置格式”--“低于”；

說明：設(shè)置“為滿足以下條件的值設(shè)置格式”将饺，條件是“低于”贡避，預(yù)覽設(shè)置為淺黃色顯示；

結(jié)果：數(shù)據(jù)區(qū)域中高于“收入”的平均值的數(shù)據(jù)都被填充為淺紅色予弧，低于收入平均值的“收入”數(shù)據(jù)被填充為黃色刮吧。

結(jié)果為：

三、自定義顯示特殊值

1掖蛤、數(shù)據(jù)高亮顯示皇筛，自動預(yù)警

例如：各平臺日銷售報表中，需自動預(yù)警最近一周的且是天貓平臺的銷售記錄數(shù)據(jù)：

操作：選中整個數(shù)據(jù)源坠七，點擊“開始”--“條件格式”--“新建格式規(guī)則”--“使用公式確定要設(shè)置的單元格“=($A2<today()-7)*($C2="天貓平臺")”水醋；

說明：設(shè)置“只為符合此公式的值設(shè)置格式”區(qū)域，公式為“=（$A2<today()-7）*($C2="天貓平臺")”彪置，預(yù)覽顯示為淺黃色拄踪；

結(jié)果：數(shù)據(jù)區(qū)域中，滿足銷售日期為近一周且天貓平臺的銷售記錄淺黃色標(biāo)注顯示拳魁。

2惶桐、數(shù)據(jù)間隔條紋，自動生成

例如：各平臺日銷售報表中，需隔行顯示：

操作：選中整個數(shù)據(jù)源姚糊，點擊“開始”--“條件格式”--“新建格式規(guī)則”--“使用公式確定要設(shè)置的單元格”--“=(MOD(ROW($A1),2)=1”贿衍；

說明：設(shè)置“只為符合此公式的值設(shè)置格式”區(qū)域，公式為“=(MOD(ROW($A1),2)=1”救恨，預(yù)覽設(shè)置為淺灰色顯示贸辈，使用MOD函數(shù)對行號進(jìn)行計算，行號為單設(shè)置淺灰色肠槽，行號為雙不設(shè)置擎淤，即可生成間隔條紋；

3秸仙、數(shù)據(jù)整行記錄嘴拢，自動標(biāo)識

例如：顯示各平臺日銷售報表中，“阿里”平臺的且是“山西”區(qū)域的產(chǎn)品銷售記錄：

操作：選中整個數(shù)據(jù)源寂纪，點擊“開始”--“條件格式”--“新建格式規(guī)則”--“使用公式確定要設(shè)置的單元格”--“=($C2=$N$1)*($D2=$N$2)”席吴；

說明：設(shè)置“只為符合此公式的值設(shè)置格式”區(qū)域，公式為“=($C2=$N$1)*($D2=$N$2)”捞蛋，預(yù)覽設(shè)置為淺黃色顯示孝冒；

結(jié)果：數(shù)據(jù)區(qū)域中，滿足N1單元格為“阿里”且N2單元格為“山西”的條件的記錄襟交，整行都顯示為黃色迈倍。

注意：條件改變，高亮標(biāo)注顯示的數(shù)據(jù)捣域，會因隨著條件更改記錄的顯示啼染。

4、數(shù)據(jù)隔列焕梅，錯列顯示

例如：各平臺日銷售報表中迹鹅，收入大于100的產(chǎn)品名稱“標(biāo)黃色”顯示，收入數(shù)據(jù)無需標(biāo)注顯示：

操作：選中整個數(shù)據(jù)源中“產(chǎn)品名稱”所在的F列贞言，點擊“開始”--“條件格式”--“新建格式規(guī)則”--“使用公式確定要設(shè)置的單元格”--“=$H2>100”斜棚；

說明：設(shè)置“只為符合此公式的值設(shè)置格式”區(qū)域，公式為“=$H>100”该窗，預(yù)覽設(shè)置為淺黃色顯示弟蚀；

結(jié)果：數(shù)據(jù)區(qū)域中，收入大于100的產(chǎn)品名稱自動標(biāo)記為黃色填充的底色

四酗失、數(shù)據(jù)可視化顯示特殊值

數(shù)據(jù)分析僅是基礎(chǔ)义钉，高階的進(jìn)化便是可視化分析。

Excel的數(shù)據(jù)條件格式的可視化规肴，主要應(yīng)用于三個方面：數(shù)據(jù)條捶闸、色階夜畴、圖標(biāo)集。

1删壮、數(shù)據(jù)條

數(shù)據(jù)條：主要是將數(shù)據(jù)按照一列數(shù)據(jù)中的最大數(shù)據(jù)作為默認(rèn)100%的填充贪绘，剩余單元格數(shù)據(jù)以此按照占比顯示長短。

例如：各平臺日銷售報表中央碟，各產(chǎn)品的毛利進(jìn)行數(shù)據(jù)條顯示：

操作：選中整個數(shù)據(jù)源中毛利所在L列税灌，點擊“開始”--“條件格式”--“新建格式規(guī)則”--“基于各自值設(shè)置所有單元格的格式”--“數(shù)據(jù)條”，且勾選“僅顯示數(shù)據(jù)條”硬耍，避免數(shù)據(jù)和圖的混亂垄琐，影響視圖边酒；

說明：設(shè)置條形圖的外觀“正值”為藍(lán)色實心邊框+藍(lán)色漸變填充的柱形圖经柴；

結(jié)果：毛利列中正值的毛利為向右的藍(lán)色柱形圖，負(fù)值的毛利為向左的紅色柱形圖

備注：可以根據(jù)“負(fù)值和坐標(biāo)軸”進(jìn)行設(shè)置負(fù)值的軸列數(shù)據(jù)顯示設(shè)置墩朦。

2坯认、色階

色階：主要是根據(jù)單元格數(shù)值的大小，進(jìn)行顏色深淺分類的標(biāo)識氓涣。

常見的色階主要有兩種：雙色刻度牛哺、三色刻度。

例如：各平臺日銷售報表中劳吠，各平臺的收入按照雙色刻度顯示：

操作：選中整個數(shù)據(jù)源中收入所在H列引润，點擊“開始”--“條件格式”--“新建格式規(guī)則”--“基于各自值設(shè)置所有單元格的格式”--“雙色刻度”；

說明：設(shè)置最小值的顏色漸變?yōu)椤吧铋偕毖魍妫畲笾档念伾珴u變?yōu)椤皽\黃色”淳附；

結(jié)果：收入中值越高，顏色越淺淡蠢古，收入越低奴曙，顏色越深沉。

備注：三色刻度類似雙色刻度的顏色深淺顯示草讶，只是多了一個顏色的漸變洽糟。

3、圖標(biāo)集

圖標(biāo)集：是利用圖標(biāo)堕战，例如表情坤溃、三角形、圓形等對數(shù)據(jù)進(jìn)行分類標(biāo)識顯示嘱丢。

例如：各平臺日銷售報表中薪介，各平臺的收入按照分類進(jìn)行顯示，大于500的用綠色旗子標(biāo)識屿讽，100-500的用黃色旗子標(biāo)識昭灵，0-100的用紅色旗子標(biāo)識：

操作：選中整個數(shù)據(jù)源中收入所在H列吠裆，點擊“開始”--“條件格式”--“新建格式規(guī)則”--“基于各自值設(shè)置所有單元格的格式”--“圖標(biāo)集”--“選擇三角旗子”標(biāo)識；

說明：根據(jù)需求的條件設(shè)置每個顏色的旗幟對應(yīng)的數(shù)字區(qū)間烂完，而不是百分比或者小數(shù)數(shù)據(jù)類型试疙，其中大于等于500為綠色旗子，100-500為黃色旗子抠蚣，0-100為紅色旗子祝旷；

結(jié)果：每一個收入的數(shù)據(jù)前面均貼上了一個分類的旗幟標(biāo)簽。

備注：數(shù)據(jù)類型和圖標(biāo)類型均可根據(jù)需求設(shè)置嘶窄，比如百分比區(qū)間怀跛、小數(shù)區(qū)間等。

Excel條件格式柄冲，作為簡化的數(shù)據(jù)可視化捷徑吻谋，讓數(shù)據(jù)分析愈發(fā)的智能和簡便化，作為辦公神技之一现横，值得學(xué)習(xí)＠焓啊！戒祠！

（注：2017.10.30,廢柴日記骇两，后續(xù)更新）