区分空格和零的条件格式(Microsoft Excel)
假设您通常将信息从另一个程序导入Excel。该信息包含数字值,但也可以包含空格。您可能要对导入的信息使用条件格式以突出显示任何零值。问题是,如果仅添加突出显示单元格的条件格式以查看它们是否为零,那么该条件还将突出显示任何空白单元格,因为它们也包含“零”值。
对于这种困境有几种不同的解决方案。一种解决方案是应用使用两个条件的条件格式。第一个条件检查空白,第二个条件检查零值。
检查空白的条件不需要调整任何格式,但是检查零值的条件可以。之所以可行,是因为如果满足第一个条件(单元格为空),则永远不会测试第二个条件。请执行以下操作:
。选择要条件格式化的范围。 (对于此示例,我假设您已选择范围A2:A99。)
。从格式菜单中选择条件格式。 Excel将显示条件格式对话框。
。使用条件1的第一个下拉列表,选择“公式为”。
。在条件1的公式框中,输入公式= ISBLANK(A2)。
(请参见图1。)
。单击添加。 Excel将第二个条件添加到对话框。
。使用条件2的第一个下拉列表,选择“单元格值为”。
。使用条件2的第二个下拉列表,选择等于。在“条件2”的值框中,输入0。(请参见图2。)
。单击条件2的格式按钮。Excel将显示“格式单元格”对话框。
。根据需要使用对话框中的控件来修改格式。
。单击“确定”关闭“设置单元格格式”对话框。
。单击确定以关闭条件格式对话框。格式化将应用于您在步骤1中选择的单元格范围。
另一个解决方案是将两个条件合并为一个条件。请按照下列步骤操作:
。选择要条件格式化的范围。 (对于此示例,我假设您已选择范围A2:A99。)
。从格式菜单中选择条件格式。 Excel将显示条件格式对话框。
。使用条件1的第一个下拉列表,选择“公式为”。
。在条件1的公式框中,输入公式= AND(A2 = 0,A2 <>“”)。
。单击条件1的格式按钮。Excel将显示“格式单元格”对话框。
。根据需要使用对话框中的控件来修改格式。
。单击“确定”关闭“设置单元格格式”对话框。
。单击确定以关闭条件格式对话框。格式化将应用于您在步骤1中选择的单元格范围。
步骤4中使用的公式检查以确保该值为0,并且该单元格不是空白。 AND函数可确保仅当两个条件都满足时,公式才会返回True,并应用格式。
还有许多其他公式也可以使用。例如,可以在步骤5或4中替换以下每个公式:
-
= AND(COUNT(A2)= 1,A2 = 0)
-
= AND(A2 = 0,NOT(ISBLANK(A2)))
-
= AND(A2 = 0,LEN(A2)> 0)
NOT(ISBLANK(A2))(A2 = 0)
如果您想要一种更快的方法来忽略空格而突出显示零值,则可以考虑使用宏。宏会更快,因为您可以导入并运行它。您不必选择一个单元格范围,也不必输入条件格式的公式。以下宏是您可以使用的宏的示例:
Sub FormatRed() TotalRows = 65000 ColNum = 1 For i = 1 To Cells(TotalRows, ColNum).End(xlUp).Row Cells(i, ColNum).Interior.ColorIndex = xlAutomatic If IsNumeric(Cells(i, ColNum).Value) Then If Cells(i, ColNum).Value = 0 Then Cells(i, ColNum).Interior.ColorIndex = 3 End If End If Next End Sub
宏检查A列中的单元格。(它检查第1至65,000行中的单元格;如果需要,可以对其进行修改。)如果该单元格包含一个数字值并且该值为零,则该单元格将填充红色。
如果该单元格包含其他内容,则将其设置回其正常颜色。
注意:
如果您想知道如何使用此页面(或_ExcelTips_网站上的任何其他页面)中描述的宏,我准备了一个特殊页面,其中包含有用的信息。
_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。
本技巧(2980)适用于Microsoft Excel 97、2000、2002和2003。可以在以下功能区中为Excel的功能区界面(Excel 2007及更高版本)找到本技巧的版本: