第2章
基础函数：打好基础，轻松实现数据处理

2.1　文本函数

2.1.1　RIGHT：从右侧提取字符函数

公式：=RIGHT(text,num_chars)

要注意的知识点

第1点：RIGHT函数用于从单元格的右侧提取字符。

第2点：此函数有两个参数。第1参数：从哪个单元格中提取字符；第2参数：提取第几个字符。

第3点：如果第2参数是1，则可以省略。

案例01　使用RIGHT函数提取单元格中的字符

案例及公式如下图所示。这里要从A1单元格中把“教育”两个字提取出来。

公式

=RIGHT(A1,2)

公式解释

第1参数：A1，即从A1单元格中提取字符。

第2参数：2，即从单元格的右侧提取两个字符。

本案例视频文件：02/案例01 使用RIGHT函数提取单元格中的字符

2.1.2　LEFT：从左侧提取字符函数

公式：=LEFT(text,num_chars)

要注意的知识点

第1点：LEFT函数用于从单元格的左侧提取字符。

第2点：此函数有两个参数。第1参数：从哪个单元格中提取字符；第2参数：提取第几个字符。

第3点：如果第2参数是1，则可以省略。

案例02　使用LEFT函数提取单元格中的字符

案例及公式如下图所示。这里要从A1单元格中把“完美”两个字提取出来。

公式

=LEFT(A1,2)

公式解释

第1参数：A1，即从A1单元格中提取字符。

第2参数：2，即从单元格的左侧提取两个字符。

本案例视频文件：02/案例02 使用LEFT函数提取单元格中的字符

2.1.3　MID：从中间提取字符函数

公式：=MID(text,start_num,num_chars)

要注意的知识点

第1点：MID函数的作用是从单元格的中间提取字符。

第2点：此函数有3个参数。第1参数：从哪个单元格中提取字符；第2参数：从哪个位置开始提取字符；第3参：提取第几个字符。

案例03　使用MID函数提取单元格中的字符

案例及公式如下图所示。这里要从A1单元格中把“在线”两个字提取出来。

公式

=MID(A1,3,2)

公式解释

第1参数：A1，即从A1单元格中提取字符。

第2参数：3，即从第3个位置开始提取字符。

第3参数：2，即提取两个字符。

本案例视频文件：02/案例03 使用MID函数提取单元格中的字符

2.1.4　LEN：计算文本长度函数

公式：=LEN(text)

要注意的知识点

第1点：LEN函数用于统计单元格中字符的个数，不分单/双字节。

第2点：此函数只有一个参数。

案例04　使用LEN函数统计单元格中有多少个字符

案例及公式如下图所示。这里要统计A1单元格中有多少个字符。

公式

=LEN(A1)

公式解释

第1参数：A1，即要统计的单元格。在A1单元格中，“完美”是两个汉字，“JY”是两个字母，“141797”是6个数字，统计结果为共有10个字符。

本案例视频文件：02/案例04 使用LEN函数统计单元格中有多少个字符

2.1.5　LENB：文本长度计算函数（区分单/双字节）

公式：=LENB(text)

要注意的知识点

第1点：LENB函数的作用是统计单元格中字符的个数。

第2点：此函数只有一个参数。

第3点：此函数区分单/双字节，在英文半角状态下，1个汉字算2个字节；1个字母算1个字节；1个数字算1个字节。而LEN函数不区分单/双字节。

案例05　使用LENB函数统计单元格中共有几个字符

案例及公式如下图所示。这里要统计A1单元格中共有几个字符。

公式

=LENB(A1)

公式解释

在A1单元格中，“完美”两个字算4个字符；“A”算1个字符；“9”算1个字符，所以共有6个字符。

本案例视频文件：02/案例05 使用LENB函数统计单元格中共有几个字符

案例06　使用LENB函数提取单元格中左边的汉字

案例及公式如下图所示。这里要提取A1单元格中左边的汉字。

公式

