室内设计培训
平面设计培训
部落窝教育
网站首页 >> Excel教程 >> 文章内容

Excel单元格部分内容提取3法,从小白技巧到大神公式都有

[日期:2018-10-12]   来源:IT部落窝  作者:老菜鸟   阅读:0[字体: ]
内容提要:提取数据是很多表妹表哥常遇到的一件事。小白有小白的技巧,大神有大神的妙招。今天的Excel教程一网打尽3种大法,从最简单的快捷技巧到通吃天下的经典公式全都有。快快领走

问题来自于一位群友的求助,大家请看图:

excel表格提取部分数据

 

数据源在A列,包含了很多项信息,现在需要从中提取出容值、封装和耐压三项数据,不难发现,需要提取的数据具有一定的规律性,分别是数据源的第二、三、四个逗号之后的数据。

当我们遇到问题的时候,找到规律是解决问题的关键。现在规律找到了,因此解决方法也就有了。这里有三种方法,从最简单的快捷键操作到经典的吃遍天下的公式都有,以下分别进行介绍。
 
 
1.快速填充法(Ctrl+ E)
excel数据提取方法
 
评价:★★★★★
优势:简单、易用。
劣势:还没有发现。
 
操作要点:
(1)在B2单元格输入0402时,要先输入一个单引号,或者把单元格修改为文本格式再输入;
(2)只输入一个数据可能无法通过Ctrl+E得到正确结果,这时候连续输入两个数据就可以了。
提示:组合键Ctrl+ E只能在Excel2013及以上的版本才能使用。
就本例而言,Ctrl+ E略微显得有些麻烦,因此再介绍一种用分列的处理方法。
 
 
2.分列法
excel提取需要的数据
 
评价:★★★★☆
优势:简单、易用。
劣势:数据量大了后工作量还是比较重。
 
操作要点:
(1)分列过程中使用逗号进行分隔;
(2)需要跳过不导入的列;
(3)对容值这列数据设置为文本格式;
(4)手工指定数据存放的目标区域。
相比第一个方法来说,使用分列就简单了许多,同时通过这个例子,大家也可以对分列这个强大的功能有了深入的了解。
使用分列虽然比较方便,但如果经常要处理这类数据的话,操作量也是蛮大的,最后我们再来分享一个公式的做法。
 
 
3. TRIM-MID-SUBSTITUTE-REPT组合公式法
使用公式:=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",99)),COLUMN(B1)*99,99))
右拉下拉即可得到所需的结果。
 
评价:★★★★☆
优势:快速,对付大量数据尤其实用,并且可以修改参数用于更复杂的字符提取。
劣势:公式不好记。
 
公式解析:
这个公式里用到了五个函数,其中有我们比较熟悉的MID和COLUMN,也有我们不太常用的TRIM、SUBSTITUTE和REPT函数。下面简单来解释一下这个公式的思路。
公式的核心部分是SUBSTITUTE($A2,",",REPT(" ",99)),这部分的作用是进行替换。
SUBSTITUTE函数格式为:
SUBSTITUTE(在哪里替换,替换什么,换成什么,换第几个)
例如:
公式=SUBSTITUTE($A2,",","-",3)的效果就是把A2单元格的第3个逗号换成-号。
 
当省略第四参数的时候,代表逗号全部替换,如图:
 
本例中是把A2中的逗号换成了REPT(" ",99),也就是99个空格。
REPT函数的格式为:
REPT(要重复的字符,重复次数)
例如:
REPT(“★”,5),就是将★重复五次。
 
至于公式中为什么要用99个空格,完全是一种套路,继续看完公式的其他部分或许就理解了。
使用SUBSTITUTE得到的数据还需要用MID函数来进行提取。MID函数大家应该比较熟悉了,基本格式为:MID(要提取的数据,从什么位置开始取,取几个字)。在本例中要提取的数据就是SUBSTITUTE(),而要提取的容值的位置原本是在第2个逗号之后,由于我们把逗号换成了99个空格,要提取的位置前面至少有两组空格也就是2*99个字符;相应封装的提取的位置是3*99,耐压的是4*99。采用公式右拉,所以这里用COLUMN(B1)*99作为提取位置。MID的最后一个参数是要取几个字符,为了保险起见,统一提取99个字。
也就是说,经过MID(SUBSTITUTE(),COLUMN(B1)*99,99)这部分公式运算后,得到的结果是我们实际需要的容值数据包含在前后空格中。为了便于大家理解,临时将空格换成-,可以直观地看出效果:
 
我们肯定不希望得到的结果中包含有大量无用的空格,因此在最外层套一个TRIM就可以去掉这些空格。TRIM函数只有一个参数,功能就是去掉字符串中多余的空格。
本例使用的实际上也是非常经典的一个公式组合:TRIM-MID-SUBSTITUTE-REPT组合。公式理解起来需要一定的基础,新手暂时无法理解的时候,可以先掌握公式的套路,根据自己的需要修改参数。
 
闲言妄语:
估计很多小白看不懂这个公式的套路哦~~小编心痒痒,就来现个丑,打个比方——冲水捞死鱼。
一堆死鱼,密密麻麻,一个靠一个。现在给你一个篓子(MID函数)要你从死鱼中把其中的红鱼捞出来。要求是不要碰到其它鱼。啷个办?把鱼分开(SUBSTITUTE函数替换逗号增大间距)呀!咋个分开?冲水(加空格)呀,使劲冲水(用99个空格,劲够大了),鱼分开了,然后一篓子就把红色死鱼捞起来了,虽然捞鱼的时候水(空格)肯定也会带上,但水马上就从网孔(TRIM函数)中漏掉(被排除了)了,只剩需要的红鱼。
为啥用水(空格)而不是其他东西(譬如逗号)来增加间距呀?网孔(TRIM函数)只适合滤水(逗号就滤不走)呀。为啥要冲那么多水(99个空格)?因为分得越开,越可以忽略其他鱼的影响(字符数不等的影响),篓子(MID函数)的口径(取字符的字符数)就可以越大(99),只需要给一个下篓子的大概位置(并非红鱼的准确位置,由COLUMN(B1)*99实现),就肯定只捞到水和红鱼。

本文配套的练习课件请加入QQ群:264539405下载。 

  

  **更多好文请加入网站:部落窝教育**

如果你在日常使用Excel遇到问题,欢迎留言;也可以加入Excel解答QQ群:537870165交流讨论。

************

 

如果您因为工作所需使用到Excel,不妨关注部落窝教育《Excel极速贯通班》直播课系统学习。

IT部落窝PS,CDR,213班 分享到: QQ空间 新浪微博 腾讯微博 人人网
photoshop教程
Photoshop教程
平面设计教程
Photoshop教程