利用条件格式,我们可以根据数据的实际情况动态地设置单元格显示的样式,今天给大家分享一些实用的条件格式设置实例。
1、标记当前周
选中B1:L1的单元格区域,在【开始】选项卡下面点击“条件格式”、“新建规则”,在弹出的对话框中选择“使用公式确定要设置格式的单元格”,并输入以下公式、设置单元格背景色为蓝色。
=WEEKNUM(B1,2)=WEEKNUM(TODAY(),2)
说明:WeekNum函数获取日期对应的周,参数2表示每周是从星期一开始。
最终结果如下。
2、标记当前日期
跟上面的操作类似,只是需要更改为如下公式。
=B1=TODAY()
说明:Today返回当前的日期,如果和标题行中的日期一致,则标记蓝色背景色。
3、标记周末
还是类似的操作,公式更改如下。
=WEEKDAY(B1,2)>5
说明:Weekday函数返回星期一到星期日每一天对应的数字,参数2表示从星期一到星期日分别对应1~7。所以如果Weekday结果大于5时,就表示是周六周日。
4、动态添加边框
选中A1:L5的区域,其他的跟之前的操作类似,只是需要在格式设置中添加边框,公式更改如下。
=COUNTA($A1:$L1)>0
说明:Counta用于统计单元格区域是否有内容,这里设置的区域引用是混合引用,也就是固定在A到L列,但是行号会变化。所以,当每行中A到L列的单元格中有内容时就添加边框。
5、隔行填充颜色
选中A2:L5的区域来设置,跟上面的操作类似,设置好需要填充的背景色,公式设置如下。
=MOD(ROW(2:2),2)
说明:Mod返回两个数相除的余数,第一个参数是被除数,第二个参数是除数。如果返回结果是0,代表false,返回结果是1代表True,所以这里奇数行就填充了背景色。
6、标记出每天的销售冠军
选中B2:L5的区域,跟上面的操作类似,公式如下。
=B2=MAX(B$2:B$5)
说明:注意,这里Max中的参数区域是混合引用,固定在第2到第5行,列号是变化的,这样才能为按列来标记最大值。
7、标出低于平均值的单元格
条件格式功能改进后,已经添加了一些预置的功能,比如高于或低于平均值,前、后n项,前、后n%项等。
在【开始】选项卡中点击“条件格式”、“项目选取规则”、“低于平均值”,然后在弹出的对话框中设置格式,这里可以选择预置的格式,也可以点击最下面的“自定义格式”来自己设置格式。
设置格式:
最终结果如下:
8、标记出最大的前20%项
跟第7个示例类似,这里需要选择菜单中的第2项,即“前10%...”,在弹出的对话框中将数字框中的10改成20就可以了。
9、标记重复值
在【开始】选项卡中点击“条件格式”、“突出显示单元格规则”、“重复值”,在弹出的对话框中设置突出显示的单元格格式,操作跟上面的操作类似。
最终结果如下。
10、高亮显示选中的行
在A2:L5的区域,鼠标选中哪行,哪行就高亮显示。
a. 首先定义一个名称SelectedRow,值设置为1。
b. 然后按Alt+F11,打开VBA编辑器,在左边的工程资源管理器中双击需要设置的工作表,在右边输入以下代码。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ThisWorkbook.Names("SelectedRow").Value = Target.Row
End Sub
c. 选中A2:L5的区域,设置条件格式,公式如下。这样就完成了。
=SelectedRow=ROW(A2)
10个条件格式应用实例就分享到这里了。
如果有不明白的话,欢迎加入QQ群9735376参与讨论。
Excel轻松学
联系客服