数据分组问题

cjh-01

如何用PQ或vba实现附件数据分组问题

Eric0236

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    已添加条件列 = Table.AddColumn(源, "分类", each if [销量] < 1000 then "1|<1000" else if [销量] < 2000 then "2|1000~2000" else "3|2000~3000"),
    按分隔符拆分列 = Table.SplitColumn(已添加条件列, "分类", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"序号", "分类"}),
    分组的行 = Table.Group(按分隔符拆分列, {"序号","分类"}, {{"姓名", each Text.Combine([姓名], "\")}, {"销量合计", each List.Sum([销量])}}),
    更改的类型 = Table.TransformColumnTypes(分组的行,{{"序号", Int64.Type}}),
    排序的行 = Table.Sort(更改的类型,{{"序号", Order.Ascending}}),
    删除的列 = Table.RemoveColumns(排序的行,{"序号"})
in
    删除的列

ThxAlot

1. let
   
2.     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY65EsIwDER/hVHtQlg+5JIEHM6OzuOSOjOB/59YEt0+rbSr1uAEDo6JELprMA3wVFTPYmBSfZZ5LFnhIgYhKVSB4FlhGZDYtq5iZA4KNwGOdnLX4L/zkORi7U/Ria3+pa+QhdV1O7w/39+YRcSx3Xc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Qty = _t]),
   
3.     #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Qty", Int64.Type}}),
   
4.     Bucketed = Table.Group(Table.AddColumn(#"Changed Type", "Bucket", each "<" & Text.From(Number.RoundAwayFromZero((1+[Qty])/1000)*1000)), "Bucket", {"Bucketed", each [Names=Text.Combine([Name],","), Total=List.Sum([Qty])]}),
   
5.     #"Expanded Bucketed" = Table.ExpandRecordColumn(Bucketed, "Bucketed", {"Names", "Total"})
   
6. in
   
7.     #"Expanded Bucketed"

复制代码

cjh-01

thank you very much

14820103

Eric0236 发表于 2022-10-15 10:10
let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    已添加条件列 = Table.AddColumn(源 ...

分类的那个竖杠是怎么输入的？还有不包含3000的你的算法是纳入的。

Eric0236

14820103 发表于 2022-10-16 09:39
分类的那个竖杠是怎么输入的？还有不包含3000的你的算法是纳入的。

|和\一个键，随便用什么符号分隔都可以，只要不和源文件有重复就行
3000的话可以再加一个判断条件
= Table.AddColumn(源, "分类", each if [销量] < 1000 then "1|<1000" else if [销量] < 2000 then "2|1000~2000" else if [销量] < 3000 then "3|2000~3000" else null)

hcm19522

參考~~~~

C2{=IFERROR(LOOKUP(1,0/(LOOKUP(B$1:B1,{0,1,2,3}*1000,{1,2,3,4})=LOOKUP(B2,{0,1,2,3}*1000,{1,2,3,4})),C$1:C1),"")&IF(SUM(N(LOOKUP(B$2:B2,{0,1,2,3}*1000,{1,2,3,4})=LOOKUP(B2,{0,1,2,3}*1000,{1,2,3,4})))=1,"","\")&A2

I2=LOOKUP(1,0/(LOOKUP(B$2:B$14,{0,1,2,3}*1000,{1,2,3,4})=ROW(A1)),C$2:C$14)

J2=SUMPRODUCT(ISNUMBER(FIND(A$2:A$14,I2))*B$2:B$14)

zpy2

select * from 按照销量多级分组求和 limit 3;
with aa as (select *,case when 销量<1000 then 'A <1000'  when 销量<2000 then 'B 1000-2000' when 销量<3000 then 'C 2000-3000' else '其他' end 级别 from 按照销量多级分组求和)
select 级别,group_concat(姓名,'\') 姓名,sum(销量) 销量合计 from aa group by 级别;

hbzql

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    添加分组列 = Table.AddColumn(源,"分组",each  if [销量]<1000 then "<1000" else if [销量]<2000 then "1000-2000" else "2000-3000"),
    分组 = Table.Group(添加分组列,"分组",{{"姓名",each Text.Combine([姓名],"/")},{"销售合计",each List.Sum([销量])}})
in
    分组

wdx223

参考~~~~~~~~~~~~