返回目录：excel表格制作
想让数据更加醒目，突出重点，这四个Excel技巧能帮上你
- 如何每隔N行，进行批量填充颜色？
- 如何突出显示符合要求的日期？
- 如何利用公式自动实现生日提醒？
- 如何利用条件格式制作项目进度图？
1.如何每隔N行，进行批量填充颜色？
如果需要每隔N行批量指定单元格格式，如何做？比如，每隔5行为一个项目区域，如下图所示。为了方便辨别每个项目而不是每行，我们需要按照每隔5行的规则填充单元格。
编辑规则“为符合此公式的值设置格式”中输入以下公式=MOD(ROUNDUP(ROW()/5,0),2)=1（或等于0），挑选合适的格式，单击“确定”按钮，随后输入该条件格式应用范围，本例为=$B$1:$K$30，随后应用，即可得到如下图所示中格式的单元格。
公式解释：用行号除以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行批量填充的目的。
为什么这里不能使用ROUNDDOWN()函数？
行号为1、2、3、4、5的行除以5分别得到0.2、0.4、0.6、0.8、1，对它们进行个位的向下取整为0、0、0、0、1，因此对2求余的结果不统一，无法满足我们的填充要求。
大家可以在下图一红框中查看使用ROUNDUP()及ROUNDDOWN()函数所得到公式结果的区别，下图二即为使用ROUNDDOWN()函数得到的错误填充结果。
2.如何突出显示符合要求的日期？
把下面的日期设置条件格式，周六用粉色标识，周日用绿色标识，其他不变，如下图所示。
条件格式公式设置如下二图所示。
注意：条件格式设置公式需要把应用范围固定，如下图所示。
如果应用范围是A、B整列，如下图一所示，公式还是=WEEKDAY($A3,2)=6，则得到错误的结果，把不是周末的标识颜色，例如，如下图二所示，2012-1-5是周四却标识粉色的周六，2012-1-6是周五却标识绿色的周日。
3.如何利用公式自动实现生日提醒？
如果需要对员工的生日实现按周自动提醒，可以这样设置公式：=ABS(DATE(YEAR(TODAY()),MONTH($B2),DAY($B2))-TODAY())<=7，如下图所示。
设置后的表格自动显示最近一周过生日的名单，如下图所示。
4.如何利用条件格式制作项目进度图？
制作进度图的数据源如下图所示。
首先准备数据并进行初步的单元格格式设置，调整列宽，去掉网格线和边框，如下图所示。
选择D3:X8数据区域，单击开始菜单→条件格式→新建规则→使用公式确定要设置格式的单元格，输入公式=(D$2>=$B3)*(D$2<=$C3)，设置单元格背景颜色，公式意思是如果第2行日期大于等于起始日小于等于结束日就表示项目某进程的完成时间，如下图所示。
再新建规则，输入公式=D$2=TODAY()，这个公式目的是突出显示整个项目进程中的当前日期。单击格式，设置虚线边框和字体颜色，如下图一所示，最终效果如下图二所示。
