☰

EXCEL 根据列对值进行分组

tag-icon • tag-icon microsoft-excel
EXCEL 根据列对值进行分组 
      Dates         Column 2    Column 3    Group By
17.02.2020 21:09:46    A          AA           1
17.02.2020 20:09:57    A          AB           2
17.02.2020 20:10:20    A          AB           2
17.02.2020 19:10:49    B          AC           3
17.02.2020 19:10:52    B          AC           3
17.02.2020 19:11:17    C          AD           4
17.02.2020 19:11:21    E          AD           5

如果第 2 列和第 3 列匹配且日期差异小于 1 分钟，是否可以分组。上表是预期场景。

答案1

一种方法是使用 Excel 2010+ 中提供的 Power Query。它不需要数据按任何特定顺序排列。并且如果数据发生变化，也可以轻松刷新。

所有步骤都可以从 UI 中完成，但输入自定义列需要在相应的对话框中输入公式

异律节律

  • 因为我在美国，你的日期/时间戳被我的系统解释为文本，所以我需要一个Change Type with Locale你可能需要或不需要的步骤
  • 然后我们添加一列，将日期/时间四舍五入到最接近的分钟。
    • 公式： =Number.Round(Number.From([Dates])*24*60)/(24*60)

在此处输入图片描述

  • 我们选择第 2、3 列和我们的自定义列，并且Group By不进行聚合

在此处输入图片描述

  • 然后，我们添加另一个自定义列，将原始日期列“拆分”为List
    • 公式：=Table.Column([Grouped],"Dates")

在此处输入图片描述

  • 然后，我们删除多余的列，并将日期/列表列重新排序到开头
  • 添加以 1 开头的索引列
  • 单击日期列的双箭头以展开到新行
  • 然后我将日期列类型更改为日期时间。

在此处输入图片描述

M 代码

    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", type text}, {"Column 2", type text}, {"Column 3", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Dates", type datetime}}, "en-150"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each Number.Round(Number.From([Dates])*24*60)/(24*60)),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Column 2", "Column 3", "Custom"}, {{"Grouped", each _, type table [Dates=datetime, Column 2=text, Column 3=text, Custom=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Dates", each Table.Column([Grouped],"Dates")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Grouped"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Dates", "Column 2", "Column 3"}),
    #"Added Index" = Table.AddIndexColumn(#"Reordered Columns", "Index", 1, 1),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Index", "Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type datetime}})
in
    #"Changed Type1"

笔记： 为了与您的示例精确保真，您可以将 Index 列的名称更改为Group By

相关内容