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

它會讓你的表格數據重點突出，更加醒目地展示在眾人的面前；它會幫你自動完成想要的格式設置，不論是原有數據還是新增數據；它還可以對表格進行美化……

這個它就是：條件格式。

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

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

該怎么做？

問題分析

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

如下圖：

設置好之后，再取消篩選即可。

但是，如果內容比較多的話，設置起來會比較繁瑣。

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

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

解決問題

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

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

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

公式如下：

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

意思是：

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

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

點擊【格式】按鈕，進行如下設置：

之后點【確定】回到上一界面。

設置好之后，點【確定】。

阿歐！

出了啥問題？

看提示框中寫的：

不能在條件格式中使用數組常量！

那這個問題怎么辦？

別急，好辦！好辦！

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

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

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

最后點【確定】，如下圖：

歐，好像還是不太對勁？

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

又是啥問題？

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

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

因為這個公式的結果會生成兩個邏輯值。
當在第 2 行的時候結果如下：

當在第 9 行的時候結果如下：

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

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

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

公式的意思是：

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

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

最終效果如下：

問題解決了！

知識擴展

??讓條件格式中的公式條件自動擴展

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

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

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

最終結果如下：

這樣重復設置條件格式的工作效率比較低下。

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

當然可以，我們按照下面的思路來改善下。

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

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

最后點擊確定即可。

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

如下圖：

公式如下：

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

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

INDIRECT ?+ 表名稱 ?+ ?[ ?+ 表標題名稱 ?+ ?]

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

超級表的標題名稱是：名稱

最后結合使用 INDIRECT 函數構成單元格引用。

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

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

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

是不是很神奇！

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

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

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

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

缺點是：當表格數據較多時，可能會出現表格卡頓。

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

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

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

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

這個區(qū)域將自動擴展為【=$A$2:$F$11】。

是不是很神奇！

這樣設置的優(yōu)點是數據增加時，條件格式的區(qū)域將也自動擴展，避免設置過多的無用區(qū)域。

寫在最后

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

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

利用條件格式功能可以提醒領導或者老板需要重點關注哪些數據。真的是一項非常好用又強大的功能。

對了，如果你想系統性學習 Excel。

正好，我們秋葉家的《秋葉 Excel 3 天集訓營》專為職場人準備，全部基于職場真實表格案例設計，還有很多超實用 Excel 技巧教學。

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

秋葉 Excel 3 天集訓營

原價?99 元??

現在?只需 1?元?

每天學習?30 分鐘

你也有可能成為 Excel 高手！

現在就掃碼報名吧！

??????

好了，今天的分享就到這里。

歡迎大家點贊&轉發(fā)！