excel 條件格式與函數(shù)不妨結(jié)合進(jìn)行自定義條件格式

?案例1：提前7天提醒待辦的事情

今天是2017年8月12日，辦理員工轉(zhuǎn)正事宜是人事部門的一項(xiàng)日常工作。員工試用期為90天，設(shè)置提前7天提醒哪些人轉(zhuǎn)正期快到了，好準(zhǔn)備相關(guān)事項(xiàng)。

?

? 操作步驟：

1.選擇A3:C9，單擊功能區(qū)條件格式中的“新建規(guī)則”。

?

2.在彈出的新建格式規(guī)則對話框中，選擇最后一個“使用公式確定要設(shè)置格式的單元格”，然后在下方的輸入框中輸入公式：

=AND(TODAY()-$C3>83,TODAY()-$C3<>

?

知識補(bǔ)充：

TODAY()是日期函數(shù)，自動獲取今天的日期。在單元格輸入=TODAY()，然后回車，該單元格就會顯示今天的日期。如下圖。

?

AND()是邏輯函數(shù)，就是且的意思。括號內(nèi)可容納多個表達(dá)式，當(dāng)所有的表達(dá)式同時成立時，返回的結(jié)果才成立。

語法：AND(表達(dá)式1,表達(dá)式2,表達(dá)式3……)

只有當(dāng)所有表達(dá)式全部成立，它返回的結(jié)果才是TRUE，否則是FALSE。

比如：

?

我們都知道，試用期為90天，如果下周有人試用期將到期，那說明入職時間在83天-90天之間。所以我們就用今天的日期-入職的日期，如果得到的結(jié)果：大于83，小于90，則說明下周將轉(zhuǎn)正。

所以我們用=AND(TODAY()-$C3>83,TODAY()-$C3<>

PS：如果要做員工生日提醒，函數(shù)要怎么寫呢？

? 案例2：利用條件格式“自動生成邊框線”

A列單元格中數(shù)值為空，邊框線也自動去掉

?

? 操作步驟：

選中A:D列，然后在功能區(qū)的條件格式中選擇“新建規(guī)則”。

選擇最后一個“使用公式確定要設(shè)置格式的單元格”,然后在下方的輸入框中輸入函數(shù)：=$A1<>””，然后點(diǎn)擊格式，將格式中的邊框設(shè)置為外邊框，表示當(dāng)條件滿足時，給單元格加上外邊框突出顯示。

?

? 案例3:庫存上下限預(yù)警

當(dāng)庫存量小于安全庫存量50時，顯示紅色警報(bào)。當(dāng)庫存量等于入庫量時，顯示黃色警報(bào)

?

? 操作步驟：

1.選擇D2:D11，然后在功能區(qū)的條件格式中選擇“新建規(guī)則”。選擇最后一個“使用公式確定要設(shè)置格式的單元格”，然后在下方的輸入框中輸入函數(shù)：=$D2<>

?

2.繼續(xù)選擇D2：D11,然后在功能區(qū)的條件格式中選擇“新建規(guī)則”。選擇最后一個“使用公式確定要設(shè)置格式的單元格”,然后在下方的輸入框中輸入函數(shù)：=$B2=$D2，然后點(diǎn)擊格式，設(shè)置填充黃色警報(bào)。

?

是不是很神奇呀？還有更神奇的，你見過有的Excel表格上隔行或隔列一個顏色或者在表格上畫國際象棋的棋盤，你知道怎么畫出來的嗎？

? 案例4：隔行填充不一樣的顏色

效果：

1.隔行顏色效果（奇數(shù)行背景顏色為青色）

2.隔行顏色效果（偶數(shù)行字體顏色為紅色）

?

? 操作步驟：

1.選中A2:H8，然后在功能區(qū)的條件格式中選擇“新建規(guī)則”。選擇最后一個“使用公式確定要設(shè)置格式的單元格”，然后在下方的輸入框中輸入函數(shù)：=MOD(ROW(),2)=1。設(shè)置好格式后按確定。

?

2.繼續(xù)選中A2:H8，然后在功能區(qū)的條件格式中選擇“新建規(guī)則”。選擇最后一個“使用公式確定要設(shè)置格式的單元格”，在下方的輸入框中輸入函數(shù)：=MOD(ROW(),2)=0。設(shè)置好格式后按確定。

?

知識補(bǔ)充：

我們知道什么叫奇數(shù)和偶數(shù)，就是某個數(shù)除以2，如果能除盡，則說明它是偶數(shù)；如果除不盡，余數(shù)為1，則為奇數(shù)。那么問題來了，這個數(shù)怎么動態(tài)選取？求余函數(shù)是哪一個函數(shù)？

在這里我們用到了兩個函數(shù)：

（1） MOD()模函數(shù)，也就是求余函數(shù)。語法也簡單，顧名思義，MOD(被除數(shù)，除數(shù))，返回值是余數(shù)。比如：MOD(5,2)=1，5除以2，商為2，余數(shù)為1，所以函數(shù)返回余數(shù)1。

（2） ROW()行函數(shù)，返回的是當(dāng)前行的行號。語法：ROW()或者ROW(A1)，一般情況下，括號內(nèi)沒有參數(shù)，表示獲取當(dāng)前單元格的行號。括號內(nèi)有參數(shù)，則表示獲取該參數(shù)單元格所在的行號。

在這里，可能會有網(wǎng)友會問，那如果是隔列呢？這里有個函數(shù)COLUMN()列函數(shù)，返回當(dāng)前單元格的列號，用法同ROW()。

所以我們棋盤紋要出現(xiàn)了。眼睛不要眨。

?

它的條件格式設(shè)置如下：講了這么多了，步驟省略幾十個字。

?

?

該條件格式的公式用于判斷行號與列號之和除以2的余數(shù)是否為0。如果為0，說明行數(shù)與列數(shù)的奇偶性相同，則填充單元格為指定色，否則就不填充。在條件格式中，公式結(jié)果返回一個數(shù)字時，非0數(shù)字即為TRUE，0為FALSE。因此，上面的公式也可以寫為：=MOD(ROW()+COLUMN(),2)<>0

只要思想不滑坡，格式的辦法總比困難多。條件格式的應(yīng)用還有很多，由于時間關(guān)系，剩下的就留給大家來探討分享了。

Excel函數(shù)的強(qiáng)大，在于它的應(yīng)用沒有定性，每個人的思維不一樣，所以解決問題的方法也不一樣，沒有人能窮盡它的用法，哪怕是它的開發(fā)者。

有一個關(guān)于Excel的故事跟大家分享一下：

有一個Excel發(fā)燒友在一次偶然的學(xué)術(shù)交流會上認(rèn)識了一個微軟的軟件工程師，剛好這個軟件工程師是負(fù)責(zé)Excel函數(shù)模塊的開發(fā)。好不容易碰到一尊“創(chuàng)世神”，這位Excel發(fā)燒友肯定得好好交流一下。于是他就把自己學(xué)習(xí)研究Excel的案例拿出來探討，問“創(chuàng)世神”有沒有什么可改進(jìn)的，結(jié)果這位“創(chuàng)世神”說：“當(dāng)初開發(fā)這個函數(shù)時，我都沒想過讓它有這個功能，你居然把它挖掘出來了。”。