送人玫瑰,手有余香,请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径! 【置顶公众号】或者【设为星标】及时接收更新不迷路 小伙伴们好,今天要和大家分享一道字符串中提取数字并求和的题目。这类型的题目我们以前经常和大家分享,朋友们应该都很熟悉了,常用的套路就是利用SUBSTITUTE函数来处理。但是,今天的这道题目又稍稍不一样。 看到了吗,今天这道题目中除了需要求和的数字外,还有其它的数字干扰。因此,如何处理这些干扰数字,就是解决这道题目的关键。 无论如何,还是需要用SUBSTITUTE函数界处理它。 在单元格B2中输入公式“=SUM(--TEXT(MID(TEXT(RIGHT(TRIM(MID(SUBSTITUTE(A2,"小时",REPT(" ",99)),ROW($1:$5)*99-98,99)),{1,2,3,4}),),2,9),"0.0;;;!0"))”,三键回车并向下拖曳即可。 思路: SUBSTITUTE(A2,"小时",REPT(" ",99))部分,将“小时”用长空格替代。这是常用套路 MID(SUBSTITUTE(A2,"小时",REPT(" ",99)),ROW($1:$5)*99-98,99))部分,从第1、100、199..位开始,提取长度为99的字符串 接下来再用TIRM函数去除多余空格。医生这些都是常用套路。它返回的结果是{"猪八戒8号旷工8";";9号旷工5";";18号旷工5.2";";22号旷工1.5";""} 接下来从右侧开始,依次提取1、2、3、4个字符。提取的结果是字符串和数字组成的内存数组,结果为{"8","工8","旷工8","号旷工8";"5","工5","旷工5","号旷工5";"2",".2","5.2","工5.2";"5",".5","1.5","工1.5";"","","",""} TEXT函数将数字转换为空值,结果为{"","工8","旷工8","号旷工8";"","工5","旷工5","号旷工5";"","","","工5.2";"","","","工1.5";"","","",""},这样做的目的是将那些不需要的数值都去除 再次利用MID函数从第2位开始,提取长度为9(任意数值,只要长度大于3即可),结果是{"","8","工8","旷工8";"","5","工5","旷工5";"","","","5.2";"","","","1.5";"","","",""} 第二次使用TEXT函数,将文本强制转换为0,结果为{"0","8.0","0","0";"0","5.0","0","0";"0","0","0","5.2";"0","0","0","1.5";"0","0","0","0"} 减负运算后SUM函数求和
怎么样,小伙伴们理解了这个公式的思路了吗? 上面的这个公式显得有些复杂,我们试着将其简化一下。 在单元格B2中输入公式“=SUM(IFERROR(--MID(TEXT(MID(SUBSTITUTE(A2,"小时",REPT(" ",9)),ROW(1:99),9),),2,8),))”,三键回车并向下拖曳即可。 思路: TEXT(MID(SUBSTITUTE(A2,"小时",REPT(" ",9)),ROW(1:99),9),)部分,和上面介绍的目的是相同的,都是要让需要提取的数字处在所在字符串的末尾,并屏除所有不需要的文本字符串和数字 接下来直接使用MID函数从字符串的第2位开始,提取一定长度的字符串,返回的结果有些是文本字符串,有些是文本和数字混合的字符串,有些则是数字 减负运算后利用IFERROR函数将错误值转换为0 SUM函数求和
朋友们,通过上面这两个公式,我们可以看到,无论怎样处理,其核心的思路就是通过配套使用RIGHT、MID和TEXT函数将需要提取的数字处在所在字符串的末尾,并屏除所有不需要的文本字符串和数字,到最终求和的目的。 好了朋友们,今天和大家分享的内容就是这些了!喜欢我的文章请分享、转发、点赞和收藏吧!如有任何问题可以随时私信我哦!-END-
长按下方二维码关注EXCEL应用之家 面对EXCEL操作问题时不再迷茫无助
|