excel数据清洗函数

一、8个核心数据清洗函数

1. TRIM：一键清除多余空格（最常用）

• 作用：仅保留文本中"单词/字符之间的1个空格"，删除开头、结尾及中间的多余空格（比如从网页复制的数据常带此问题）。
• 语法：=TRIM(需要清理的文本/单元格)
• 注意：TRIM 不能清除全角空格（中文空格），全角空格需要搭配 SUBSTITUTE 替换。

2. CONCATENATE：合并多列文本（替代"&"）

• 作用：将2个及以上的文本、数字或单元格内容，合并成1个字符串（比手动输入"&"更简洁，尤其多列合并时）。
• 语法：=CONCATENATE(文本1/单元格1, 文本2/单元格2, ...)（最多 255 个项目）

3. REPLACE VS SUBSTITUTE：精准替换文本（别用混！）

两者均用于替换文本，但适用场景完全不同，错用会导致结果错误，核心区别见下表：

• (1) REPLACE：按位置替换（固定格式数据）
  语法：=REPLACE(原文本, 开始位置, 替换长度, 新文本)

• (2) SUBSTITUTE：按内容替换（灵活匹配）
  语法：=SUBSTITUTE(原文本, 要替换的旧文本, 新文本, [替换第几次出现的旧文本])（最后一个参数可选，默认替换所有）

4. LEFT / RIGHT / MID：拆分文本（不用手动删）

三者均用于"提取文本中的指定部分"，区别在于提取方向，覆盖 90% 的文本拆分需求。

• (1) LEFT：从左侧开始提取
  语法：=LEFT(原文本, 提取的字符数)（字符数可选，默认1）

• (2) RIGHT：从右侧开始提取
  语法：=RIGHT(原文本, 提取的字符数)（字符数可选，默认1）

• (3) MID：从中间任意位置提取
  语法：=MID(原文本, 开始位置, 提取的字符数)（开始位置从1算起）

5. LEN / LENB：统计字符数（核对数据完整性）

• 作用：统计文本的长度，核心用于检查数据格式是否合规（如手机号是否11位、身份证是否18位），两者区别在于对"中文"的计数方式。
• 语法：
• =LEN(原文本)：中文、英文、数字均计为1个字符

• =LENB(原文本)：中文计为 2 个字节，英文/数字计为1 个字节

6. FIND / SEARCH：定位文本位置（配合拆分用）

• 作用：查找"目标文本"在"原文本"中的起始位置，返回数字（如"a"在"abc"中是第1位），常与 MID/LEFT/RIGHT 搭配，实现"动态拆分"。

• 核心区别：FIND 区分大小写、不支持通配符；SEARCH 不区分大小写、支持通配符。

• (1) FIND：精准定位（区分大小写）
  语法：=FIND(要找的文本, 原文本, [开始查找的位置])（开始位置可选，默认1）

• (2) SEARCH：模糊定位（支持通配符）
  语法：=SEARCH(要找的文本, 原文本, [开始查找的位置])

7. TEXT：统一数据格式（关键！）

• 作用：将数字（如日期、数值）按指定格式显示，解决"同一数据不同格式"的问题（如日期有的是"2024/8/21"，有的是"2024.8.21"）。
• 语法：=TEXT(要格式化的数字/日期, "格式代码")
• 常用格式代码：
• 日期："YYYY-MM-DD"（如2024-08-21）、"MM月DD日"（如08月21日）
• 数值：最好这么写："0.00" 保留两位小数；"#,##0" 千位分隔符。