条件格式的11种应用

条件格式的11种应用

1、  单元格数值满足一定条件字体或背景颜色标红. 1

2、  单元格数值满足一定条件标上相应的图标. 2

3、  将单元格数字变成条形图. 4

4、  查找重复值或唯一值. 4

5、  国际象棋棋盘式底纹设置方法. 5

6、  奇偶行不同的斑马纹. 7

7、  永恒的间隔底纹. 8

8、  每隔N行批量填充颜色. 9

9、  突出显示符合要求的日期. 11

10、 自动实现生日提醒. 14

11、 用条件格式制作项目进度图. 15

1、单元格数值满足一定条件字体或背景颜色标红

使用Excel条件格式可以直观地查看和分析数据、发现关键问题以及识别模式和趋势,用户可以为自己的数据区域设置精美而又宜于阅读的格式。这种设置效果不同于单元格格式,它是完全动态的,无论在数据区域中增加或删除行、列,格式都会自动进行相应地调整,保持原有的风格。

例如,要把表1的超过100%的数字标红,以突出显示。

98.30%

95.29%

106.73%

115.20%

97.12%

92.46%

100.27%

101.33%

99.28%

97.22%

103.42%

104.33%

105.49%

101.94%

106.27%

110.02%

75.38%

75.41%

81.89%

83.39%

68.87%

66.18%

85.77%

0.00%

表1

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

图 1

点击确定后效果如图2:

图 2

2、单元格数值满足一定条件标上相应的图标

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

图 3

选择图标集,找到交通灯图标,设置如图4:

图 4

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

图 5

图 6

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

3、将单元格数字变成条形图

用数据条可以清晰看到数据的大小,不需要通过制作图表就能看出来,如图7:

图 7

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

图 8

4、查找重复值或唯一值

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

图 9

图 10

5、国际象棋棋盘式底纹设置方法

要设置如图11所示的国际象棋棋盘式底纹,方法如下:

图 11

选中要设置底纹的数据区域,单击条件格式。

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

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

图 12

同样的方法,再设置第2种颜色,如图13:

图 13

6、奇偶行不同的斑马纹

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

图 14

条件格式公式设置方法如图15:

图 15

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

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

7、永恒的间隔底纹

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

图 16

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

图 17

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

8、每隔N行批量填充颜色

上面的第6点和第7点的隔行填色,只是隔一行,如果需要每隔N行批量指定单元格格式,如何做?比如,每隔5行为一个项目区域,如图18,为了方便辨别每个项目而不是每行,我们需要按照每隔5行的规则填充单元格。

图 18

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

图 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()函数得到的错误填充结果。

图 20

图 21

9、突出显示符合要求的日期

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

图 22

条件格式公式设置如图23和24:

图 23

图 24

注意:条件格式设置公式需要把应用范围固定,如图25:

图 25

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

图 26

图 27

10、   自动实现生日提醒

如果需要对员工的生日实现按周自动提醒,可以这样设置公式:=ABS(DATE(YEAR(TODAY()),MONTH($B1),DAY($B1))-TODAY())<=7,如图28:

图 28

设置后的表格自动显示最近一周过生日的名单,如图29:

图 29

11、   用条件格式制作项目进度图

制作进度图的数据源如图30:

图 30

首先准备数据并进行初步的单元格格式设置,调整列宽,去掉网格线和边框,如图31:

图 31

选择D3:X8数据区域,点击菜单开始→条件格式→新建规则→使用公式确定要设置格式的单元格,输入公式=(D$2>=$B3)*(D$2<=$C3),设置单元格背景颜色,公式意思是如果第2行日期大于等于起始日小于结束日就表示项目某进程的完成时间。如图32。

图 32

再新建规则,输入公式=D$2=TODAY(),这个公式目的是为了突出显示整个项目进程中的当前日期。点击格式,设置虚线边框和字体颜色,如图33,最终效果图如图34。

图 33

图 34

今天的分享到此结束,如果想看更多历史文章,请从菜单所有文章查看。

(0)

相关推荐