在 Excel 中有一個很特別的功能，會使人眼前一亮。

它會讓你的表格數(shù)據(jù)重點(diǎn)突出，更加醒目地展示在眾人的面前；

它會幫你自動完成想要的格式設(shè)置，不論是原有數(shù)據(jù)還是新增數(shù)據(jù)；

它還可以對表格進(jìn)行美化……

這個它就是：條件格式。

如下圖，這是一個資金流水賬：

摘要中如果是「期初余額」和「本期合計」的內(nèi)容，就把整行自動設(shè)置為黃色。變成下面這樣：

該怎么做？

問題分析

這個問題如果手動解決的話，可以直接在摘要列中進(jìn)行篩選，然后分別填充單元格顏色。

如下圖：

設(shè)置好之后，再取消篩選即可。

但是，如果內(nèi)容比較多的話，設(shè)置起來會比較繁瑣。

想讓某一單元格的內(nèi)容等于某一個值時，就讓它自動著色的話，當(dāng)然還是要請出 Excel 中的「條件格式」啦！

下面就跟我一起來看看怎么做到的吧！

解決問題

? 選中數(shù)據(jù)區(qū)域，并調(diào)出【條件格式】對話框。

選中【A2:F10】單元格區(qū)域，然后點(diǎn)擊【開始】==》【條件格式】==》【新建規(guī)則】，調(diào)出【新建格式規(guī)則】對話框：

選中【使用公式確認(rèn)要設(shè)置格式的單元格】，并在其中輸入如下內(nèi)容：

公式如下：

• =$C2={"期初余額","本期合計"}

意思是：

如果【C2】單元格的內(nèi)容等于"期初余額"或者"本期合計"，該條件格式成立。將會按照我們下面的設(shè)置進(jìn)行單元格格式的設(shè)置。

這里的 C 列要固定住，因為我們只用比較摘要位的內(nèi)容。

點(diǎn)擊【格式】按鈕，進(jìn)行如下設(shè)置：

之后點(diǎn)【確定】回到上一界面。

設(shè)置好之后，點(diǎn)【確定】。

阿歐！

出了啥問題？

看提示框中寫的：

不能在條件格式中使用數(shù)組常量！

那這個問題怎么辦？

別急，好辦！好辦！

即然不給寫在這里的文本框中，那我們可以寫在單元格中，然后去引用這些單元格不就行了！

新建一張【表 2】工作表，將內(nèi)容寫在【A1:A3】。

然后在【編輯格式規(guī)則】中進(jìn)行如下設(shè)置：

最后點(diǎn)【確定】，如下圖：

歐，好像還是不太對勁？

期初余額所在的行已經(jīng)自動填色，但是本期合計行并沒有發(fā)生變化。

又是啥問題？

這次問題的原因是出在公式本身。

• =$C2=表 2!$A$2:$A$3

因為這個公式的結(jié)果會生成兩個邏輯值。
當(dāng)在第 2 行的時候結(jié)果如下：

當(dāng)在第 9 行的時候結(jié)果如下：

此公式雖然生成兩個值，但是最終只會返回數(shù)組中的第一個值，如果第一個值是 TRUE，就符合條件格式的設(shè)置要求，該行就會填充黃色；如果第一個值是 FALSE，就不符合條件格式的設(shè)置要求，就不會填充顏色。

原理知道了，這下就好辦了！在公式的外面套一個 OR 函數(shù)。

• =OR($C2=表 2!$A$2:$A$3)

公式的意思是：

只要兩個值當(dāng)中有一個值是 TRUE,最終結(jié)果就返回 TRUE，該行就會自動著色。

如果兩個值都是 FALSE，最終結(jié)果就返回 FALSAE，該行就不會自動著色。

最終效果如下：

問題解決了！

知識擴(kuò)展

??讓條件格式中的公式條件自動擴(kuò)展

之前我們在【表 2】工作表中設(shè)置了兩個條件，一個是期初余額，一個是本期合計。

如果想要再新增一個條件：「本年累計」的話，需要在【表 2】工作表【A4】單元格中輸入本年累計。

并且還要再重新設(shè)置下條件格式中的公式。如下圖：

最終結(jié)果如下：

這樣重復(fù)設(shè)置條件格式的工作效率比較低下。

