☰

如何聚合給定日期的文字數據

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"

在此輸入影像描述

相關內容