Расчет среднего в Excel

Question

У меня есть следующие CSV-файлы
Файл Genre_all.xlsx

A                            B  

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

Теперь мне нужен файл следующим образом
Файл Genre_avg.xlsx

A               B  
Action          6  
Adventure       5.2  
Horror          5.2  
Music           7.2  

Столбец B является средним из баллов, соответствующих жанру.
Например. Для действий: (4.8+7.2)/2 = 6
Какую команду я должен использовать, чтобы получить следующий вывод?

Answer 1

Если у вас есть первая таблица как sheet1 а вторая как sheet2 , ячейка B2 в sheet2 может быть

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

и так далее.

Answer 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 он встроен.