=LEFT(A2,LENB(A2)-LEN(A2))

公式解释

LENB(A2)-LEN(A2)用于计算汉字的个数，因为使用LENB函数统计时，汉字算2个字符，数字算1个字符，而使用LEN函数统计时数字和汉字都算1个字符，因此LENB(A2)-LEN(A2)就是将数字抵消了，而2倍的汉字的字符个数减掉1倍的汉字的字符个数，剩下的就是1倍的汉字的字符个数，也就是汉字的个数。

本案例视频文件：02/案例06 使用LENB函数提取单元格中左边的汉字

案例07　使用LENB函数提取单元格中右边的数字

案例及公式如下图所示。这里要提取A1单元格中右边的数字。

公式

=RIGHT(A2,2*LEN(A2)-LENB(A2))

公式解释

此函数是把所有的字符当作汉字，也就是将字符个数放大两倍：2*LEN(A2)，如果A2单元格中有28个字符，而LENB函数只是将字符个数放大了2倍，数字个数没有被放大，因此2倍的字符个数减去2倍的字符个数等于零，而2倍的数字个数减去1倍的数字个数得到的就是数字的个数。

本案例视频文件：02/案例07 使用LENB函数提取单元格右边的数字

2.1.6　MIDB：从指定位置提取字符函数（区分单/双字节）

公式：=MIDB(text,start_num,num_bytes)

要注意的知识点

第1点：MIDB函数有3个参数，第1参数表示从哪一个字符串或者单元格中提取字符；第2参数表示从第几个位置开始提取；第3参数表示提取几个字符。

第2点：此函数区分单/双字节，即1个汉字算2个字节，1个字母算1个字节，1个数字算1个字节。

案例08　使用MIDB函数从单元格中提取指定的字符

案例及公式如下图所示。这里要从A1单元格中提取指定的字符。

公式

=MIDB(A1,11,2)

公式解释

第1参数：从A1单元格中提取字符；第2参数：从第11个字符开始提取，“王氏镜片”算8个字符，而“VZ”算2个字符，所以从第11个字符（即“4”）开始提取；第3参数为2，即提取2个字符，两个数字算两个字符，所以结果为“45”。

本案例视频文件：02/案例08 使用MIDB函数从单元格中提取指定的字符

2.1.7　SEARCH：不区分大/小写、支持通配符的查找函数

公式：=SEARCH(find_text,within_text,start_num)

要注意的知识点

第1点：SEARCH函数用于确定某个字符或文本字符串在另一个文本字符串中的位置。

第2点：此函数共有3个参数。第1参数：查找值；第2参数：被查找的字符串；第3参数：从哪个位置开始查找。

第3点：如果从第1个字符开始查找，则第3参数可以不写，只写前两个参数。

第4点：不区分英文的大/小写。

第5点：支持通配符用法。

案例09　使用SEARCH函数查找指定字符的位置

案例及公式如下图所示。这里要从A1单元格中查找“佛山小老鼠”中“老”字的位置。

公式

=SEARCH("老",A1,1)

公式解释

第1参数：查找汉字“老”。

第2参数：从单元格A1中查找。

第3参数：从第1个位置开始查找。

结果返回4，因为“老”字在第4个字符位置。由于第3参数是1，则此公式也可以写成=SEARCH("老",A1)。

本案例视频文件：02/案例09 使用SEARCH函数查找指定字符的位置

2.1.8　SEARCHB：查找指定字符位置函数（区分单/双字节）

公式：=SEARCHB(find_text,within_text,start_num)

要注意的知识点

SEARCHB函数和SEARCH函数的用法基本一样，区别在于SEARCH函数区分单/双字节。

案例10　使用SEARCHB函数提取汉字中间的数字

案例及公式如下图所示。这里要从A列的单元格中提取汉字中间的数字。

公式

=MIDB(A1,SEARCHB("?",A1),2*LEN(A1)-LENB(A1))

公式解释

