购买
下载掌阅APP,畅读海量书库
立即打开
畅读海量书库
扫码下载掌阅APP

第9章
文本函数综合实战

前面几章的函数已经讲完了,下面我们将它们组装在一起使用。

我们回忆一下前面几章讲了哪些函数?

(1)提取字符函数左、中、右:LEFT、MID、RIGHT。

(2)提取倒数第几位字符:LEFT+RIGHT的组合。

(3)替换字符串中的某些内容。

替换指定字符:SUBSTITUTE。

替换位置:REPLACE。

(4)要找到某固定字符。

找到:FIND。

寻找:SEARCH。

想想它们之间微小的差异。

(5)经典组合:FIND+SUBSTITUTE,由此可以找到第n个指定字符。

(6)长度的概念,字符与字节的差异:LEN、LENB。

(7)零散而简单的几个函数:CELL、REPT、TRIM、CHAR、CODE、NUMBERSTRING。

以上所讲过的最难的部分,非FIND+SUBSTITUTE莫属,如果将该函数理解透了,本篇部分80%的内容就掌握了。

9.1 案例:使用一个公式提取不同段的数字

如图9-1所示,将A列的数据以“*”分段,分别提取到B、C、D列中。

图9-1 分段提取数字数据源

我们只要在B4单元格写下一个公式,然后向右、向下复制就可以完成提取。

提示

这一部分内容难度并不高,不属于必知必会范畴。如果上论坛求助类似问题,一定会看到下面将要讲的经典公式组合。

接下来采取分步操作。

首先,在H4单元格中输入公式“=SUBSTITUTE(A4,"*",REPT("",99))”,用99个空格替换“*”,这样就把每一段数字之间的间距拉大了,得到结果:

在I4单元格中输入公式“=MID(H4,1,99)”,得到结果:

在J4单元格中输入公式“=MID(H4,1+99,99)”,得到结果:

在K4单元格中输入公式“=MID(H4,1+99+99,99)”,得到结果:

将H4:K4单元格区域的公式向下复制到H6:K6单元格区域,图9-2所示为目前得到的辅助列效果。

图9-2 分段提取数字辅助列

至此,离最后结果只差清除空格了。什么函数能清除空格?TRIM函数!于是:

I4单元格公式改为“=TRIM(MID(H4,1,99))”;

J4单元格公式改为“=TRIM(MID(H4,1+99,99))”;

K4单元格公式改为“=TRIM(MID(H4,1+99+99,99))”。

这样就可以得到E4:G4单元格区域的效果。

公式中的99是做什么的?用99个空格替换星号“*”,就是为了拉大每段数字之间的距离。

这里为什么要写99,能不能写其他的数字呢?当然可以,99、100、999都可以,只要数值足够大。但是习惯上写99,参考6.2节用FIND+SUBSTITUTE函数的思路作为这道题的解释。

每段数字都足够大了,于是从第一个字符开始提取,公式为“=MID(H4,1,99)”,这里的99是由最开始的REPT("",99)决定的,保证提取的长度不会超出空格的长度,同时也不会因为提取长度过短造成结果偏差。

第二段的提取公式为“=MID(H4,1+99,99)”,1+99是因为第一段提取时,已经提取了99个字符,那第二段就从99个字符之后,即99+1的位置开始提取,同时长度为99。

第三段的提取公式为“=MID(H4,1+99+99,99)”,同样的道理,前两段各提取了99个字符,于是从第99+99+1位置开始提取,长度仍为99字符。

这里想清楚了,整个公式就搞定了。现在的难点是怎样把这三段提取合并为一个公式。

仔细观察,“=MID(H4,1,99)”“=MID(H4,1+99,99)”“=MID(H4,1+99+99,99)”的差异就是MID函数的第2个参数,依次为1、1+99、1+99+99,进一步转变为1+99*0、1+99*1、1+99*2。找到了相似的结构,就实现了数字的单纯递增。

于是I4单元格的公式变为“=MID($H4,1+99*(COLUMN(A:A)-1),99)”。

现在将H4单元格的公式整合进去,并在外面套上TRIM函数:

将这个公式放在B4单元格,并向下向右复制到B4:D6单元格区域,图9-3所示为最终的结果。

图9-3 分段提取数字最终公式

有时看到其他人给的公式会有些变化,变化主要在于1+99*(COLUMN(A:A)-1)这部分,利用小学的数学知识,如乘法分配律、加法交换律等,1+99*(COLUMN(A:A)-1)=1+99*COLUMN(A:A)-99*1=99*COLUMN(A:A)-98,于是公式进一步简化为:

9.2 案例:提取工作簿、工作表的路径及名称

在8.1节讲CELL函数时,说到用公式CELL("filename",$A$1)获取当前工作簿的信息,然而这个信息中的路径、工作簿名、工作表名都是在一起的,并未分开。现在用同一种思路将这三段分别提取。

首先,CELL("filename",$A$1)的结果为“E:\写书\函数100例V3\课件\第二章-文本函数\[第5课文本函数综合实战.xlsx]文本函数综合实战”。

然后,把左中括号和右中括号分别替换为99个空格,并进行相应提取。

提取路径的公式为:

提取工作簿名的公式为:

提取工作表名的公式为:

图9-4 所示为最终的提取结果。

图9-4 提取工作簿相关信息

所有公式都有一个套路,将中括号替换为99个空格。具体的含义不讲了,自己慢慢体会。提取工作簿信息的公式并不唯一,可以充分利用本章所讲过的内容,写出多种解决思路。例如,提取路径:

实战练习

1 数码字填写:如练习图2-1所示,在B2:M8单元格区域,使用以MID函数为核心编写公式,将数字填写至相应的位置,完成与第5章数码字填写的相同效果。

练习图2-1数码字填写

2 如练习图2-2所示,分段提取中文和英文。

(1)使用LEN与LENB的思路。

(2)使用SEARCHB结合通配符的思路。

练习图2-2提取中文和英文

3 如练习图2-3所示,根据E列的要求,完成对A列字符串相应位置的字符提取。

练习图2-3提取指定位置字符

4 如练习图2-4所示,完成复杂数字提取。

(1)使用TRIM+MID+SUBSTITUTE+REPT+COLUMN的思路分段提取数字,并对提取的数字进行乘积计算。

(2)在B8单元格中尝试用一个公式直接得到乘积结果,并向下复制到B10单元格。

提示

乘积函数为PRODUCT,其语法和使用方式与SUM函数一致;数组公式需按【Ctrl+Shift+Enter】组合键结束。

练习图2-4复杂数字提取

5 如练习图2-5所示,使用CELL函数,提取本工作簿标题中的章节号。

附注:章节号在最后一条短横线“-”和英文点号“.”之间。

练习图2-5提取章节号 2fTHN8yAMtXj4YXrSGU92Po6+/YxU5wCyS6z+Njra0olS6Fz7UtvIvAAOeDP0dk1

点击中间区域
呼出菜单
上一章
目录
下一章
×