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 函数将

This UNIQUE function will spill an array of unique values from defined range into column C.

在D1中:

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

FILTER 将从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/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.在该菜单中，选择按列1分组，选择一个新的列名称，例如:分组"，然后从操作"下拉列表中选择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"

如果我没记错的话，PowerQuery可以从Excel-2010中获得，因此您无需访问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屋！