SEARCHB("?",A1)：找到A1单元格中第1个数字出现的位置，“?”通配符代表任意一个单字节。

2*LEN(A1)-LENB(A1)：得到数字的个数。

本案例视频文件：02/案例10 使用SEARCHB函数提取汉字中间的数字

2.1.9　FIND：区分大/小写、不支持通配符的查找函数

公式：=FIND(find_text,within_text,start_num)

要注意的知识点

第1点：FIND函数用于查找字符的位置。

第2点：此函数共有3个参数。第1参数：查找值；第2参数：被查找的字符串；第3参数：从哪个位置开始查找。

第3点：如果从第1个位置开始查找，则第3参数可以不写，只写前面两个参数即可。

第4点：区分英文的大/小写。

第5点：不支持通配符用法。

第6点：和SEARCH函数的用法类似。

案例11　使用FIND函数从字符串中查找某个字符串所在的位置

案例及公式如下图所示。这里要从A1单元格中查找字符“佛山”所在的位置。

公式

=FIND("佛山",A1)

公式解释

第1参数：查找值为“佛山”。

第2参数：从A1单元格中查找。

第3参数：省略了，因为是1。

结果返回6，因为前面有5个汉字，这里把“佛山”当作一个整体。

本案例视频文件：02/案例11 使用FIND函数从字符串中查找某个字符串所在的位置

2.1.10　ASC：将全角双字节字符转换为半角单字节字符函数

公式：=ASC(text)

要注意的知识点

第1点：ASC函数用于把全角双字节的字符转换为半角单字节的字符，有时一些单字节字符是在全角状态下输入的，从而就变成了双字节字符，而ASC函数可以把它还原成单字节字符。

第2点：汉字经过处理之后，还是双字节的。

第3点：此函数有一个参数。

案例12　使用ASC函数把逗号转换为单字节字符

案例及公式如下图所示。这里要把A1单元格中的“在线，教育”中的逗号转换为单字节字符。

公式

=LENB(A1)

=LENB(ASC(A1))

公式解释

没有经过ASC函数处理，A1单元格中的逗号算双字节字符，经过ASC函数处理之后，此逗号变为单字节字符。因为1个汉字算2个字符，所以=LENB(A1)返回10；而=LENB(ASC(A1))返回9。

本案例视频文件：02/案例12 使用ASC函数把逗号转换为单字节字符

2.1.11　WIDECHAR：把单字节字符转换为双字节字符函数

公式：=WIDECHAR(text)

要注意的知识点

第1点：WIDECHAR函数用于把单字节字符转换为双字节字符。

第2点：此函数只有1个参数。

案例13　使用WIDECHAR函数把单字节字符转换成双字节字符

案例及公式如下图所示。这里要把A1单元格中的“在线, 12教育”转换成双字节字符。

公式

=WIDECHAR(A1)

公式解释

此函数只有一个参数，即要处理的字符串。

本案例视频文件：02/案例13 使用WIDECHAR函数把单字节字符转换成双字节字符

2.1.12　CHAR：将数字转换为字符函数

公式：=CHAR(number)

要注意的知识点

第1点：CHAR函数的作用就是将数字转换为字符。

第2点：此函数只有1个参数，即数字。

案例14　使用CHAR函数自动填充26个大写字母

案例及公式如下图所示。这里要在A行中填充26个大写字母。

公式

=CHAR(COLUMN()+64)

公式解释

COLUMN函数中没有参数，表示公式在哪一个单元格中就返回哪一个单元格的列号。当公式在A1单元格中时，返回1，然后1+64等于65，而65对应的是字母A。当公式在B1单元格中时，返回2，2+64=66，而66对应的是字母B，依此类推。

本案例视频文件：02/案例14 用CHAR函数自动填充26个大写字母

2.1.13　CODE：将字符转换为数字函数

公式：=CODE(text)

要注意的知识点

第1点：CODE函数用于根据字符返回ASCII码。一个字符对应一个数字编码。

第2点：此函数只有1个参数，即要处理的字符。

