【CDA干货】背熟这8个Excel常用函数，数据清洗不要太轻松

作者：李诗怡，CDA持证人，大数据工程技术专业大三在读

   学习数据分析，我们最基础的入门就是Excel了，接下我给大家再来一个干货，为大家介绍8类Excel常用函数

数据清洗类函数

   数据清洗类函数通俗来说：对脏数据进行整理、修正和规范化，就像清洗脏东西一样去除数据中的杂质，让数据变得干净、规范，以便后续分析或使用。

一、先搞懂:数据清洗到底洗什么?

“脏数据”并非指错误数据，而是不符合分析标准的信息，常见类型包括:

格式混乱:文本前后多余空格、日期显示为“2024.08.21"“2024-08-21”等多种格式;信息冗余:姓名与手机号合并在同一单元格(如“张三 13800138000“);

内容错误:同一类别出现不同表述(如“北京”“北京市”);

缺失或重复:关键信息空白、同一行数据多次出现。

接下来的函数，将针对性解决这些问题，每个函数均附“语法+实战场景+注意事项”，直接套用即可。

二、8个核心数据清洗函数(附实战案例)

1.TRIM:一键清除多余空格(最常用)

作用:仅保留文本中“单词/字符之间的1个空格”，删除开头、结尾及中间的多余空格(比如从网页复制的数据常带此问题)。

语法:=TRIM(需要清理的文本/单元格)

实战场景:

A 列是从表单导出的姓名，部分姓名前后有多余空格(如“  姓名  ”)，需清理后用于匹配。操作:在 A2 输入 =TRIM(A1)，下拉填充，结果为“姓名”(无空格)注意:TRIM 无法删除“全角空格”(如中文输入法下的空格)，若遇到全角空格，需先替换为半角空格(用“查找和替换”:查找”"，替换为"”)。

2.CONCATENATE:合并多列文本(替代“&”)

