发票号展开

题目:

将简写的发票号展开,如下图:

解法:

先直接上代码:

let
    源 = Excel.CurrentWorkbook(){[Name="发票号"]}[Content],
    构建list = Table.AddColumn(源, "展开", each [
        a=Text.SplitAny(Text.From([发票号]),"/-"),
        b=Text.Start(a{0},Text.Length(a{0})-Text.Length(a{1})),
        c=List.Accumulate({{"-",".."&b},{"/",","&b}},[发票号],(x,y)=>Text.Replace(x,y{0},y{1})),
        d=try Expression.Evaluate("{"&c&"}") otherwise {[发票号]},
        e=List.Transform(d,each Number.ToText(_,"00000000"))
    ][e]),
    展开 = Table.ExpandListColumn(构建list, "展开")
in
    展开

看不懂是不是?我们一步步来拆解看下。

观察数据源的发票中,共有3种类型的格式,其中"-"表示连续号码,"/"表示单独n个号码,什么都没有的就是它本身。
很容易想到数据结构中的list,只要分别把"-"和"/"替换为".."和",",再在外面加一对大括号,比如"123-133"替换为"{123..133}","256/259/261"替换为"{256,259,261}",再用Expression.Evaluate将文本转换为表达式即可。
但问题是"-"和"/"后面的数字是简写,我们希望把它还原成完整的发票号,比如"01345678-681"还原成"01345678-01345681"。这也不是难事,先把发票号按"-"或"/"拆分,取第一段的文本左边的第一段长度减去第二段长度得到共同的前缀,也就是工作表函数中的left(len()-len()),Power Query中只是换了个函数名。
得到前缀后,在替换的时候将前缀放到替换符的后面,这样替换完就刚好将前缀插入到简写的发票号前面。因为有"-"和"/"所以需要替换两次,嵌套两次Text.Replace的话太low了,所以我用了List.Accumulate进行批量替换。
再把文本转换为表达式,但其中的"22345651"是特殊情况,前面一直是error所以用try otherwise组合进行特殊处理。
最后一步再用Number.ToText把数字还原成8位数的文本。

附件

