excel 条件格式 公式 包含：掌握文本、数字、日期匹配的终极指南
Excel 条件格式公式包含：文本、数字、日期精确匹配的实用技巧
Excel 条件格式公式包含什么？ 它允许您使用自定义公式来动态设置单元格的格式，当公式返回 TRUE 时，格式将被应用。这在根据单元格内容（包括文本、数字或日期）自动高亮显示、修改或突出显示数据时非常有用。
Excel 的条件格式功能是数据可视化和分析的强大工具。通过结合公式，您可以实现比预设规则更为灵活和智能的格式设置。本文将深入探讨如何利用公式来实现条件格式中“包含”的各种场景，帮助您更有效地处理和解读数据。
一、 理解 Excel 条件格式与公式
在深入探讨“包含”的各种用法之前，我们首先需要理解 Excel 条件格式的基本原理以及公式在其中的作用。
1.1 Excel 条件格式基础
条件格式允许您根据单元格中的值，自动应用特定的格式，例如更改字体颜色、背景色、添加数据条、图标集等。它主要有以下几种方式应用：
- 突出显示单元格规则： 预设的规则，如“大于”、“小于”、“等于”、“介于”、“文本包含”等。
- 项目层次规则： 如“顶端/底端项目”、“高于平均值”等。
- 数据条、色阶、图标集： 基于数值的视觉化表现。
- 使用公式确定要设置格式的单元格： 这是本文的重点，也是最灵活的方式。
1.2 公式在条件格式中的作用
当您选择“使用公式确定要设置格式的单元格”时，Excel 会将您输入的公式计算为 TRUE 或 FALSE。如果公式计算结果为 TRUE，则该单元格（或应用该公式的范围内的单元格）将应用您设置的格式。反之，如果结果为 FALSE，则格式不被应用。
公式通常会引用活动单元格（即您正在设置格式的范围的左上角单元格），并且 Excel 会自动将该公式应用于范围内其他单元格，但要注意相对引用和绝对引用。
二、 Excel 条件格式公式包含文本的技巧
在处理包含文本的数据时，我们常常需要根据单元格是否包含特定的字符串来设置格式。以下是几种常用的方法。
2.1 使用 SEARCH 函数查找子字符串
SEARCH(find_text, within_text, [start_num]) 函数用于在一个文本字符串中查找另一个文本字符串，并返回该子字符串在文本字符串中开始的位置（不区分大小写）。如果未找到，则返回错误值 #VALUE!。
场景： 标记包含特定关键词的订单备注。
公式示例： 假设您想标记 A2 单元格中包含“紧急”二字的行。
=SEARCH("紧急", A2)>0
- 解释：
SEARCH("紧急", A2)：在 A2 单元格中查找“紧急”。- 如果找到，“紧急”的起始位置会是一个大于 0 的数字。
- 如果未找到，会返回 #VALUE! 错误。
>0：将 SEARCH 函数的结果与 0 进行比较。任何大于 0 的数字（表示找到）都会使条件为 TRUE。#VALUE! 错误会使条件为 FALSE（Excel 会自动处理错误值，使其不触发格式）。
应用步骤：
- 选中需要设置格式的单元格范围（例如 A2:E100）。
- 转到“开始”选项卡，点击“条件格式” > “新建规则”。
- 选择“使用公式确定要设置格式的单元格”。
- 在“为符合此公式的值设置格式”框中输入公式：
=SEARCH("紧急", A2)>0（确保 A2 是您选定范围的左上角单元格）。 - 点击“格式”按钮，设置您想要的字体颜色、填充颜色等。
- 点击“确定”关闭格式对话框，再点击“确定”应用规则。
2.2 使用 FIND 函数（区分大小写）
FIND(find_text, within_text, [start_num]) 函数与 SEARCH 类似，但它区分大小写。
场景： 标记包含特定大小写敏感的产品代码。
公式示例： 标记 A2 单元格中包含“ABC”的行（区分大小写）。
=FIND("ABC", A2)>0
- 解释： 与 SEARCH 类似，但“ABC”和“abc”会被视为不同。
2.3 使用 ISNUMBER 函数结合 SEARCH 或 FIND
SEARCH 和 FIND 在找到子字符串时返回数字，未找到时返回错误。ISNUMBER 函数用于判断一个值是否为数字。
公式示例： 标记 A2 单元格中包含“待处理”的行。
=ISNUMBER(SEARCH("待处理", A2))
- 解释：
- 如果 SEARCH 找到“待处理”，返回一个数字，ISNUMBER 为 TRUE。
- 如果 SEARCH 未找到，返回 #VALUE!，ISNUMBER 为 FALSE。
这种方式更简洁，直接返回 TRUE/FALSE，避免了与 0 比较的步骤，也是一种常见的做法。
2.4 使用 COUNTIF 函数计算特定文本的出现次数
COUNTIF(range, criteria) 函数可以计算一个范围内符合给定条件的单元格的数目。当应用于单个单元格作为范围时，可以用来判断是否包含特定文本。
场景： 标记 A2 单元格中包含“已完成”的行。
公式示例：
=COUNTIF(A2, "*已完成*")>0
- 解释：
A2：作为 COUNTIF 的范围。"*已完成*"：条件，其中星号 (*) 是通配符，表示任意字符序列。这表示查找包含“已完成”的文本。- 如果 A2 单元格包含“已完成”，COUNTIF 返回 1，大于 0 为 TRUE。
- 如果 A2 单元格不包含“已完成”，COUNTIF 返回 0，大于 0 为 FALSE。
注意： COUNTIF 的通配符 (*) 和 (?) 在条件格式公式中非常有用。
2.5 使用 ISNUMBER 和 SEARCH/FIND 配合通配符（更复杂但有时必要）
虽然 SEARCH 和 FIND 本身就能查找子字符串，但在某些需要更精确匹配模式时，可能会结合通配符。
场景： 标记 A2 单元格以“PR-”开头，后跟任意数字的行。
公式示例：
=ISNUMBER(SEARCH("PR-??????", A2)) （假设后面有6个数字）
或者使用更通用的模式：
=SUMPRODUCT(--ISNUMBER(FIND("PR-", A2)))>0
解释： 上述第二个示例是通过 SUMPRODUCT 计算 FIND 的结果。更常见的做法是直接使用 SEARCH/FIND 查找一个固定的字符串。
2.6 检查整个单元格是否等于特定文本
如果不是“包含”，而是要求单元格的文本必须完全等于某个值，则公式可以非常简单。
场景： 标记 A2 单元格内容完全等于“完成”的行。
公式示例：
=A2="完成"
解释： 直接比较 A2 单元格的值与字符串“完成”。
三、 Excel 条件格式公式包含数字的技巧
对于数字，我们可以根据其值的大小、范围、是否为整数、是否包含特定数字等进行格式设置。
3.1 检查数字是否大于、小于、等于特定值
这些是最基本的数字比较。
场景： 标记 A2 单元格数值大于 100 的行。
公式示例：
=A2>100
场景： 标记 A2 单元格数值小于等于 50 的行。
公式示例：
=A2<=50
3.2 检查数字是否在特定范围内
使用 AND 函数组合两个条件。
场景： 标记 A2 单元格数值在 50 到 100 之间的行（包含 50 和 100）。
公式示例：
=AND(A2>=50, A2<=100)
3.3 检查数字是否包含特定数字（作为子字符串）
这与文本包含类似，需要将数字转换为文本来查找。
场景： 标记 A2 单元格数值（作为文本）包含“7”的行。
公式示例：
=ISNUMBER(SEARCH("7", TEXT(A2, "0")))
- 解释：
TEXT(A2, "0")：将 A2 单元格的数字转换为文本格式。这里的 "0" 是一个占位符，确保数字被正确转换为字符串。对于小数，可以使用 "0.00" 等。SEARCH("7", ...)：在转换后的文本中查找数字“7”。ISNUMBER(...)：判断是否找到。
3.4 检查数字是否为整数
使用 INT 函数（向下取整）或 TRUNC 函数（截断小数部分）与原数字比较。
场景： 标记 A2 单元格数值为整数的行。
公式示例：
=A2=INT(A2)
- 解释： 如果 A2 是整数，其值与向下取整后的值相等。
3.5 检查数字是否为偶数或奇数
使用 MOD 函数（求余数）。
场景： 标记 A2 单元格数值为偶数的行。
公式示例：
=MOD(A2, 2)=0
- 解释： 偶数除以 2 的余数为 0。
场景： 标记 A2 单元格数值为奇数的行。
公式示例：
=MOD(A2, 2)<>0 或 =MOD(A2, 2)=1 (对于正数)
四、 Excel 条件格式公式包含日期的技巧
处理日期时，我们常常需要根据日期是否在特定期间、是否为工作日、是否早于或晚于某个日期等来设置格式。
4.1 检查日期是否大于、小于、等于特定日期
日期在 Excel 中实际上是序列号，可以直接进行比较。
场景： 标记 A2 单元格日期早于 2023 年 12 月 31 日的行。
公式示例：
=A2
- 解释：
DATE(2023, 12, 31)：创建一个表示 2023 年 12 月 31 日的日期序列号。- 直接比较 A2 的日期序列号与该日期序列号。
提示： 您也可以直接输入日期字符串，Excel 会尝试将其转换为日期，例如 =A2<"2023/12/31"，但使用 DATE 函数更严谨。
4.2 检查日期是否在特定日期范围内
同样使用 AND 函数。
场景： 标记 A2 单元格日期在 2025 年 1 月 1 日至 2025 年 3 月 31 日之间的行（包含边界）。
公式示例：
=AND(A2>=DATE(2025, 1, 1), A2<=DATE(2025, 3, 31))
4.3 检查日期是否为本周、本月、本年
使用 WEEKNUM、MONTH、YEAR 函数。
场景： 标记 A2 单元格日期在本周的行。
公式示例：
=WEEKNUM(A2, 1)=WEEKNUM(TODAY(), 1)
- 解释：
WEEKNUM(A2, 1)：计算 A2 单元格所在的周数（星期日作为一周的第一天）。WEEKNUM(TODAY(), 1)：计算当前日期所在的周数。- 比较两者是否相等。
场景： 标记 A2 单元格日期在本月的行。
公式示例：
=MONTH(A2)=MONTH(TODAY())
场景： 标记 A2 单元格日期在本年的行。
公式示例：
=YEAR(A2)=YEAR(TODAY())
4.4 检查日期是否为工作日
使用 NETWORKDAYS 或 WORKDAY 函数（这两个函数需要“分析工具”加载项，或者您可以使用更基本的公式）。这里介绍不依赖加载项的方法。
场景： 标记 A2 单元格日期为工作日的行。
公式示例：
=OR(WEEKDAY(A2, 2)=5, WEEKDAY(A2, 2)=6)
- 解释：
WEEKDAY(A2, 2)：返回 A2 单元格是星期几（星期一=1，星期日=7）。OR(WEEKDAY(A2, 2)=5, WEEKDAY(A2, 2)=6)：判断是否是星期五（5）或星期六（6）。如果不是，则为工作日。更正： 这种逻辑是查找非工作日，为了查找工作日，应该判断是否不是周六和周日。
更正后的工作日公式：
=AND(WEEKDAY(A2, 2)<=5, WEEKDAY(A2, 2)>=1)
或者更简洁的：
=WEEKDAY(A2, 2)<=5
解释： WEEKDAY(A2, 2) 返回 1-5 代表周一到周五，6 代表周六，7 代表周日。所以小于等于 5 就意味着是工作日。
4.5 检查日期是否过了今天
场景： 标记 A2 单元格日期已经过了今天的行。
公式示例：
=A2
五、 组合使用公式实现更复杂的“包含”场景
Excel 条件格式的强大之处在于可以组合使用各种函数。
5.1 包含特定文本且值满足数字条件的行
场景： 标记 A2 单元格包含“订单”二字，并且 B2 单元格数值大于 500 的行。
公式示例：
=AND(ISNUMBER(SEARCH("订单", A2)), B2>500)
- 解释： 使用 AND 函数将两个条件连接起来。
5.2 包含特定日期范围且状态为“待处理”的行
场景： 标记 A2 单元格日期在 2025 年 6 月份，并且 B2 单元格文本为“待处理”的行。
公式示例：
=AND(MONTH(A2)=6, YEAR(A2)=2025, B2="待处理")
5.3 跨列判断包含关系
当您需要设置格式的范围比条件判断的列更多时，需要注意公式中的引用。
场景： 标记 A2:D100 范围内，如果 A 列包含“关键”，则整行（A2:D2，A3:D3 等）都应用格式。
公式： =ISNUMBER(SEARCH("关键", $A2))
- 解释：
$A2：这里的关键在于绝对引用列（$A）和相对引用行（2）。- 当公式应用于 A2 时，判断 $A2（即 A2）是否包含“关键”。
- 当 Excel 将公式应用于 B2 时，它实际应用的是 $A2（仍然是 A2），所以 B2 的内容不影响判断。
- 当 Excel 将公式应用于 A3 时，它实际应用的是 $A3（即 A3）。
- 这样，无论当前单元格在哪一列，公式都会始终检查 A 列的对应行，从而实现对整行的格式化。
六、 实际应用场景与注意事项
条件格式公式包含的技巧在各种场景下都有广泛的应用。
6.1 数据校验与预警
- 高亮显示超出预算的费用。
- 标记即将到期的合同或库存。
- 警示重复录入的数据。
6.2 数据分析与洞察
- 突出显示销售额排名前 N 的产品。
- 识别特定类型（如“退货”、“缺货”）的订单。
- 根据季度或月份自动区分数据。
6.3 提高报表的可读性
- 用颜色区分不同状态的任务。
- 用图标强调重要的数据点。
- 使复杂的表格信息一目了然。
6.4 注意事项
- 公式中的单元格引用： 确保您理解相对引用（A1）、绝对引用（$A$1）和混合引用（$A1, A$1）的区别，这对于跨列或跨行的条件格式至关重要。
- 公式返回 TRUE/FALSE： 您的公式必须能够返回 TRUE 或 FALSE。
- 性能考虑： 对于包含大量数据和复杂公式的工作表，条件格式可能会影响 Excel 的性能。尽量简化公式，并只对必要的范围应用规则。
- 规则顺序： 如果应用了多个条件格式规则，它们的顺序很重要。Excel 会按照规则的顺序应用格式，一旦某个规则满足条件并应用了格式，后续规则可能就不会再对该单元格起作用（除非您在规则管理中勾选了“停止应用如果为真”）。
- 测试： 在应用到大量数据之前，先在一个小范围内测试您的公式，确保其行为符合预期。
掌握 Excel 条件格式的公式包含技巧，能极大地提升您处理和分析数据的效率和可视化能力。通过灵活运用本文介绍的函数和方法，您可以轻松实现各种复杂的条件格式需求，让您的 Excel 工作表更智能、更直观。
