Excel使用公式确定要设置格式的单元格

Excel教学 3.4K

如果在创建自己的条件格式规则时未看到所需的确切选项，则可以使用逻辑公式指定格式设置条件。例如，你可能需要将选定区域中的值与函数返回的结果进行比较，或计算所选区域之外的单元格中的数据，这些数据可位于同一工作簿中的其他工作表中。你的公式必须返回 True 或 False（1 或 0），但是你可以使用条件逻辑将一组对应的条件格式串在一起，例如，为一小组文本值（例如产品类别名称）中的每个值都使用不同的颜色。

注意: 若要在公式中输入单元格引用，只需直接在工作表或其他工作表上选中单元格即可。在工作表上选中单元格之后，将插入绝对单元格引用。如果希望 Excel 调整所选区域中每个单元格的引用，请使用相对单元格引用。

提示: 如果任何单元格包含的公式返回错误，则条件格式不会应用于这些单元格。若要解决此问题，请在公式中使用 IS 函数或 IFERROR 函数返回你指定的值（例如 0 或“N/A”），而不是错误值。

1.在“开始”选项卡上的“样式”组中，单击“条件格式”旁边的箭头，然后单击“管理规则”。

将出现“条件格式规则管理器”对话框。

2.执行下列操作之一：

若要添加条件格式，请单击“新建规则”。将出现“新建格式规则”对话框。
若要基于已列出条件格式添加新条件格式，请选择规则，然后单击“重复规则”。重复规则被复制并出现在对话框中。选择重复，然后选择“编辑规则”。将出现“编辑格式规则”对话框。
若要更改条件格式，请执行下列操作：
1. 确保在“显示其格式规则”列表框中选择了相应的工作表、表或数据透视表。
2. 也可以采用以下方式更改单元格区域：在“应用于”框中单击“折叠对话框”以临时隐藏对话框，在该工作表或其他工作表上选择新的单元格区域，然后单击“展开对话框”。
3. 选择规则，然后单击“编辑规则”。将出现“编辑格式规则”对话框。
在“规则应用于”下，根据需要按以下操作方法更改数据透视表的“值”区域中的字段范围：
- 按选定内容设置范围： 请单击“所选单元格”。
- 按相应字段设置范围： 请单击“所有显示 <值字段> 值的单元格”。
- 按值字段设置范围： 请单击“所有为 <行> 显示 <值字段> 的单元格”。
在“选择规则类型”下，单击“使用公式确定要设置格式的单元格”。
1. 在“编辑规则说明”下的“为符合此公式的值设置格式”列表框中，输入一个公式。
  
  公式必须以等号 (=) 开头且必须返回逻辑值 TRUE (1) 或 FALSE (0)。
2. 单击“格式”以显示“设置单元格格式”对话框。
3. 选择当单元格值符合条件时要应用的数字、字体、边框或填充格式，然后单击“确定”。可以选择多个格式。选择的格式将在“预览”框中显示出来。

示例 1：使用两个带有准则（使用 AND 和 OR 测试）的条件格式

以下示例显示了两个条件格式规则的使用。如果第一条规则不适用，则应用第二条规则。
第一条规则：购房者预计需要￥75,000 作为首期付款，每月￥1,500 作为按揭还款。如果预付定金和每月还款满足这些要求，则单元格 B4 和 B5 的格式设置为绿色。
第二条规则：如果首期付款或每月还款未满足购房者的预算，则 B4 和 B5 的格式设置为红色。更改某些值（例如，APR、贷款期限、预付定金和购买金额）以查看条件格式单元格将发生什么情况。

示例 2：使用 MOD 和 ROW 函数每隔一行加上底纹

应用于此工作表中的每个单元格的条件格式会为单元格区域中的每隔一行加上蓝色单元格颜色底纹。你可以通过单击第 1 行上的正方形和 A 列的左侧来选择工作表中的所有单元格。MOD 函数返回一个数（第一个参数）除以除数（第二个参数）之后的余数。 ROW 函数返回当前行编号。如果将当前行编号除以 2，那么对于偶数编号，余数始终为 0，对于奇数编号，余数始终为 1。由于 0 为 FALSE 而 1 为 TRUE，因此对每个奇数行设置格式。规则使用此公式：=MOD(ROW(),2)=1。