条件格式的11种应用

点击次数:67   更新时间2019-06-25     【关闭分    享:

  使用Excel条件格式可以直观地查看和分析数据、发现关键问题以及识别模式和趋势,用户可以为自己的数据区域设置精美而又宜于阅读的格式。

  这种设置效果不同于单元格格式,它是完全动态的,无论在数据区域中增加或删除行、列,格式都会自动进行相应地调整,保持原有的风格。

  点击菜单开始→条件格式→新建格式规则,点击“格式”把字体颜色设为红色(见图1)。

  财务上对应收账款的回笼总是希望越快越好,对于未回笼的账款根据账龄长短用三色交通灯提醒,对账龄小于1个月绿色交通灯,表示账款目前安全,2个月黄色预警,表示有点危险,3个月以及以上红色告警,表示账龄太久,需要加紧催款,如图3。

  再举个比较常见的例子,数据增长和下降用图标突出显示,如图5,设置方法如图6。

  看到这个例子,有人可能会问为什么不能做到像股市的“红涨绿跌”呢?如果想要箭头的颜色变换,可以通过“插入”形状,设置形状的填充颜色,

  如果只想显示数据条,不显示数字,在编辑格式规则时在“仅显示数据条”前面方框内打勾,如图8。

  如果需要查找某数据区域是否存在重复值,将重复值醒目标识,点击条件格式→突出显示单元格规则→重复值,设置为自己想要的格式,如图9。如果选中“唯一”则把唯一值醒目标识,如图10。

  点击新建规则,选择最下面的使用公式确定要设置格式的单元格,输入公式=MOD(ROW()+COLUMN(),2)=0,单击“格式”按钮,在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为浅橙色,单击“确定”按钮,如图11。

  函数Row返回行号,Column返回列号,Mod是两数相除求余数,该条件格式的公式用于判断行号与列号之和除以2的余数是否为0。如果为0,说明行数与列数的奇偶性相同,则填充单元格为浅橙色,否则就不填充。

  当单元格数据行较多,我们为了让显示效果更加醒目,可以让工作表间隔固定行显示阴影,效果如图14所示:

  该条件格式的公式用于判断行号是否被2整除。如果公式返回结果为1,则对奇数行填充灰色底纹,如果公式返回结果为0,偶数行不填充。

  隔行填充颜色除了传统的条件格式公式设置,还有一个最简单的方法,只需要1秒钟就可以轻松搞定,单击数据区域任意单元格,按【Ctrl+T】创建表,2007版本以及以上的表默认就自动隔行填充颜色,颜色可以在“表格样式”中选择自己喜欢的颜色。

  上面的斑马纹是完全动态的,无论在数据表中插入行或者删除行,其风格都不会改变。但是有一种情况会例外。如果对此数据表进行自动筛选操作,并设置A列的筛选,则间隔底纹效果就被破坏了,如图16所示。

  如果不希望自动筛选对间隔底纹效果产生不良影响,可以通过下面的方法来实现,如图17:

  利用Subtotal函数来判断可见行的奇偶次序,另外,在条件格式的公式中,使用的是相对引用,这会让条件格式的判断对象随着行的变化而自动改变,达到“只判断当前行的序号”的目的。

  上面的第6点和第7点的隔行填色,只是隔一行,如果需要每隔N行批量指定单元格格式,如何做?

  比如,每隔5行为一个项目区域,如图18,为了方便辨别每个项目而不是每行,我们需要按照每隔5行的规则填充单元格。

  编辑规则“为符合此公式的值设置格式”中输入以下公式=MOD(ROUNDUP(ROW()/5,0),2)=1(或等于0),挑选合适的格式,点击“确定”,随后输入该条件格式应用范围,本例为=$B$1:$K$30,随后应用,即可得到如图19中格式的单元格。

  公式解释:用行号除以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求余的结果不统一,无法满足我们的填充要求。大家可以通过图20红框中查看使用Roundup()及Rounddown()函数所得到公式结果的区别,图21即为使用Rounddown()函数得到的错误填充结果。

  把下面的日期设置条件格式,周六用粉色标识,周日用绿色标识,其他不变,如图22:

  如果应用范围是A、B整列,如图26,公式还是=WEEKDAY($A3,2)=6,则得到错误的结果,把不是周末的标识颜色,例如,如图27,2012-1-5是周四却表示粉色的周六,2012-1-6是周五却标识绿色的周日。

  首先准备数据并进行初步的单元格格式设置,调整列宽,去掉网格线数据区域,点击菜单开始→条件格式→新建规则→使用公式确定要设置格式的单元格,输入公式=(D$2=$B3)*(D$2=$C3),设置单元格背景颜色,公式意思是如果第2行日期大于等于起始日小于结束日就表示项目某进程的完成时间。如图32。

  再新建规则,输入公式=D$2=TODAY(),这个公式目的是为了突出显示整个项目进程中的当前日期。点击格式,设置虚线

山东某某环保工程有限公司
技术:18265875858
电话:0533-8175858
传真:0533-8175858
地址:山东省淄博高新区英雄路58号
邮箱:baidu@163.com