Как агрегировать текстовые данные за указанную дату

Question

Я создаю базу данных Excel для 1) отслеживания часов и 2) отслеживания типов событий. В Sheet1 я ввожу отдельные события на определенную дату и потраченные минуты. Моя цель — консолидировать Sheet2.

Лист1

Дата	Тип события	Затраченное время (минуты)
2021-12-09	Администрация	10
2021-12-09	Заседание совета	60
2021-12-05	Общественная работа	90
2021-12-05	Администрация	10
2021-12-05	Подготовка	5

Лист2

Дата	События	Общее время (чч:мм)
2021-12-09	Администрация, заседание совета	01:10
2021-12-05	Администрация, Общественная работа, Подготовка	01:45

Я понял, как использовать SUMIF для Sheet2!C2:C3, но как мне реализовать Sheet2!B?

Большое спасибо

Answer 1

Если у вас есть Excel 365, вы можете комбинировать функции TEXTJOINи FILTER.

=TEXTJOIN(", ",TRUE,FILTER(Sheet1!$B$2:$B$6,Sheet1!$A$2:$A$6=$A2))

Answer 2

Если у вас нет Office 365, вы можете получить желаемый результат с помощью Power Query, доступного в Windows Excel 2010+.

• Выберите ячейку в исходной таблице.
• Data => Get&Transform => From Table/Range
• Когда откроется пользовательский интерфейс PQ, перейдите кHome => Advanced Editor
• Обратите внимание на имя таблицы в строке 2 кода.
• Замените существующий код наМ-кодниже
• Измените имя таблицы в строке 2 вставленного кода на «реальное» имя таблицы.
• Изучите все комментарии, а также Applied Stepsокно, чтобы лучше понять алгоритм и шаги.

М-код

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

//set data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Event Type", type text}, 
        {"Time Spent (Minutes)", Int64.Type}}),

//Group by Date
// Aggregate by concatenating Event Type text and SUMming Minutes
//    (change minutes => duration to get desired output format
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {
        {"Event Type", each Text.Combine(List.Sort([Event Type]),", "), type nullable text}, 
        {"Time Spent (Minutes)", each #duration(0,0,List.Sum([#"Time Spent (Minutes)"]),0), type nullable duration}
        })
in
    #"Grouped Rows"