第3部分 条件格式及数据突显方式

Excel制表中有些数据会基于某些特殊原因需要突显出来，加以区别。这种需求一般都通过条件格式满足。但除了条件格式这种易操作且可即时预览的方式外，也可以通过其他一些技巧完成。本部分就数据突显需求而进行多元化探讨，通过13个实例讲解其中存在的一些技巧。

本部分主要知识点：

● 条件格式及其他数据突显方式

● 管理条件格式规则

第14章 条件格式及其他数据突显方式

Excel每一个版本都可以通过条件格式实现数据突显，不过Excel 2007以前的版本中条件格式的条件上限是三个，在一定条件下限制了它的运用，难以满足用户在特殊状况下的需求。Excel 2007中没有上限，仅受内存限制。也就是说，只要系统内存足够大，可以使用几十、几百个条件对数据进行管理。

● 实例105利用字体颜色突出显示当前行

● 实例106利用特殊字体突显男生成绩

● 实例107灰色底纹显示优异成绩之成员

● 实例108对区域中最大值添加下划线

● 实例109对区域中超过平均值之数据加粗倾斜

● 实例110用彩色条标示区域值的大小

● 实例111用三色交通灯图标标示成绩

● 实例112将重复值加上虚框

● 实例113圈释目标

实例105 利用字体颜色突出显示当前行

【技巧说明】 用黄色字体突出显示当前光标所在行。

【案例介绍】 在进行多行多列的大型工作簿的数据输入且当前单元格在屏幕右边时，往往在视觉上难以对齐左侧的行标题。对当前行加以明显的字体颜色区别则有助于快速录入数据。

【案例实现】 参见以下步骤：

