【原】实战 | 按照条件提取金额并求和,第三种方法最简单！

Excel办公实战 2021-06-29

展开全文

今天也是一期答疑实战问题！大家办公过程中有什么疑难问题，数据脱敏后，需求整理好，可以发到我邮箱：1071238377@qq.com。我根据实际问题的普遍性和实际情况，不定期开展答疑！

看一下今天的问题：提取元前面的数值并相加

这个问题，要说使用公式一式完成提取和求和是有点麻烦的，定位元之前，向前截取，提取多层比较棘手，但是方法我们多的是！先来三种吧！

比如我们先使用公式提取出全部数值，再求个和就比较简单了！

方案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)=>
        let 
            res = List.Transform(
                List.Select(
                    Text.SplitAny(x[字符],Text.Remove(x[ 字符],{"0".."9","元","."})),
                        (y)=>Text.Contains(y,"元")),(a)=>Number.From(Text.Split(a,"元"){0}
                    )
                ) 
        in 
            Text.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 Long
    Set reg = CreateObject("vbscript.regexp")
    With reg
        .Global = True
        .Pattern = "(\d+(\.\d+)?)" & EndStr
        Set matches = .Execute(dataStr)
        For i = 0 To matches.Count - 1
            ReDim Preserve res(0 To i)
            res(i) = Val(matches(i).submatches(0))
        Next
    End With
    getNumTotal = Application.Sum(res)
End Function