19 Replies to “发票号展开”

  1. 老师, 这句code实在没看懂,能给我解析一下吗?
    c=List.Accumulate({{"-",".."&b},{"/",","&b}},[发票号],(x,y)=>Text.Replace(x,y{0},y{1})), ,
    我查阅了List.Accumulate的定义也没看懂这个定义,哪个部分代表“X", 哪个部分代表“Y”, 构造的{{"-",".."&b},{"/",","&b}}, 应该是什么样子的,为什么要加上“-” 和“/" 在list里面,谢谢老师

    1. http://pqfans.com/1897.html 这篇是专门讲acc的,相当于for循环,使用acc就能够实现批量替换的效果。
      x表示每次循环的结果,起始值为[发票号],y表示第一参数里的list。
      以第一行为例,起始值x为"01345678-681",b为"01345",第一次循环把x中的y{0}替换为y{1},y{0}是"-",y{1}是"..01345",替换结果为"01345678..01345681"。
      第二次循环x为上一次循环的结果,再把x中的"/"替换为",01345",但是x中没有"/"所以不发生替换。
      这样不论原来的文本中有"-"还是"/"都能成功替换。
      在这个案例中只循环两次,你直接嵌套两次Text.Replace也是可以的,但是如果需要替换很多次,用这个方法就方便多了。

  2. VBA果然处理起来复杂一点,我真是闲得慌 哈哈

    Public i%
    Sub fapiao()
    Dim arr, j%, fp, fpd, lenfp
    arr = Range("A2:A" & [a65536].End(xlUp).Row)
    i = 2
    For Each fp In arr
    If InStr(fp, "-") > 0 Then
    brr = Split(fp, "-")
    lenfp = Len(brr(1))
    For j = CVar(Right(brr(0), lenfp)) To CVar(brr(1))

    Cells(i, 2) = "'" & Left(brr(0), Len(brr(0)) - lenfp) & j
    i = i + 1
    Next
    ElseIf InStr(fp, "/") > 0 Then
    brr = Split(fp, "/")
    lenfp = Len(brr(0))
    For j = 0 To UBound(brr)
    If Len(brr(j)) < lenfp Then brr(j) = Left(brr(0), lenfp - Len(brr(j))) & brr(j)
    Cells(i, 2) = "'" & brr(j)
    i = i + 1
    Next
    Else
    Cells(i, 2) = "'" & fp
    i = i + 1
    End If
    Next
    End Sub

  3. 更新vba代码 哈哈 我是来捣乱的 水平太低 写的很复杂

    Public i%
    Sub fapiao()
    Dim arr, j%, fp, lenfp, d
    d = [a65536].End(xlUp).Row
    [b2:b65536].ClearContents
    If d = 2 Then
    arr = Split([a2], ",")
    Else
    arr = Range("A2:A" & [a65536].End(xlUp).Row)
    End If
    i = 2

    For Each fp In arr
    If InStr(fp, "-") > 0 And InStr(fp, "/") = 0 Then
    brr = Split(fp, "-")
    lenfp = Len(brr(1))
    For j = CVar(Right(brr(0), lenfp)) To CVar(brr(1))
    Cells(i, 2) = "'" & Left(brr(0), Len(brr(0)) - lenfp) & j
    i = i + 1
    Next
    ElseIf InStr(fp, "-") = 0 And InStr(fp, "/") > 0 Then
    brr = Split(fp, "/")
    lenfp = Len(brr(0))
    For j = 0 To UBound(brr)
    If Len(brr(j)) 0 And InStr(fp, "/") > 0 Then
    brr = Split(fp, "/")
    For j = 0 To UBound(brr)
    If InStr(brr(j), "-") = 0 Then

    If Len(brr(j)) < lenfp Then brr(j) = Left(brr(0), lenfp - Len(brr(j))) & brr(j)
    Cells(i, 2) = "'" & brr(j)
    i = i + 1
    Else
    brr1 = Split(brr(j), "-")
    lenfp = Len(brr1(0))
    For k = CVar(Right(brr1(0), Len(brr1(1)))) To CVar(brr1(1))
    Cells(i, 2) = "'" & Left(brr1(0), lenfp - Len(brr1(1))) & k
    i = i + 1
    Next
    End If
    Next
    Else
    Cells(i, 2) = "'" & fp
    i = i + 1
    End If
    Next
    End Sub

  4. 1、each [……][e]是为了构建record后深化成list吗?
    2、从表导入的时候,会自动加上Table.TransformColumnTypes(源,{{"发票", type any}}),这样的话,貌似a=Text.SplitAny(Text.From([发票号]),"/-")中的text.from就可以不用了?
    3、d=try Expression.Evaluate("{"&c&"}") otherwise {[发票号]},为什么是{[发票号]}?为什么要加{}?

    1. 1、是,因为这些过程全部在添加自定义列这一个步骤里完成,没法分成多个步骤写,但如果全部嵌套在一起就太乱了,所以用这种方法实现分步,类似于DAX中var的用法
      2、最好是去掉自动生成的转换类型,自己手动转换
      3、这是对单数字行进行的异常处理,如果不加{},结果会是这一列中其他数据都是list,而这一行的数据是number,这种情况下没法展开的

  5. 老师好像有点错误 c=List.Accumulate({{"-",".."&b},{"/",","&b}},[发票号],(x,y)=>Text.Replace(x,y{0},y{1}))
    应该改成 c=List.Accumulate({{"-",","&b},{"/",".."&b}},[发票号],(x,y)=>Text.Replace(x,y{0},y{1})),

  6. 我用了比较笨的办法,该好好学学Acc函数了

    let
    源 = Excel.CurrentWorkbook(){[Name="发票号"]}[Content],
    已添加自定义 = Table.AddColumn(源, "Custom", each try
    if Text.Contains([发票号],"-") then
    let a=Text.Split([发票号],"-"),b=Text.Length(a{0}),c=Text.Length(a{1}),d=Number.From(Text.End(a{0},c)),e={d..Number.From(a{1})},f=Text.Start(a{0},b-c),g=List.Transform(e,each f & Text.From(_)) in g
    else
    let a=Text.Split([发票号],"/"),b=Text.Length(a{1}),c=Text.Start(a{0},Text.Length(a{0})-b),d={a{0}}&List.Transform(List.Skip(a),each c&_) in d
    otherwise {[发票号]}),
    #"展开的“Custom”" = Table.ExpandListColumn(已添加自定义, "Custom")
    in
    #"展开的“Custom”"

  7. 我们遇到的都是拆这个,晕!:“HQ11A.2.1、HQ11C.2.2、2.3、2.5-2.7、2.9-2.11、2.13-2.21、HQ11C.3.5、HQ11C.5.1、HQ11F.1.2、HQ11F.1.4-1.6、1.15、1.17-1.19、1.21”

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注