对标签进行分组并连接其文本值(如数据透视表)

对标签进行分组并连接其文本值(如数据透视表)

我有一个包含如下数据的电子表格:

产品 | 属性
----------+----------
产品 A | 青色
产品 B | 青色
产品 C | 青色
产品 A | 洋红色
产品 C | 洋红色
产品 B | 黄色
产品 C | 黄色
产品 A | 黑色
产品 B | 黑色

我想要做的是按 A 列对所有内容进行分组,并让 B 列成为与 A 列共有的值的逗号分隔列表,如下所示:

产品 | 属性
----------+-----------------------------------------
产品 A | 青色、洋红色、黑色
产品 B | 青色、黄色、黑色
产品 C | 青色、洋红色、黄色、黑色

不幸的是,数据透视表只知道如何处理数字值,而它最多只能计算 A 列出现的次数。

GROUP_CONCAT(Attribute)最终,我将数据导入 MySQL 数据库,并在带有子句的查询中使用 MySQL 函数,成功实现了这一目标GROUP BY Product,但在尝试找出 Excel 解决方案时,我的头反复撞在桌子上。

供将来参考,在没有宏的情况下,这在 Excel 中可行吗?无论是否可行,如何实现这一点?

答案1

   |     A     |     B
---+-----------+-----------
 1 |  PRODUCT  | ATTRIBUTE
 2 | Product A | Cyan
 3 | Product B | Cyan
 4 | Product C | Cyan
 5 | Product A | Magenta
 6 | Product C | Magenta
 7 | Product B | Yellow
 8 | Product C | Yellow
 9 | Product A | Black
10 | Product B | Black

假设行 1:1 是标题行。

  1. 种类按 A 列按产品分组

  2. 在 C 列中以逗号分隔格式准备数据在 C2 中输入以下公式并复制到 C3:C10。

    =IF(A2<>A1, B2, C1 & "," & B2)
    
  3. 识别有用的行输入 D2=A2<>A3并复制到 D3:D10。

  4. 复制 C:D 列,然后特殊粘贴作为值(AltE- S- V- Enter)。您现在将获得:

    Product A    Cyan       Cyan                   FALSE
    Product A    Magenta    Cyan,Magenta           FALSE
    Product A    Black      Cyan,Magenta,Black     TRUE
    Product B    Cyan       Cyan                   FALSE
    Product B    Yellow     Cyan,Yellow            FALSE
    Product B    Black      Cyan,Yellow,Black      TRUE
    Product C    Cyan       Cyan                   FALSE
    Product C    Magenta    Cyan,Magenta           FALSE
    Product C    Yellow     Cyan,Magenta,Yellow    TRUE
    
  5. 删除无用的行通过FALSE使用自动筛选器在 D 列中进行筛选,然后删除这些行。

  6. 结束。A 和 C 列就是您所需要的。

答案2

我知道这是一篇旧帖子,但我今天遇到了这个挑战。我使用了PowerQuery来自 Microsoft 的插件(注意:它默认内置于 Excel 2016 中)。

  1. 选择您的桌子
  2. 在 POWER QUERY 选项卡(或 2016 年的数据)下,选择“来自表格”
  3. 点击“产品”栏
  4. 在“变换”选项卡下,选择“分组依据”
  5. 在“视图”选项卡上,确保选中“公式栏”
  6. 改变公式

    从:

    = Table.Group(#"Changed Type", {"Product"}, {{"Count", each Table.RowCount(_), type number}})
    

    到:

    = Table.Group(#"Changed Type", {"Product"}, {{"Attributes", each Text.Combine([Attribute], ", "), type text}})
    

    点击此处查看上方步骤图片

第 6 步是利用 Power Query (M) 公式执行 UI 中提供的基本操作未公开的数据操作。Microsoft 在网上提供了 Power Query 中所有高级功能的完整参考。

答案3

这里有几种方法,都是“非宏观的”......

  1. 对于小数据集,在先按产品排序(类似于按产品分组)后,您可以先复制“产品”列,将其粘贴到其他地方,然后删除重复项。接下来,复制每个产品的“属性”,并在每个产品旁边“选择性粘贴,转置”。然后在最终结果列中将逗号与每个转置属性连接起来。诚然,如果您的产品列表很长,所有这些“复制/选择性粘贴/转置”很快就会过时。

  2. 如果您有大量数据,则可以使用几个公式来得出最终结果,如下所示。F2、G2、H2、I2 和 N2 中的公式由蓝色箭头表示。根据需要将它们复制到下面的行。请注意,J2:L2 使用与 I2 相同的公式。此外,F2 公式引用了跨越范围 A:A 的命名范围“Products”。

在此处输入图片描述

相关内容