CDA数据分析师 出品
作者：李诗怡

一、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" 千位分隔符。

二、7个核心关联匹配函数

1. VLOOKUP：按行跨区域找数据（最常用）

• 作用：在指定的表格或数据区域的第一列里找目标值，找到后返回同一行指定列的数据。特别适合通过唯一标识，在不同表格里查找信息，比如用ID查员工信息、用编号查价格。
• 公式写法：=VLOOKUP(要找的值, 包含要找值的区域, 要返回数据所在的列数, 精确匹配还是近似匹配)

2. LOOKUP：多场景数据匹配工具（支持单行/单列和多行/多列）

• 作用：有两种用法，一种是在单行或单列里找数据（向量形式），另一种是在多行多列里找数据（数组形式）。功能和 VLOOKUP 差不多，但支持数组匹配和二分法查找。不过日常用 VLOOKUP 更简单好上手。
• 公式写法：

  • 向量形式（推荐）：=LOOKUP(要找的值, 查找区域, [结果区域])（查找区域和结果区域都得是单行或单列）
  • 数组形式（不推荐）：=LOOKUP(要找的值, 数据区域)（数据区域里的数据要按从小到大排序，功能有限，和其他表格软件兼容时可以用）

3. INDEX：按行列位置取数据

• 作用：只要知道数据在表格或数组里的行号和列号，就能精准取出对应位置的数据。经常和 MATCH 函数搭配使用，先找到数据的位置，再提取数据，实现动态查询。
• 公式写法：

  • 数组形式（常用）：=INDEX(数据区域, 行号, [列号])（数据区域是目标数据所在范围，行号和列号不能都不填）
  • 引用形式：=INDEX(引用区域, 行号, [列号], [区域序号])（引用区域包含多个数据区域，区域序号用来指定从哪个区域取数据）

4. MATCH：查找数据的位置

• 作用：在单行或单列区域里找某个数据，返回它在这个区域里是第几行或第几列（不返回具体数据），主要用来定位数据位置，经常和 INDEX 函数搭配使用。
• 公式写法：=MATCH(要找的值, 查找区域, [匹配方式])

5. ROW：快速获取行号

• 作用：返回指定单元格或区域所在的行号，如果不指定，就返回写公式的那个单元格的行号。经常用来批量生成序号，或者在公式里动态引用行数据。
• 公式写法：=ROW([单元格或区域])（不写单元格或区域的话，就默认返回公式所在单元格的行号）

6. COLUMN：快速获取列号

• 作用：和ROW函数类似，用来返回指定单元格或区域所在的列号，如果不指定，就返回写公式的那个单元格的列号。常用来批量生成列标识，或者在公式里动态引用多列数据。
• 公式写法：=COLUMN([单元格或区域])（不写单元格或区域的话，就默认返回公式所在单元格的列号）

7. OFFSET：动态引用数据区域

• 作用：以某个单元格为起点，按照设定的行数和列数移动，得到一个新的单元格或数据区域。最大的优势是动态调整，当起点区域的数据增加或减少时，引用的范围也会跟着变，很适合做动态图表和数据汇总。
• 公式写法：=OFFSET(起点单元格或区域, 向下/向上移动的行数, 向右/向左移动的列数, [返回区域的行数], [返回区域的列数])

  • 起点单元格或区域：作为移动起点的单元格或数据区域，比如A1
  • 向下/向上移动的行数：正数表示向下移动，负数表示向上移动
  • 向右/向左移动的列数：正数表示向右移动，负数表示向左移动
  • 返回区域的行数/列数（可填可不填）：移动后新区域的行数和列数，如果不填，就和起点区域的行数、列数一样

三、16个核心计算统计类函数

1. SUM：基础数据求和

• 作用：对单个值、单元格引用或单元格区域中的数值进行求和，是 Excel 中最基础的汇总函数，适用于所有需要累加的场景。
• 语法：=SUM(数值1/单元格1/区域1, 数值2/单元格2/区域2, ...)（最多可包含 255 个参数）

