如何根据表中的名称及其点数（从列中）获取总和的列表？

Question 1

此公式解决方案可在 Excel 2010 至 2019 中采用，包括 OP 的 Excel 2016

1. 中间表在 K:L 列设置

1.1] 在“UniqueName”中K2，复制公式：

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,ROW($2:$7)/1%+{2,3,4,5}/(COUNTIF(K$1:K1,$B$2:$E$7&"")=0),1),"R0C00"),0),"")

1.2] 在“TotalScore”中L2，复制公式如下：

=IF(K2="","",SUMPRODUCT((B$2:E$7=K2)*{5,3,2,1}))

2. 结果表在 G 列：I 中设置，条件在 G 列中

2.1]在“要点”中H2，把公式抄下来：

=LARGE($L$2:$L$12,ROW(A1))

2.2]在“名称”中I2，复制公式：

=INDEX(K:K,AGGREGATE(15,6,ROW($K$2:$K$12)/($L$2:$L$12=H2),COUNTIF(H$2:H2,H2)))

Answer

此公式解决方案可在 Excel 2010 至 2019 中采用，包括 OP 的 Excel 2016

1. 中间表在 K:L 列设置

1.1] 在“UniqueName”中K2，复制公式：

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,ROW($2:$7)/1%+{2,3,4,5}/(COUNTIF(K$1:K1,$B$2:$E$7&"")=0),1),"R0C00"),0),"")

1.2] 在“TotalScore”中L2，复制公式如下：

=IF(K2="","",SUMPRODUCT((B$2:E$7=K2)*{5,3,2,1}))

2. 结果表在 G 列：I 中设置，条件在 G 列中

2.1]在“要点”中H2，把公式抄下来：

=LARGE($L$2:$L$12,ROW(A1))

2.2]在“名称”中I2，复制公式：

=INDEX(K:K,AGGREGATE(15,6,ROW($K$2:$K$12)/($L$2:$L$12=H2),COUNTIF(H$2:H2,H2)))

Question 2

如果您有 Windows Excel O365，您可以使用中间表和复杂公式来执行此操作。

如果您不这样做，Power Query 可以说是更好的选择。

原始数据

中间表 工作表上的任意位置，给出每个人的总分

在这种情况下，数据H8:I18

H7: Total Score
I7: Name
I8: =UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,Scores[[5Points]:[1Point]])&"</s></t>","//s"))
H8: =SUM(IFERROR(1/(I8=Scores[[5Points]:[1Point]])*{5,3,2,1},""))  and fill down to match the "Name" column

最终结果

C13: Pts
D13: Name
B14:B17 1st|2nd|3rd|4th

C14:  =INDEX(SORT(H8:I18,1,-1),{1;2;3;4},{1,2})

正如我所写，如果您没有 O365 但有 Excel 2010+，则可以使用 Power Query。代表原始数据名为Scores。如果您要使用其他名称，请在代码第 2 行中更改表名。

我建议您查看将代码粘贴到 Power Query 中的高级编辑器中时出现的“应用步骤”，并阅读代码中的注释以了解发生了什么

let
    Source = Excel.CurrentWorkbook(){[Name="Scores"]}[Content],

//Remove unneeded first column
    #"Removed Columns" = Table.RemoveColumns(Source,{"Name"}),

//Unpivot all columns to create a single Name column
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Name"),

//Split of the point amount from the original column Headers
    #"Split Column by Character Transition" = Table.SplitColumn(
            #"Unpivoted Columns", "Attribute", 
            Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)),
            {"Attribute.1"}),

//Set the appropriate data types
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Character Transition",{{"Attribute.1", Int64.Type}, {"Name", type text}}),

//Group by "Name" and aggregate the SUM
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Pts", each List.Sum([Attribute.1]), type nullable number}}),
    
//Find the fourth largest value
    max4 = List.MaxN(Table.Column(#"Grouped Rows","Pts"),4),
    threshold = List.Last(max4),

//Filter for the top 4 items and sort descending
    top4 = Table.SelectRows(#"Grouped Rows", each [Pts] >= threshold),
    #"Sorted Rows" = Table.Sort(top4,{{"Pts", Order.Descending}}),

//Convert to Ordinal values for Rank column
//Note that if you will be returning ranks > 9, will need to change the List.ContainsAny Values to be only the last digit
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Rank", each if List.ContainsAny({0,4..9},{[Index]}) then 
            Number.ToText([Index],"0'th'") else 
          if List.ContainsAny({2,3},{[Index]}) then 
            Number.ToText([Index],"0nd") else 
            Number.ToText([Index],"0st")),

//Remove unwanted Index column and re-order the columns
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Rank", "Pts", "Name"})
in
    #"Reordered Columns"

Answer