作用:将2个及以上的文本、数字或单元格内容，合并成1个字符串(比手动输入“&"更简洁，尤其多列合并时)

语法:=CONCATENATE(文本1/单元格1，文本2/单元格2，...)(最多 255 个项目)

实战场景:

A 列是“姓”，B 列是“名”，需合并为“姓名“

操作:在 A2输入 =CONCATENATE(A1,B1)。对比优势:若用“&“需写 =A1&B1，列数多时 CONCATENATE 更易核对

3.REPLACE VS SUBSTITUTE:

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

(1)REPLACE:按位置替换(固定格式数据)

语法:=REPLACE(原文本，开始位置，替换长度，新文本)

实战场景:需将中间 4 位替换为“”(脱敏处理)。A列是手机号(如“13800138000”)，分析:手机号共 11 位，中间4位是第 4-7 位，因此“开始位置=4，替换长度= 4”。

操作:在 B1输入 =REPLACE(A1,4,4,"****")，结果为“1388000“。

(2)SUBSTITUTE:按内容替换(灵活匹配)

语法:=SUBSTITUTE(原文本，要替换的旧文本，新文本，[替换第几次出现的旧文本])(最后一个参数可选，默认替换所有)

实战场景 1:统一城市名称A 列地址中，“北京”“北京市”混用(如“北京市朝阳区”“北京海淀区”)，需统一为“北京市”。操作:在 B1输入 =SUBSTITUTE(A1,“北京","北京市")，下拉后"北京海淀区"变为“北京市海淀区"，“北京市朝阳区“不变(因无“北京”单独出现)。

实战场景 2:替换指定次数

A 列文本是“苹果，香蕉，苹果，橙子”，需仅将第 2 个“苹果”替换为“葡萄”。操作:输入 =SUBSTITUTE(A1,"苹果","葡萄",2)，结果为“苹果，香蕉，葡萄，橙子”

4.LEFT/RIGHT/MID:拆分文本(不用手动删)

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

(1)LEFT:从左侧开始提取

语法:=LEFT(原文本，提取的字符数)(字符数可选，默认1)

实战场景:A列是“20240821"(日期)需提取年份“2024”。操作:输入 =LEFT(A1,4)，结果为“2024”。

(2)RIGHT:从右侧开始提取

语法:=RIGHT(原文本，提取的字符数)(字符数可选，默认1)

实战场景:A列是“20240821”，需提取日期“21”操作:输入 =RIGHT(A1,2)，结果为“21”。

(3)MID:从中间任意位置提取

语法:=MID(原文本，开始位置，提取的字符数)(开始位置从1算起)
实战场景:A 列是“20240821"，需提取月份“08“分析:月份是第 5-6位，因此“开始位置=5，提取字*=2”操作:输入=MID(A1,5,2)，结果为“08”。

5.LEN/LENB:统计字符数(核对数据完整性)

作用:统计文本的长度，核心用于检查数据格式是否合规(如手机号是否11位、身份证是否 18位)，两者区别在于对“中文”的计数方式。

语法:

=LEN(原文本):中文、英文、数字均计为1个字符,

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

实战场景:检查手机号格式

A 列是收集的手机号，需筛选出非 11 位的错误数据。

操作:在 B1 输入 =LEN(A1)，下拉后筛选B列中“不等于 11"的行，即为错误手机号(如“13800138”"138001380000")。

6.FIND/SEARCH:定位文本位置(配合拆分用)

作用:查找“目标文本”在“原文本”中的起始位置，返回数字(如“a"在“abc”中是第1位)，常与MID/LEFT/RIGHT 搭配，实现“动态拆分”。两者核心区别:FIND 区分大小写、不支持通配符;SEARCH 不区分大小写、支持通配符

(1)FIND:精准定位(区分大小写)

语法:=FIND(要找的文本，原文本，[开始查找的位置])(开始位置可选，默认1)

实战场景:拆分邮箱中的用户名

A 列是邮箱“zhangsan@163.com"，需提取“@”前的用户名。分析:先找“@”的位置，再用 LEFT 提取该位置前的字符。操作:

1.找“@“ 位置:=FIND("@",A1)，结果为 9("zhangsan"是8个字符,“@“在第9位);提取用户名:=LEFT(A1,FIND(“@",A1)-1)，结果为“zhangsan”(减1是排除“@”本身)

(2)SEARCH:模糊定位(支持通配符)

语法:=SEARCH(要找的文本，原文本，[开始查找的位置])

实战场景:提取地址中的“区”前面的内容

A 列地址格式不统-(如“上海市浦东新区”“广州市天河区")，需提取“区”前面的所有字符。

操作:

1.找“区” 的位置:=SEARCH("区",A1)，结果为 6(“浦东新区“的“区”在第6位);提取内容:=LEFT(A1,SEARCH(“区",A1))，结果为“浦东新区”。2.通配符用法:若地址中“区”可能是“县”(如“苏州市昆山县”)，可写 =SEARCH("区|县",A1)，一次性匹配两种情况。

7.TEXT:统一数据格式(关键!)

作用:将数字(如日期、数值)按指定格式显示，解决“同一数据不同格式”的问题(如日期有的是"2024/8/21"，有的是“2024.8.21”)。

语法:=TEXT(要格式化的数字/日期，"格式代码")

常用格式代码:

日期:"YYYY-MM-DD"(如2024-08-21)、"MM月DD日"(如08月21日)

数值:"8.00"(保留2位小数)、"#,##8"(千位分隔符，如 1,234)。

实战场景:统一日期格式

A 列日期格式混乱(如“2024/8/21""45542”(Excel序列号)“2024.8.21")，需统一为“2024-08-21"操作:在 B1输入 =TEXT(A1,"YY-MM-DD")，下拉后所有日期均显示为“2024-08-21"。

Excel是最常用的数据处理工具，也是CDA数据分析师一级的重要考点。CDA数据分析师证书，与CPA注会、CFA特许金融师并驾齐驱，其权威性与实用性不言而喻。在互联网行业中，应用数据分析是非常适配的，该行业数据量庞大、发展快。CDA数据分析师在互联网行业的数据岗中认可度非常高，一般都要求考过CDA数据分析师二级，CDA二级中包含了模型搭建的详细内容，对于数据岗的工作来说特别有帮助。

CDA数据分析师之所以备受青睐，离不开它广泛的企业认可度。众多知名企业在招聘数据分析师时，都会明确标注CDA持证人优先考虑。像是中国联通、德勤、苏宁等大型企业，更是将CDA持证人列为重点招募对象，甚至为员工的CDA考试提供补贴，鼓励他们提升数据处理与分析能力。这足以证明，CDA证书在求职过程中，能为你增添强大的竞争力，使你从众多求职者中脱颖而出。

CDA数据分析师在银行业的数据岗中认可度非常高，一般都要求考过CDA数据分析师二级，CDA二级中包含了模型搭建的详细内容，对于数据岗的工作来说特别有帮助，一些企业可以给报销考试费。