就在上个月一个做运营的朋友跟我抱怨说她在表格里用红黄绿三种颜色标记了一批客户状态忙活了一下午最后想按颜色统计一下到底红色几家、绿色几家结果在Excel里翻遍了函数列表愣是没找到一个能直接“按颜色数数”的函数。她问我是不是我用的Excel版本不对其实不是版本的问题而是很多人在用“填充颜色”做数据标记的时候都默认以为表格软件一定能“看懂”颜色。但现实很残酷——Excel原生函数压根不认颜色。这就要说到今天要聊的color系列函数了。准确地说Excel并没有一个叫Color的函数而是围绕颜色统计有一整套“曲线救国”的方案包括Ribbon界面里的“按颜色排序筛选”、旧版宏表函数GET.CELL以及VBA下的Interior.Color和Font.Color系列。这套东西的应用场景比你想的要广考勤表里用颜色标出勤状态、项目看板里用颜色区分任务进度、销售报表里用颜色圈出重点客户这些都涉及“填充颜色统计”和“字体颜色统计”。这篇内容我从实际使用角度出发把color系列函数的三种主流实现方式、完整操作步骤、常见坑以及几个真实场景的实例全部分享出来。不管你是不想写代码的白板用户还是愿意用VBA提高效率的进阶玩家都能找到直接能用的方案。接下来进入正题。1. 为什么Excel没有原生“按颜色统计”函数1.1 被颜色标记的数据是Excel的“盲区”很多人第一次在Excel里搜索“颜色统计”相关函数时会发现一个尴尬的事实官方函数库里根本没有按颜色统计的函数。SumIf、CountIf虽然强大但它们的条件参数只能匹配数值、文本、日期不能匹配“单元格颜色”。换句话说Excel把颜色当成“格式属性”而不是“数据内容”。你在单元格里填了红色背景但对函数来说这个红色根本不参与计算。这就导致一个典型痛点数据在视觉上已经分好类了但计算机层面“看不到”分类。你手动数吧几百行数据眼睛都要瞎了用筛选吧如果标记不规范还得一个个勾颜色。更麻烦的是如果你把这份表发给别人对方换个皮肤或主题颜色可能全变了统计结果跟着乱套。1.2 三条主流技术路线宏表函数、VBA、条件格式辅助既然原生函数不行就得靠“旁门左道”。目前业内常用的是三条路线方案实现方式是否需要代码适用人群局限宏表函数GET.CELL定义名称调用旧版Excel 4.0宏函数不用代码但要用名称管理器不想写VBA的普通用户不能自动刷新需要启用宏自定义颜色识别不稳定VBA自定义函数编写Function过程读取Interior.Color或Font.Color需要写VBA代码进阶用户、需要灵活统计的人需要另存为xlsm首次信任设置麻烦条件格式辅助列在格式规则中通过公式生成标记值不用代码场景简单、愿意改变习惯只适用于事前规划事后补救困难我自己在实际项目里会根据“数据是不是已经定死了”来决定方案。如果表格已经用颜色标记完了我基本直接上VBA如果只是临时想看一眼数字宏表函数GET.CELL够用如果这张表从零开始搭建我更推荐从一开始就加一个辅助列把“颜色代表什么”用文字写进单元格里这样后期不管是统计还是筛选都方便。不要神化颜色标记它应该是锦上添花而不是唯一的数据入口。2. GET.CELL宏表函数不写VBA也能统计颜色2.1 核心机制借用“老古董”宏表函数识别颜色GET.CELL是Excel 4.0时期就存在的宏表函数微软早就把它从公式提示列表里移除了但底层仍然支持。它的作用是获取指定单元格的格式信息包括填充色、字体色、边框样式等。函数的语法是GET.CELL(类型号, 单元格)其中比较常用的类型号包括63返回单元格的填充背景色编号24返回单元格的字体颜色编号1返回单元格的字体名称2返回字体大小关于 GET.CELL(63) 返回的“颜色编号”我这里多说一句。它返回的不是你在调色板里看到的RGB数值比如红色是255那种而是当前工作簿调色板里的一个索引值。什么意思呢如果你用的是Excel默认调色板红色背景可能返回3黄色可能返回6但这些数字在不同环境下可能不同。所以用GET.CELL判断颜色时通常不是直接跟“红色”比较而是先让公式识别一次目标色块再用识别结果去匹配其他单元格。2.2 定义名称与相对引用的关键细节宏表函数不能像普通函数一样直接写在单元格里必须通过“定义名称”来间接使用。步骤如下选中工作表中的任意一个单元格比如在A1单元格旁边放一个涂好目标颜色的参考色块假设在B1涂红色。切换到“公式”选项卡点击“定义名称”。名称填“GetFillColor”自己起随便引用位置输入GET.CELL(63,Sheet1!A1)注意这里有个关键细节引用位置中的 Sheet1!A1 必须写成相对引用不能加美元符号。因为定义名称后我们在C1单元格输入 GetFillColor 时公式会获取当前单元格左侧相邻单元格A1的填充色在C2输入时它会自动获取A2的颜色。这就是相对引用的作用。确定后在C1输入 GetFillColor向下拖动填充就看到一串数字——这就是每个单元格的填充颜色索引值。同理要识别字体颜色就再定义一个名称GetFontColor引用位置写GET.CELL(24,Sheet1!A1)补充一个坑定义名称之前一定要选好“活动单元格”。所谓活动单元格就是你当前选中的那个格子一般是B1或A1。因为相对引用是相对活动单元格来偏移的如果活动单元格选得不对公式偏移的方向就会乱套。最稳妥的做法是选中A1再打开定义名称对话框引用写成GET.CELL(63,A1)这样公式就表示“获取左侧0列0行的单元格颜色”也就是A1本身。大家在实际操作时先单击选中要统计区域左上角那个单元格再去定义名称。2.3 用辅助列完成颜色求和与颜色计数拿到颜色索引号之后统计就变成普通函数的活路了。比如统计A1:A20中红色背景的单元格数量COUNTIF(C1:C20, GetFillColor)其中 GetFillColor 是你先在某个参考单元格比如B1输入GetFillColor得到的红色索引值。注意GET.CELL63如果单元格没有填充色返回0。如果单元格是“无填充”但它套用了表格样式或主题颜色返回值也可能不是0这点后面在常见问题里细说。要按颜色求和比如计算红色背景单元格对应在B列中的数值总和SUMIF(A1:A20, GetFillColor, B1:B20)这里A列是填充色识别列B列是数值列。逻辑就是用COUNTIF或SUMIF把颜色索引当作匹配条件。只要颜色索引列生成正确后面想怎么统计都行。2.4 宏表函数的刷新机制与保存注意事项宏表函数有一个区别普通函数的大毛病修改颜色后公式结果不会立刻更新。比如你把一个单元格从红色改成绿色旁边的 GetFillColor 可能还停留在原来的红色索引值上。这时候需要手动强制重算快捷键是 CtrlAltF9。这是我使用宏表函数时踩过最深的坑——统计了半天发现结果跟看到的颜色对不上就是因为忘记刷新。另外工作簿只要用了宏表函数就相当于含宏文件。保存格式要是xlsx在某些旧版本Excel里可能直接报错或提示兼容性问题在较新版本中xlsx仍可正常保存但为了保险起见建议另存为xlsm或xls格式。分享给别人之前也要提醒对方启用宏否则公式可能一打开就失效。3. VBA自定义color系列函数灵活统计的进阶方案3.1 用Interior.Color读取填充色写出第一个按颜色计数函数宏表函数用着不用写代码但灵活性有限碰到自定义颜色、条件格式、大批量数据时容易翻车。这个时候VBA方案就显示出优势了。VBA里和颜色相关的属性主要是两个Interior.Color填充色和Font.Color字体色。这两个属性返回的都是RGB长整型数值比如红色是255黄色是65535。因为是RGB值所以精确度比GET.CELL的索引值要高自定义颜色也能准确匹配。打开VBA编辑器的方法按 AltF11在左侧工程窗口右键选中VBAProject插入“模块”。在模块里写下Function ColorCount(rng As Range, colorCell As Range) As Long Dim cell As Range Dim targetColor As Long Application.Volatile targetColor colorCell.Interior.Color For Each cell In rng If cell.Interior.Color targetColor Then ColorCount ColorCount 1 End If Next cell End Function这段函数的意思是你给它两个参数一个是待统计区域rng一个是涂了目标颜色的参考单元格colorCell它会把区域内和参考单元格颜色一致的数量返回出来。在单元格里用起来很简单ColorCount(A1:A20, B1)B1就是你涂的那个红色参考块。这个函数的优势在于颜色值以RGB形式存储在变量里不管你是主题色、自定义色还是从别的程序复制过来的颜色只要RGB值一致就能匹配上。3.2 按字体颜色统计与求和函数写法字体颜色统计的思路和填充色一样只是把Interior.Color替换成Font.Color。下面给出一个同时支持“填充色”和“字体色”两种统计模式的函数这样你一个函数就能应对不同的标记习惯Function SumByColor(rngData As Range, rngColor As Range, Optional ByVal useFont As Boolean False) As Double Dim cell As Range Dim targetColor As Long Application.Volatile If useFont Then targetColor rngColor.Font.Color Else targetColor rngColor.Interior.Color End If For Each cell In rngData If useFont Then If cell.Font.Color targetColor Then SumByColor SumByColor cell.Value End If Else If cell.Interior.Color targetColor Then SumByColor SumByColor cell.Value End If End If Next cell End Function使用方式SumByColor(B2:B20, E2) // 按填充色求和 SumByColor(B2:B20, E2, TRUE) // 按字体颜色求和这里把第三个参数命名为 useFont默认值为False这样你在输入公式时不写第三个参数就是按填充色统计写TRUE就是按字体颜色统计。我在实际工作中经常遇到一种情况有人不涂填充色而是用红色字体标记异常用蓝色字体标记正常。这种标记方式用上面这个函数就太合适了。3.3 多条件组合颜色数值范围/文本关键词只判断颜色有时候不够。比如你想统计“红色填充且销售额大于10000的单元格数量”或者“橙色字体且产品名包含‘A型号’的条目数”单纯一个颜色函数就没法满足。但这种场景也很常见我的处理办法是给VBA函数增加条件参数。Function CountByColorCondition(rngColor As Range, colorCell As Range, _ rngCheck As Range, checkType As String, checkValue As Variant) As Long Dim cell As Range Dim targetColor As Long Dim i As Long Application.Volatile targetColor colorCell.Interior.Color For i 1 To rngColor.Cells.Count If rngColor.Cells(i).Interior.Color targetColor Then Select Case checkType Case If rngCheck.Cells(i).Value checkValue Then CountByColorCondition CountByColorCondition 1 Case If rngCheck.Cells(i).Value checkValue Then CountByColorCondition CountByColorCondition 1 Case If rngCheck.Cells(i).Value checkValue Then CountByColorCondition CountByColorCondition 1 Case 包含 If InStr(rngCheck.Cells(i).Value, checkValue) 0 Then CountByColorCondition CountByColorCondition 1 End Select End If Next i End Function用法举例如果A列背景色是红色C列是销售额想统计“红色且销售额大于10000”的条数CountByColorCondition(A1:A20, E2, C1:C20, , 10000)这个函数把颜色判断和数值判断分开在两个区域上两个区域要求一一对应所以写公式前务必要确保rngColor和rngCheck区域大小一致。如果区域错位统计结果会完全错了。刚开始写这类自定义函数时我自己就犯过区域对不齐的错最后检查了半天才发现是B列和C列的范围没对齐低级错误却最坑人。3.4 自定义函数不能自动重算用Application.Volatile解决VBA自定义函数有个怪脾气在单元格里引用了颜色区域后你修改背景色函数结果常常不更新。这就好像Excel察觉不到“颜色变了”这件事。我在前面写的代码里都加了Application.Volatile这行它的作用是告诉Excel“这种函数需要频繁更新”让Excel在重新计算时顺带刷新它。加上这行后只要按F9重算函数就会重新读取颜色值。但还是有个细节要提醒Application.Volatile只保证在Excel“重算”时更新函数不能用它让函数监控颜色变化事件。所以如果图表或公式不刷新始终记着一招——CtrlAltF9强制全量重算。VBA没有留下直接监听颜色变化的完美事件机制这是所有颜色统计方案的通病明确了这一点很多问题就能理解了。4. 实例应用讲解考勤表、任务看板与销售报表4.1 实例一考勤表按颜色统计出勤状态先看一个最普遍的考勤场景。某个团队用颜色标记考勤绿色代表满勤橙色代表迟到黄色代表事假红色代表旷工灰色代表休息。每天一行姓名在A列日期从B列开始往后排。表格做完之后月底要统计每个人“绿色几个月”“迟到几次”这时候手工数眼睛肯定看瞎。如果只有这一张表最快的方法是用宏表函数GET.CELL。新建一个辅助工作表先把表头复制过去然后在B2单元格中定义名称用GET.CELL(63,A2)的偏移方式生成一行行的颜色识别码。接着用COUNTIF统计每行中绿色出现的次数。不过说实话考勤表这种“有规律”的数据结构我更推荐在第一天就设定好规则把考勤状态做成下拉列表再用条件格式把列表里的文字映射成颜色。这样颜色只是可视化的外衣真正统计时数的是状态文本用COUNTIF直接算。比如“满勤”出现了几次就是COUNTIF(B2:AF2,满勤)。颜色统计不是不能用而是这种场景有更好的常规做法。只有当别人已经发给你一张“只有颜色、没有标记文字”的考勤表时才值得动用color系列函数。4.2 实例二项目任务看板统计完成进度项目看板是另一个很典型的应用场景。看板上每一行是一个任务状态用填充颜色区分灰色是未开始黄色是进行中绿色是已完成红色是延期。旁边还有一列预计工时。领导过来问一句“这周完成了多少工时”如果任务表里只有颜色标记没有状态列那就得用VBA的ColorSum来统计绿色行的工时合计。我在某公司的项目复盘里就是这么干的。团队用共享表格维护任务进度颜色标得明明白白但从没人想着加“状态”列。到月底要做项目周报才想到要按颜色汇总工时。下面这个公式帮了大忙ColorSum(D2:D100, F1)D列是工时F1是涂了绿色的参考单元格。函数遍历D2:D100每行看的是同行的A列颜色只要A列填充色和F1一致就把D列的数值累加。这个逻辑的关键点在于颜色判断区和求和区行号必须对齐。实际写代码时我建议把VBA函数的判断区设计成“与求和区传入”行号一致的两列如CountByColor中rngColor和rngData的行号保持一致否则求和结果会串行。项目看板场景里颜色与状态共存是最佳实践。我见过一些优秀的看板模板直接在状态列预留“隐藏文本”文本值用条件格式映射到颜色统计时直接用COUNTIFS汇总。颜色只是给人看的文本是给函数看的。这个思路希望你可以用到自己的项目里。4.3 实例三销售报表标记重点客户与异常数据销售报表里经常出现一个需求把核心客户用红色填充突出显示把风险客户用橙色填充显示其他普通客户不填颜色。然后月底要求“红色客户一共有几个”“橙色客户贡献了多少销售额”。这类业务的难点在于销售数据往往从系统中导出颜色是事后手工加的所以它天然适合VBA方案。我在处理某销售部门报表时写了一个组合的统计脚本一次性输出四种结果红色客户数量橙色客户数量红色客户销售额合计橙色客户销售额合计脚本本质就是在工作表变化时自动更新汇总单元格但我没有用变化事件而是直接在汇总区写了四个自定义函数调用。这里给一个简化版示例Function CountColor(rng As Range, rgbValue As Long) As Long Dim cell As Range Dim cnt As Long For Each cell In rng If cell.Interior.Color rgbValue Then cnt cnt 1 Next cell CountColor cnt End Function注意这个函数的第二个参数不是颜色单元格而是RGB数值。调用时你不能写“红色”要写255。这在实际操作中不太直观所以我更常用“参考单元格”方式而不是直接写RGB值。你可以在报表旁边专门放一行色块区域每个色块填一种颜色函数引用色块即可。这样只要维护色块公式不用改还方便别人看明白你的统计逻辑。4.4 实例四文本表格里的字体颜色统计填充色统计做好之后字体颜色的应用场景一样躲不开。最典型的就是评审表评审人把有问题的项目用红色字体标注没问题的用黑色字体最后的评审结论要数“红色标注出了多少个问题”。字体颜色统计用VBA也很顺手。关键是判断条件从Interior.Color改成Font.Color。前面给的SumByColor函数已经兼顾了这个场景。实际用的时候建议把“标识字体红色”和“标识单元格填充黄色”这两类情况都做成规范这样统计时一个函数通吃。我总结半个经验很多团队里大家标颜色都靠手感和自觉有人用红色字体有人用红色填充还有人用红色边框统计之前必须先把新口径统一。VBA能统计的只是“你用什么颜色、标在哪个位置”它读不懂你的心思。拿到的表格如果标记方式五花八门再好的函数也白搭。5. 常见问题与排查技巧实录5.1 宏表函数结果全部为0或没有变化用GET.CELL统计时遇到最多的问题就是公式结果全是0。排查顺序一般是这样确认定义名称是否正确引用位置有没有把单元格写错。确认公式所在单元格和引用单元格的相对位置。如果定义名称写的是GET.CELL(63,Sheet1!$A$1)那所有公式都会去读A1一个格子的颜色。确认工作簿已经启用宏。如果修改颜色后结果不变按CtrlAltF9强制重算。检查是否保存成了xlsx并再次打开部分旧环境下宏表函数会失效建议改用xlsm。5.2 VBA函数返回#NAME?或#VALUE!自定义函数返回#NAME?通常有两个原因一是代码没放到标准模块里二是函数名写错。插入模块时要注意在VBA工程窗口中选中VBAProject右键选择“插入”-“模块”而不是“类模块”。返回#VALUE!则多半是参数类型不匹配。比如ColorSum函数要求第二个参数是一个Range你写成了一个数字VBA一计算就报错。排查时可以临时在单元格里输入CountColor(A1:A10, B1)确认B1真的填了颜色而不是写着“红色”两个字。这个低级错误我真的见过很多次尤其是在共享工作簿里别人把参考单元格的内容误删了填成了文本。5.3 颜色相近匹配不上RGB与调色板色差VBA里用Interior.Color返回RGB值解决大部分匹配问题但还有一个坑有些颜色是通过主题色或者条件格式生成的它的Color值看起来是一个RGB实际在界面上设置时与肉眼看到的颜色有细微色差。比如“黄色”这个颜色有人用标准调色板黄色RGB 255,255,0有人用Excel主题里的“着色4”RGB 255,200,0二者肉眼都叫黄色但数值完全不同。最稳妥的匹配方式有二一是参考单元格法直接拿一个真实涂好色的格子去匹配二是在代码里把颜色值近似归一化比如把RGB拆开判断三个通道是否都在允许的误差范围内。不过实战里我一般不会把误差范围搞得太复杂参考单元格法够用了。5.4 条件格式产生的颜色统计不到这是一个很多人没意识到的盲区。GET.CELL和VBA的Interior.Color都只能读取“静态填充色”条件格式动态生成的颜色是不算数的。比如你设置了“数值大于100时整行标红色”这个红色是条件格式渲染出来的不是单元格本身的格式所以颜色统计函数读到的还是原始无填充状态。解决条件格式颜色统计问题的方案有两个。方案一是不统计颜色直接数条件既然你已经有规则“数值大于100”那统计时直接COUNTIF(区域,100)根本不需要绕道颜色。方案二是使用VBA的DisplayFormat属性读取显示格式它可以拿到条件格式渲染后的颜色。例如If cell.DisplayFormat.Interior.Color targetColor Then但DisplayFormat不能用于自定义函数里读取单元格格式在某些Office版本里会报错稳定性一般。所以我还是推荐方案一条件格式能覆盖统计需求的就别去读颜色老老实实读值。5.5 性能问题几千行数据卡成狗VBA自己写循环读取每一个单元格的Interior.Color在几千行数据时问题不大但要是几万行还要逐个求和速度就会明显变慢。我在帮某团队处理过一张近两万行的明细表ColorSum函数跑一次要卡十几秒。优化思路主要有两个。第一个思路是把颜色值一次性读进数组Dim colorArr() As Long ReDim colorArr(1 To rng.Cells.Count) For i 1 To rng.Cells.Count colorArr(i) rng.Cells(i).Interior.Color Next i这也是逐格读只是把“读颜色”和“判断求和”分成两个阶段代码结构更清晰。第二个思路是先用筛选或者辅助列把数据范围缩小再对筛后的区域做统计。如果只是做一次性统计完全不建议用VBA函数直接用筛选SUBTOTAL更快。我处置大表时优先会用辅助列配合GET.CELL生成颜色索引然后用SUMIFS、COUNTIFS处理速度比分步刷自定义函数还快。5.6 单元格无填充与白色填充的区分还有一个容易踩坑的细节。肉眼看着没填颜色的单元格GET.CELL和VBA读出来的值可能是0也可能是白色RGB 16777215或者无色-4142即VBA里的xlNone。在实际表格里“无填充”与“填充了白色”是不同的概念二者在VBA里的返回值也不同。如果你希望统计“无填充”的单元格只判断0是不够的还要排除-4142和16777215两种情况。这里建议统一约定默认所有单元格不填充需要标记的才填颜色。这样统计时只数有颜色值的单元格逻辑最简单。如果表格里已经混入了“看起来没色其实填了白底”的单元格建议先全选区域将填充色改成“无填充”再重新标记。5.7 颜色统计在WPS中的差异补充一个很多人关心的问题WPS表格能不能用这些color系列函数根据我自己的测试WPS对宏表函数GET.CELL的支持比较稳定定义名称的方法和Excel基本一致但VBA支持上两者差异较大WPS默认没有内置VBA编辑器部分版本可选启用宏而且自定义函数的兼容性也参差不齐。如果你的团队同时使用Excel和WPS最好提前确认VBA方案在WPS里能正常跑否则推荐全部走宏表函数或辅助列方案这样两边都能用。写到最后的一些心里话color系列函数的本质其实是在跟Excel的“格式与数据分离”机制较劲。颜色是用来给人看的函数是用来处理数据的两者天然有一条鸿沟。我用这套方案解决过考勤统计、项目进度汇总、销售报表等多种问题最大的教训是颜色标记虽然好看但真正稳的统计方案一定是以文本或数值为核心颜色只是可选的视觉辅助。给自己搭表格的时候能加一列状态文字就尽量加别让颜色成为唯一的信息载体拿到别人的表能先用筛选看一眼数据分布再决定要不要上颜色统计函数别上来就闷头写公式。如果你实在躲不开“只有颜色、没有文字”的存量表格希望这篇文章里的GET.CELL和VBA函数能帮你省下几个小时的眼力。最后再分享一个小技巧写好的VBA颜色统计函数建议保存在个人宏工作簿里这样下次新建任何工作簿都能直接输入函数名调用不用每次重新插模块。我就是这样把常用的ColorSum、ColorCount、CountByColorCondition三个函数固定下来了遇到颜色统计需求直接填参数效率比临时写代码高得多。