第 1 章：数据输入与格式化¶
从"随意填表"到"结构化数据管理"——建立数据质量的防线
数据分析的黄金法则： Garbage In, Garbage Out 。输入阶段的数据质量决定了分析结果的可靠性。本章教你如何从源头保证数据质量。
1.1 数据验证：从源头控制数据质量¶
数据验证的八种类型¶
| 验证类型 | 用途 | 示例 |
|---|---|---|
| 整数 | 限制输入为整数 | 年龄：18~65 |
| 小数 | 限制输入为小数 | 折扣率：0.1~0.5 |
| 序列 | 从下拉列表中选择 | 部门：销售部、市场部、研发部 |
| 日期 | 限制日期范围 | 入职日期：2020-01-01 之后 |
| 时间 | 限制时间范围 | 打卡时间：08:00~09:00 |
| 文本长度 | 限制字符数 | 手机号：11 位 |
| 自定义 | 使用公式验证 | 身份证号校验 |
| 任何值 | 仅显示输入提示 | 输入引导 |
实战：创建部门下拉列表
- 选中需要验证的单元格区域
- 数据 → 数据验证 → 设置
- 允许：序列
- 来源：
销售部,市场部,研发部,财务部,人事部 - 输入信息：标题"请选择部门"
- 出错警告：样式"停止"，标题"输入错误"，错误信息"请从下拉列表中选择部门"
数据验证的高级技巧¶
动态下拉列表
使用 INDIRECT 函数创建级联下拉列表。例如：选择"华东区"后，城市下拉列表只显示华东区的城市。
前提：已定义名称"华东区"、"华南区"等，每个名称对应一个城市列表。
1.2 条件格式：让数据"可视化"¶
条件格式的五种类型¶
| 类型 | 说明 | 典型场景 |
|---|---|---|
| 突出显示规则 | 满足条件时高亮 | 大于目标值标绿、小于标红 |
| 项目选取规则 | 前 N 项/后 N 项 | Top 10 销售额、Bottom 5 利润率 |
| 数据条 | 单元格内条形图 | 直观比较数值大小 |
| 色阶 | 三色/双色渐变 | 温度热力图、绩效分布 |
| 图标集 | 方向箭头、红绿灯等 | KPI 达成状态 |
实战：销售数据可视化
- 选中销售额列
- 开始 → 条件格式 → 数据条 → 绿色数据条
- 选中达成率列
- 条件格式 → 图标集 → 三色交通灯
- 设置规则：≥100% 绿灯，≥80% 黄灯，<80% 红灯
使用公式的条件格式¶
条件格式的真正威力在于使用 自定义公式 。
| 需求 | 公式 | 说明 |
|---|---|---|
| 隔行变色 | =MOD(ROW(),2)=0 |
偶数行变色 |
| 标记重复值 | =COUNTIF($A:$A,A1)>1 |
重复出现标红 |
| 标记周末日期 | =WEEKDAY(A1,2)>5 |
周六日高亮 |
| 高于平均值 | =A1>AVERAGE($A:$A) |
高于平均值标绿 |
1.3 自定义数字格式¶
格式代码的结构¶
常用自定义格式¶
| 格式代码 | 效果 | 说明 |
|---|---|---|
#,##0 |
1,234 | 千位分隔符 |
#,##0.00 |
1,234.56 | 两位小数 |
0.00% |
12.50% | 百分比 |
¥#,##0 |
¥1,234 | 货币符号 |
[绿色]#,##0;[红色]-#,##0 |
正数绿色，负数红色 | 条件颜色 |
0"元" |
123元 | 添加文字后缀 |
000-0000-0000 |
138-1234-5678 | 电话号码格式 |
yyyy-mm-dd |
2025-12-01 | 日期格式 |
实战：财务报表数字格式
| 数据类型 | 格式代码 |
|---|---|
| 金额（元） | #,##0.00 |
| 金额（万元） | 0.00,"万元" |
| 百分比 | 0.00% |
| 增长率 | [颜色10]+0.00%;[颜色3]-0.00%;0.00% |
格式代码中的颜色
[颜色N] 可以设置条件颜色。常用：[颜色3] 红色、[颜色10] 绿色、[颜色5] 蓝色。
1.4 表格（Table）：结构化数据管理¶
普通区域 vs 表格¶
| 对比维度 | 普通区域 | 表格（Ctrl+T） |
|---|---|---|
| 自动扩展 | 手动调整 | 新增数据自动扩展 |
| 公式填充 | 手动拖拽 | 自动填充整列 |
| 引用方式 | 单元格地址（A1） | 结构化引用（[列名]） |
| 筛选排序 | 手动设置 | 自动带标题行筛选 |
| 样式 | 手动设置 | 一键切换表格样式 |
实战：将数据区域转换为表格
- 选中数据区域任意单元格
Ctrl+T（或插入 → 表格）- 勾选"表包含标题"
- 确定
此后新增行时，公式、格式、验证规则全部自动继承。
结构化引用¶
表格中的公式使用 列名 而非单元格地址，可读性大幅提升。
要点总结¶
- 能使用数据验证创建下拉列表和输入限制
- 掌握条件格式的五种类型，能使用公式创建自定义规则
- 理解自定义数字格式的代码结构，能创建常用格式
- 能将数据区域转换为表格，使用结构化引用
课后练习¶
- 创建一个员工信息表，包含数据验证（部门下拉列表、年龄 18~65、手机号 11 位）
- 为销售数据表添加条件格式（数据条、色阶、图标集）
- 将数据区域转换为表格，体验结构化引用的便利性
下一章预告： 第 2 章：公式与函数基础 —— 从 SUM 到 VLOOKUP，掌握 Excel 的核心计算引擎。