Excel:如何收集一列中与另一列中的重复项相关联的唯一值? [英] Excel: How to gather unique values in one column that are associated with duplicates in another column?

问题描述

有这样的数据:

Column A     Column B
1               98
1               12
1               21 
1               31   
2               37
2               40
3               48 
4               34
4               88
4               74 
4               99
7               82
7               19
7               29
7               50 
7               95
7               85

其中 Column B 中的所有值都是唯一的，并且 Column A 有多个重复项.如何将 A 值分组在一列中，并在另一列中显示 B 的连接值，如下所示:

where all values in Column B are unique, and Column A has multiple duplicates. How can I group the A values in one column, and display concatenated values of B in another, like this:

Column C   Column D
1             98,12,21,31
2             37,40
3             48
4             34,88,74,99
7             82,19,29,50,95,85

我尝试过将连接、索引和匹配结合使用，但一切都变得一团糟.

I've tried with a combination of concatenate and index and match, but it all just turns into a mess.

有什么好的建议吗?

推荐答案

让我在@Harun24HR 的答案中添加两个额外的方法.这两个选项都假设您没有示例数据中的标题.

Let me add two additional methods to the answer by @Harun24HR. Both options assume you don't have headers as per your sample data.

选项 1):动态数组函数

当您可以访问动态数组函数时，您可以使用以下内容:

When one has access to dynamic array functions you may use the following:

在C1中:

=UNIQUE(A1:A17)

这个UNIQUE 函数将将定义范围内的唯一值数组溢出到 C 列中.

在D1中:

=TEXTJOIN(",",TRUE,FILTER(B$1:B$17,A$1:A$17=C1))

而 FILTER 将提取所有值从 A 列匹配的 B 列是 TEXTJOIN 将这些值连接到您想要的字符串中.

Whereas FILTER will extract all values from column B where column A matches it is TEXTJOIN that will concatenate these values into your desired string.

下拉...

选项 2):PowerQuery

如果您想尝试 PowerQuery/GetAndTransform，那么您不需要任何公式，也不需要 VBA.请按照以下步骤操作:

Would you want to experiment with PowerQuery/GetAndTransform then you don't need any formulas nor VBA for that matter. Follow these steps:

• 选择A1:B17，然后从功能区中选择获取和转换数据"下的Data > From Table/Range
• 选择导入不带标题的数据.将打开一个新窗口.
• 从功能区中单击Transform > Group By.在该菜单中选择按 Column1 分组，选择一个新的列名称，例如:分组"，然后从操作下拉列表中选择 All Rows 并单击 OK.
• 您会注意到一个额外的列.现在在功能区上单击 Add Column > Custom Column 并输入以下公式:Table.Column([Grouped], "Column2").这应该添加包含值列表的第三列.
• 从表中删除Grouped.然后单击新添加的列名称右侧的图标，您将有两个选项.选择 Extract Values，然后选择逗号作为分隔符.

• Select A1:B17 and from the ribbon choose Data > From Table/Range under "Get & Transform Data"
• Choose to import data without headers. A new window will open.
• From the ribbon click Transform > Group By. Within that menu choose to group by Column1, choose a new column name, e.g.: "Grouped" and then choose All Rows from the Operation dropdown and click OK.
• You'll notice an extra column. Now on the ribbon click Add Column > Custom Column and enter the following formula: Table.Column([Grouped], "Column2"). This should add a third column that holds a list of values.
• Remove Grouped from the table. Then click on the icon to the right of the newly added column name, and you'll have two options. Choose Extract Values, then choose a comma as your delimiter.

下面的 M 代码中可能存在翻译错误，但应该是这样:

There might be a translation-error in the M-code below, but this should be it:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Grouped", each _, type table [Column1=number, Column2=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Grouped], "Column2")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Grouped"})
in
    #"Removed Columns"

如果我没记错的话，可以从 Excel-2010 中使用 PowerQuery，因此您不需要访问诸如 TEXTJOIN 之类的高级公式来执行此操作.

PowerQuery is available from Excel-2010 if I'm not mistaken so you wouldn't need access to advanced formulas like TEXTJOIN to perform this.

这篇关于Excel:如何收集一列中与另一列中的重复项相关联的唯一值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！