Excel财务小技巧之13「数据验证+条件格式」

上一期我们用“文本函数+分列+日期函数”搞定了手机配件门店手工台账的脏数据。但上述数据清洗是事后的处理，今天我们来分享“数据验证+条件格式”：

用数据验证做下拉菜单，限制填写内容，从源头杜绝随意填写

用条件格式标记重复项、异常值，让异常数据一眼可见

一、案例导入

1.场景导入

上述手机配件销售数据：

核心字段包含：订单编号、销售日期、配件品类、型号、销量、单价、付款方式、销售员等

2.上述数据的具体问题

一是字段填写随意

比如：付款方式既有“刷卡”，又有“信用卡”、“借记卡”

比如：格式五花八门，销售日期有人填：“4.6”,有人填”2026-04-05″

二是核心字段漏填

销售员、门店区域等关键项空白

三是异常数据很难发现

比如：单价为0，销售金额为负等

接下来我们就进入“数据验证+条件格式”的学习

二、数据验证（数据有效性）

1.数据验证是什么？

（1）数据验证有什么用？

回忆一下，我们曾经在前面讲数据多级联动的时候使用过数据验证：

Excel财务小技巧之3「3步搞定数据多级联动」

数据验证（旧版Excel叫数据有效性）是 Excel 的填写规则设置工具，能给指定单元格设置下拉菜单、格式限定、范围约束、必填项等

加了数据验证之后，填写人只能按规则填写，从根源杜绝随意性。

（2）如何操作数据验证？

Step1：数据选项卡→数据验证

Step2：选择允许条件，比如：整数、日期、时间、序列等，还可以自定义

2.核心使用场景

（1）基础下拉菜单——限定固定选项，杜绝乱写

【适配场景】

付款方式、配件品类、门店区域、销售员等固定选项字段，是配件门店最核心、最常用的数据验证用法，彻底解决 “同一字段多种写法” 的问题。

【案例】

付款方式下拉菜单

【操作步骤】

Step1：先在空白区域列出全部付款方式的列表

Step2：选择付款方式对应列：从第2行选到最后1行

在数据验证的允许值中选择“序列”

来源选择Step1中设置好的列表

【设置优势】

后续门店新增付款方式等，只需修改数据源区域，所有下拉菜单自动同步更新，无需重新设置，一劳永逸。

（2）格式限定——规定日期、数值格式，杜绝格式混乱

【适配场景】

销售日期、金额、数量等

【案例1】销售日期格式限定

【操作步骤】

允许值选择日期；数据范围可以选择介于、大于等等；

日期范围根据实际需要填写

【操作效果】

并不是下拉菜单的格式，而是如果不按要求填写会报错

【案例2】单价/销售金额——限定为正数

【操作步骤】

将销售数量设为大于0的整数

将单价设为100以内的小数（因为配件金额通常在100以内，且定价都是29.8元这种一位小数）

（3）必填项设置——核心字段不能为空，杜绝漏填

【适配场景】

订单编号、配件品类、销售数量、单价、销售金额、付款方式等财务核算核心字段，解决漏填导致后续分析无法进行的问题。

【案例】设置订单编号不为空

【操作步骤】

Step1：配置规则：公式输入=A2<>””（表示 A2 单元格不能为空）

【设置效果】

（4）高阶用法——IF函数自定义

【适配场景】

用于满足业务的个性化规则

【案例】

比如：手机壳销售量一单不能多于3件

【操作步骤】

设置自定义公式：

公式需要从第2行开始，且需要相对引用

=IF(C2="手机壳",G2<=3,TRUE)C2 为配件品类、G2 为销售数量

【设置效果】

（2）设置出错警告

出错提示要具体：避免模糊提示，直接告知 “正确填写要求”，如 “请从下拉菜单选择，禁止手动填写”，减少填写人沟通成本；

（3）圈释无效数据

利用好“圈释无效数据”：若台账已有部分数据，可点击【数据】→【数据验证】→【圈释无效数据】，用红色圆圈标记所有不符合规则的数据，一键核查修正。

三、条件格式

1.条件格式是什么？

（1）条件格式有什么用？

如果说数据验证是 “事前防错”，那条件格式就是 “事中预警 + 事后可视化”：它能根据自定义规则，自动给单元格设置：字体颜色、填充颜色、数据条、色阶、图标集（箭头）

让异常数据（0 值 / 负数 / 空白）一眼可见，同时让核心分析数据（高销量 / 高毛利 / 高业绩）可视化呈现，无需手动核对、无需复杂公式计算，大幅提升财务分析效率。

（2）如何操作条件格式？

选中目标单元格 / 区域 → 【开始】→ 【条件格式】→ 选择格式类型（突出显示单元格规则 / 数据条 / 色阶 / 图标集）→ 配置规则

有的小伙伴们会问：我都已经设置数据验证了，为什么还会出现异常数据，并需要用条件格式来突出呢

