令人恼火的不规范数据源，用这个公式一招干翻它！
每天一点小技能
职场打怪不得怂
编按：一个单元格内只放一个属性的数据是Excel数据规范的要点，但是在日常工作中，我们还是容易遇到不按规范录入的数据源，那么我们应该怎么处理呢？如何精确提取我们所需要的数据，进行下一步的操作呢？
正文：
临近春节，公司组织了各类团拜活动，行政部门的小刘负责统计大家需要的物资数量并配合采购发放，可是大家汇总的结果让小刘头疼不已，数据是这样的。
我们之前讲过的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和回车键完成输入。
小结：一个不规范数据的统计问题，带出来很多值得思考的问题。今天的教程里有很多公式、函数在应用方面的细节问题，十分期待各位同学将本期内容的收获与大家分享，共同进步。
在线咨询Excel课程
Excel教程相关推荐
主讲老师： 滴答
Excel技术大神，资深培训师；
课程粉丝100万+；
开发有《Excel小白脱白系列课》
《Excel极速贯通班》
原价299元
限时特价 99 元
少喝两杯咖啡，少吃两袋零食
就能习得受用一生的Excel职场技能！