公式一式完成提取和求和

今天也是一期答疑实战问题！大家办公过程中有什么疑难问题，数据脱敏后，需求整理好，可以发到我邮箱：[e-mail]。我根据实际问题的普遍性和实际情况，不定期开展答疑！看一下今天的问题：提取元前面的数值并相加

这个问题，要说使用公式一式完成提取和求和是有点麻烦的，定位元之前，向前截取，提取多层比较棘手，但是方法我们多的是！先来三种吧！比如我们先使用公式提取出全部数值，再求个和就比较简单了！方案1 | 使用公式处理▼ 我是一条普通公式=IFERROR(-LOOKUP(1,-RIGHT(LEFT($A2,FIND("@",SUBSTITUTE($A2,"元","@",COLUMN(A1)))-1),ROW($1:$10))),"")这样我们就提取了全部元前面的数值

下面我们还是来谈一下思路：思路决定出路！（看懂公式的直接跳过）1、我们想要提取元前面的数值，那么首先要找到元，一般查找函数有FIND，但是FIND只能找到首个元的位置，这里不太合适，就要考虑是否有其他可替代方案，Excel中文本函数大脑过一下，只有少数可以按位置的，其中一个就是SUBSTITUTE，可以按出现的次数替换！第三参数指定！涉及的部分：Column(A1)表示把第一个元替换成@，右拉就是第二、第三……SUBSTITUTE($A2,"元","@",COLUMN(A1))     2、SUBSTITUTE把元按照出现的顺序依次替换成其他字符，这样就可以使用FIND来查找了！比如我们这里替换成@，然后使用FIND找到位置-1就是数值结束的位置涉及的部分：FIND("@",SUBSTITUTE($A2,"元","@",COLUMN(A1))) 3、找到位置后，我们就可以从右边进行截取，截取的位置，逐渐增加，比如我们依次截取1到10位！，截取到文本就是非数值，最后我们使用LOOKUP的特性，如果第二参数的数值都比第一参数小，那么返回最后一个数值！LOOKUP(1,-RIGHT(LEFT($A2,FIND("@",SUBSTITUTE($A2,"元","@",COLUMN(A1)))-1),ROW($1:$10)))4、第四部就是使用IFERROR做容错，对于超过没有的报错显示成空！求和自己SUM一下就不说了！这种方式还是有点麻烦，我们换一个函数处理，看一下PQ如何处理这种问题！方案2 | 使用Power Query处理我们把计算过程对应的明细也提取出来了，更加清晰！PQ对于数据清洗依据强的可怕！

格式化一下代码，分析一下处理思路：

思路简要分析:借助Text.SplitAny可以按照多个字符拆分的，我们把需要的的XXX元，先从内容中移除，使用剩下的去分割内容，最后形成一个List,提取其中的包含元的就是我们的需要的目标，最后按照元拆分，保留元前面的数值即可！PQ是强类型的，所以在求和之前还需要使用Number.From把文本型数值转数值型，否则无法求和！最后的Text.Format是格式化文本的作用！▍左右滑动查看完整代码：let源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],结果 = Table.AddColumn(源, "金额合计", (x)=>letres = List.Transform(List.Select(Text.SplitAny(x[字符],Text.Remove(x[ 字符],{"0".."9","元","."})),(y)=>Text.Contains(y,"元")),(a)=>Number.From(Text.Split(a,"元"){0}))inText.Format("#{0}=#{1}",{Text.Combine(List.Transform(res,Text.From),"+"),List.Sum(res)}))in结果以上的方式都可以解决问题，但是对于这个问题，更加合适的其实是正则表达式！方案3 |  正则表达式处理-自定义函数我们使用正则表达式自定义一个函数，直接处理即可！

▼源码分享'公众号：Excel办公实战'作者：E精精'功能：提取指定标识前面的数值并求和'-------------------------------------------------------Function getNumTotal(dataStr As String, EndStr As String)Dim reg As Object, res(), i As LongSet reg = CreateObject("vbscript.regexp")With reg.Global = True.Pattern = "(\d+(\.\d+)?)" & EndStrSet matches = .Execute(dataStr)For i = 0 To matches.Count - 1ReDim Preserve res(0 To i)res(i) = Val(matches(i).submatches(0))NextEnd WithgetNumTotal = Application.Sum(res)End Function练习数据源：复制到Excel中即可！字符土豆5斤10元，白菜5.8元，肉4斤92.8元萝卜5元5斤，Excel视频20.5元1套土豆5元20斤，胡萝卜12元7.2斤白菜12元，柠檬20元，花菜18元，青椒1斤2量20元今天我们就想到这里！“方法总比问题多”！这里是【易办公 早下班】的Excel办公实战