【原】每隔N行批量填充颜色技巧

【引语】在我的excel书《Excel高手捷径：一招鲜，吃遍天》中第11招条件格式介绍了隔行填充颜色的技巧，本文梁悦老师给我们分享了每隔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

图5，使用rounddown()函数得到的错误填充结果。

操作视频如下：