2. SUMIF：单条件数据求和

• 作用：对满足指定单个条件的单元格区域进行求和，适用于按一个规则筛选后汇总的场景。
• 语法：=SUMIF(条件判断区域, 条件, [需要求和的区域])
  注：如果条件判断区域与需要求和的区域一致，可省略第三个参数

3. SUMIFS：多条件数据求和

• 作用：对同时满足多个条件的单元格区域进行求和，是 SUMIF 的升级版本，适用于复杂的多规则筛选汇总场景。
• 语法：=SUMIFS(需要求和的区域, 条件1判断区域, 条件1, 条件2判断区域, 条件2, ...)
  （注：参数顺序固定，先写"求和区域"，再依次写"条件区域 + 条件"）

4. SUMPRODUCT：多列数据乘积求和

• 作用：先对多个对应区域的单元格进行乘积运算，再将所有乘积结果求和，特别适用于 数量 × 单价 = 总价，销量 × 提成比例 = 提成金额等场景。
• 语法：=SUMPRODUCT(区域1, 区域2, [区域3], ...)

5. COUNT：基础数据计数

• 作用：统计指定区域内数值型数据的单元格个数，不包含文本、空白单元格或逻辑值 TRUE/FALSE，适用于统计有效数值数量的场景。
• 语法：=COUNT(单元格1/区域1, 单元格2/区域2, ...)

6. COUNTIF：单条件数据计数

• 作用：统计指定区域内满足单个条件的单元格个数，适用于按一个规则筛选后计数的场景，如统计某分数段的人数、某类商品的数量。
• 语法：=COUNTIF(条件判断区域, 条件)

7. COUNTIFS：多条件数据计数

• 作用：统计指定区域内同时满足多个条件的单元格个数，是 COUNTIF 的升级版本，适用于多规则筛选计数场景。
• 语法：=COUNTIFS(条件1判断区域, 条件1, 条件2判断区域, 条件2, ...)

8. MAX：查找数据最大值

• 作用：从指定区域或数值中找出最大的数值，适用于快速定位极值，如找出最高销售额、最高分、最大订单量等。
• 语法：=MAX(数值1/单元格1/区域1, 数值2/单元格2/区域2, ...)

9. MIN：查找数据最小值

• 作用：从指定区域或数值中找出"最小的数值"，与 MAX 功能相反，适用于定位最低值，如最低销售额、最低分、最小库存等。
• 语法：=MIN(数值1/单元格1/区域1, 数值2/单元格2/区域2, ...)

10. RANK：数据排名

• 作用：返回指定数值在指定区域中的排名，默认从大到小排序，若存在相同数值，会返回相同排名，适用于业绩排名、成绩排名等场景。
• 语法：=RANK(需要排名的数值/单元格, 排名参考区域, [排名方式])
• 重复值跳号问题：RANK 对相同数值返回相同排名，但会跳过后续名次（如两个第2名，下一名直接为第4名）。Excel 新版本提供了两个替代函数：

  • RANK.EQ：与 RANK 行为一致，相同数值取最高名次，跳号
  • RANK.AVG：相同数值取平均名次（如两个并列第2名会显示为2.5），不跳号但名次可能为小数
    （注：排名方式为可选参数，0 或省略代表降序排名，1 代表升序排名）

11. RAND：生成 0-1 随机小数

• 作用：无需参数，随机生成一个大于等于 0 且小于 1 的小数，每次刷新表格时会重新生成新的随机数，适用于生成随机样本、模拟概率等场景。
• 语法：=RAND()

12. RANDBETWEEN：生成指定范围随机整数

• 作用：生成介于两个指定整数之间的随机整数，解决 RAND 只能生成 0-1 小数的局限，适用于生成随机 ID、随机分配组别等场景。
• 语法：=RANDBETWEEN(最小值, 最大值)