[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。

[2] 单击菜单【插入】\【模块】，打开模块代码窗口。

[3] 在右边代码窗口输入以下代码：

Sub添加条件格式()
    Dim rng As Range
    Set rng=Rows("1:" & Range("a1").End(xlUp).Row)
    With rng
      .FormatConditions.Delete  '如果已有条件格式，将之删除
      .FormatConditions.Add Type:=xlExpression,
            Formula1:="=ROW()=CELL(""row"")"  '添加公式
      .FormatConditions(rng.FormatConditions.Count).SetFirstPriority
            '将此条件格式规则的优先级值设置为“1”
      With .FormatConditions(1).Font
          .ThemeColor=xlThemeColorAccent6   '指定要使用的主题颜色
          .TintAndShade=0.1'条件格式规则单元格的填充颜色变浅或变深的设置参数
      End With
    End With
End Sub

[4] 关闭VBE窗口返回到工作表。

[5] 用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮。

[6] 在单元格I3中输入数据100并按Enter键，可以看到当前单元格所在行的数据已全部以不同的颜色区别开来。

图3.1 以不同颜色突显当前行

提示

本实例参见光盘样本：..\第3部分\实例105.xlsm。

【相关知识说明】

（1）FormatConditions：即条件格式，可以通过ADD方法对区域添加条件格式；使用Modify方法可更改现有的条件格式。

（2）SetFirstPriority：将当前条件的优先级提升为1，当然在区域中同时存在多个条件格式时使用。

（3）实现本例效果也可以不使用条件格式，可以用以下代码，但如果用户的工作表中本身已有多种颜色标示数据的话，它将消除黑色以外的颜色；否则效果等于实例中的代码。

Private Sub Worksheet_Change(ByVal Target As Range)
Cells.Font.ColorIndex=xlAutomatic
Cells.Font.TintAndShade=0
    With Target.EntireRow.Font
      .ThemeColor=xlThemeColorAccent6
      .TintAndShade=-0.249977111117893
    End With
End Sub

实例106 利用特殊字体突显男生成绩

【技巧说明】 利用Arial Black字体显示表中男生的成绩。

【案例介绍】 学生成绩表如图3.2所示，现需要突出显示男生成绩。

【案例实现】 参见以下步骤：

[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。

[2] 使用快捷键Ctrl+R，显示工程资源管理器。

[3] 双击左边列表中的“成绩表”，打开工作表代码窗口，并输入以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Application.ScreenUpdating=False
    ActiveSheet.UsedRange.Font.Name="宋体"
    For Each rng In Application.Intersect(Range("b:b"),
        ActiveSheet.UsedRange.Offset(2, 0))
    If rng="男" Then
    rng.Resize(1, 5).Font.Name="Arial Black"
    End If
    Next
    Application.ScreenUpdating=True
End Sub

[4] 关闭VBE窗口返回到工作表。

[5] 将“田伯光”的性别改为“女”，已用区域中男生成绩全部以Arial Black字体显示，如图3.3所示。再将“田伯光”性别改为“男”，则其成绩也将以Arial Black字体显示。

图3.2 学生成绩表

图3.3 突显男生成绩

提示

本实例参见光盘样本：..\第3部分\实例106.xlsm。

【相关知识说明】

鉴于不同字体将使字符进行相应的缩放，影响行高，Excel的条件格式不允许修改字符的字体，故本例未使用条件格式，但达到了条件格式的同等功能。

实例107 灰色底纹显示优异成绩之成员

【技巧说明】 将优异成绩之学生姓名单元格显示为灰色底纹。

【案例介绍】 学生成绩表如图3.4所示，现需要将成绩为“优”的学生姓名以灰色底纹显示。

【案例实现】 参见以下步骤：

[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。

[2] 使用快捷键Ctrl+R，显示工程资源管理器。

[3] 双击左边列表中的“成绩表”，打开工作表代码窗口，并输入以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    For Each rng In Application.Intersect(Range("f:f"),
        ActiveSheet.UsedRange.Offset(2, 0))
    If rng="优" Then
    With rng.Offset(0,-5).Interior
      .ThemeColor=xlThemeColorDark1
      .TintAndShade=-0.149998474074526
      .PatternTintAndShade=0
    End With
    End If
    Next
End Sub

[4] 在数据后面添加一行数据，输入数据后，可以发现评语为“优”者姓名已加上灰色底纹，如图3.5所示。

图3.4 学生成绩表

图3.5 突显评语为“优”的学生姓名

提示

本实例参见光盘样本：..\第3部分\实例107.xlsm。

实例108 对区域中最大值添加下划线

【技巧说明】 对成绩表中最高成绩者添加下划线。

【案例介绍】 如图3.6所示，现需要将成绩表中最高成绩者的姓名加上下划线。

【案例实现】 参见以下步骤：

[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。

[2] 单击菜单【插入】\【模块】，打开模块代码窗口。

[3] 在右边代码窗口输入以下代码：

Sub添加条件格式()
    Cells.FormatConditions.Delete
    Dim rng As Range
    Set rng=Application.Intersect(Range("a:a"),
        ActiveSheet.UsedRange.Offset(2, 0), ActiveSheet.UsedRange)
    With rng
      .Select
      .FormatConditions.Add Type:=xlExpression, Formula1:=_
      "=OFFSET(A3,,4)=MAX($E:$E)"
      .FormatConditions(.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1).Font
          .Underline=xlUnderlineStyleSingle
          .TintAndShade=0
      End With
      .FormatConditions(1).StopIfTrue=False
    End With
End Sub

[4] 关闭VBE窗口返回到工作表。

[5] 使用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，可以看到最高分者“田伯光”姓名下已添加下划线，如图3.7所示。

提示

本实例参见光盘样本：..\第3部分\实例108.xlsm。

图3.6 学生成绩表

图3.7 对最高分者加下划线

【相关知识说明】

（1）=OFFSET(A3,,4)=MAX($E:$E)：A列条件格式的公式，表示若当前单元格偏移4列的单元格等于E列最大值时，则实施条件格式中添加的条件。

（2）本例中添加条件格式的区域是A3∶A11，但为了使程序通用，在区域后面添加数据后本程序仍然不需要修改即可使用，故使用动态区域引用。Intersect(Range("a:a"), ActiveSheet.UsedRange.Offset(2, 0), ActiveSheet.UsedRange)表示A列前两行以外的所有已用区域，此区域可以根据数据增减而变化。

实例109 对区域中超过平均值之数据加粗倾斜

【技巧说明】 对区域中超过平均值之数据加粗倾斜。

【案例介绍】 以实例108数据为例，现需要将成绩表中超过平均成绩的分数加粗倾斜显示。

【案例实现】 参见以下步骤：

[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。

[2] 单击菜单【插入】\【模块】，打开模块代码窗口。

[3] 在右边代码窗口输入以下代码：

Sub对区域中超过平均值之数据加粗倾斜()
    Range("E3:E" & [e1048576].End(xlUp).Row).Select
    With Selection
      .FormatConditions.AddAboveAverage             '添加条件格式
      .FormatConditions(1).AboveBelow=xlAboveAverage '类型
      With .FormatConditions(1).Font               '字体加粗/倾斜
          .Bold=True
          .Italic=True
      End With
    End With
End Sub

[4] 关闭VBE窗口返回到工作表。

[5] 使用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，可以看到高于平均分的分数都已经加粗倾斜显示，如图3.8所示。

图3.8 高于平均分的分数加粗倾斜显示

提示

本实例参见光盘样本：..\第3部分\实例109.xlsm。

实例110 用彩色条标示区域值的大小

【技巧说明】 用彩色条标示区域值的大小。

【案例介绍】 以实例108数据为例，以彩色条标示平均分。

【案例实现】 参见以下步骤：

[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。

[2] 单击菜单【插入】\【模块】，打开模块代码窗口。

[3] 在右边代码窗口输入以下代码：

Sub添加图标集条件格式()
    Range("E3:E" & [e1048576].End(xlUp).Row).Select
    With Selection
      .FormatConditions.AddDatabar  '添加图标集条件格式规则
      .FormatConditions(.FormatConditions.Count).SetFirstPriority
                                      '设定优先级别
      .FormatConditions(1).BarColor.ColorIndex=5  '指定颜色值
    End With
End Sub

[4] 关闭VBE窗口返回到工作表。

[5] 使用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，可以看到E列数据已用彩色图标显示，如图3.9所示。

图3.9 彩色条标示的平均分

提示

本实例参见光盘样本：..\第3部分\实例110.xlsm。

【相关知识说明】

AddDatabar：返回一个代表指定区域数据条件格式规则的Databar对象。

实例111 用三色交通灯图标标示成绩

【技巧说明】 用三色交通灯（绿色/黄色/红色）图标标示成绩。

【案例介绍】 学生成绩表如图3.10所示。成绩一般以小于60分为差，大于90分为优。本实例以三色中绿色对90分以上者表示褒奖，用黄色表示激励，红色表示警告。

【案例实现】 参见以下步骤：

[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。

[2] 单击菜单【插入】\【模块】，打开模块代码窗口。

[3] 在右边代码窗口输入以下代码：

Sub图标条件格式()
    Range("C3:C" & [C1048576].End(xlUp).Row).Select  '对C列设置条件格式
    With Selection
      .FormatConditions.Delete               '删除以前的条件格式
      .FormatConditions.AddIconSetCondition  '添加条件格式
      .FormatConditions(.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1)
        .IconSet=ActiveWorkbook.IconSets(xl3TrafficLights1) '图标样式
      End With
      With .FormatConditions(1).IconCriteria(2)
          .Type=xlConditionValuePercent      '使用百分比
          .Value=60
          .Operator=7          '指定条件格式的操作符，位置7表示大于且等于
      End With
      With .FormatConditions(1).IconCriteria(3)
          .Type=xlConditionValuePercent
          .Value=90
          .Operator=7
      End With
    End With
End Sub

[4] 关闭VBE窗口返回到工作表。

[5] 使用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，工作表中将产生数值及颜色，如图3.11所示。

图3.10 学生成绩表

图3.11 对成绩添加三色交通灯图标

提示

本实例参见光盘样本：..\第3部分\实例111.xlsm。

【相关知识说明】

（1）ActiveWorkbook.IconSets：此属性用于基于IconSet集合中的单元格图标对工作簿中的数据进行筛选，可选项见表3.1。

表3.1 条件格式可用图标集

（2）IconSetCondition.IconCriteria：返回一个IconCriteria集合，该集合代表图标集条件格式规则的一组条件。本实例中使用xlConditionValuePercent类型，即百分比。其可选项目见表3.2。

表3.2 可使用的条件值类型列表

（3）Operator：代表条件格式的操作符，本例中值为7表示大于或等于。具体值与属性名称的对应关系见表3.3。

表3.3 具体值与属性名称的对应关系

实例112 将重复值加上虚框

【技巧说明】 将重复值加上虚框。

【案例实现】 参见以下步骤：

【案例介绍】 如图3.12所示之学生成绩表，现需对平均成绩相同者加虚框显示。

[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。

[2] 单击菜单【插入】\【模块】，打开模块代码窗口。

[3] 在右边代码窗口输入以下代码：

Sub将重复值加上虚框()
    Range("E3:E" & [E1048576].End(xlUp).Row).Select
    With Selection
      .FormatConditions.AddUniqueValues
      .FormatConditions(1).DupeUnique=xlDuplicate
      .FormatConditions(1).Borders.LineStyle=xlDot
    End With
End Sub

[4] 关闭VBE窗口返回到工作表。

[5] 使用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，平均成绩相同者都已将成绩加虚框显示，如图3.13所示。

图3.12 学生成绩表

图3.13 虚框显示相同平均分

提示

本实例参见光盘样本：..\第3部分\实例112.xlsm。

【相关知识说明】

（1）DupeUnique=xlDuplicate：表示对重复值添加条件格式。

（2）LineStyle：此处表示单元格边框的线型，xlDot为虚线型。

实例113 圈释目标

【技巧说明】 对姓“罗”的学生加上圆圈标示。

【案例实现】 参见以下步骤：

【案例介绍】 如图3.14所示之学生成绩表，现需突出显示姓“罗”的学生。

[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。

[2] 单击菜单【插入】\【模块】，打开模块代码窗口。

[3] 在右边代码窗口输入以下代码：

Sub查找罗并标示红圈2() '效率更高
  Dim Cell As Range
  For Each Cell In Range("A1:H50")
    If Cell Like "罗*" Then
  With Worksheets(1).Ovals.Add(Cell.Left,Cell.Top,Cell.Width,Cell.Height)
      .Interior.Pattern=xlNone
      .Border.ColorIndex=3
      End With
      End If
  Next
End Sub

[4] 关闭VBE窗口返回到工作表。

[5] 使用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，姓“罗”的学生姓名都已加红圈显示。如图3.15所示。

图3.14 学生成绩表

图3.15 对姓罗的学生加红圈显示

提示

本实例参见光盘样本：..\第3部分\实例113.xlsm。

【相关知识说明】

（1）Ovals：表示椭圆图形，可以用ADD方法生成新图形。新增图形时有4个参数，分别表示它的左边距、上边距及宽度、高度。

（2）删除所生成的红圈可以用前面介绍的方法删除，也可以用快捷键F5调出定位功能，选择对象，然后按“Delete”键一次全部删除。

（3）本例方法是通过在单元格画圈实现目的。还有另一种方法也可以实现，即使用数据有效性。为拓展思路，再给一解，利用数据有效性完成本例之效果：

Sub圈释目标() '数据有效性方法
    With Range("a3:A" & Range("A1048576").End(xlUp).Row).Validation
    .Delete     '删除数据有效性
    .Add Type:=xlValidateCustom,AlertStyle:=xlValidAlertStop,Operator:=_
      xlBetween, Formula1:="=left(A3,1)<>""罗"""     '添加数据有效性
      .IgnoreBlank=True      '允许空值
      .InCellDropdown=True    '显示含有有效取值的下拉列表
    End With
    ActiveSheet.CircleInvalid  '圈释无效数据
End Sub
上一章目录下一章