美文网首页Python精选Excel
excel数据处理: 如何用99个空格提取单元格数据

excel数据处理: 如何用99个空格提取单元格数据

作者: de2f8df230bf | 来源:发表于2019-01-03 13:56 被阅读12次

    编按:脑洞大开,提取单元格数据用99个空格就成!真想扒开那些大神的脑袋看看,是怎么想出这样匪夷所思的方法的。

    需要从规格型号中提取容值、封装、耐压三组数据,如下:

    数据源在A列,数据量很大,需要提取的容值、封装和耐压三项数据在单元格中的字节位置不固定。能找到的规律就是提取的数据分别位于数据源的第二、三、四个逗号之后。

    当我们遇到问题的时候,找到规律是解决问题的关键。现在规律找到了,因此解决方法也就有了。这里有三种方法,从最简单的快捷键操作到脑洞大开的空格替换法都有,以下分别进行介绍。

    1.快速填充法Ctrl+E

    操作要点:

    (1)在B2单元格输入0402时,要先输入一个单引号,或者把单元格修改为文本格式再输入;

    (2)只输入一个数据可能无法通过Ctrl+E得到正确结果,这时候连续输入两个数据就可以了。

    提示:组合键Ctrl+ E只能在Excel2013及以上的版本才能使用。

    就本例而言,Ctrl+ E略微显得有些麻烦,因此再介绍一种用分列的处理方法。

    2.分列法

    操作要点:

    (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函数只有一个参数,功能就是去掉字符串中多余的空格。

    理解了这个公式吗?为何要用99个空格呢?

    ****部落窝教育-excel数据提取技巧****

    原创:老菜鸟/部落窝教育(未经同意,请勿转载)

    更多教程:部落窝教育(www.itblw.com)

    微信公众号:exceljiaocheng

    相关文章

      网友评论

        本文标题:excel数据处理: 如何用99个空格提取单元格数据

        本文链接:https://www.haomeiwen.com/subject/uyufrqtx.html