案例15　使用CODE函数求字母A的ASCII码

案例及公式如下图所示。这里要求字母A的ASCII码是多少。

公式

=CODE(A1)

公式解释

CODE函数和CHAR函数是一对“姐妹函数”。在此案例中，A对应的ASCII码是65。

本案例视频文件：02/案例15 使用CODE函数求字母A的ASCII码

2.1.14　UPPER：将小写字母转换为大写字母函数

公式：=UPPER(text)

要注意的知识点

第1点：UPPER函数用于把小写字母转换为大写字母。

第2点：此函数只有一个参数，即要处理的字母。

案例16　使用UPPER函数把字母由小写转换为大写

案例及公式如下图所示。这里要把A1单元格中的字母由小写转为大写。

公式

=UPPER(A1)

公式解释

UPPER函数：把小写字母转换为大写字母。

LOWER函数：把大写字母转换为小写字母。

PROPER函数：把每一个单词的首字母转换为大写字母。

后两个函数在这里就不举例了，读者可以自己动手测试。

本案例视频文件：02/案例16 使用UPPER函数把字母由小写转换为大写

2.1.15　REPLACE：查找和替换函数

公式：=REPLACE(old_text,start_num,num_chars,new_text)

要注意的知识点

第1点：REPLACE函数为查找和替换函数。

第2点：此函数用于把一个字符串替换成另一个字符串。

第3点：此函数有4个参数。第1参数：在哪个字符串中进行查找和替换；第2参数：从哪个位置开始查找；第3参数：替换几个字符；第4参数：替换成新的字符串。

第4点：当第3参数省略时，就相当于插入字符串。

案例17　使用REPLACE函数把银行卡号每隔4位加一个空格

案例及公式如下图所示。这里要把A1单元格中的银行卡号每隔4位加一个空格。

公式

=REPLACE(REPLACE(REPLACE(REPLACE(A1,5,," "),10,," "),15,," "),20,," ")

公式解释

最内层的REPLACE函数的第1参数是A1；第2参数是5，所以从第5个字符开始查找；第3参数省略，相当于插入字符串的作用；第4参数为空格。其他REPLACE函数的参数含义依此类推。

本案例视频文件：02/案例17 使用REPLACE函数把银行卡号每隔4位加一个空格

2.1.16　TEXT：格式转换函数

公式：=TEXT(value,format_text)

要注意的知识点

第1点：TEXT函数用于根据第1参数的值显示第2参数的格式。

第2点：此函数有两个参数。第1参数：要处理的数据；第2参数：要显示的格式。

第3点：第2参数有4节：第1节为正数；第2节为负数；第3节为零；第4节为文本，中间用分号分隔。

案例18　使用TEXT函数将0值屏蔽且保留1位小数

案例及公式如下图所示。这里要把A列单元格中的0值屏蔽且保留1位小数。

公式

=TEXT(A2,"0.0;-0.0;")

公式解释

第2参数共有3节：第1节为正数——0.0，保留1位小数；第2节为负数—— -0.0，也是保留1位小数；第3节什么也不显示，为空格。

本案例视频文件：案例18使用TEXT函数将0值屏蔽且保留1位小数

案例19　使用TEXT函数计算2008-8-8是星期几

案例及公式如下图所示。

公式

=TEXT(A2,"AAAA")

公式解释

第2参数为AAAA，表示中文的“星期”。

第2参数为AAA，表示数字，如星期五，只显示“五”。

第2参数为DDDD，表示英文的星期几。

第2参数为DDD，表示英文的星期几的前3个字母，如Monday，只显示Mon。

本案例视频文件：02/案例19 用TEXT函数计算2008-8-8是星期几

案例20　使用TEXT函数根据分数判断成绩等级

案例及公式如下图所示。其中当分数小于60时为不及格；当分数大于或等于60且小于70时为及格；当分数大于或等于70且小于80时为良好，当分数大于或等于80时为优秀。

