在日常工作中，很多人习惯用不同底色来标记数据——红色代表异常，黄色代表待处理，绿色代表已完成。但当领导突然说“把标红的数字汇总一下”时，Excel里根本没有一个现成的函数能做到。

这时候，VBA自定义函数就是最直接的解法。

为什么Excel做不到？

Excel的SUMIF、COUNTIF等函数只能基于数值或文本条件进行统计，无法识别单元格的格式属性（比如背景色、字体颜色）。改变单元格颜色并不会触发Excel重新计算。换句话说，颜色是“画”上去的，Excel的内置函数根本“看”不见它。

要绕过这个限制，最简洁的方案就是自己动手写一个自定义函数。

一、按背景色求和：SumByColor

把下面的代码复制到VBA模块中：

Function SumByColor(Ref_color As Range, Sum_range As Range) As Double
    Application.Volatile
    Dim iCol As Integer
    Dim rCell As Range
    iCol = Ref_color.Interior.ColorIndex
    For Each rCell In Sum_range
        If iCol = rCell.Interior.ColorIndex Then
            SumByColor = SumByColor + rCell.Value
        End If
    Next rCell
End Function

参数说明：

  • Ref_color：一个带目标颜色的单元格（告诉函数“我要统计这种颜色”）
  • Sum_range：需要进行求和计算的数据区域

使用方法：
假设A1单元格是红色背景，要求A1:B10区域中所有红色背景单元格的数值之和：

=SumByColor(A1, A1:B10)

二、按背景色计数：CountByColor

同样的逻辑，统计个数：

Function CountByColor(Ref_color As Range, Count_range As Range) As Long
    Application.Volatile
    Dim iCol As Integer
    Dim rCell As Range
    iCol = Ref_color.Interior.ColorIndex
    For Each rCell In Count_range
        If iCol = rCell.Interior.ColorIndex Then
            CountByColor = CountByColor + 1
        End If
    Next rCell
End Function

使用方法：
统计A1:B10区域中和A1颜色相同的单元格个数：

=CountByColor(A1, A1:B10)

三、怎么把代码装进去？

三步搞定：

  1. 打开VBA编辑器：按 Alt + F11
  2. 插入模块：点击菜单 插入 → 模块
  3. 粘贴代码：把上面的代码粘贴到右侧的代码窗口中，关闭VBA编辑器即可

保存文件时记得选 .xlsm（启用宏的工作簿） 格式，否则自定义函数会丢失。

四、三个常见问题

1. 函数不更新怎么办？

手动修改单元格颜色不会触发Excel自动重算-。两种解法：

  • 按 F9 手动强制重算-
  • 在函数中加入 Application.Volatile（上面的代码已经包含了），让函数在工作簿任何计算发生时自动重算

2. 条件格式产生的颜色能识别吗？

不能。 上面的 Interior.ColorIndex 只识别手动填充的背景色，不识别条件格式动态生成的顏色。

如果必须统计条件格式产生的颜色，需要改用 DisplayFormat.Interior.Color 属性-10。但这个方案有性能代价，不推荐在大范围数据上使用。

3. 在其他工作簿能用吗？

自定义函数只存在于创建它的那个工作簿中--。其他工作簿要用，要么把代码复制过去，要么把包含函数的工作簿另存为 加载宏（.xla） 。

五、一个不用VBA的替代方案

如果不想碰VBA，还有一个“曲线救国”的办法：

在名称管理器中新建一个名称（比如 CellColor），引用位置输入：

=GET.CELL(63, OFFSET(Sheet1!$A$1, ROW()-1, COLUMN()-1, 1, 1))

这个宏表函数可以提取单元格的背景色编号

在数据旁边新建辅助列，输入 =CellColor 获取每个单元格的颜色编号

用 SUMIF 或 COUNTIF 按颜色编号进行统计

缺点是需要辅助列，且 GET.CELL 是宏表函数，每次修改颜色后需要按 F9 刷新。

按颜色求和计数的需求高频出现，但Excel官方一直没给出现成的解决方案。两个自定义函数——SumByColor 和 CountByColor——就是最直接、最省事的答案。

代码不过十几行，花五分钟配置好，以后遇到“把标红的数字汇总一下”这种需求，输入公式就能秒出结果。