能不能讓條件增加時，格式的設(shè)置自動化呢？

當(dāng)然可以，我們按照下面的思路來改善下。

① 將【表 2】工作表中的條件區(qū)域設(shè)置成超級表。

選中表 2 中的【A2】單元格，然后按【CTRL+T】，如下圖：

最后點(diǎn)擊確定即可。

② 修改條件格式中的公式。

如下圖：

公式如下：

• =OR($C2=INDIRECT("表 1[名稱]"))
  

將原來條件中直接引用單元格區(qū)域改成如下形式：

INDIRECT ?+ 表名稱 ?+ ?[ ?+ 表標(biāo)題名稱 ?+ ?]

這里的超級表的名稱是：表 1

超級表的標(biāo)題名稱是：名稱

最后結(jié)合使用 INDIRECT 函數(shù)構(gòu)成單元格引用。

通過以上兩個步驟的設(shè)置之后，我們來驗證下效果。

在【表 2】工作表中【A4】單元格中輸入本年累計，如下圖：

【表 1】工作表中的本年累計行將自動著色。

是不是很神奇！

? 讓數(shù)據(jù)源中的條件格式區(qū)域自動擴(kuò)展

我們一開始設(shè)置條件格式的時候，是只選擇了有數(shù)據(jù)的【$A$2:$F$10】單元格區(qū)域。

我們也可以手動把這個條件格式的區(qū)域放的足夠大，比如選擇【$A$2:$F$9999】區(qū)域等。

這樣設(shè)置的好處是可以滿足后期數(shù)據(jù)增加，不需要手動再重新設(shè)置條件格式區(qū)域。

缺點(diǎn)是：當(dāng)表格數(shù)據(jù)較多時，可能會出現(xiàn)表格卡頓。

當(dāng)然，我們同樣也可以像上面一樣，把數(shù)據(jù)源區(qū)域設(shè)置成超級表形式。比如把數(shù)據(jù)源設(shè)置成超級表【表 2】這個表名稱。

條件格式的區(qū)域選擇實(shí)際的【$A$2:$F$10】數(shù)據(jù)區(qū)域。

此時，當(dāng)我們有新增數(shù)據(jù)的時候，比如：在【C11】單元格中寫入期初余額的時候，該行將會自動上色。

我們再來看下【條件格式規(guī)則管理器】中的應(yīng)用于數(shù)據(jù)區(qū)域。

這個區(qū)域?qū)⒆詣訑U(kuò)展為【=$A$2:$F$11】。

是不是很神奇！

這樣設(shè)置的優(yōu)點(diǎn)是數(shù)據(jù)增加時，條件格式的區(qū)域?qū)⒁沧詣訑U(kuò)展，避免設(shè)置過多的無用區(qū)域。

寫在最后

今天我們分享了，Excel 中最亮眼的一個功能【條件格式】。

并且可以將數(shù)據(jù)區(qū)域中條件格式【應(yīng)用于區(qū)域】和條件格式中的【公式條件設(shè)置】這兩個都變成自動擴(kuò)展功能，大大地提高了我們的工作效率。真正實(shí)現(xiàn)了一次設(shè)置，終身使用，不需要再手動改變區(qū)域或者重新設(shè)置條件。

利用條件格式功能可以提醒領(lǐng)導(dǎo)或者老板需要重點(diǎn)關(guān)注哪些數(shù)據(jù)。真的是一項非常好用又強(qiáng)大的功能。

對了，如果你想系統(tǒng)性學(xué)習(xí) Excel。

正好，我們秋葉家的《秋葉 Excel 3 天集訓(xùn)營》專為職場人準(zhǔn)備，全部基于職場真實(shí)表格案例設(shè)計，還有很多超實(shí)用 Excel 技巧教學(xué)。

每天學(xué)習(xí)大概?30 分鐘，從日常的功能出發(fā)，全程演示，一課一練，夯實(shí)進(jìn)階每一步。

秋葉 Excel 3 天集訓(xùn)營

原價?99 元??

現(xiàn)在?只需 1?元

?每天學(xué)習(xí)?30 分鐘

你也有可能成為 Excel 高手！

現(xiàn)在就掃碼報名吧！

??????

*廣告