每隔N行批量填充顏色技巧

當我們在Excel中操作內容繁多的表格時，為了方便辨別我們經常會應用到條件格式，利用mod()函數定義隔行填充規則，對表格行顏色進行區分。如下圖1，我們在「使用公式確定要設置格式」時用到的公式是=mod(row(),2)=0（或等於1），即針對單元格行號對2求余，餘數為0（或1）則指定相應格式變化（下圖指定填充橘色，條件格式應用範圍為=$A$1:$I$20）。

圖1

如果我們需要每隔N行批量指定單元格格式的時候，應該如何來應用呢？請參考圖2的案例：每隔5行為一個項目區域，為了方便辨別每個項目而不是每行，我們需要按照每隔5行的規則填充單元格。

圖2

步驟詳解：

1、在「開始」菜單中，選擇條件格式→新建規則→管理規則→使用公式確定要設置格式的單元格；

2、編輯規則「為符合此公式的值設置格式」中輸入以下公式=MOD(ROUNDUP(ROW()/5,0),2)=1（或等於0），挑選合適的格式，點擊確定，隨後輸入該條件格式應用範圍，本例為=$B$1:$K$30，隨後應用，即可得到如圖3中格式的單元格。

圖3

3、公式=MOD(ROUNDUP(ROW()/5,0),2)=1 解釋如下：

用行號除以5，得到的值針對個位進行向上取整，隨後針對該整數對2求余，結果為1的行進行格式變化。也就是說，如行號為1、2、3、4、5，除以5分別得到0.2、0.4、0.6、0.8、1，對它們進行個位的向上取整為1、1、1、1、1；結果對2求余皆為1，因此前5行滿足公式全部填充。以此類推，由於6-10行結果2求余為0，因此不填充。所以當需要每隔N行批量填充時，我們利用行號除以N，將結果圈定在(0,1]、(1,2]……之間，再用roundup()函數統一成1、2……。因此我們應用通用公式=MOD(ROUNDUP(ROW()/N,0),2)=1（或等於0）即可達到每隔N行批量填充的目的。

4、擴展思考：為什麼這裡不能使用ROUNDDOWN()函數？

讓我們用剛才的例子想一下：行號為1、2、3、4、5的行除以5分別得到的0.2、0.4、0.6、0.8、1，對它們進行個位的向下取整為0、0、0、0、1，因此對2求余的結果不統一，無法滿足我們的填充要求。大家可以通過圖4紅框中查看使用roundup()及rounddown()函數所得到公式結果的區別，圖5即為使用rounddown()函數得到的錯誤填充結果。

通過以上應用對比，希望大家能夠加深對這兩個函數的理解。

最後讓我們再來鞏固一遍函數語法：遠離0值舍入：ROUNDUP(number,num_digits)，遠離零值向上舍入數字。Number為需要向上舍入的任意實數。Num_digits 舍入後的數字的小數位數。靠近0值舍入：ROUNDDOWN(number,num_digits)，靠近零值向下舍入數字。Number為需要向下舍入的任意實數。Num_digits 舍入後的數字的小數位數。

圖4