實用Excel技巧分享：「條件格式」和「函數公式」配合使用

在之前的文章《實用Excel技巧分享：「資料有效性」可以這樣用！在》中，我們學習了3個讓數據效能更有效率的小技巧。而今天我們來聊聊帶函數公式的條件格式，透過2個實例來介紹一下「條件格式」和「函數公式」如果配合使用，快來學習學習！

條件格式大家都會玩，但如何同時找出100行資料中每一行的最大值並標註出來，相信你應該不知道。今天我們透過2個實例跟大家一起學習條件格式配合函數公式後的用法。

實例一【多行資料的最高分標色】

說起用條件格式標註出最大值，相信大家都知道，使用如下圖所示的「專案選取規則」裡的各項就可以完成，但是這個操作比較受限。如果我們有100行數據，要同時把每一行的最高值標註出來呢？下面跟大家講解用公式解決這個問題。

近期進行excel、word、ppt、綜合四個科目階段評估。表中記錄了學員考試成績，現在需要同時將每位學員的最高分所在單元格以綠色填滿。

操作步驟：

（1）勾選D2:G​​13資料區域後點選【開始】標籤中【條件格式】，選擇【新建規則】。

（2）開啟【新格式規則】對話方塊後選擇【使用公式決定要設定格式的儲存格】規則類型。在下方的編輯規則說明中輸入公式=AND(D2=MAX($D2:$G2),D2"")。點選【格式】將儲存格填滿顏色設定為綠色。

函數公式解析

（1）D2=MAX($D2:$G2)判斷D2是否等於$D2: $G2區域中的最大值，如果相同則回傳TRUE，否則回傳FALSE。其中列座標加了$符號為絕對引用，這樣資料就是在D-G列進行判斷。行座標為相對引用，每向下移一行，公式就會自動變成對應的行座標。

（2）D2「」表示D2大於小於也就是不等於空白儲存格，則傳回TRUE，否則傳回FALSE。

（3）AND函數判斷以上2個條件是否都為真，如果都為真則回傳TRUE，單元格將會填滿綠色。

編輯完成格式規則後點選【確定】進入【條件格式設定管理器】（備註：條件格式設定管理員可以理解為儲存設定好的條件格式）

#在管理員中看到了剛剛設定的條件格式，目前條件格式所套用的區域固定為$D$2:$G$13儲存格區域。如果我們的資料區域發生了變化，只需修改應用程式單元格區域即可。

點選上圖中的【應用】-【確定】就完成了最高分標色。最終結果如下。

大家可以試著改公式並將最低分標色哦！

實例二【合約到期提醒】

如何設定合約到期提醒表格？相信做合約管理的夥伴都有這樣的需求。最簡單的方式是使用“條件格式”裡的“發生日期”，可以將即將到期的資料所在儲存格以特殊顏色顯示。

但這種方式只有下面幾個選項，若我想要找出5天內將要到期的合約呢？就需要使用函數公式了。

下表是聯通集團公司行銷部員工勞動合約記錄表，表中記錄員工入職時間以及合約終止時間。現在需要透過條件格式把合約即將期滿的員工自動標色提醒。

操作步驟：

（1）勾選H2:H13資料區域後點選【開始】標籤中的【條件格式】 。選擇【新格式規則】，開啟【新格式規則】對話方塊後選擇【使用公式決定要設定格式的儲存格】

（2）在編輯規則說明中我們輸入設定條件為=DATEDIF(TODAY(),H2,"d")

可能很多小夥伴還不太熟悉DATEDIF(TODAY(),H2,"d")

下面我們用一張表格簡單介紹一下這個函數：

#大家可以看到，我們透過入職日期與目前日期（today ()返回目前日期）進行對比，分別以「y」、「m」、「d」為計算單位，依序回傳兩個日期相差的年數、月數、天數。 注意：函數公式第1個參數為小的日期，第2個參數為大的日期。

了解DATEDIF函數後，我們不難理解前面設定合約到期提醒的公式DATEDIF(TODAY(),H2,"d")

今天我們透過2個實例跟大家分享了在條件格式中結合公式來判斷資料並標註。現實工作中相關的實例非常多，例如：

1、 透過=COUNTIF($H$3:$H$13,H2)>1函數，可以標註重複資料。

2、 透過=VLOOKUP($H2,$M:$M,1,0)"#N/A"函數，將配對到的儲存格直接標色。大家可以試著操作一下！

相關學習推薦：excel教學

以上是實用Excel技巧分享：「條件格式」和「函數公式」配合使用的詳細內容。更多資訊請關注PHP中文網其他相關文章！