【引语】在我的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 舍入后的数字的小数位数。
图5,使用rounddown()函数得到的错误填充结果。
操作视频如下:
联系客服