求按Excel条件格式颜色计算单元格平均值的VBA改进方案

问题背景 #

在Excel中使用绿-黄-红颜色刻度条件格式的数据集，需要按单元格实际显示的颜色（包含不同色调的细微差异）计算平均值。现有VBA函数仅能处理手动填充颜色的单元格，无法识别条件格式生成的颜色。

原代码核心缺陷：使用Interior.ColorIndex只能获取手动设置的填充色索引，无法读取条件格式渲染的实际显示颜色。

解决思路 #

• 改用DisplayFormat.Interior.Color获取单元格实际显示的RGB颜色值，该属性可识别条件格式生成的格式效果。
• 用Color（长整型RGB值）替代ColorIndex，精准区分细微色调差异（ColorIndex是有限索引值，无法区分同色系的不同色调）。
• 修正原代码的变量拼写错误，添加除数为0的容错处理，避免运行报错。

修改后的VBA函数 #

Function AvgCellsByCondFormatColor(CellRange As Range, CellColor As Range) As Variant
    Dim TargetColor As Long
    Dim RunningSum As Double
    Dim RunningCount As Long
    Dim cell As Range
    
    ' 获取目标颜色的实际显示RGB值（支持条件格式）
    TargetColor = CellColor.DisplayFormat.Interior.Color
    
    RunningSum = 0
    RunningCount = 0
    
    ' 遍历目标区域
    For Each cell In CellRange
        ' 匹配实际显示颜色
        If cell.DisplayFormat.Interior.Color = TargetColor Then
            ' 仅处理数值型单元格
            If IsNumeric(cell.Value) Then
                RunningSum = RunningSum + cell.Value
                RunningCount = RunningCount + 1
            End If
        End If
    Next cell
    
    ' 处理无匹配单元格的情况，避免除以0错误
    If RunningCount = 0 Then
        AvgCellsByCondFormatColor = 0 ' 也可改为返回"无匹配单元格"文本
    Else
        AvgCellsByCondFormatColor = RunningSum / RunningCount
    End If
End Function

使用说明 #

1. 按Alt+F11打开VBA编辑器，插入新模块，粘贴上述代码。
2. 在Excel单元格中调用函数，例如：=AvgCellsByCondFormatColor(A1:C10, E1)，其中A1:C10是目标数据区域，E1是指定参考颜色的单元格（需为条件格式渲染后的颜色）。

内容的提问来源于stack exchange，提问作者em4019