實用Excel技巧分享:“條件格式”和“函數公式”配合使用

在之前的文章《實用excel技巧分享:“數據有效性”可以這樣用!》中,我們學習了3個讓數據有效性更高效的小技巧。而今天我們來聊聊帶函數公式的條件格式,通過2個實例來介紹一下“條件格式”和“函數公式”如果配合使用,快來學習學習!

實用Excel技巧分享:“條件格式”和“函數公式”配合使用

條件格式大家都會玩,但如何同時找出100行數據中每一行的最大值并標注出來,相信你應該不知道。今天我們通過2個實例跟大家一起學習一下條件格式配合函數公式后的用法。

實例一【多行數據的最高分標色】

說起用條件格式標注出最大值,相信大家都知道,使用如下圖所示的“項目選取規則”里的各項就可以完成,但是這個操作比較受限制。如果我們有100行數據,要同時把每一行的最高值標注出來呢?下面給大家講解用公式解決這個問題。

實用Excel技巧分享:“條件格式”和“函數公式”配合使用

近期進行excelword、ppt、綜合四個科目階段考核。表中記錄了學員考試成績,現在需要同時將每一位學員的最高分所在單元格用綠色填充。

實用Excel技巧分享:“條件格式”和“函數公式”配合使用

操作步驟:

(1)選中D2:G13數據區域后單擊【開始】選項卡中【條件格式】,選擇【新建規則】。

實用Excel技巧分享:“條件格式”和“函數公式”配合使用

(2)打開【新建格式規則】對話框后選擇【使用公式確定要設置格式的單元格】規則類型。在下方的編輯規則說明中輸入公式=AND(D2=MAX($D2:$G2),D2″”)。單擊【格式】將單元格填充顏色設置為綠色。

實用Excel技巧分享:“條件格式”和“函數公式”配合使用

函數公式解析

(1)D2=MAX($D2:$G2)判斷D2是否等于$D2:$G2區域中的最大值,如果相同則返回TRUE,否則返回FALSE。 其中列坐標加了$符號為絕對引用,這樣數據便都是在D-G列進行判斷。行坐標為相對引用,每向下移一行,公式就自動變為對應的行坐標。

(2)D2“”表示D2大于小于也就是不等于空白單元格,則返回TRUE,否則返回FALSE。

(3)AND函數判斷以上2個條件是否都為真,如果都為真則返回TRUE,單元格將填充綠色。

編輯完成格式規則后單擊【確定】進入【條件格式設置管理器】(備注:條件格式設置管理器可以理解為存儲設置好的條件格式)

實用Excel技巧分享:“條件格式”和“函數公式”配合使用

在管理器中看到了剛剛設置的條件格式,當前條件格式應用的區域固定為$D$2:$G$13單元格區域。如果我們的數據區域發生了變化,只需修改應用單元格區域即可。

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

實用Excel技巧分享:“條件格式”和“函數公式”配合使用

大家可以試著改一下公式并將最低分標色哦!

實例二【合同到期提醒】

如何在表格中設置合同到期提醒?相信做合同管理的伙伴都有這樣的需求。最簡單的方式是使用“條件格式”里的“發生日期”,可以將即將到期的數據所在單元格用特殊顏色顯示。

實用Excel技巧分享:“條件格式”和“函數公式”配合使用

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

實用Excel技巧分享:“條件格式”和“函數公式”配合使用

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

實用Excel技巧分享:“條件格式”和“函數公式”配合使用

操作步驟:

(1)選中H2:H13數據區域后單擊【開始】選項卡中的【條件格式】。選擇【新建格式規則】,打開【新建格式規則】對話框后選擇【使用公式確定要設置格式的單元格】

實用Excel技巧分享:“條件格式”和“函數公式”配合使用

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

實用Excel技巧分享:“條件格式”和“函數公式”配合使用

可能很多小伙伴還不是很熟悉DATEDIF(TODAY(),H2,”d”)

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

實用Excel技巧分享:“條件格式”和“函數公式”配合使用

大家可以看到,我們通過入職日期與當前日期(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技巧分享:“數據有效性”可以這樣用!

? 版權聲明
THE END
喜歡就支持一下吧
點贊11 分享