如何根据Excel单元格填充颜色赋值并计算区域平均值

原有代码问题 #

你写的VBA代码无法得到正确结果，核心问题有3个：

• 色值匹配错误：16777215是纯白色的背景色值，和你需要识别的红、琥珀、蓝三个目标颜色完全不匹配
• 计算逻辑错误：匹配到对应颜色时直接给函数返回值赋固定值1，没有做分数累加，也没有覆盖琥珀色、蓝色的判断分支
• 缺少异常处理：没有做除以0的防护，也没有跳过空单元格的逻辑，统计结果会有误差

可用实现代码 #

Excel没有内置函数可以直接识别单元格背景色做计算，用VBA自定义函数是最便捷的实现方式，直接把下方代码粘贴到VBA编辑器的标准模块中即可使用：

Option Explicit

Function AvgByColor(calcRange As Range) As Double
    Dim cell As Range
    Dim totalVal As Double
    Dim validCnt As Long
    Dim cColor As Long
    
    totalVal = 0
    validCnt = 0
    
    For Each cell In calcRange
        ' 跳过空单元格减少统计误差
        If Not IsEmpty(cell) Then
            cColor = cell.Interior.Color
            ' 按颜色匹配对应赋值，下方RGB值可根据你表格实际颜色调整
            Select Case cColor
                Case RGB(255, 0, 0) ' 红色，赋值1
                    totalVal = totalVal + 1
                    validCnt = validCnt + 1
                Case RGB(255, 192, 0) ' 琥珀色，赋值2
                    totalVal = totalVal + 2
                    validCnt = validCnt + 1
                Case RGB(0, 112, 192) ' 蓝色，赋值3
                    totalVal = totalVal + 3
                    validCnt = validCnt + 1
            End Select
        End If
    Next cell
    
    ' 避免无有效单元格时触发除以0错误
    AvgByColor = IIf(validCnt > 0, totalVal / validCnt, 0)
End Function

使用说明 #

• 函数调用方式：在需要输出结果的单元格输入=AvgByColor(统计区域)，比如要统计B2:B9区域就写=AvgByColor(B2:B9)，回车即可直接得到平均值
• 如果代码里的RGB值和你表格实际颜色不匹配，可以录制一个空宏，选中对应颜色单元格读取Interior.Color属性拿到准确色值，替换对应Case行的判断条件即可
• 如果单元格颜色是通过条件格式生成的，需要把代码里的cell.Interior.Color改成cell.DisplayFormat.Interior.Color才能正确识别颜色

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