Excel技巧|Excel条件格式功能的灵活应用

来源:澳洲CPA 时间:2021-10-24 作者:澳洲CPA编辑:无忧草
打印 RSS |
导读:如何使用自动化格式,现在就告诉你。

Excel有许多内置的自动化条件格式,非常适合用于控制面板和异常报告,此外还可利用公式控制条件格式并按需要设置格式。

下面就详细为您介绍如何在控制面板和报告中灵活运用条件格式功能。

温馨提示

√ 条件格式通常基于要设置格式的单元格的值,但利用公式可基于其他单元格的值设置格式。

√ 使用条件格式时需要记住的是,大约每12名男性中或每100名女性中就有一人患有某种形式的色盲,其中最常见的是红绿色盲,因此使用蓝色会更安全。

点击这里,边看边学习。

以下是文字版内容:

如果我们有一个日期列表,需要为周末和节假日设置不同的日期格式,就可以利用公式设置条件格式。

Excel技巧|Excel条件格式功能的灵活应用

图一

图1显示了从月初到月底的日期列表,以及开始日期和节假日列表。

单元格D2是数据输入单元格。单元格A2中包含按月份生成日期的公式,并且该公式已复制到整列。B列包含日期转星期缩写公式。本文的配套视频将详细介绍这些公式,并演示生成连续日期的最新方法。

我们希望突出显示A列和B列中的所有周末或节假日。该方法也可用于突出显示其他日期,如两周一次的付款日。

我们可以利用公式设置条件格式

No.1

用于确定周末

可按照以下步骤利用公式设置条件格式。

1.选择要应用的范围,在本例中为A2:B32。

2.点击“条件格式”图标下拉菜单并选择“新建规则”。

3.选择顶部区域的最后一项“使用公式……”,并在公式输入框中输入公式:=WEEKDAY($A2,2)>5

4.创建的公式必须返回TR√E才能应用格式。它的作用类似于IF函数的第一部分。这称为逻辑测试,返回TRE或FALSE。点击“格式”按钮,点击“填充”选项卡,选择想要应用的灰色,然后点击“确定”。

5.“新建格式规则”对话框如图2所示。点击“确定”应用格式。

Excel技巧|Excel条件格式功能的灵活应用

图2

结果如图3所示。

Excel技巧|Excel条件格式功能的灵活应用

图三

公式解析

WEEKDAY函数返回代表一周中第几天的数值(1到7),通常可写成=WEEKDAY($A2),数值1代表周日,数值7代表周六。

当公式写成=WEEKDAY($A2,2)时,后面的“,2”代表不同的星期表示方法,设置起来很简单。在此例中,周一=1,周日=7。这意味着对于公式=WEEKDAY($A2,2)>5,所有周末将返回TR√E。

该公式的另一个重要部分是使用“$A2”引用单元格。利用公式为某一区域设置条件格式时,公式中的单元格引用必须与该区域左上角的单元格相关。因为公式也处理B列,因此我们使用$符号表示对A列的绝对引用。通常会通过反复试验来修正公式。

No..2

用于突出显示节假日

Excel中没有内置的节假日列表,因此必须自行创建。在图1中,F列中有5个日期。

可按照以下步骤以蓝色突出显示节假日。

1.选择范围A2:B32。

2.点击“条件格式”图标下拉菜单并选择“新建规则”。

3.选择顶部区域的最后一项“使用公式……”,并在公式输入框中输入公式:=CO√NTIF($F$2:$F$6,$A2)>0

4.点击“格式”按钮,点击“填充”选项卡,选择想要应用的蓝色,然后点击“确定”。图4显示了对话框。

Excel技巧|Excel条件格式功能的灵活应用

图4

5. 点击“确定”。图5显示了部分列表。

Excel技巧|Excel条件格式功能的灵活应用

图5

如果更改单元格D2,可以看到12月份的结果(参见图6)。

如果希望12月25日和26日的周末灰色优先级高于节假日蓝色,可通过修改条件格式来实现。

1.选择范围A2:B32。

2.点击“条件格式”图标下拉菜单并选择“管理规则”(最后一项)。

3.点击底部的规则,点击“复制规则”图标右侧的向上小箭头,然后点击“确定”。

图7显示了修改后的对话框和结果。

Excel技巧|Excel条件格式功能的灵活应用

图7

规则的顺序很重要,因为会按顺序处理条件。如果一个单元格符合多个条件格式,则应用列出的第一个条件格式。

在图7中,首先列出的周末灰色条件覆盖了节假日蓝色条件。

可应用的规则数量没有实际限制。

点击这里,阅读微信原文。

沪公网安备 31011802001002号