提取各指标TOP5

题目:


如图,从左表到右表,提取各指标中TOP5所对应的姓名。
 

解法1:

常规思路,逆透视后分组,排序并添加索引,筛选出TOP5然后展开。

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    逆透视 = Table.UnpivotOtherColumns(源, {"姓名"}, "属性", "值"),
    分组 = Table.Group(逆透视, {"属性"}, {"a", each Table.TransformColumns(Table.FirstN(Table.AddIndexColumn(Table.Sort(_,{"值",1}),"排名",1,1),5),{"排名",each "TOP"&Text.From(_)})}),
    展开 = Table.ExpandTableColumn(分组, "a", {"姓名", "排名"}),
    合并 = Table.AddColumn(展开, "类别", each [属性]&[排名]),
    删除 = Table.RemoveColumns(合并,{"属性", "排名"})
in
    删除

 

解法2:

直接使用#table构建表格,配合List.Transform得到每个指标的子表然后合并,一步出结果但是会比较难理解。

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    结果 = Table.Combine(List.Transform(List.Skip(Table.ColumnNames(源)),each #table({"指标编号","姓名"},List.Zip({List.Transform({"1".."5"},(x)=>_&"TOP"&x),Table.FirstN(Table.Sort(源,{_,1}),5)[姓名]}))))
in
    结果

附件

17 Replies to “提取各指标TOP5”

  1. 用replacevalue的思路

    let
        Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
        n = Table.Group(Table.UnpivotOtherColumns(Source, {"姓名"}, "a", "b"), {"a"},{"c",(v)=>Table.FirstN(Table.Sort(Table.Combine( List.Transform({1..5}, (t)=> Table.ReplaceValue (Table.Range(v,t-1,1), each _[a], 0, (x,y,z)=> x & "top" & Text.From(t), {"a"} )
    )), {"b",Order.Descending}),5)}),
        m = Table.ExpandTableColumn(n, "c", {"姓名", "a", "b"}, {"姓名", "a.1", "b"})
    in
        m
    
    1. 咨询一下,上面n里面的v是代表分组后表吗?最里层替换和外面排序是谁先谁后?感觉是不是先对v进行排序在进行替换比较合理?

  2. 老师好:我的做法:在Power Query逆透视列后加载到PowerPivo,运用RANKX结合EARLIER对值进行排名(=RANKX(FILTER('表1[属性]=EARLIER('表1[属性])),[值])),透视后也基本达到要求。但是若要达到您所示效果,还要增加几列。

  3. let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    逆透视的其他列 = Table.UnpivotOtherColumns(更改的类型, {"姓名"}, "属性", "值"),
    分组的行 = Table.ExpandTableColumn(Table.Group(
    逆透视的其他列, {"属性"}, {{"计数", each
    [ t1 = Table.Sort(_,{{"值", Order.Descending}}),
    t2 = Table.AddIndexColumn(t1, "top名次", 1 , 1),
    t3 = Table.SelectRows(t2, each [top名次] <= 5),
    t4 = Table.AddColumn(t3,"top",each Text.From([属性])&"top"&Text.From([top名次]))

    ][t4]
    }} )
    , "计数", {"姓名", "top"}, {"姓名", "top"}),
    删除的列 = Table.RemoveColumns(分组的行,{"属性"})
    in
    删除的列

  4. 施神:
    解法2:中的--------Table.Sort(源,{_,1}
    我单独拿出来用会出现“Expression.Error: 指定的排序条件无效。”的提示。
    请问这里的下划线“_”应该怎么去理解,上下文环境是什么。能不能帮忙解答下。

    1. 逐个提取除姓名外的列名,先构建类别列,后面构建姓名列,用list.zip压缩成对,再用#table构建成表,最后合并
      简直是神思路,佩服阳神

        1. 感谢阳神解答,给您32个赞!!!
          let
          源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
          结果 = Table.Combine(List.Transform(List.Skip(Table.ColumnNames(源)),each #table({"指标编号","姓名","成绩"},List.Zip({List.Transform({"1".."5"},(x)=>_&"TOP"&x),Table.MaxN(源, _ ,5)[姓名],Table.Column (Table.MaxN(源, _ ,5),_)}))))
          in
          结果

  5. let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    指标 = List.Skip(Table.ColumnNames(源)),
    自定义1 = Table.Combine(List.Transform(指标,each Table.SelectColumns(Table.TransformColumns(Table.AddIndexColumn(Table.MaxN(源,_,5),"top",1),{"top",(x)=>_&Number.ToText(x,"top0")}),{"top","姓名"}) ))
    in
    自定义1

  6. let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    转换 = Table.ToColumns( 源),
    组合 = Table.Combine( List.Transform({1..List.Count(转换)-1},each Table.AddIndexColumn(Table.AddColumn(Table.MaxN(Table.FromColumns({转换{0},转换{_}},
    {"姓名","分数"}),
    "分数",5),
    "指标",(n)=>"指标"&Text.From(_)&"TOP"),
    "a",1))),
    合并 = Table.CombineColumns(Table.TransformColumnTypes(组合, {"a", type text}),{"指标", "a"},each Text.Combine(_,""),"指标排名")
    in
    合并

    多做点,顺便把分数带出来

  7. 楼主的源代码,格式调整一下就容易理解了
    let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    结果 =
    Table.Combine //合并构建出来的全部表格
    (
    List.Transform
    (
    List.Skip(Table.ColumnNames(源)), //源表表头所构成的List 去除"姓名",后文中的_指代该列表
    each #table //构建表
    (
    {"指标编号","姓名"}, //表头
    List.Zip
    (
    {
    List.Transform({"1".."5"},(x)=>_&"TOP"&x), //[指标编号]列为 各指标&TOP1..5
    Table.FirstN(Table.Sort(源,{_,1}),5)[姓名] //对源表分别根据各指标排序,取各指标的前5项的姓名
    }
    )
    )
    )
    )
    in
    结果

发表回复

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