13. AVERAGE：计算数据平均值

• 作用：计算指定区域或数值的算术平均值，适用于统计平均成绩、平均销售额、平均客单价等场景。
• 语法：=AVERAGE(数值1/单元格1/区域1, 数值2/单元格2/区域2, ...)

14. SUBTOTAL：多功能汇总函数

• 作用：一个函数实现求和、计数、平均值、最大值、最小值等 11 种汇总功能，并且能手动忽略隐藏行的数据（不会忽略隐藏列），适用于需要灵活切换汇总方式或处理筛选后数据的场景。
• 语法：=SUBTOTAL(汇总方式代码, 区域1, [区域2], ...)
• 常用汇总方式代码（部分）：

15. INT：数值向下取整

• 作用：将数值向下取整为最接近的整数，即不大于该数值的最大整数，适用于需要舍弃小数部分的场景，如计算可购买商品的整数数量、员工实际出勤天数。
• 语法：=INT(数值/单元格)

16. ROUND：数值四舍五入

• 作用：将数值按指定小数位数进行四舍五入，适用于需要保留固定小数位数的场景，如金额保留 2 位小数、百分比保留 1 位小数。
• 语法：=ROUND(需要四舍五入的数值/单元格, 保留的小数位数)
  （注：保留小数位数为正数时保留对应小数位，为0时取整，为负数时对整数部分四舍五入）

四、5类核心逻辑运算函数

1. IF：基础条件判断（最常用）

• 作用：根据指定条件的真（TRUE）或假（FALSE），返回两种不同的结果，是逻辑运算中最基础、应用最广泛的函数，可实现单条件或多条件嵌套判断。
• 语法：

  • 单条件：=IF(判断条件, 条件为真时返回的结果, 条件为假时返回的结果)
  • 多条件嵌套：=IF(判断条件1, 结果1, IF(判断条件2, 结果2, 结果3))（可根据需求无限嵌套，建议不超过3层，避免逻辑混乱）

2. AND：多条件与判断（必须全满足）

• 作用：对多个判断条件进行与运算，只有所有条件均为真 TRUE时，最终结果才为真 TRUE；只要有一个条件为假 FALSE，结果即为假 FALSE。常与 IF 函数搭配，实现多条件筛选或判断。
• 语法：=AND(条件1, 条件2, ..., 条件30)（最多支持 30 个条件）

3. OR：多条件或判断（满足一个即可）

• 作用：对多个判断条件进行或运算，只要有一个条件为真 TRUE，最终结果就为真 TRUE；只有所有条件均为假 FALSE时，结果才为假 FALSE。同样常与 IF 函数搭配，适用于满足任一条件即可的场景。
• 语法：=OR(条件1, 条件2, ..., 条件30)（最多支持 30 个条件）

4. IS 函数：数据类型/状态验证（排查异常）

IS 函数是一组验证型函数，主要用于判断单元格内容的类型或状态，比如是否为空、是否为数字、是否存在错误值等，若符合指定条件则返回 TRUE，否则返回 FALSE。常用于数据清洗中的异常排查，确保数据格式合规。

常用 IS 函数及功能

• 语法（通用）：=IS函数(需要验证的单元格/内容)
• 注意事项：

  • ISBLANK 仅判断完全空白的单元格，若单元格中有空格、换行符等不可见字符，会被视为非空白，需结合 TRIM 函数先清理内容。
  • ISNUMBER 会将日期判定为数字，因为 Excel 中日期以序列号存储，若需区分日期和普通数字，需结合 TEXT 函数进一步判断。

推荐学习书籍 《CDA一级教材》适合CDA一级考生备考，也适合业务及数据分析岗位的从业者提升自我。完整电子版已上线CDA网校，累计已有10万+在读~ !

免费加入阅读：https://edu.cda.cn/goods/show/3151?targetId=5147&preview=0