其实：条件格式和数据验证是双保险：

举个例子：我们拿到别人的表，一个个设置数据验证很麻烦，直接用条件格式，一眼看出异常数据

再举个例子：有时数据验证可能只设置了部分单元格，导致有些数据异常没有被检查出来，就需要用条件格式二次校验

【适配场景】

快速标记异常值、空白值、重复值，是条件格式最常用、最基础的用法

【具体案例】

【案例1】标记异常值：标记销量小于等于0的值

【操作步骤】

Step1：条件格式→突出显示单元格规则→其他规则

【案例2】标记空值（结合公式）

=ISBLANK(B2)若单元格有空格等假空值，需用=LEN(TRIM(B2))=0替代

【案例3】标记重复值

（2）进阶可视化：数据条+色阶

【案例1】数据条

【适用场景】

数据条更多用于达成率等的可视化分析

【操作步骤】

Step1：条件格式→数据条→其他规则

Step2：编辑规则：选择基于各自值设置所有单元格格式

【注意】对于最小值和最大值的选择：在完成率等财务场景下，我一般会将最小值设为0，最大值设为比实际数据的最大值偏高一点（避免单元格被占满）

比如：此处实际数据最大值为132.9%，我会把最大值的范围设置成1.35，最小值设置成0

【案例2】色阶

【操作步骤】

Step1：条件格式→色阶→其他规则

【结果呈现】

颜色越深，说明销售额越高

通常来说，如果分析的目的是为了发现问题，则问题越大，颜色越醒目，具体可以根据需求对颜色进行适当调整

（3）高阶可视化：图标集

【操作步骤】条件格式→图标集→选择：箭头、红绿灯、星级等

【案例1】星级：根据销售额差异设置不同星级

【案例2】箭头：用于标记销售趋势

【注意】

一是通常需要加辅助列，比如：3月环比：使用3月销售额-2月销售额

二是通常和数值进行比较，比如：＞0对应向上箭头；=0对应黄色水平箭头；<0对应红色向下箭头等（财务通常遵循“绿好黄差红预警”规则）

【案例3】红绿灯：用于标记完成率等

（4）公式自定义

【案例1】3月销售指标定的有些高，把超过24万的筛选出来，同一调整

【案例2】

多规则的顺序设置

【操作步骤】

进入条件格式→管理规则，按上下箭头调整公式的执行顺序

调整规则是：

小范围在前，大范围在后：

举个例子：单元格值大于28万这个范围要小于大于20万这个范围（大家可以画一个简易的数轴感受一下）

如不按规则设置会导致格式覆盖

【调整后】

【结果输出】

四、数据清洗总结

1.数据清洗实操流程

最后，根据我们数据清洗篇所学内容，我总结了如下数据清洗实操流程，可以实现 “源头防错 + 过程预警 + 分析可视化” 的全流程管控：

（1）Step 1：搭建标准化台账表头

保留核心字段：订单编号、销售日期、品类、品名、商品编码、销售数量、单价 (元)、销售金额 (元)、付款方式、销售员、门店代码等

字段要求：字段名标准化，无生僻字 / 特殊符号。

（2）Step 2：全字段数据验证

下拉菜单：用于各种类型选择

格式限定：日期、数量、金额等

必填项：主要是各类分析字段（标签），以及销售单号等唯一值类

自定义规则：可选，根据业务需求

（3）Step3：关键字段设置条件格式（预警 + 可视化）

异常标记：单价 / 金额≤0 标红、核心字段空白标黄、重复订单编号标橙；

数据可视化：达成率设置数据条、分类实际销售设置成色阶

业绩分析：设置图标集、红绿灯等

（4）Step4：台账维护规范

列表维护：数据源单独存放并隐藏，仅财务可修改

数据更新：新增数据时，直接下拉选择，无需手动填写，保证格式统一；

异常核对：通过条件格式标记的异常数据，及时沟通销售员修正；

规则更新：每月更新业绩达标规则、品类基础销量，只需修改数据验证 / 条件格式的数据源 / 公式，台账自动同步。

模板保存：保存台账模版，实现多次高效使用

2.功能搭配，搞定数据清洗

一是数据验证 + 条件格式

先设置数据验证防错，再设置条件格式预警，双重保障数据准确性；

二是和清洗函数联动

若台账已有历史数据，先用 TRIM/SUBSTITUTE 清洗，再用“圈释无效数据”标记，最后设置数据验证 + 条件格式，实现新旧数据统一标准化；

3.条件格式注意事项

（4）条件格式的优先级

设置完条件格式后，会发现格式刷失效了，因为条件格式的优先级最高

（5）色阶/数据条/图标集暂不支持单元格的相对引用

解决方法是添加辅助列，并在辅助列设置条件格式

【本期总结】

本期是数据清洗的第2篇，核心是数据验证+条件格式

【下期预告】

下期我计划总结一下财务分析常用的函数和快捷键。