WPS表格如何按颜色筛选单元格并统计数量与求和?

在一张需要反复核对的项目台账里,用不同填充色标记状态是很多人的习惯:黄色代表待跟进,绿色代表已回款,灰色代表作废。等到需要回答“这批标黄的记录有几条、金额合计多少”时,WPS表格按颜色筛选就是最直接的入口——它把颜色当成一个维度,先把可见数据集缩小,再叠加统计手段得出结论。本文按“问题—约束—解法”的顺序,把操作路径、统计口径和边界条件一次讲清楚。
一、按颜色筛选在WPS表格里解决的是什么问题
先把能力边界说明白:筛选属于“视图层”操作,只决定哪些行显示出来,并不修改任何一个单元格的值。因此“按颜色筛选之后再求和”,本质上仍是一次普通求和,区别只在于统计范围被限制在可见行上。理解这一点,后面所有统计方案就能串成一条线——要么让工具自动忽略隐藏行,要么让颜色先变成公式能识别的值。
与相邻功能的边界同样值得区分。按值筛选(数字大于某值、文本包含某个关键词)有明确的判定规则,换台设备、换个账号打开,结果都一样;按颜色筛选依赖的却是单元格当前的填充格式,一旦格式被主题切换、条件格式或复制粘贴改写,筛选口径就会跟着漂移。至于查找功能里的“按格式定位”,解决的是“找到并跳转”,而不是“只看某几行”,自然也给不出合计。
颜色来自手动填充,还是来自条件格式
这是最容易被忽略、又最容易踩坑的一点。手动填充的颜色是单元格的静态格式属性;条件格式产生的颜色则是规则运行时渲染出来的视觉效果,单元格本身并没有被涂上那个色。经验性观察是:按颜色筛选通常能稳定识别手动填充色,而对条件格式渲染出的颜色,识别情况在不同版本和不同实现下表现并不一致。
验证方法很简单,几分钟就能跑完:在一列里写若干数字,给它加一条“高于平均值时填充红色”的条件格式,然后开启筛选,展开该列的下拉菜单,看颜色列表里有没有出现这个红。预期可观测现象是——颜色项可能不出现,也可能只出现一部分。如果你的日常工作正好依赖这个颜色,就应该把它改写成静态填充,或者干脆增加一个辅助字段。
提示:条件格式与手动填充可以同时存在于一个单元格上。此时屏幕显示的是条件格式的颜色,而按颜色筛选读到的可能仍是手动填充色。遇到“看着是这个色、筛出来却是另一批行”的情况,先怀疑这一点。
二、动手前的三项检查
第一项,数据必须是连续区域,首行是唯一的列标题,区域内没有整列合并的单元格。合并单元格会让筛选区域的实际判定范围变得难以预测,经验性观察是:合并单元格密集的表格,筛选结果与预期不符的概率明显更高。第二项,确认颜色是手动涂的还是规则生成的,理由见上一节。第三项,确认你要统计的“数量”到底指哪一个——非空单元格个数,还是其中的数字个数,这两个口径在中文字段混排的表里差别很大。
为什么要把这三项放在动手之前?因为按颜色筛选本身没有“撤销历史”之外的任何保护机制。一旦在脏数据上开筛,结果看起来是“对的”,实际却可能包含或遗漏某些行,而这类错误很难在事后被发现。
下拉菜单里看不到颜色选项,通常是这四个原因
- 当前列的填充色完全一致。没有颜色差异,自然也没有可筛的色块。
- 颜色由条件格式渲染而来,而非手动填充。
- 选区不是连续数据区,或者筛选根本没有真正开启(列标题上没有出现下拉箭头)。
- 版本与界面差异。移动端的功能整体比桌面端精简,入口是否存在要以实际界面为准。
逐条排查一遍,通常三五分钟就能定位。如果四条都排除了还是找不到入口,就走下一节给出的两条回退路线,不要在界面上反复试错。
三、桌面端最短操作路径(Windows 与 macOS)
两个平台的菜单层级基本一致,差别主要在快捷键习惯上,下面给出的路径在两处都适用。
- 点击数据区域内任意一个单元格,按 Ctrl+A 选中连续数据区。如果按一下选中了整张工作表,说明数据区周围有空行或空列,改用鼠标从左上角拖到右下角框选。
- 切换到「数据」选项卡,点击「筛选」,各列标题右侧出现下拉箭头。
- 点开目标列的下拉箭头,找到「按颜色筛选」(部分版本显示为「颜色筛选」),在展开的色块列表中点选目标颜色。
菜单文案会随版本和界面语言略有差异,以你屏幕上实际显示的名称为准,不必强行对应本文的措辞。另外,为什么建议先选区域而不是直接对整列开筛?因为整列筛选会把标题下方的空白区域也纳入范围,后续用 SUBTOTAL 统计时,公式的区域边界很容易被写偏。
只想看某一种颜色,还是想排除它
这两件事在界面上的操作差别不大,结果却完全相反。如果只是查看某一种标记,直接点选该色块即可;如果目标是“排除已完成的绿色,只看剩下的”,更稳妥的做法是在辅助列上按值筛选,而不是在颜色列表里逐个反选——后者的可读性和可复现性都更差,换个人接手时容易看错。
颜色筛选与值筛选叠加时的“与/或”关系
同一列下拉菜单里勾选的多个项目之间是“或”的关系;不同列之间则是“与”。所以先按颜色筛 A 列、再按文本筛 B 列,得到的是两个条件的交集,这通常正是你想要的。但如果需求本身是并集(比如“标黄的或者标灰的都要看”),单靠两次筛选做不到,需要先在辅助列里把两类条件合并成一个字段,再对该字段筛选一次。
找不到入口时的两条回退路线
第一条是查找按格式定位:打开查找对话框,进入「选项」,「格式」中选择“从单元格选择格式”,然后点一个已知颜色的单元格,确定后执行“查找全部”。结果列表会把所有匹配的单元格位置列出来,可以逐条处理。适用边界是条目不多、只需要知道“在哪里”而不需要合计的场景;几十条以内还顺手,上百条就不现实了。
第二条是辅助列:把颜色所代表的状态写成文字,再用 SUMIF 或 COUNTIF 汇总。这条路的初期成本略高,但只要这张表的统计会重复发生,长期看反而更省时间,也更不容易出错。
四、移动端的差异:Android 与 iOS 能做到哪一步
WPS 移动版对表格的筛选支持整体比桌面端精简。经验性观察是:基础的值筛选一般可用,而按颜色筛选这类依赖格式识别的入口,在移动端不一定提供;即便提供,受触屏界面所限,操作路径和桌面端也不会完全对应。因此这里不给出移动端的具体菜单路径,而是一个更实用的建议:需要按颜色筛选并统计时,优先在桌面端完成,移动端主要用来查看结果。
如果你手边确实只有手机,可以考虑先在桌面端筛选好并保存文件,再传到手机上查看。但要注意一点:筛选状态是否能随文件一起保存,和你保存时的具体操作有关,最稳妥的做法是在另一台设备上打开确认一次,看筛选结果是否与预期一致,再据此决定后续流程。
五、筛选之后,把数量和合计算出来
到这里,可见行已经确定,真正需要做判断的是统计口径。有三种常见做法,上手成本、可复现性和适用范围各不一样,下面逐个说明。
方案A:状态栏即时统计
筛选完成后,直接选中需要统计的数值区域,把视线移到窗口底部的状态栏。默认可能只显示一两个指标,在状态栏上单击右键,可以勾选「平均值」「计数」「数值计数」「最小值」「最大值」「求和」等项目。其中「计数」统计的是非空单元格个数,「数值计数」只统计其中的数字——在中文字段和数字混排的列里,这两个数字经常差很多。
为什么快却不够用:状态栏的结果不能被引用,不能参与其他公式,也不会在下次打开时保留。它适合“看一眼就够”的场景,比如临时确认这批记录的金额有没有超过某个额度;一旦要把这个数字写进报表,就必须换方案。
方案B:SUBTOTAL 函数
SUBTOTAL 的核心特性是会忽略被筛选隐藏的行,这正是它区别于普通 SUM 的地方。把公式写在数据区域之外的空单元格里,避免被自己的筛选结果隐藏掉。
=SUBTOTAL(9, C2:C1000) =SUBTOTAL(2, C2:C1000) =SUBTOTAL(3, B2:B1000)
第一行对 C 列可见行求和,第二行统计 C 列可见数字的个数,第三行统计 B 列可见的非空单元格个数。有三个细节需要留意:其一,区域不要包含公式自身所在的单元格,否则会形成循环引用;其二,区域要覆盖全部数据行,新增行时记得同步扩展;其三,如果表格里还存在手动隐藏的行,编号的选择会直接影响结果,这一点下一节展开。
方案C:辅助列 + SUMIF / COUNTIF
如果颜色标记是长期的业务习惯,建议在录入阶段就加一列“状态”,把颜色想表达的含义写成文字。这样一来,统计就不再依赖筛选状态,也不必担心格式被改动。
=COUNTIF($D$2:$D$1000, "待跟进") =SUMIF($D$2:$D$1000, "待跟进", $C$2:$C$1000)
为什么推荐这条路:文字值是可以排序、校验、筛选、导出的数据;颜色只是格式,复制粘贴、套用主题、增删条件格式都可能让它悄悄改变。把颜色当信号灯用没问题,把颜色当数据库用,迟早会在某次文件流转中出问题。
三种方案的取舍对照
| 对比维度 | 方案A 状态栏 | 方案B SUBTOTAL | 方案C 辅助列+SUMIF |
|---|---|---|---|
| 上手成本 | 最低,无需写公式 | 需写一个公式 | 需先补齐字段 |
| 是否随筛选动态变化 | 是 | 是 | 否,按字段固定 |
| 结果能否被其他公式引用 | 否 | 能 | 能 |
| 对颜色的依赖程度 | 高 | 高 | 低 |
| 适合场景 | 临时核对 | 固定报表区 | 长期台账 |
六、SUBTOTAL 的编号怎么选:1–11 与 101–111
SUBTOTAL 的第一个参数是函数编号,同一个函数有两套编号:1–11 和 101–111。它们的差别只有一个——101–111 系列在忽略筛选隐藏行的基础上,还会额外忽略手动隐藏的行。
| 编号 | 含义 | 忽略筛选隐藏行 | 忽略手动隐藏行 |
|---|---|---|---|
| 9 | 求和 | 是 | 否 |
| 109 | 求和 | 是 | 是 |
| 2 | 数字个数 | 是 | 否 |
| 102 | 数字个数 | 是 | 是 |
| 3 | 非空个数 | 是 | 否 |
| 103 | 非空个数 | 是 | 是 |
选择原则可以归纳成一句:如果数据的可见性完全由筛选控制,两套编号结果一致;如果表格里还存在手动隐藏的行(比如折叠了辅助行、临时藏起了几行说明),就用 101–111 系列,避免把本不该统计的行算进来。
边界提醒:SUBTOTAL 只处理“行”的隐藏,对列隐藏无效。另外它不能嵌套自身,跨工作表引用时也无法保持忽略隐藏行的行为——这类场景应该改用 SUMIF 或数据透视表。
七、让它可持续:数据透视表与字段化改造
数据透视表按字段值聚合,它本身并不识别填充色。想让透视表按状态、按月出汇总,前提仍然是把颜色翻译成字段。所以“先补状态列,再做透视表”是唯一顺序正确的做法。
做法并不复杂:新增一列“状态”,用文字记录含义;选中数据区域后插入数据透视表;把“状态”拖到行区域,把金额拖到值区域,默认即为求和。新增数据后需要右键刷新。如果数据源的范围扩大了,还要回到透视表的数据源设置里检查引用区域是否需要调整,否则新行不会被统计进去。
什么时候不该上透视表:数据只有几十行、每周只汇总一次的场合,透视表的维护成本高于它的收益,用 SUBTOTAL 反而更轻。
八、什么时候不该用按颜色筛选
- 颜色含义没有在团队内形成约定,或者约定只存在于某一个人的记忆里。
- 同一张表里存在相近颜色,比如深黄与浅黄、两种不同的绿。
- 统计结果需要交给别人复算或复核。
- 统计需要自动化,例如按周导出、定时汇总。
- 数据行数很大,而颜色是逐格手工涂上去的。
判断标准其实可以收敛成一句话:如果这个统计会重复发生,就把颜色换成字段;如果它只发生一次,并且你能接受结果不可复算,那么用颜色筛选也无可厚非。
还有一类容易被忽视的“不该用”:数据本身需要对外流转时。颜色在导出为纯文本、CSV 或打印黑白稿时会全部丢失,此时基于颜色的筛选口径就彻底失效了。
九、协作与合规:颜色作为数据载体的风险
颜色是一层信息透明度很低的数据。把文件发给同事后,对方看到的是色块,却看不到你脑中的约定;更麻烦的是,不同的显示设备、不同的主题设置,都可能让相近的颜色在观感上趋于一致。
一个可复现的验证方法:新建一张表,标记三行,其中两行使用肉眼接近的两种黄色;把文件换一台设备或用另一个账号打开;观察两行是否仍能被区分,以及按颜色筛选的下拉列表里,它们是作为两个独立色块出现还是被归并。预期结果是相近色可能难以区分,甚至被合并为同一项。
另一个风险是条件格式覆盖。给已经手动填充的单元格再加一条条件格式后,原先能筛出来的颜色可能从列表中消失。验证方式与上面类似,重点观察筛选下拉里的颜色项有无变化,并记录变化前后的行数差异。
如果表格需要对外提供,尤其是涉及财务、审计口径的场景,建议把颜色承载的信息同时落到一个文本字段中,颜色只作为显示层的辅助。这样即便颜色丢失,统计口径依然完整。
注意:颜色筛选的结果会随着文件在不同设备、不同版本之间流转而变化,这不是软件缺陷,而是格式作为数据载体本身的固有局限。凡是需要复算的场景,都不要把唯一的口径寄托在颜色上。
十、故障排查:现象、原因与处置
下面按“现象→可能原因→验证方法→处置”的结构列出五类常见问题,遇到时可以对照排查。
| 现象 | 可能原因 | 验证方法 | 处置 |
|---|---|---|---|
| 下拉菜单没有颜色选项 | 该列无颜色差异;颜色来自条件格式;筛选未真正开启 | 查看该列是否存在两种以上填充色 | 改为手动填充或改用辅助列 |
| 选了颜色却筛不出行 | 颜色被条件格式覆盖;选错了单元格颜色与字体颜色的分组 | 同时查看两个颜色分组下的选项 | 改用查找按格式定位 |
| 状态栏不显示求和 | 状态栏项目未勾选;选中区域内没有数值 | 右键单击状态栏查看勾选状态 | 勾选“求和”“数值计数” |
| SUBTOTAL 结果不随筛选变化 | 公式区域含公式自身;误用了 SUM | 切换筛选后观察公式结果是否改变 | 改用 SUBTOTAL 并限定区域 |
| 筛选行数与手工数不一致 | 区域内含隐藏行或空行;筛选范围未覆盖全部数据 | 清除筛选后对比总行数 | 重新框选连续区域再开启筛选 |
排查时有一个通用原则:先确认“筛选结果本身是否正确”,再去怀疑统计公式。很多人会在公式上反复调整半小时,最后发现是筛选区域一开始就选漏了几行。
十一、最佳实践检查表
- 颜色只作为显示层使用,其含义必须同时落到一个文本字段上。
- 单张表使用的颜色控制在三到五种,且避免相近色同时出现。
- 统计公式统一放在数据区域之外,优先使用 SUBTOTAL。
- 需要长期复用的统计,走辅助列加 SUMIF、COUNTIF 的路线。
- 每次新增数据后,回头检查公式区域是否覆盖了新行。
- 对外提供文件前,把颜色对应的状态字段一并说明清楚。
- 移动端主要用于查看,编辑与筛选尽量回到桌面端完成。
这七条里,第一条是根,其余六条都是围绕它展开的补丁。如果一张表从建立之初就把状态写成字段,后面几乎不会再遇到本文描述的多数问题。
十二、一套可复现的验证流程
下面的流程可以在一张新表上跑完,用来确认你自己这台设备、这个版本上的真实行为,而不是照搬别人的结论。
- 新建表格,A 列写十行文本,B 列写十个金额,给其中三行的单元格填充同一种黄色。
- 开启筛选,按颜色筛出黄色,记录可见行数,预期为三行。
- 在数据区之外的单元格写
=SUBTOTAL(9, B2:B11),记录结果,预期等于这三行的金额之和。 - 清除筛选,公式结果应当自动变为全部十行之和。
- 给其中一行再加一条条件格式,然后重复第 2 步,观察颜色列表是否发生变化。
- 把两次的颜色列表与行数差异记录下来,这就是当前环境下颜色筛选的真实表现。
花十分钟跑完这套流程的价值在于:你会清楚地知道,哪些结论在你这里成立、哪些不成立。不同版本、不同主题、不同显示设备都可能让颜色筛选的表现出现差异,自己验证一次最可靠。
十三、常见问题
WPS表格能直接按颜色求和吗?
=SUBTOTAL(9, 区域) 求和;或者把颜色对应的状态写成文本列,用 SUMIF 汇总。为什么筛选下拉里没有“按颜色筛选”?
条件格式产生的颜色能用来筛选吗?
筛选后状态栏不显示求和怎么办?
移动端可以做按颜色筛选吗?
十四、小结与下一步
回到最初的问题:WPS表格的按颜色筛选负责缩小可见范围,而数量与合计要另找工具来算。三句话可以说清整件事——筛选本身不统计,只筛选;统计可见行数量与金额,SUBTOTAL 是最省事且可被引用的方案;如果这个统计以后还会重复发生,就把颜色改写成文本字段,用 SUMIF 或 COUNTIF 一劳永逸。
至于什么时候该用颜色、什么时候不该用,判断标准在前文已经给过:一次性排查可以用颜色,长期台账必须用字段。颜色在协作和对外流转中的脆弱性,是它无法承担数据职责的根本原因。
下一步建议很具体:拿一张你手上真实在用的台账,按第十二节的流程跑一遍,然后把“颜色→文本字段”的改造补上。下一次做周报时,你会发现统计这件事不再需要额外解释,也不再需要叮嘱同事“这个黄色是什么意思”。


