<pre id='iiijjwq'><tfoot id='iiijjwq'></tfoot></pre>
  • <center id='iiijjwq'></center>
      <p id='iiijjwq'><tt id='iiijjwq'></tt></p>

    1. <tt id='iiijjwq'><b id='iiijjwq'><td id='iiijjwq'></td></b></tt>

      当前位置:首页 > 当前栏目 > 正文

      16个Excel文本函数,这篇文章教你全弄清楚!

      发布日期:2016-09-13 13:09:52

      利用Excel函数解决文本处理问题,是最常见不过的任务啦~


      比如下面这些,你有没有遇到过?


      合并两个单元格的内容?

      把省市区分拆?

      提取身份证号码中的出生日期?

      批量替换数据的单位?


      在我眼里,每个文本函数都是一个诗人……接下来就带你领略,各种文本函数的风骚之处。


      一共分为六个部分,文本合并、文字提取、字符清洗、文字替换、精确查找、长度计算。


      总有你要用上的!





      文本合并


      合并和提取是文本处理中最最常见的任务。


      例如,将下表中各个单元格的文字合成1个新的句子:



      而利用不同的函数公式,操作方法和结果都有所不同。



      &连字符&能够直接将一个一个的文本连接起来,形成一个新的文本:

      公式:=A1&B1&C1

      结果:爱老虎油


      然而,&连字符先天残疾,有很多个字符时,也不能直接引用整片区域进行合并,依然只能一个个手工添加。操作过程如下:



      Phonetic 函数用Phonetic就省事很多,它可以引用一个区域(只能一个),将区域内所有单元格的文本型数据拼合在一起。
      例如将下表中的全部文本拼合起来:


      可以使用如下公式


      公式:=phonetic(A1:D1)
      结果:爱老虎油



      但是明明D1里有一个数字1314,为什么没出现在结果里呢?


      因为,phonetic只是个兼职合并函数而已。它本职工作是提取日文拼音,是日文版来客串一下的,人家只是恰好可以合并文字而已。
      这个客串函数个性非常独特,因为它眼里只有文本型数据,对公式结果、数值、日期时间等等通通视而不见。所以1314就是被狗吃掉了……


      此公式的输入就比&连字符简单多了:





      Concat 函数

      而Concat就比&和phonetic更加完美,因为它既能引用一个区域直接合并,又不会漏掉数值、日期和公式结果,还能引用多个区域,简直不能更完美了。



      公式:=concat(A1:D1)

      结果:爱老虎油1314



      不过,很遗憾,完美版本的Concat只有2016版才有。

      在之前的版本中,它有个前身,叫 Concatenate。Concatenate和&公式一个德性,只能逐个添加。名称又太长,还不如用&来得方便。估计是Office工程师自己都忍不了了,才在2016版添加了一个加强版吧。
      这个加强版可棒了:



      以上三种合并函数,分别合成的结果放在一起,结果就是酱紫的:

       


      一个宅男表哥用公式向喜欢的妹子发出三行爱的告白。

      结果,这个妹子是个表妹,迅速用下面这个函数做出了回应……



      Textjoin 函数

      用分隔符将多个字符串联起来。


      公式:textjoin(“!”,True,A1:D1)&”!”

      结果:你!是!个!好人


      于是,此公式的含义为,将A1:D1中每个单元格的内容用叹号串联起来,如果有空格则忽略,最后缺少的叹号用&单独补一个。


      因为有函数辅助输入工具条,写这个公式也不难:




      有一个神奇的函数,可以按照指定的次数,将自己反复拼合起来,俗称自撸。啊,不,是重复。



      Rept 函数

      它的语法是Rept(字符串,重复次数)。好想让妹子用Rept再来一次暴击:



      公式:rept(A1,3)

      结果:你是个好人!你是个好人!你是个好人!



      公式含义再明显不过了。



      好喜欢Rept函数输入后按下Enter键的感觉:










      文字提取


      提取型文本函数,就是从1个文本中挑出一部分。常用的提取函数就有Left、Right、Mid(middle的简写)三种。

      Left 函数

      从左边开始算起,提取若干字符。例如从下面诗句提取左边的7个字符:


      公式:=Left(A1,7)

      结果:一江春水向东流



      Right 函数而提取右边的7个字符,就要用函数Right:


      公式:=Right(A1,7)

      结果:我也没有女盆友


      Left 和 Right 函数语法结构相同:函数名(字符串,提取数量)。只是一左一右,提取的方向不一样。



      但是第三个函数Mid就厉害得多,它能够从中间指定的位置开始提取。


      Mid 函数基本语法是 Mid(字符串,起始位置,提取数量)


      例如,从下面诗句中提取清泪两字,就可以从第11个字符开始提取2个字符(逗号也算一个)。



      公式:=Mid(A3,11,2)&Right(A3,1)

      结果:清泪流


      注意到了嘛,上面的公式还用到了连字符&,将两个公式计算的结果拼在一起,得到了最终结果。





      字符清洗


      从网页上或神队友那弄来的数据,可能会有莫名其妙的空格和换行符。这些字符虽然看不见,但却会导致公式计算结果出错。所以通常会用Clean或Trim两个函数进行清洗。


      他们语法也一样,都是 函数名(字符串)。但功能上却有细微的差别:



      Clean 函数

      字面意思是清洁,它只清除换行符等看不见的非打印字符,却无法去除空格。



      Trim 函数

      字面意思是修剪,它会裁头去尾,将前后的空格以及文本内部多余的空格全部清除,但是按英文使用习惯,英文中间会自动保留一个空格字符作间隔。


      例如,A1单元格中的字符串有很多多余的空格及2个换行符。


      分别用Clean和Trim函数处理的结果如下:





      文字替换

      Replace和Substitue这哥俩的能力是”置换“。功能类似,但是定位的方法不同。



      Substitute 函数

      Substitute是以字符定位字符。

      例如,找出诗句中的“船”字,然后替换成“床”,公式和结果如下:


      公式:=Substitute(A1,””,””)

      结果:百年修得同度,霉霉三月又分手



      Replace 函数

      而Replace则是以位置定位字符。

      例如,从第13个字符开始提取1个字符“汉“,然后把”汉“替换成新的字符“妹“,公式和结果如下:


      公式:=Replace(A1,13,1,””)

      结果:此情可待成追忆,我要做撩妹高手



      感觉自己是在冒着跪榴莲的风险在写这篇推送……


      替换函数还有一项独门秘技:把替换为的字符写成空值(“”),替换就变成了删除。看哪个字符不顺眼,一言不合就可以让它消失。




      精确查找


      Find和Search,都能掘地三尺,精准定位某个字符在文本中的具体位置。



      Find 函数


      公式:=Find("King",A1)

      结果:2


      计算结果说明King在整句中是从第2个字符开始出现的。



      Search 函数


      公式:=Search("洪荒",A1)

      结果:6


      结果表明,“洪荒”在整句中是从第6个字符开始的。


      虽然 Find 和 Search 都是返回某个字符在字符串中的位置信息,结果都是一个数值。但还是有细微差别,Search 可以用通配符模糊查找。例如,“K?ng”就能把 King、Kong、Kang 都找出来,而Find却做不到。


      但是,计算返回结果是一个位置数值,有用吗?

      接着往下看,你就知道可以怎么用!





      她是怎么知道的?




      长度计算

      Excel中有两把度量文本长度的尺子:Len 函数 和 LenB 函数



      Len 函数

      不管中文还是英文、数字,Len都将每个字符算作1。 

      公式:=Len(A1)

      结果:15


      两句七律加一个符号,总共15个字符。



      LenB 函数

      LenB后面多出来的那个B是Byte(字节) 的意思,是按字节来算。1个汉字及中文标点都是双字节,长度都是2。但英文字母和数字通常都是1个字节,长度只有1。


      于是,同样是14个汉字和一个标点的诗句,用LenB的计算结果不一样。


      公式:=LenB(A1)

      结果:30



      LenB 和 Len之间的差异,

      常常用来处理中英文混合的情况,比如

      公式:=LenB(“King是好人”)-Len(“King是好人”)

      结果:3


      由于汉字LenB计算出来的长度是Len的两倍,但英文字母长度相等,两个公式之差,就是汉字的个数。
      以上结果正说明字符串中有3个汉字。如果再配合Right函数,就可以把中文单独提取出来。



      Text 格式转换函数

      text函数能够让单元格数值按照指定的格式显示出来,例如固定显示成4位数,不足就补0的处理方法如下:


      公式:=TEXT(A1,"0000")

      结果:0069








      以上就是常用的文本函数用法,然而很多人可能会有这样的疑问。

      “合并和提取用在哪很好理解,可是查找一个字符并返回位置信息,到底有什么用?”


      有些函数计算结果自身并没有多大意义,却能够辅助完成其他任务。比如Len先求出长度后,就可以按照长度来排个名次……


      更厉害的是,计算结果还可以作为其他函数的参数啊。比如要把下图中每一个数字提取出来变成 QiuYeXXXX的格式,只用一个函数,是办不到的。


      但是多个函数组合起来使用,上万行的数据也能双击搞定,只需要花几秒钟时间写一个公式就够了:



      图中演示的公式就综合运用了4种文本函数,逐步求值的结果如下图:


      文本函数远不止这些,但常用的就是下面这16个啦!其他函数,需要用时再找吧~



      我真的不会去背这些函数,但是会根据他们能解决的问题进行观察和梳理,留意他们的特点。然后在看到其他案例时,自己再去了解一番同时加深理解。


      函数看起来枯燥乏味,但只要摸清脉络,就会变得很好玩。


      想当初爱上Excel,也就是纯粹觉得好玩。而文本函数中最喜欢的,就是Rept了。


      因为

      它可以完美表达心情:

      Rept(“你是个好人!”,6)

      Rept("

      上一篇:归来的乔布斯:Apple AR/VR的真正王牌
      下一篇:华为欲借欧洲、俄国等市场扩大云计算 叫板西方巨头

      相关文章:

      • 2016-06-23

      • 2016-06-23

      • 2016-06-23

      • 2016-06-23

      • 2016-06-23

      Copyright © 2016 66科技网 dhu66.com All Rights Reserved.
      本站文章来自网友的提交收录,版权归作者所有
      如有权益问题请及时与我们联系处理