☰

在 Excel 中计算平均值

tag-icon • tag-icon microsoft-excel worksheet-function
在 Excel 中计算平均值

我有以下 csv 文件
文件 Genre_all.xlsx

A                            B  

Action|Adventure|Horror      4.8  
Action|Music                 7.2  
Horror|Adventure             5.6

现在，我需要一个如下文件
File Genre_avg.xlsx

A               B  
Action          6  
Adventure       5.2  
Horror          5.2  
Music           7.2

B 列是与类型对应的分数的平均值。
例如，对于动作：(4.8+7.2)/2 = 6
我应该使用什么命令来获得以下输出？

答案1

如果第一个表格为sheet1，第二个表格为sheet2，则单元格 B2sheet2可以是

=AVERAGEIF(sheet1!A:A;"*" & A2 & "*";sheet1!B:B)

等等。

答案2

这可以使用 PowerQuery 来完成。

拆分第一列，然后对前 3 个结果列进行逆透视。然后您需要按值分组

Value   Average
Action  6
Adventure   5.2
Horror  5.2
Music   7.2

您通常使用 PowerQuery 进行点击，但生成的 M 代码是：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type number}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Column1",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column2"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Value", "Column2"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Value"}, {{"Average", each List.Average([Column2]), type number}})
in
    #"Grouped Rows"

如果您有 Excel 2010 或 2013，PowerQuery 是一个免费插件。Excel 2016 已内置该插件。

相关内容