请帮忙写下代码

根据各个序列号，写出对应的发票号码，要求：连续的发票号码用"-"连接，不连续的发票号码用"，"连接。 请大神帮帮忙吧

10914111556166.zip (9.99 KB, 下载次数: 0)

2021-2-18 20:37 上传

点击文件名下载附件

PowerQuery参考解法
10914111556167.zip (15.54 KB, 下载次数: 0)

2021-2-18 20:43 上传

点击文件名下载附件

我也来个纯属自娱自乐的

1. let
   
2. 
     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIwMja1VIrVgfPMDFB4hsg8c3MUngWY5wTlQUxxhvAsDFB4hig8IzDPBcozRuGZoPCANsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SN = _t, INV = _t]),
   
3. 
     #"Grouped Rows" = Table.Group(Source,{"SN"},
   
4. 
     {{"All", each
   
5. 
       let
   
6. 
         lst = [INV],
   
7. 
         lst_num = List.Sort(List.Transform(lst, Number.From), Order.Ascending)
   
8. 
       in
   
9. 
         List.Accumulate(lst, {}, (s,c)=>if List.Contains(lst_num, Number.From(c)-1) then List.RemoveLastN(s,1) & {s{List.Count(s)-1}&{c}} else s&{{c}})
   
10. 
      }}),
    
11. 
      #"Expanded Each List" = Table.TransformColumns(#"Grouped Rows", {"All", each List.Transform(_, each if List.Count(_)>1 then Text.From(List.Min(_))&"-"&Text.From(List.Max(_)) else Text.From(_{0}))}),
    
12. 
      #"Extracted Values" = Table.TransformColumns(#"Expanded Each List", {"All", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
    
13. 
    in
    
14. 
      #"Extracted Values"

复制代码

let
  源 = Excel.CurrentWorkbook(){[Name="表_2"]}[Content],
  自定义1 =
  Table.Group(
    源,
    "序列号",
    {
      "发票号码",
      each
      List.Accumulate(
        [发票号码] & {""},
        { "", "", "" },
        (x,y)=>
          if
          x{1} = "" and x{2} = ""
          then
          { x{0}, y, y }
          else if
          Number.From( y ) - Number.From( x{2} ) = 1
          then
          { x{0}, x{1}, y }
          else
          {
            Text.Trim(
              x{0} & "," & x{1} &
              { "-" & x{2}, "" }{ Byte.From( x{1} = x{2} ) },
              ","
            ),
            y,
            y
          }
      ){0}
    }
  )
in
  自定义1

也试着做做
= Table.Group(源, {"序列号"}, {{"发票号码", each
      [m=Table.AddIndexColumn(_,"序号",0),
     n=Table.AddColumn(m,"con",each try if Number.From([发票号码])-Number.From(m[发票号码]
      {[序号]-1})=1 then 0 else 1otherwise 1),
     o=Text.Combine(Table.Group(n,{"con"},{"合并",each Text.Combine([发票号码],"-")},0,(x,y)
       =>Number.From(y=x))[合并],",")
     ][o]}})

let
  源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
  分组 = Table.Group(源, {"序列号"}, {{"发票号码", each
      [m=Table.AddIndexColumn(_,"序号",0),
     n=Table.AddColumn(m,"con",each try if Number.From([发票号码])-Number.From(m[发票号码]
      {[序号]-1})=1 then 0 else 1otherwise 1),
     o=Text.Combine(Table.Group(n,{"con"},{"合并",each
                  if List.Count([发票号码])>1
                  then List.First([发票号码])&"-"&List.Last([发票号码])
                  else [发票号码]{0}},
           0,(x,y)=>Number.From(y=x))[合并],",")
     ][o]}})
in
  分组

高手云集 果断围观

前排顶，很好！

努力学习中