二维码 购物车
部落窝在线教育欢迎您!

如何精确提取单元格内不同属性的数据

 

作者:老菜鸟来源:部落窝教育发布时间:2021-02-03 16:52:05点击:4522

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

一个单元格内只放一个属性的数据是Excel录入规范的要点,但是在日常工作中,我们还是容易遇到不按规范录入的源数据。那么,当遇到多个不同属性的数据挤在一个单元格中的情况时,我们应该怎么处理呢?如何才能在繁杂的数据中,精确提取到我们所需要的数据呢?小E今天带来的就是解决此类问题的两种方法——分列法和公式法!

 

正文:

临近春节,公司组织了各类团拜活动,行政部门的小刘负责统计大家需要的物资数量并配合采购发放,可是大家汇总的结果让小刘头疼不已。数据是这样的:

 

 

之前讲过的Excel录入规范,即一个单元格只能存放一种属性的数据。但在这个数据表中,一个单元格存放了三个属性的数据,数量、单位和名称都合并到了一起存放。这让小刘感到束手无策,根本无法汇总。

 

那么该怎么解决呢?

以下,从两个方面来给出解决方案:

1、从处理数据源这个方向下手处理。

2、针对这个数据源找到一个适合的汇总公式。

方案一:改造数据源

既然问题出在数据源录入不规范,将原本应该是三列存放的数据放在了一列,那么只要把一列再拆分到三列,问题就可以解决了。因此现在要做的就是根据B列的内容,得到CDE三列结果。

 

 

这就涉及到数据提取的方法,以下分别说明。

 

1、名称怎么提取?

可以使用公式=RIGHT(B2,LENB(B2)-LEN(B2)-1)提取名称。

 

 

思路分析:

名称在单元格的右侧,因此用RIGHT函数提取。在这个问题中的关键在于确定名称的字数。确定单元格内汉字的字数通常用=LENB(B2)-LEN(B2)这个套路。

 

公式解析:

LENLENB都是统计字数的函数,区别在于LEN不分单字节和双字节,LENB是区分单字节和双字节的。比如,统计一个汉字时,LEN的结果是1,而LENB的结果是2。因此,在统计B2单元格字数时,LEN(B2)的结果是6,而LENB(B2)的结果就是9LENB(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、扩展学习:单位怎么提取?

这个问题与今天的主题无关,只当是再熟悉一下LENLENB的应用吧,公式为:

=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得到的就是四个数字5444,其余都是错误值。

 

 

FIND(I2,B:B)-2的结果中同样只有四个数字,分别是3222

 

 

注意第一个数字3,就是第一个茶叶所对应的数量的长度,这一步的作用也就很容易理解了,就是得到每个名称所对应的数量的长度。

长度确定了,就可以直接用LEFT函数去提取数量。因此就有了LEFT(B:B,FIND(I2,B:B)-2),对应的结果如图所示。

 

 

到这一步,只需要将数值转为文本,将错误值变成0,再用SUM求和就是每个赠品的数量合计了。

数值转文本是通过“- -”实现的,错误值变成0则是IFERROR的特性。

最后通过SUM函数完成求和即可。

注意这个公式是数组公式,需要按着Ctrlshift和回车键完成输入。

 

小结:一个不规范数据的统计问题,带出来很多值得思考的问题。今天的教程里有很多公式、函数在应用方面的细节问题,十分期待各位同学将本期内容的收获与大家分享,共同进步。

 

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

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

Excel运用规范1:一个单元格只记录一条信息

Excel规范2:如何做一张能满足后续分析需求的表格?

只再说最后一次:Excel数据源表家规

大神专场:Excel数据处理常用技巧两则:分列转置

版权申明:

本文作者老菜鸟;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。