如何精确提取单元格内不同属性的数据
作者:老菜鸟来源:部落窝教育发布时间:2021-02-03 16:52:05点击:4941
编按:
一个单元格内只放一个属性的数据是Excel录入规范的要点,但是在日常工作中,我们还是容易遇到不按规范录入的源数据。那么,当遇到多个不同属性的数据挤在一个单元格中的情况时,我们应该怎么处理呢?如何才能在繁杂的数据中,精确提取到我们所需要的数据呢?小E今天带来的就是解决此类问题的两种方法——分列法和公式法!
正文:
临近春节,公司组织了各类团拜活动,行政部门的小刘负责统计大家需要的物资数量并配合采购发放,可是大家汇总的结果让小刘头疼不已。数据是这样的:
之前讲过的Excel录入规范,即一个单元格只能存放一种属性的数据。但在这个数据表中,一个单元格存放了三个属性的数据,数量、单位和名称都合并到了一起存放。这让小刘感到束手无策,根本无法汇总。
那么该怎么解决呢?
以下,从两个方面来给出解决方案:
1、从处理数据源这个方向下手处理。
2、针对这个数据源找到一个适合的汇总公式。
方案一:改造数据源
既然问题出在数据源录入不规范,将原本应该是三列存放的数据放在了一列,那么只要把一列再拆分到三列,问题就可以解决了。因此现在要做的就是根据B列的内容,得到C、D、E三列结果。
这就涉及到数据提取的方法,以下分别说明。
1、名称怎么提取?
可以使用公式=RIGHT(B2,LENB(B2)-LEN(B2)-1)提取名称。
思路分析:
名称在单元格的右侧,因此用RIGHT函数提取。在这个问题中的关键在于确定名称的字数。确定单元格内汉字的字数通常用=LENB(B2)-LEN(B2)这个套路。
公式解析:
LEN和LENB都是统计字数的函数,区别在于LEN不分单字节和双字节,LENB是区分单字节和双字节的。比如,统计一个汉字时,LEN的结果是1,而LENB的结果是2。因此,在统计B2单元格字数时,LEN(B2)的结果是6,而LENB(B2)的结果就是9,LENB(B2)-LEN(B2)即可得出B2单元格内汉字的字数。
结果如下图所示,大家理解一下即可明白。
因为统计出的汉字中还有一个汉字是量词单位,所以,赠品名称的字数需要在汉字的总字数中减1,即LENB(B2)-LEN(B2)-1。最后,再用Right函数提取名称就完成了。
2、数量怎么提取?
可以使用公式=LEFT(B2,LEN(B2)*2-LENB(B2))提取数量
数量在单元格的左侧,因此用LEFT函数提取。如图,同样用LEN函数和LENB函数配合计算出数字的长度。
这个公式的原理,大家研究一下就可以明白了,在此不再赘述。
实际上要解决小刘的问题,有名称和数量就足够了,使用SUMIF函数就能得到最终的结果。
公式=SUMIF(C:C,I2,F:F)相信同学们都不陌生,就不解释了。
在这里补充一句,如果要直接用SUMIF函数的话,公式LEFT(B2,LEN(B2)*2-LENB(B2))的前面需要加--,把LEFT函数得到的文本型结果转变为数字型结果。
3、扩展学习:单位怎么提取?
这个问题与今天的主题无关,只当是再熟悉一下LEN和LENB的应用吧,公式为:
=MID(B2,LEN(B2)*2-LENB(B2)+1,1)
其中的具体原理,大家可以自己梳理一下,很简单。
以上的三个问题,都不止一种公式可以实现,有兴趣的同学不妨开动脑筋,看看自己能否用其他公式做到。
接下来要介绍的是不改造数据源,直接用公式一步到位的方法。
这个公式就是=SUM(IFERROR(--LEFT(B:B,FIND(I2,B:B)-2),))
下面简单解释一下这个公式的原理。
以茶叶为例,FIND(I2,B:B)可以实现两个功能,如果B列包含“茶叶”的数据则得到“茶叶”这两个字所在的位置,如果不含“茶叶”则会得到错误值。
如下图所示,B列实际上有四个单元格是包含了“茶叶”的,FIND得到的就是四个数字5、4、4、4,其余都是错误值。
FIND(I2,B:B)-2的结果中同样只有四个数字,分别是3、2、2、2。
注意第一个数字3,就是第一个茶叶所对应的数量的长度,这一步的作用也就很容易理解了,就是得到每个名称所对应的数量的长度。
长度确定了,就可以直接用LEFT函数去提取数量。因此就有了LEFT(B:B,FIND(I2,B:B)-2),对应的结果如图所示。
到这一步,只需要将数值转为文本,将错误值变成0,再用SUM求和就是每个赠品的数量合计了。
数值转文本是通过“- -”实现的,错误值变成0则是IFERROR的特性。
最后通过SUM函数完成求和即可。
注意这个公式是数组公式,需要按着Ctrl、shift和回车键完成输入。
小结:一个不规范数据的统计问题,带出来很多值得思考的问题。今天的教程里有很多公式、函数在应用方面的细节问题,十分期待各位同学将本期内容的收获与大家分享,共同进步。
本文配套的练习课件请加入QQ群:902294808下载。
做Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!
扫下方二维码关注公众号,可随时随地学习Excel:
相关推荐:
大神专场:Excel数据处理常用技巧两则:分列转置)
版权申明:
本文作者老菜鸟;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。