浏览 AI 知识库
Excel 日期格式怎么批量统一：保留原值并标记异常
Excel 日期看起来统一，不代表底层值真的一致。保留原列，分别识别文本、序列号和模糊日期，再把无法确定的行留给人工判断。
按实际操作顺序推进
每完成一段就检查当前输出，确认结果符合预期后再继续。
日期全变成同一种格式后，日和月可能已经被调换
一份报名表的日期列同时出现 `2026/9/3`、`03-09-2026`、Excel 序列号和“9 月上旬”。直接统一格式后，屏幕上都像日期，但其中两行已经把日月调换。
- 原值列仍完整保留
- 按年、月、日抽样后与来源记录一致
- 异常值没有被自动补成某一天
动手前，把材料和不能改的部分定下来
| 需要确认 | 本例内容 |
|---|---|
| 现有材料 | 教学表共 12 行，保留 A 列原始报名日期；B 列准备写规范值。已知业务采用北京时间和 `yyyy-mm-dd`，但 `03-09-2026` 的来源地区未知。 |
| 不能越过的边界 | 只有能唯一解释的值才转换；模糊文本和日月顺序不明的值进入异常表，不以当前电脑区域设置猜测。 |
| 要交付的结果 | 原值列、规范值列和错误清单 |
Excel 日期格式批量统一，从“先识别类型模式”开始做
先识别类型模式
统计日期格式、小数点、千位符、币种、单位和空值写法，区分真正文本与显示格式。
定义目标标准
日期采用明确日期或日期时间与时区，金额保留币种和含税口径，单位统一到约定基准。
分规则转换
每种原始模式使用独立规则，记录命中数量；模糊日期和未知单位进入异常表。
保留换算依据
单位换算写原值、原单位、系数、规范值和规范单位；币种转换还要记录汇率来源和日期。
核对分布与边界
比较转换前后最小、最大、空值和总额，抽查每类模式，防止月日颠倒和倍数错误。
同一列的 5 个值，需要 4 条规则和 1 个待确认
| 原值 | 识别结果 | 规范值 | 规则/异常 |
|---|---|---|---|
| 2026-09-01 | ISO 日期 | 2026-09-01 | D01 直接解析 |
| 09/02/2026 | 月/日或日/月不明确 | 待确认 | E01 模糊日期，不猜 |
| 1.2k 元 | 金额缩写 | 1200.00 CNY | A03 ×1000，保留币种 |
| 3.5 kg | 重量 | 3500 g | U02 ×1000，记录系数 |
| - | 业务占位符 | 空值 | N02 保留原值和原因 |
正式清洗应使用真实口径表；日期顺序、币种和单位无法确认时进入异常清单。
规范值正确，还要证明总量没有被悄悄改变
| 检查项 | 清洗前 | 清洗后 | 判断 |
|---|---|---|---|
| 记录数 | 1,248 | 1,248 | 不得因解析失败丢行 |
| 异常数 | 未知 | 37 | 逐条有原值与原因 |
| 已确认金额合计 | 按原格式分组重算 | 规范币种合计 | 两种算法在确认范围内一致 |
| 边界 | 最小/最大和空值 | 转换后分布 | 无 1000 倍或月日颠倒 |
完成后的原值列、规范值列和错误清单
B 列得到 8 个明确日期；`03-09-2026`、`04-05-2026` 标记“地区格式待确认”，`9 月上旬` 标记“非精确日期”，1 个空值保持空白。异常表保留原行号、原值、原因和责任人，没有覆盖 A 列。
为什么“排序后日期顺序仍然异常”还不能交付
排序后日期顺序仍然异常
- 原因
- 部分单元格只是长得像日期的文本
- 怎么改
- 用 `ISTEXT`、序列值和最小/最大日期联合检查，并回到来源地区确认