Excel VBA按字体颜色求和时如何识别并排除区域内小计单元格

小计单元格识别逻辑 #

采用通用业务表格里的小计单元格共性特征做判定，满足任意一条即判定为小计单元格，遍历求和时直接跳过：

• 求和单元格左侧相邻的标签单元格包含「小计」「合计」「subtotal」「total」类汇总关键词
• 单元格本身输入了SUM/SUBTOTAL类汇总公式，而非静态业务数值
• 可根据自身表格格式扩展判定规则，比如识别加粗字体、专属填充色等自定义小计标识

修改后可直接使用的VBA代码 #

Public Function SumByColor(pRange1 As Range, pRange2 As Range) As Double
' 支持排除小计单元格的按字体颜色求和
Application.Volatile
Dim rng As Range
Dim xTotal As Double
Dim isSkip As Boolean
xTotal = 0

For Each rng In pRange1
    isSkip = False
    ' 规则1：左侧单元格含汇总关键词则跳过
    If rng.Column > pRange1.Column Then
        leftText = Trim(LCase(rng.Offset(0, -1).Text))
        If InStr(leftText, "小计") > 0 Or InStr(leftText, "合计") > 0 _
            Or InStr(leftText, "subtotal") > 0 Or InStr(leftText, "total") > 0 Then
            isSkip = True
        End If
    End If
    ' 规则2：单元格本身为汇总公式则跳过
    If rng.HasFormula Then
        fText = LCase(Replace(rng.Formula, " ", ""))
        If Left(fText, 10) = "=subtotal(" Or Left(fText, 5) = "=sum(" Then
            isSkip = True
        End If
    End If
    ' 非跳过项匹配字体颜色后累加
    If Not isSkip And IsNumeric(rng.Value) Then
        If rng.Font.Color = pRange2.Font.Color Then
            xTotal = xTotal + rng.Value
        End If
    End If
Next
SumByColor = xTotal
End Function

自定义调整说明 #

• 函数参数和原函数完全一致：第一个参数为要求和的目标数值区域，第二个参数为带指定目标字体颜色的参照单元格
• 如果表格汇总标签在数值单元格上方，把代码里的rng.Offset(0, -1)修改为rng.Offset(-1, 0)即可
• 如果小计单元格统一设置了字体加粗格式，可以在判定规则部分新增If rng.Font.Bold Then isSkip = True
• 如果有自定义的汇总关键词，直接在关键词判断的代码行追加对应的InStr判断条件即可
• 代码新增了数值类型校验，避免求和区域内存在文本内容时触发计算错误

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