我有一个包含如下数据的电子表格:
产品 | 属性 ----------+---------- 产品 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 是标题行。
种类按 A 列按产品分组
在 C 列中以逗号分隔格式准备数据在 C2 中输入以下公式并复制到 C3:C10。
=IF(A2<>A1, B2, C1 & "," & B2)
识别有用的行输入 D2
=A2<>A3
并复制到 D3:D10。复制 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
删除无用的行通过
FALSE
使用自动筛选器在 D 列中进行筛选,然后删除这些行。结束。A 和 C 列就是您所需要的。
答案2
我知道这是一篇旧帖子,但我今天遇到了这个挑战。我使用了PowerQuery来自 Microsoft 的插件(注意:它默认内置于 Excel 2016 中)。
- 选择您的桌子
- 在 POWER QUERY 选项卡(或 2016 年的数据)下,选择“来自表格”
- 点击“产品”栏
- 在“变换”选项卡下,选择“分组依据”
- 在“视图”选项卡上,确保选中“公式栏”
改变公式
从:
= 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
这里有几种方法,都是“非宏观的”......
对于小数据集,在先按产品排序(类似于按产品分组)后,您可以先复制“产品”列,将其粘贴到其他地方,然后删除重复项。接下来,复制每个产品的“属性”,并在每个产品旁边“选择性粘贴,转置”。然后在最终结果列中将逗号与每个转置属性连接起来。诚然,如果您的产品列表很长,所有这些“复制/选择性粘贴/转置”很快就会过时。
如果您有大量数据,则可以使用几个公式来得出最终结果,如下所示。F2、G2、H2、I2 和 N2 中的公式由蓝色箭头表示。根据需要将它们复制到下面的行。请注意,J2:L2 使用与 I2 相同的公式。此外,F2 公式引用了跨越范围 A:A 的命名范围“Products”。