小夥伴們好啊，今天和大家分享一個條件格式有關的技巧。

如下圖所示，A列已經按部門進行了排序，要根據不同部門來標記顏色。

本題的主要切入點是：

自A2單元格開始向下依次判斷有多少個不重複值，再判斷不重複值的數量是不是2的倍數。

將公式運用到條件格式當中，就可以實現題目要求了。

具體操作的方法如下：

選擇A2:C14單元格區域，依次單擊【開始】→【條件格式】

→

【新建規則】

→

【使用公式確定要設置格式的單元格】。

輸入以下公式：

=MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),),2)

當選擇A2:C14單元格區域的時候，第一個選中的單元格「A2」叫做活動單元格，在條件格式中可以直接針對這個單元格使用公式，Excel會自動將公式應用到我們提前選中的區域（A2:C14）。

接下來，我們簡單說一下條件格式中這個公式的意思。

=MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),),2)

先來看這一部分：

SUM(1/COUNTIF($A$2:$A2,$A$2:$A2))

細心的小夥伴可能發現了，這個公式中第一個A2有兩個$（$A$2），而第二個A2隻有一個$（$A2），這一美元到底有什麼作用呢？

說到這裡，有必要和大家先說說絕對引用與相對引用：

A1——相對引用，向右向下複製公式時引用的範圍都會變；

A$1——列相對行絕對引用，向右複製公式時列標變化而向下複製時行號不會變。

$A1——列絕對行相對引用，向右複製公式時列標不會變而向下複製公式時行號會變。

$A$1——向右向下複製公式時引用的範圍都不會變。

也可以理解成這個$就像一個釘子，

釘

到行號前面，行號就不變了；

釘

到列標前面，列標就不會變了。這麼一說有點像繞口令，大家有時間可以試一下，實際動動手，會更容易理解一些。

本例中的

$A$2:$A2這一部分，

$A$2使用的是絕對引用， $A2使用的是列絕對引用。

當公式作用到B、C列中時，引用的列都不會發生變化，始終會計算A列的內容。

當公式作用到第三行時，$A2的行號發生變化，公式的引用區域就變成

$A$2:$A3。當公式作用到第四行時，公式的引用區域就變成$A$2:$A4……

也就是對A列

自A2開始，到公式所在行

的數據區域進行不重複的計數。

關於不重複計數的計算過程，可以參考一下下面的簡要說明。

公式中包含了一個簡單的數學邏輯：

任意一個數據重複出現N次，N個1/N的和值為1。

公式中的「

COUNTIF($A$2:$A2,$A$2:$A2)

」部分，作用是分別

統計

$A$2:$A2這個動態擴展的

單元格區域中，每個元素出現的次數。

以A14單元格為例，返回內存數組結果為：

{4;4;4;4;4;4;4;4;2;2;2;2;1}

再使用1除以返回的內存數組，

即相當於計算COUNTIF函數所返回內存數組的倒數。

如果單元格的值在區域中是唯一值，這一步的結果是1。

如果重複出現兩次，這一步的結果就有兩個1/2。

如果單元格的值在區域中重複出現3次，結果就有3個1/3…

即每個元素對應的倒數合計起來結果仍是1。

最後用SUM函數求和，得出在動態擴展的數據範圍中

的

不重複人數。

雖然這是一個數組公式，但是在條件格式中使用數組公式時，不需要按Shift+ctrl+回車結束。

MOD函數返回不重複人數與2相除的餘數。

得到結果為1或是0。在條件格式中，如果指定的條件返回邏輯值TRUE或是不等於0，就會返回我們指定的格式。

由於在實際運算中，可能會因為浮點誤差造成MOD函數計算結果錯誤，所以在公式中使用了ROUND函數，對總人數的計算結果進行修約保留到整數。

練習文件在此：

https://pan.baidu.com/s/13oupHquSfTuN0b2T9d_5Iw

好了，今天的內容就是這些吧，祝各位小夥伴假期愉快！

圖文製作：祝洪忠

扎心提示

：假期模式即將結束，系統切換中……

推薦閱讀：

相關文章