记住43个Excel函数的用法，掌握数据分析 ( 二 ）

清理处理类

数据处理之前，需要对提取的数据进行初步清洗，如清除字符串空格，合并单元格、替换、截取字符串、查找字符串出现的位置等。

• 清除字符串空格：使用Trim/Ltrim/Rtrim

• 合并单元格：使用concatenate

• 截取字符串：使用Left/Right/Mid

• 替换单元格中内容：Replace/Substitute

• 查找文本在单元格中的位置：Find/Search

09

Trim函数

Trim函数是Excel中一个文本函数，它可以去掉文本字符串中的前导空格和尾随空格。其函数语法如下：=TRIM(text)

其中，text是要去除空格的文本字符串，可以是一个引用或常量。

功能：清除掉字符串两边的空格

实例说明：

我们可以通过一些简单的实例来学习trim函数的使用，以更好地理解它的作用。

1.去除前导空格。

如下图，我们可以在B列中使用trim函数，去掉A列中字符串前面的空格。具体方法是在B2单元格中输入如下公式：=trim(A2)

然后将该公式向下填充到B5单元格，会得到如下表格：

从表格中可以看出，trim函数成功去掉了A列中的前导空格。

2.去除尾随空格

我们另外新建一个工作表，继续在B列中使用trim函数，去掉A列中字符串后面的空格。

具体方法是在B2单元格中输入如下公式：=trim(A2)

然后将该公式向下填充到B5单元格，会得到如下表格：

从表格中可以看出，trim函数成功去掉了A列中的尾随空格。

在使用trim函数时，还需要注意：

trim函数只能去除文本字符串前后的空格，不能“完全去除”中间的空格。

例如，对于字符串“abc def”，使用trim函数仅能去掉前后的空格，无法“完全去掉”中间的空格。

遇到中间有空格的数据，我们不需要使用函数，可以通过查找替换的方法去除。

10

 Trim函数

Trim是修剪、整理的意思。

在EXCEL中，TRIM函数主要用于把单元格内容前后的空格去掉，但并不去除字符之间的空格。如果单元格中有从其他程序中获得带有不规则空格的文本时，可以使用TRIM函数。

如下图所示，在B2中录入TRIM(A2),可以看到，B1中还是A1单元格内容，只是前面空格去除了，但是中间的单元格依然在。向下拉，既可以把整个A列单元格中前后无用的单元格去掉了。

11

Concatenate函数

Concatenate函数的作用是连接多个单元格的数据。合并单元格中的内容，还有另一种合并方式是&，需要合并的内容过多时，Concatenate效率更快。

语法：=Concatenate(单元格1，单元格2……)

示例：

如下图所示，在D2单元格输入公式：=CONCATENATE(A2,B2,C2)

该公式可以将A2、B2、C2三个单元格的文本连接在一起。

如果想要使用“-”连接不同单元格的文本，在D2单元格输入公式：

=CONCATENATE(A2,'-',B2,'-',C2)，使用“-”分隔A2、B2、C2单元格的文本。

Concatenate函数在连接多个单元格的文本时，参数必须是单个单元格，需要连接的多个单元格之间用逗号分隔。如果参数直接输入单元格区域，则返回错误值。

如下图所示，D2单元格的公式：=CONCATENATE(A2:C2)

Concatenate函数的参数是单元格区域A2:C2，公式返回错误值。

当需要连接的单元格较多时，Concatenate函数就不适用了。Concat函数解决了这个问题，可以直接连接单元格区域。

如下图所示，在D2单元格输入公式：=CONCAT(A2:C2)

CONCAT函数虽然可以直接连接单元格区域，但是如果需要使用分隔符，用法和CONCATENATE函数一样。

如下图所示，在D2单元格输入公式：

=CONCAT(A2,'-',B2,'-',C2)，连接A2、B2、C2单元格的文本，并且使用“-”作为分隔符。

12

TEXTJOIN函数

Textjoin函数除了可以直接连接单元格区域，还可以指定分隔符，以及选择是否忽略空单元格。

语法为：TEXTJOIN(delimiter,ignore_empty,text1,[text2],…)，

参数delimiter为指定的分隔符。

参数ignore_empty为True时，忽略空单元格；ignore_empty为False时，包含空单元格。

如下图所示，在D2单元格输入公式：=TEXTJOIN(,TRUE,A2:C2)

由于没有指定分隔符，因此textjoin函数直接连接A2:C2单元格的文本。

在如下图所示，在D2单元格输入公式：

=TEXTJOIN('-',TRUE,A2:C2)

指定分隔符“-”，连接A2:C2单元格区域的文本。

当要连接的单元格区域中有空单元格时，可以选择是否忽略空单元格。

如下图所示，要求连接A2:A7区域的文本，其中含有空单元格。

在D1单元格输入公式：=TEXTJOIN('、',TRUE,A2:A7)

参数“True”表示忽略空单元格。

在D2单元格输入公式：=TEXTJOIN('、',FALSE,A2:A7)，

参数False表示包含空单元格。

13

 Left函数

功能：从左截取字符串

语法：=Left(值所在单元格，截取长度)

示例：

在B1单元格输入=LEFT(A1,1)，结果是“数”；表明截取A1单元格字符串的第1个字符

在B2单元格输入=LEFT(A2,3)，结果是“一条鱼”；表明截取A2单元格字符串的前3个字符

在B3单元格输入=LEFT(A3)，结果是“草”；表明截取A3单元格字符串的第1个字符。

14

Right函数

功能：从右截取字符串

语法：= Right (值所在单元格，截取长度)

打开表格，在B1单元格输入=RIGHT(A1,2)，结果是“价格”；表明截取A1单元格字符串右侧的2个字符。

在B2单元格输入=RIGHT(A2)，结果是“数”；表明截取A2单元格字符串右侧的第1个字符。

15

 Mid函数

功能：从中间截取字符串

语法：= Mid（指定字符串，开始位置，截取长度）

示例1：利用MID函数，从字符串第2个字符开始，截取2个字符，结果如下图所示。

示例2：根据身份证号码提取出生年月。

16

Replace函数

Replace函数的作用是：替换掉单元格的字符串

语法为：REPLACE(old_text,start_num,num_chars,new_text)

参数1：old_text原来的文本，也就是将要进行替换的文本。
参数2：start_num开始替换的位置。
参数3：num_chars将要替换的长度。
参数4：new_text新文本，也就是替换成的文本。

示例1：

下图A列是公司商品名称，其中Apple-LO中的O应为P，需要把O替换为P。

在B2中录入公式“=REPLACE(A2,8,1,'P')”，回车，“Apple-LO-325”已经被替换成了“Apple-LP-325”。

公式中，A2是指被替换的文本所在的单元格。
8是指O位于“Apple-LO-325”中的第8个字符串的位置。
1是指需要替换从O开始起1个字符串。
“P”是指需要替换成的内容

示例2：

公司中奖客户的电话号码，为保护客户隐私，只需要显示前后各3位，中间5为我们要用*隐藏起来。

在C2中录入公式“=REPLACE(B2,4,5,'*****')，回车，函数返回结果：“135*****651”。向下复制公式，就实现了全部字符的替换。

此公式中：我们要从第４位开始替换，一共替换５位，所以第二第三参数分别为４和５。

注意：因为此函数替换的是文本，当单元格内容为数字时，需要设置为文本格式。

示例3：

有时候，replace函数还可以和其他函数结合在一起，来实现单元格部分字符的替换。例如：城市实行电话号码升级活动，将北京电话号码前加上6，上海电话号码前加上9。

在D2中录入公式：
=IF(B2='北京',REPLACE(C2,1,1,'61'),REPLACE(C2,1,1,'91'))

回车，C2单元格电话号码升级成功。向下复制公式即可替换所有字符串。

此函数的含义是：用if函数判断B2中的内容，若是北京，则用replace函数将第一位数1替换成61，也就是加上6。否则，则将一位数替换成91，也就是第一位数加上9。

17

 Substitute函数

Substitute函数属于文本查找类函数，就是查找某个字符，然后替换成别的字符。

和replace接近，不同在于Replace根据位置实现替换，需要提供从第几位开始替换，替换几位，替换后的新的文本；而Substitute根据文本内容替换，需要提供替换的旧文本和新文本，以及替换第几个旧文本等。

语法：Substitute（文本，旧文本，新文本，从第几个开始替换）。

示例1：

Substitute函数基本用法技巧。如下图，将型号进行升级，升级规则为A开始的型号，全部修改为B开始。效果如D列所示：

D2单元格公式为：=SUBSTITUTE(C2,'A','B')

也就是使用Substitute函数将“A”替换为“B”。

示例2：

Excel中substitute函数隐藏手机号码中间四位。

C2单元格公式为：=SUBSTITUTE(B2,MID(B2,4,4),'****')

用MID提取手机号码中间四位，然后用Substitute函数将其替换为四个*。

18

 Find函数

功能：查找文本位置

语法：=Find（要查找字符，指定字符串，第几个字符）

示例1：从学生家庭住址中查找文本”小区“在什么位置。

在单元格E2中输入公式：=FIND('小区',D2,1)。

实现功能：从单元格D2中的第1个字符开始，查找文本”小区“所在位置，把结果存放到单元格E2中。得到的结果是：5。

注意：Find函数区分大小写。例如：=find('a','AiHao',1)，返回的结果是4，不是1。Find函数不支持通配符，通配符被当成普通字符处理。

19

Search函数

功能：返回一个指定字符或文本字符串在字符串中第一次出现的位置,从左到右查找

语法：=search（要查找的字符，字符所在的文本，从第几个字符开始查找）

区别：Find和Search这两个函数功能几乎相同，实现查找字符所在的位置，区别在于Find函数精确查找，区分大小写；Search函数模糊查找，不区分大小写。

示例：如下图，输入公式=SEARCH(B2,A2)

注意：空格也算一个字符，函数仅查询第一个符合条件的字符，并返回所在位置。

20

Len函数

功能：文本字符串的字符个数

语法：LEN(文本)

示例：输入函数【=LEN(A2)】，就可以计算A2单元格的数据长度。

21

 Lenb函数

功能：返回文本中所包含的字符数（中文汉字算2个字符）

语法：LENB(文本)

LENB与LEN函数的区别在于：LENB统计汉字的字数是=时按照每个汉字2个字符算，而LEN函数是把汉字统计为1个。

如下图：

运用示例：如下图，提取A列的汉字。

公式分析：利用LENB与LEN函数的区别，找出A列单元格的汉字的字数，结合LEFT函数，从左到右提取相应数量的字符，就是所需要提取的汉字。

以上是43个常用函数分类的第二类，接下来将陆续介绍其他类别。本系列的函数介绍，希望能够帮助到大家！

END

---

更多推荐

推荐两个冷门且有用的函数，值得收藏！关于SUMIF函数的另类用法一分钟提取身份证号码信息学会这个新函数PERCENTILESUMPRODUCT函数求和