送人玫瑰,手有余香,请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径!
【置顶公众号】或者【设为星标】及时接收更新不迷路
小伙伴们好,今天天气不错,和大家唠一唠数字提取的问题。
如下图,我们总是强调规范的数据录入,总是有人不放在心上。当遇到麻烦就没辙了!
这道题目的要求是从源数据中提取乘号“*”后面的数据并相加。这类题目总的一个思路就是用长空格替代星号,然后依次提取数据,转换数据,最后求和。
话不多说,一起来烧脑吧!
如下图,这组公式看起来不太长,我们一起来庖丁解牛啊!
在单元格C2中输入公式“=SUMPRODUCT(--TEXT(LEFT(TEXT(MID(SUBSTITUTE(A2&"s","*",REPT(" ",99)),ROW(1:999),99),),98),"0;;;!0"))”并向下拖曳即可。
思路:
A2&"s"部分,给源数据加一个后缀,目的是防止源数据最后结尾处是数字,在后续使用TEXT函数处理时会出错
SUBSTITUTE(A2&"s","*",REPT(" ",99))部分,利用长度是99的空格替代乘号“*”,这个是常用的方法,不再赘述
MID(SUBSTITUTE(A2&"s","*",REPT(" ",99)),ROW(1:999),99)部分,在上面完成替代后,利用MID函数从第1个字符开始向后,依次提取长度为99的字符串。这里的ROW(1:999)只要确保大于最长的字符串长度就好
TEXT(MID(SUBSTITUTE(A2&"s","*",REPT(" ",99)),ROW(1:999),99),)部分,第一次使用TEXT函数,将提取出来的数字全部转换为空
LEFT(TEXT(MID(SUBSTITUTE(A2&"s","*",REPT(" ",99)),ROW(1:999),99),),98)部分,利用LFET函数在上一条结果的基础上提取长度为98的字符串,这样做的目的是,上条提取的结果中,一定且必定会有一组长度为99的字符串,其构成为“空格+数字+1个文本”这种结构,用LEFT函数提取后,刚好将数字提取出来了
再次利用TEXT函数将所有的文本强制转换为0,并利用减负运算将文本型数字转换为数值型数字
最后利用SUMPRODUCT求和
总结一下:规范的数据录入时一切工作的基础
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
我就知道你“在看”
注意!前方有红包挡道!速点阅读原文消灭之!
联系客服