Excel 中具有 Max 等效性的 Sql Group By

Question

我的 Excel 表有以下数据作为示例：

type   |make    |price
--------------------
cars   |honda   |500
cars   |acura   |600
cars   |ford    |550
trucks |toyota  |350
trucks |ford    |475
trucks |chevy   |400

我可以使用数据透视表按类型和品牌进行分组，并获取价格的最大值，但我真正想要的是获取价格的最大值，然后获取与该最大值相关联的单个记录，以便数据最终看起来像：

type   |make    |max of price
--------------------
cars   |acura   |600
trucks |ford    |475

我可以轻松地在 SQL 中对数据库执行此查询，但在 Excel 中找不到执行此查询的正确方法。

Answer 1

使用FILTER来获取正确的行：

=FILTER($A$2:$C$7,MAXIFS($C$2:$C$7,$A$2:$A$7,$A$2:$A$7)=$C$2:$C$7)

第一个参数是要过滤的数据范围。第二个参数是条件。因此，按类型计算的最大价格等于当前行价格。

Answer 2

您可以使用 Power Query 执行此操作。

• 不进行聚合就进行分组type。
• 然后添加两个自定义列以返回make与price每个结果表（组）的最高价格相关联的列。

M 代码

let
    Source = Excel.CurrentWorkbook(){[Name="vehicles"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"type"}, {{"Grouped", each _, type table [type=nullable text, make=nullable text, price=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "make", each Record.Field(Table.Max([Grouped],"price"),"make")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "price", each Record.Field(Table.Max([Grouped],"price"),"price")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"make", type text}, {"price", Currency.Type}, {"type", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Grouped"})
in
    #"Removed Columns"