各位表親好啊,
今天老祝和大家分享一個條件格式的高階用法,
先看一下最終效果:
要求在上圖所示的工作表中, 將不同部門的資料用顏色突出顯示, 如果部門有變化, 會自動更新顯示效果。
本題的主要切入點是, 自A2儲存格開始向下依次判斷有多少個不重複值, 再判斷不重複值的數量是不是2的倍數。 將公式運用到條件格式當中, 就可以實現題目要求了。
先對部門進行排序,
選擇A2:C14儲存格區域。
依次按一下【開始】【條件格式】【新建規則】。
在新建格式規則對話方塊中選擇規則類型:【使用公式確定要設置格式的儲存格】。
在【規則編輯說明】對話方塊中【為符合此公式的值設置格式】,
輸入公式:
=MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),),2)
按一下【格式】按鈕, 在【設置單元格格式】對話方塊中, 按一下【填充】選項卡, 選擇灰色, 按一下確定。
回到【新建格式規則】對話方塊, 再次按一下確定, 完成條件格式的設置。
接下來,
我們簡單說一下條件格式中這個公式的意思。
=MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),),2)
SUM(1/COUNTIF(區域,區域))是計算不重複個數的經典公式。
有興趣的同學可以點擊這裡學習一下原理:
當選擇A2:C14儲存格區域的時候, 第一個選中的儲存格“A2”叫做作用儲存格, 在條件格式中可以直接針對這個儲存格使用公式, Excel會自動將公式應用到我們提前選中的區域(A2:C14)。
細心的朋友會發現這個公式中第一個A2有兩個$($A$2),
這一美元到底有什麼作用呢?剛剛接觸Excel的同學可能會有點困惑, 下面先來說說絕對引用與相對參照:
A1——相對參照, 向右向下複製公式時引用的範圍都會變。
A$1——列相對行絕對引用, 向右複製公式時列標變化而向下複製時行號不會變。
$A1——列絕對行相對參照, 向右複製公式時列標不會變而向下複製公式時行號會變。
$A$1——向右向下複製公式時引用的範圍都不會變。
也可以理解成這個$就像一個掛鉤, 掛到行號前面, 行號就不變了;掛到列標前面, 列標就不會變了。
這麼一說有點像繞口令, 大家有時間可以試一下, 實際動動手, 會更容易理解一些。
在本例中, SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)), $A$2使用的是絕對引用, $A2使用的是列絕對引用。
當公式作用到BC列中時, 列標都不會發生變化,所以還會計算A列的內容。
當公式作用到第三行時,$A2的行號發生變化,公式的引用區域就變成:
SUM(1/COUNTIF($A$2:$A3,$A$2:$A3))
當公式作用到第四行時,公式的引用區域就變成
SUM(1/COUNTIF($A$2:$A4,$A$2:$A4))
…………
也就是對A列自A2開始,到公式所在的當前行的資料區域進行不重複的計數。
MOD函數返回兩數相除的餘數。
如果SUM(1/COUNTIF($A$2:$A2,$A$2:$A2))計算的結果是1、3、5、7…..這樣的奇數,MOD函數的計算結果就是1;反之,MOD函數的計算結果就是0。
在條件格式中,如果指定的條件返回邏輯值TRUE或是不等於0,就會返回我們指定的格式。
通過對不重複值出現的次數,實現了不同數值顏色突出顯示的目的。
ROUND函數,在這裡將SUM函數的運算結果保留到整數,是為了避免因為浮點誤差出現的錯誤判斷。
好了,今天的分享就是這些吧,練習檔在此:
https://(去掉括弧和裡面的內容)pan.(去掉括弧和裡面的內容)baidu.(去掉括弧和裡面的內容)com/s/1xBwD-yoVbl7tSXp9FmrsIg
圖文製作:祝洪忠
請點擊此處輸入圖片描述
列標都不會發生變化,所以還會計算A列的內容。當公式作用到第三行時,$A2的行號發生變化,公式的引用區域就變成:
SUM(1/COUNTIF($A$2:$A3,$A$2:$A3))
當公式作用到第四行時,公式的引用區域就變成
SUM(1/COUNTIF($A$2:$A4,$A$2:$A4))
…………
也就是對A列自A2開始,到公式所在的當前行的資料區域進行不重複的計數。
MOD函數返回兩數相除的餘數。
如果SUM(1/COUNTIF($A$2:$A2,$A$2:$A2))計算的結果是1、3、5、7…..這樣的奇數,MOD函數的計算結果就是1;反之,MOD函數的計算結果就是0。
在條件格式中,如果指定的條件返回邏輯值TRUE或是不等於0,就會返回我們指定的格式。
通過對不重複值出現的次數,實現了不同數值顏色突出顯示的目的。
ROUND函數,在這裡將SUM函數的運算結果保留到整數,是為了避免因為浮點誤差出現的錯誤判斷。
好了,今天的分享就是這些吧,練習檔在此:
https://(去掉括弧和裡面的內容)pan.(去掉括弧和裡面的內容)baidu.(去掉括弧和裡面的內容)com/s/1xBwD-yoVbl7tSXp9FmrsIg
圖文製作:祝洪忠
請點擊此處輸入圖片描述