Calculando Média no Excel

Question

Eu tenho os seguintes arquivos csv: Arquivo Genre_all.xlsx

A                            B  

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

Agora, preciso de um arquivo da seguinte forma: Arquivo Genre_avg.xlsx

A               B  
Action          6  
Adventure       5.2  
Horror          5.2  
Music           7.2  

A coluna B é a média das pontuações correspondentes ao gênero.
Por exemplo. Para a ação: (4.8 + 7.2) / 2 = 6
Qual comando devo usar para obter a seguinte saída?

    

Answer 1

Se você tiver a primeira tabela como sheet1 e a segunda como sheet2 , a célula B2 em sheet2 poderá ser

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

e assim por diante.

    

Answer 2

Isso pode ser feito usando o PowerQuery.

Divida a primeira coluna e, em seguida, desmova as primeiras 3 colunas resultantes. Você precisaria então agrupar em Value

Value   Average
Action  6
Adventure   5.2
Horror  5.2
Music   7.2

Você geralmente usa o PowerQuery com apontar e clicar, mas o código M resultante é:

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"

Se você tiver o Excel 2010 ou 2013, o PowerQuery é um suplemento gratuito. O Excel 2016 foi incorporado.