條件格式，是Excel中我最喜歡的一項(xiàng)功能。
所謂條件格式（Conditional Formatting），簡(jiǎn)單說(shuō)就是讓符合條件的單元格自動(dòng)顯示為提前設(shè)定的格式。
比如——
“讓成績(jī)表中所有大于90分的單元格都自動(dòng)用黃色底色進(jìn)行高亮顯示”，在這條規(guī)則中，條件是：?jiǎn)卧穹謹(jǐn)?shù)>90，格式是：黃色底色。
“讓身份證列表中所有不是18位的單元格自動(dòng)顯示為黑底白字”，這條規(guī)則中，條件是：?jiǎn)卧裰猩矸葑C號(hào)位數(shù)不等于18，格式是：黑底白字。
條件格式常用于數(shù)據(jù)預(yù)警和數(shù)據(jù)強(qiáng)調(diào)，在一定程度上提升了Excel報(bào)表的智能化程度。
下面我們通過(guò)案例來(lái)了解條件格式在Excel中的應(yīng)用。
所謂數(shù)據(jù)預(yù)警，就是將我們關(guān)注的數(shù)據(jù)自動(dòng)加上格式進(jìn)行高亮或強(qiáng)調(diào)。比如：
項(xiàng)目管理表中未能按時(shí)完成的任務(wù)
銀行風(fēng)險(xiǎn)報(bào)表中不良率高于2%的數(shù)據(jù)
考試成績(jī)表中不及格的學(xué)生
……
這些都是我們常常要關(guān)注的數(shù)據(jù)，下面通過(guò)GIF來(lái)看如何對(duì)數(shù)據(jù)進(jìn)行預(yù)警。
▼ 成績(jī)不及格的數(shù)據(jù)
說(shuō)明：對(duì)于簡(jiǎn)單的數(shù)量關(guān)系判斷，我們可以自己編寫(xiě)公式，也可以使用條件格式菜單中的“突出顯示單元格規(guī)則”。
▼ 前10名、后10名
說(shuō)明：在“項(xiàng)目選取規(guī)則”中可以根據(jù)排名或與平均值的關(guān)系對(duì)數(shù)據(jù)進(jìn)行預(yù)警。這個(gè)案例中，成績(jī)分布在C、E、G三列，也可以自定義公式，但必須先將排名區(qū)域定義為名稱。
▼ 身份證號(hào)不等于18位的
說(shuō)明：LEN函數(shù)可以計(jì)算文本的字符個(gè)數(shù)，<>為不等號(hào)。
▼ 將暫停的工作事項(xiàng)自動(dòng)著色
▼ 將已發(fā)放的優(yōu)惠券自動(dòng)變灰
說(shuō)明：這個(gè)GIF案例是我在為會(huì)員發(fā)放動(dòng)態(tài)圖表視頻課優(yōu)惠碼時(shí)制作的，A列是提前生成的優(yōu)惠碼列表，一個(gè)碼只能供一個(gè)人用。所以，每發(fā)出一個(gè)優(yōu)惠碼，我就在G列雙擊完成打勾，這時(shí)對(duì)應(yīng)行就會(huì)自動(dòng)標(biāo)灰，表示此碼已使用。
關(guān)于報(bào)表美化，常見(jiàn)的一個(gè)案例是隔行底紋的制作，即每隔一行添加一個(gè)底色。
當(dāng)然，簡(jiǎn)單的報(bào)表我們手動(dòng)也可以完成。但是當(dāng)我們對(duì)報(bào)表數(shù)據(jù)進(jìn)行篩選、排序等操作后，手動(dòng)制作的隔行底紋效果很容易被破壞。
而使用條件格式添加隔行底紋，有兩個(gè)好處：一是快，不管報(bào)表有多少行，寫(xiě)一個(gè)公式瞬間搞定；二是格式不會(huì)受到排序、增刪行等報(bào)表操作的影響。
▼ 隔行底紋的制作
說(shuō)明：除了用MOD嵌套R(shí)OW函數(shù)，也可以直接用ISEVEN或ISODD嵌套R(shí)OW函數(shù)快速設(shè)置隔行底紋效果。
▼ 方塊圖的制作
如上圖所示，方塊圖常用于百分比數(shù)據(jù)（如進(jìn)度、占比、完成率、增長(zhǎng)率）的表達(dá)。方塊圖通常由10*10總共100個(gè)小方塊組成，每個(gè)小方塊代表1%。這樣，要表達(dá)的數(shù)據(jù)是百分之多少，就有多少個(gè)小方塊被設(shè)置前景色。
你能想象在Excel中如何制作方塊圖嗎？
我在《左手?jǐn)?shù)據(jù)，右手圖表》中提供了兩種制作方法，一種是用散點(diǎn)圖，另一種就是條件格式。相對(duì)而言，條件格式法更為簡(jiǎn)單、靈活，下面簡(jiǎn)單作介紹。
首先，構(gòu)造一個(gè)10*10的單元格區(qū)域，每個(gè)單元格按次序填充數(shù)據(jù)0.01至1，并全部加上前景色，邊框線改為白色。
然后，對(duì)此區(qū)域添加條件格式：=A4>$A$1，設(shè)置單元格顏色為灰色。
這樣，所有大于A1（即0.75）的單元格就會(huì)自動(dòng)變?yōu)榛疑?/span>
最后，用自定義格式（;;;）將單元格內(nèi)的小數(shù)全部隱藏，并使用Excel照相機(jī)功能拍成圖片，設(shè)置圖片長(zhǎng)寬比為1：1即可。
▼ 甘特圖模擬
用同樣的方法，我們還可以在項(xiàng)目管理工作中模擬甘特圖，GIF效果如下：
這個(gè)案例留給大家自己思考，這里就不贅述了。
如果說(shuō)以上這些案例只是條件格式的簡(jiǎn)單應(yīng)用，那下面這兩個(gè)案例理解起來(lái)可能需要?jiǎng)狱c(diǎn)腦筋了。
當(dāng)條件格式與下拉菜單、控件等功能結(jié)合在一起的時(shí)候，我們可以讓報(bào)表的交互性實(shí)現(xiàn)一個(gè)質(zhì)的突破。
▼ VLOOKUP函數(shù)的動(dòng)態(tài)演示
說(shuō)明：本例聚光燈效果用條件格式完成，用以演示VLOOKUP函數(shù)的查詢過(guò)程。
詳細(xì)制作過(guò)程可點(diǎn)擊以下鏈接：
教程 | 「VLOOKUP函數(shù)的動(dòng)態(tài)演示」及制作詳解
▼ 像控制開(kāi)關(guān)一樣控件條件格式
說(shuō)明：給條件格式添加一個(gè)開(kāi)關(guān)，這個(gè)案例我們?cè)?月31日推文中做過(guò)詳細(xì)介紹，原文鏈接如下：
最后，我們?cè)賮?lái)看條件格式最重要的一個(gè)應(yīng)用，即數(shù)據(jù)可視化——通過(guò)添加數(shù)據(jù)條或圖標(biāo)讓數(shù)據(jù)更直觀。
▼ 用條件格式生成數(shù)據(jù)條
說(shuō)明：最簡(jiǎn)單的可視化就是用條件格式生成數(shù)據(jù)條，可以直接生成在數(shù)據(jù)單元格，也可以多復(fù)制一列，一列顯示數(shù)據(jù)，一列顯示數(shù)據(jù)條。
▼ 為數(shù)據(jù)添加顏色標(biāo)記
說(shuō)明：本例中K列的圓點(diǎn)用于標(biāo)記優(yōu)良差，根據(jù)O列計(jì)算的季度考核系數(shù)自動(dòng)進(jìn)行判斷，考核系數(shù)大于1.3，代表“優(yōu)”，用紅色表示；考核系數(shù)小于1.2代表“差”，用淺紅色表示；介于1.2到1.3則為“良”，用中間的紅色表示。注意，在設(shè)置條件格式的時(shí)候只需添加兩條規(guī)則就可以了。
從以上案例我們可以看到，條件格式本身并不難掌握，但要想用好條件格式，關(guān)鍵還是在于函數(shù)的運(yùn)用——也就是如何將條件用函數(shù)的語(yǔ)言表達(dá)出來(lái)。
條件格式中的函數(shù)運(yùn)用，有三個(gè)點(diǎn)需要特別注意：
1、明確條件格式中等號(hào)“=”的含義
在條件格式中編寫(xiě)條件跟在單元格中編寫(xiě)函數(shù)一樣，都是用等號(hào)開(kāi)頭，但是一定要注意其中的區(qū)別。
在函數(shù)中等號(hào)代表返回一個(gè)函數(shù)或公式計(jì)算結(jié)果，而條件格式中的等號(hào)則為判斷之意，等號(hào)后面是要判斷的條件，條件結(jié)果要么是TRUE，要么是FALSE。
2、條件格式中函數(shù)的引用方式
除了等號(hào)，條件格式中的函數(shù)用法與單元格中一樣，但是務(wù)必要注意條件格式中的單元格引用方式。
由于條件格式是逐個(gè)單元格判斷的，所以如果整個(gè)一列單元格如果都是條件單元格，那么在條件中引用的時(shí)候務(wù)必要鎖定列而不鎖定行，比如前面這個(gè)案例。
這個(gè)過(guò)程跟公式的復(fù)制有點(diǎn)類似，向下復(fù)制的時(shí)候行不能被鎖定。
3、條件單元格與作用范圍
最后一點(diǎn)，要弄清楚條件格式的作用范圍。有時(shí)候，你的判斷條件只是針對(duì)某一列數(shù)據(jù)，但是最后設(shè)置條件格式的時(shí)候要求滿足條件的整行數(shù)據(jù)都要加上格式。
案例文件下載
卓越之道會(huì)員：在會(huì)員群獲取
非會(huì)員：后臺(tái)回復(fù)關(guān)鍵字「7829」獲取下載地址
聯(lián)系客服