当然这个问题也可以用IF或者LOOKUP、VLOOKUP、HLOOKUP等函数求解。

公式

=TEXT(TEXT(A2,"[<60]不及格;[<70]及格;0"),"[<80]良好;优秀")

公式解释

这里应用了TEXT函数嵌套，也就是说，最外面的TEXT函数的第1参数为一个TEXT函数。

嵌套的TEXT函数中有两个条件：返回不及格和及格的结果，剩下的大于或等于70的数值全放在第3节上，第3节只用了一个0作为占位符，然后由第1个TEXT函数进行处理。

最外面的TEXT函数的第2参数设置了小于80的数值显示为“良好”，剩下的大于或等于80的数值显示为“优秀”。

本案例视频文件：02/案例20 用TEXT函数根据分数判断成绩等级

案例21　使用TEXT函数将大于100的数值显示为100，将小于或等于100的数值显示为其本身

案例及公式如下图所示。这里用TEXT函数将大于100的数值显示为100，将小于或等于100的数值显示为其本身。

公式

=TEXT(A1,"[>100]1!0!0;0")

公式解释

这里的TEXT函数的第2参数有两节，第1节设置了条件，不满足条件的放在第2节中。

为什么第1节显示100还要在它前面加两个0？这是强制显示0，否则就会把0当作数字占位符了。

本案例视频文件：02/案例21 使用TEXT函数将大于100的数值显示为100，将小于或等于100的数值显示为其本身

2.1.17　T：检测给定值是否为文本函数

公式：=T(value)

要注意的知识点：

第1点：T函数用于判断单元格中的值是否是文本，如果是文本，则返回文本本身，如果是数值，则返回空值。

第2点：日期、时间都是数值。

第3点：T函数也有降维的作用，返回的是文本值。

案例22　使用T函数实现只保留文本

案例及公式如下图所示。这里只保留A列中的文本。

公式

=T(A1)

公式解释

因为A2单元格中的“100”是文本，所以保留此数值。

A6单元格中的是时间，A7单元格中的是日期，由于时间和日期都是数字，所以这里显示为空值。

本案例视频文件：02/案例22 使用T函数实现只保留文本

2.1.18　TRIM：清除空格字符专用函数

公式：=TRIM(text)

要注意的知识点

第1点：TRIM函数用于清除单元格中两端的空格，中间的空格不清除。

第2点：此函数只有一个参数，即要处理的单元格。

案例23　使用TRIM函数清除单元格两端的空格

案例及公式如下图所示。这里要清除A1单元格两端的空格。

公式

=TRIM(A1)

公式解释

这里使用A1单元格作为参数，将“大”字前面的空格去掉，将最后一个“好”字后面的空格也去掉。

本案例视频文件：02/案例23 使用TRIM函数清除单元格两端的空格

2.1.19　SUBSTITUTE：按值替换函数

公式：=SUBSTITUTE(text,old_text,new_text,instance_num)

要注意的知识点：

第1点：SUBSTITUTE函数共有4个参数：第1参数：要处理的文本；第2参数：被替换的字符；第3参数：要替换成的字符；第4参数：替换第几个字符。

第2点：如果将字符全部替换，那么第4参数可以省略。

案例24　使用SUBSTITUTE函数统计单元格中有多少个“c”

案例及公式如下图所示。这里要统计A1单元格中有多少个“c”。

公式

=LEN(A1)-LEN(SUBSTITUTE(A1,"c",""))

公式解释

在SUBSTITUTE函数中，第1参数：A1；第2参数：“c”；第3参数：“”；第4参数：省略，也就是全部替换。

此公式把A1单元格中的“c”替换成空值，再用LEN函数统计字符的个数。

当字母“c”没有被替换成空值时，用LEN函数统计字符的个数。

用替换前字符的个数减去替换后空值的个数，就得到了字母“c”的个数。

本案例视频文件：02/案例24 使用SUBSTITUTE函数统计单元格中有多少个“c”