☰

如何聚合给定日期的文本数据

tag-icon • tag-icon microsoft-excel worksheet-function database
如何聚合给定日期的文本数据

我正在创建一个 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？

非常感谢

答案1

如果您有 Excel 365，您可以组合TEXTJOIN和FILTER函数。

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

结果

答案2

如果您没有 Office 365，则可以使用Power QueryWindows Excel 2010+ 中提供的获取所需的输出

  • 选择原始表格中的某个单元格
  • Data => Get&Transform => From Table/Range
  • 当 PQ UI 打开时，导航至Home => Advanced Editor
  • 记下代码第 2 行的表名称。
  • 用以下代码替换现有代码M 代码以下
  • 将粘贴代码第 2 行的表名更改为您的“真实”表名
  • 检查任何注释以及窗口Applied Steps，以更好地理解算法和步骤

M 代码

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"

在此处输入图片描述

相关内容