【20180417】- 條件格式的幾種常見用法

來自專欄 SUT事務所

其實早在去年12月份，就已經為大家分享了一期關於條件格式的文章，如果為學習過的小夥伴可以移步【20171208】- 你真的會使用條件格式嗎？進行學習。在該文章中為大家介紹了如何使用條件格式來製作備忘錄清單，其中核心就是使用條件格式來實現。

既然前期已經介紹了條件格式，那為啥今天還來介紹呢？這是由於SUT學習交流群（群號：615356012）裡面有位廣東的小夥伴諮詢了相關問題，小夥伴的問題如下：

該小夥伴希望根據庫存的數量來標註那一天庫存不夠用了，把這一天標註出來。比如拿第一行來說，庫存只有51，只夠1號50使用，2號還需要使用50就不夠用了，此時標註2號的第一行的50為黃色即可。在解決該小夥伴的問題之前，我們先來學習一下條件格式常用的幾種類型，等學習完，再來解決該小夥伴的問題。

一般條件格式應用於那些場景呢？我這裡稍微搜集了一下，大概有以下幾種常見用法，這裡省略掉了一些簡單的比如標記大於或小於某些值的用法。

一 隱藏工資敏感信息

有些HR在編寫工資信息的時候，不希望路過座位的同事看到，可以使用條件格式進行標註，隱藏單元格的具體數值，可以使用數據條代替。比如如下的工資表信息：

此處演示隱藏重要的基本工資、獎金和實發工資列信息。可以通過如下的動態圖進行隱藏。

動態圖中我為了簡便，使用了格式刷，實際操作不建議這樣操作，因為如果新增了行，可能未設置的行信息也就暴露出來了。

通過設置，可以把重要的信息加以隱藏，不管是設置單元格格式為三個英文分號（;;;），還是設置字體和背景色一致。當滑鼠點選條件格式單元格，會在編輯欄看到具體的數值信息。

二 隔行（列）變色

有些時候我們需要設置的單元格格式根據數據所在行或列進行相應的顯示，此時可以使用Row()和Column()結合條件格式達到。比如說實現如下圖所示的效果。

如果需要實現偶數行行變色，可以通過公式=MOD(ROW(),2)-1來達到，後面的-1如果去掉的話，也就是奇數行被填充顏色。

如果需要實現偶數列字體變為紅色，可以通過公式=MOD(COLUMN(),2)-1來達到，後面的-1如果去掉的話，也就是奇數列字體為紅色。

具體設置請見動態圖演示：

如果需要隔行變色，也可以使用Ctrl+T或Ctrl+L來創建表，套用表格樣式，也可以快速實現隔行變色。

三 生日提醒

一般的公司的HR都會在員工生日的時候標註當月或最近一周有那幾位員工生日。為了演示，我模擬了如下數據：

從上圖可以看出，生日列複製了三份。分別是最近一周生日、往後一周生日和本月生日。

①最近一周生日，需要標註包括往前和往後7天的生日人員信息，可以使用公式=ABS(DATE(YEAR(TODAY()),MONTH($D1),DAY(D1))-TODAY())<=7，其實該公式也非常容易理解，因為生日提醒主要根據員工的出生月和日來決定，所以年就可以使用Today()來代替，因為往前7天和Today()相減會是負數，故使用ABS求絕對值。

②往後一周生日，需要標註包括往後7天的生日人員信息，可以使用公式=AND(DATE(YEAR(TODAY()),MONTH($E1),DAY(E1))-TODAY()>=0,DATE(YEAR(TODAY()),MONTH($E1),DAY(E1))-TODAY()<=7)，其中使用了AND函數，當兩個條件都滿足的時候，才返回TRUE，也就是需要標註的員工信息。

③本月生日，很多大型公司（哦，不對，可能大型公司有專門的人員管理軟體，會自動提示，不會使用到Excel），會每月舉辦生日會，HR會把當月所有生日的員工聚到一起，訂很多蛋糕，買很多吃的、喝的，大家一起慶祝，也能讓員工感受大家庭的溫暖。設置本月員工生日提醒的公式非常簡單，公式為=MONTH($F1)=MONTH(TODAY())，就是那員工的信息取月和Today()行數取月進行比較，相同月份的標註出來即可。

在文章的開頭，有位廣東的小夥伴諮詢的問題，如果僅僅是他提供的數據，那可以做到圖2的樣式，當庫存不夠的當天，用黃色底紋標註出來，可以使用如下的條件格式來實現。公式為：=COLUMN()=8-INT(($I2-$J2)/50)。

如果數據真如該小夥伴模擬的那樣，那上面的條件格式已經可以滿足要求，但實際該小夥伴需要的是，每天需要的庫存都是不固定的，是動態的，那上面我使用的公式也就無法湊效了。

PS：在這裡呼籲大家在問問題的時候，就算模擬數據也要模擬到位，否則別人給你解答問題的時候，也是在浪費彼此的時間。

為了模擬庫存是不固定的，我特地使用函數RandBetween(1,100)來模擬庫存，但在模擬好數據後，我使用了選擇性粘貼，把公式變為了數值類型。通過修改公式，我發現想要實現標註到某一天的某一個單元格庫存不夠使用會比較困難，故和小夥伴溝通後，使用變通的方法加以實現，實現的效果如下：

其實該顯示我感覺比小夥伴需要的樣式更加直觀，通過上圖可以發現，拿最後一行舉例，庫存總共有231，前面23+16+33+44+35+23=174，但如果174+75就會超過庫存，所以庫存只能滿足到6號。

其實該小夥伴的問題，也可以使用VBA來實現，有興趣的小夥伴可以編寫分享一下哦。

上面小夥伴的問題，大家如果有更加好用的方法，歡迎留言或加入QQ群（群號：615356012）交流學習哦，期待你的進步^_^Written by Steven in 20180417^_^

微信公眾號：SaveUTime

SUT學習交流群：615356012，入群費用：5元，非誠勿擾~

關注公眾號，提高效率，節約您的時間！