题目:
将简写的发票号展开,如下图:
解法:
先直接上代码:
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位数的文本。
我会跟填的那个人说,你给我老实点填 _( ゚Д゚)ノ
老师, 这句code实在没看懂,能给我解析一下吗?
c=List.Accumulate({{"-",".."&b},{"/",","&b}},[发票号],(x,y)=>Text.Replace(x,y{0},y{1})), ,
我查阅了List.Accumulate的定义也没看懂这个定义,哪个部分代表“X", 哪个部分代表“Y”, 构造的{{"-",".."&b},{"/",","&b}}, 应该是什么样子的,为什么要加上“-” 和“/" 在list里面,谢谢老师
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也是可以的,但是如果需要替换很多次,用这个方法就方便多了。
老师,谢谢您的耐心讲解,您真的该出本书了,太高深了,谢谢老师
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
更新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
这么复杂的应用被归类到普通,pq真是另一个无底洞?
膜拜啊
谢谢分享 学习了
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、是,因为这些过程全部在添加自定义列这一个步骤里完成,没法分成多个步骤写,但如果全部嵌套在一起就太乱了,所以用这种方法实现分步,类似于DAX中var的用法
2、最好是去掉自动生成的转换类型,自己手动转换
3、这是对单数字行进行的异常处理,如果不加{},结果会是这一列中其他数据都是list,而这一行的数据是number,这种情况下没法展开的
多谢大神指教,DAX和PQ都是刚入门,需继续努力学习
实在没读懂题,左截取8个字符?
老师好像有点错误 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})),
我用了比较笨的办法,该好好学学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”"
我们遇到的都是拆这个,晕!:“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”
解决了吗?
如数据 是:01345998-1001 ,这个方法实现不了
那就固定前缀是几位,这只能写死这样了