☰

如何在 Excel 中获取按两个值分组的行的总数并对第三个值强制唯一性？

tag-icon • tag-icon microsoft-excel worksheet-function google-spreadsheets
如何在 Excel 中获取按两个值分组的行的总数并对第三个值强制唯一性？

我有一个 Google Sheets 电子表格，它连接到一个外部集成，可以自动用其中一个表格中包含的新“事件”填充行。我想创建第二个表格，其中包含公式，可以自动汇总这些事件的数量，但有一些注意事项。用几张图片来解释这个问题可能是最简单的。以下是输入表格数据的样子（简化）：

输入

如您所见，每个事件行都包含一个唯一的事件 ID、一些日期字段、一个用户 ID 和一个用户名。我最终想要做的是计算每个用户每个月创建了多少个事件。因此，所需的输出将如下所示：

输出

我想也许我可以用COUNTIFS函数来实现这一点？但有一个关键的警告：有时将行添加到输入事件列表的集成会出错并添加重复的行。我在上面的屏幕截图中显示了这一点，您可以看到第 3 行和第 4 行是相同的，都具有相同的事件 ID 126。有什么方法可以让公式足够智能，以便它自动过滤掉任何这样的意外重复？

答案1

有很多方法可以解决这个问题，但就可读性而言，可以GROUPBY()使用MS365 （仅限Beta Users）并用于Google-Sheets使用QUERY()函数。下面显示的公式简单而直接。

在 Excel 中：

在此处输入图片描述

=LET(
     _UniqueRecords, CHOOSECOLS(UNIQUE(EventTable),4,6),
     _Output, GROUPBY(_UniqueRecords,TAKE(_UniqueRecords,,1),COUNTA,,0,-1),
     VSTACK({"Month","User Name","Count"},_Output))

笔记： GROUPBY()或PIVOTBY()功能目前可用于MS365 测试版版本。还要确保将范围转换为有助于自动调整的Structured Referencesaka Tables，从而最大限度地减少在公式中手动更新范围的需要，并确保节省时间，减少出错的风险。

EventTable指的是范围A1:F9

Excel使用函数的另一种方法MMULT()：

在此处输入图片描述

=LET(
     _UniqueRecords, CHOOSECOLS(UNIQUE(EventTable),4,6),
     _JoinRecords, TAKE(_UniqueRecords,,1)&"|"&TAKE(_UniqueRecords,,-1),
     _Counts, MMULT(N(_JoinRecords=TOROW(_JoinRecords)),SEQUENCE(ROWS(_JoinRecords),,,0)),
     _Output, UNIQUE(HSTACK(_UniqueRecords,_Counts)),
     VSTACK({"Month","User Name","Count"},_Output))

也许在 Google 表格中：

在此处输入图片描述

=LET(
     _UniqueRecords, CHOOSECOLS(UNIQUE(A2:F),4,6),
     QUERY(_UniqueRecords,"Select Col1, Col2, Count(Col1)
                           Where Col1 is not null
                           Group By Col1, Col2
                           Order By Col1 desc
                           Label Col1 'Month', Col2 'User Name', Count(Col1) 'Count'"))

这也可以使用和Power Query中的Windows Excel 2010+Excel 365 (Windows or Mac)

在此处输入图片描述

要使用 Power Query，请按照以下步骤操作：

  • 首先将源范围转换为表格并进行相应命名，在此示例中，我将其命名为EventTable
  • Data接下来，从Tab --> Get & Transform Data--> Get Data--> From Other Sources-->打开一个空白查询Blank Query
  • 上面的命令可以Power Query打开窗口，现在从HomeTab --> Advanced Editor--> 删除您看到的任何内容并粘贴以下内容M-Code，然后按Done
let
    Source = Excel.CurrentWorkbook(){[Name="EventTable"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Time", type date}, {"End Time", type date}, {"Month", type text}, {"User Name", type text}, {"Event ID", Int64.Type}, {"User ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Month", "User Name"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"User Name", Order.Ascending}, {"Month", Order.Descending}})
in
    #"Sorted Rows"

在此处输入图片描述

  • 最后，将其导回Excel--> 单击Close & Load或Close & Load To--> 第一个单击的将创建一个New Sheet具有所需输出的，而后者将提示一个窗口询问您将结果放在何处。

相关内容