办公软件

WPS 表格条件格式实战:用数据条、色阶和图标集一眼看出进度与异常

约 6 分钟读完
WPS 表格条件格式实战:用数据条、色阶和图标集一眼看出进度与异常

摘要

本文以桌面版 WPS 表格为例,从建立示例表开始,逐步演示数据条、三色阶、图标集和公式规则,并结合任务进度、金额与截止日期制作醒目效果。文中还给出公式写法、规则优先级调整和常见失效排查方法,适合制作项目看板与异常清单。

本文目录

简要引言

条件格式可以根据单元格中的数值自动改变外观:进度高就显示更长的数据条,金额异常就改变底色,完成度不足则显示警示图标。它只改变显示效果,不会修改原始数据,适合用来制作项目进度看板、费用清单和异常台账。

适用设备与系统版本

本教程以 Windows 10/11 上当前版本的桌面版 WPS Office 为主要示例,macOS 桌面版的菜单位置基本相似。国产手机、平板上的 WPS 以及网页版可能没有完整的数据条、图标集或规则管理功能,遇到菜单缺失时建议更新客户端并改用电脑操作。

不同版本的 WPS 可能把数据条、色阶、图标集直接列在条件格式菜单中,也可能收在“新建规则”内。本文会同时标明两种位置。

准备工作:建立一张可测试的示例表

  1. 新建 WPS 表格,在第一行依次输入:A任务编号、B负责人、C进度、D截止日期、E金额(元)、F状态。
  2. 输入几条测试数据,例如:A001、张敏、80%、=TODAY()+10、12000、正常;A002、李强、50%、=TODAY()-3、8600、未完成。再增加一条重复编号和负数金额,便于检查后面的异常规则。
  3. 实际使用时,可把数据向下填充到第 101 行。右键进度列,选择“设置单元格格式”→“数字”→“百分比”,小数位数设为 0;截止日期列则设为日期格式。
  4. 注意,单元格中显示 80% 时,底层数值是 0.8,而不是 80。因此后面的公式用 $C2<1 判断“未满 100%”,图标阈值则输入 80%。

操作一:用数据条显示完成进度

  1. 选中进度列 C2:C101。
  2. 在“开始”选项卡中点击“条件格式”,选择“数据条”。如果当前版本没有直接显示,则进入“新建规则”,选择同名的数据条规则。
  3. 选择一种数据条样式。若对话框允许编辑上下限,将最小值设为数值 0,最大值设为数值 1;没有编辑选项时保持自动即可。
  4. 点击“确定”。此时数值越接近 100%,数据条越长。修改一个进度数值后,条形长度会自动更新。

数据条适合表现比例,不适合判断精确差额。如果进度列存成了带单位的文本,条件格式通常无法正确计算,应先改为真正的百分比或数值。

操作二:用色阶识别金额高低

  1. 选中金额列 E2:E101。
  2. 打开“开始”→“条件格式”→“色阶”,选择三色阶。
  3. 把最低值设为绿色,中间值按第 50 百分位设为黄色,最高值设为红色。这样高金额会逐渐变红,便于发现需要复核的费用。
  4. 如果单位金额过高代表风险较小,可以交换最低值与最高值的颜色。

色阶根据所选区域动态计算。加入一笔特别大的金额后,其他数值的颜色可能随之变化;如果业务有固定上限,例如超过 10000 元必须标红,建议改用后面的公式规则。

操作三:用图标集快速标记进度状态

  1. 再次选中 C2:C101,进入“条件格式”→“图标集”,优先选择交通灯或箭头图标。
  2. 设置第一档为大于等于 80%,第二档为大于等于 50%,剩余低于 50% 的记录显示第三种图标。
  3. 通过预览确认绿色对应高进度、红色对应低进度。不同图标包的排列可能不同,应以预览结果为准。
  4. 数据条与图标集同时作用于同一列时,部分版本可能出现遮挡或不叠加。遇到这种情况,可以在 G 列输入 =C2 并向下填充,再把图标集应用到 G 列,保留 C 列的数据条。

操作四:用公式规则标记逾期、负数和重复编号

  1. 标记逾期任务:选中 A2:F101,进入“条件格式”→“新建规则”,选择“使用公式确定要设置格式的单元格”一类规则,输入:

    =AND($D2<>"",$D2<TODAY(),$C2<1)

    在格式区域选择浅红色底色;若显示详细格式设置,可把字体颜色改为深红。公式中的 $D2、$C2 表示锁定列、相对变化行,因此每一行会检查自己的日期和进度。

  2. 标记负数金额:选中 E2:E101,新建公式规则,输入:

    =AND($E2<>"",$E2<0)

    设置红色底色,负数便会被自动挑出。

  3. 标记重复任务编号:选中 A2:A101,新建公式规则,输入:

    =COUNTIF($A$2:$A$101,$A2)>1

    设置黄色底色。如果 A 列是允许重复的项目名称,应先增加唯一编号列,再对编号列设置规则。

  4. 标记关键字段缺失:选中 A2:F101,新建公式规则,输入:

    =OR($A2="",$B2="",$D2="")

    设置浅黄色底色,可快速找出没有编号、负责人或截止日期的记录。

操作五:整理规则并做成可复用看板

  1. 通过“开始”→“条件格式”→“管理规则”查看当前工作表中的全部规则。
  2. 调整规则顺序,并为互斥的异常规则勾选“停止如果为真”(如果当前版本提供该选项),避免同一个单元格被多层颜色覆盖。
  3. 数据更新后,条件格式会自动重新计算。复制到其他报表时,可先用“格式刷”测试;稳妥起见,也可以在新表中重新建立规则并调整应用范围。

注意事项

  • 条件格式依赖真正的数值。文本形式的“80%”“100 元”不会参与数据条和色阶计算。
  • 色阶和图标集适合快速观察,固定业务标准应使用公式规则,以免数据变化后判断结果跟着漂移。
  • 公式中的 $ 不能随意省略。应用范围左上角单元格决定公式的相对位置,整行高亮通常还需要锁定列。
  • 条件格式不是数据校验。它能提示异常,却不会阻止用户输入错误内容;日期、金额和必填项仍应使用“数据有效性”功能检查。
  • 尽量只对实际数据区域设置规则,不要直接应用到整列或整张工作表,以免文件变大、刷新变慢。

小结

数据条适合显示完成比例,色阶适合比较数值高低,图标集适合快速标记状态,公式规则则能处理逾期、负数、重复编号和缺失字段。把这几类规则组合起来,普通 WPS 表格也能变成清晰、会自动更新的项目进度看板。

常见问题

WPS 表格里找不到数据条、色阶或图标集怎么办?

先在桌面版 WPS 中打开“开始”→“条件格式”,并检查是否收在“新建规则”内。旧版客户端可通过右上角菜单更新;网页版和手机版的功能可能不完整,完整制作规则建议使用电脑。

数据条和图标集同时设置在进度列,为什么只显示一种?

可能是当前版本的显示效果或规则冲突所致。可以把其中一种移到辅助列:新建 G 列并输入 `=C2`,将图标集应用到 G 列,数据条继续保留在 C 列。

公式条件格式为什么没有效果,或者整片区域都变色?

请检查公式是否以等号开头、应用范围左上角是否为 A2、是否正确使用 `$` 锁定列,以及截止日期是否为真正的日期值。修改公式后,可先在目标区域重新应用规则。

参考来源