如何在Excel中根据一列中的值合并行?

avwztpqn  于 2022-12-20  发布在  其他
关注(0)|答案(3)|浏览(172)

如果这些行在一列中有重复值,我想合并/连接这些行。合并步骤适用于多列。下表显示了该问题的示例:

+-----+--------+--------+--------+--------+--------+----------+
|     |   A    |   B    |   C    |   D    |   E    |     F    |
+-----+--------+--------+--------+--------+--------+----------+
| Dog |        |        | param1 |        |        |          |
+-----+--------+--------+--------+--------+--------+----------+
| Dog | param2 |        |        |        |        |          |
+-----+--------+--------+--------+--------+--------+----------+
| Dog |        |        |        |        |        |          |
+-----+--------+--------+--------+--------+--------+----------+
| Dog |        |        |        |        |        | param3   |
+-----+--------+--------+--------+--------+--------+----------+
| Cat |        | param5 |        |        |        |          |
+-----+--------+--------+--------+--------+--------+----------+
| Cat |        |        |        | param6 |        |          |
+-----+--------+--------+--------+--------+--------+----------+

我有大约4000个唯一的行值和大约30个列。重复的行值的范围从n=1到n=10。
我喜欢的table:

+-----+--------+--------+--------+--------+--------+----------+
|     |   A    |   B    |   C    |   D    |   E    |     F    |
+-----+--------+--------+--------+--------+--------+----------+
| Dog | param2 |        | param1 |        |        | param3   |
+-----+--------+--------+--------+--------+--------+----------+
| Cat |        | param5 |        | param6 |        |          |
+-----+--------+--------+--------+--------+--------+----------+

这可以在Excel中用一些魔法完成吗?或者我需要像Python这样的高级工具吗?我已经尝试了多个公式,但没有成功。
先谢谢你

ymzxtsji

ymzxtsji1#

这也可以使用Windows Excel 2010+和Excel 365(Windows或Mac)中提供的Power Query来完成
使用增强查询的步骤

  • 选择数据表中的某个单元格
  • 第一个月
  • PQ编辑器打开时:Home => Advanced Editor
  • 记录第2行中的表名称
  • 将下面的M代码粘贴到您看到的位置
  • 将第2行中的Table名称改回最初生成的名称。
  • 阅读评论并探索Applied Steps以了解算法
    M代码
let

//change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],

//set all columns to data type text
    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(Table.ColumnNames(Source), each {_, type text})),

//Group by Animal
//Then "Fill Up" each column and return only the first row
    #"Group Animal" = Table.Group(#"Changed Type","Animal", 
        {"Consolidate", each Table.FillUp(_,Table.ColumnNames(_)){0}}),

//Expand the grouped table and re-set the data types to text
    #"Expanded Consolidate" = Table.ExpandRecordColumn(#"Group Animal", "Consolidate",
        List.RemoveFirstN(Table.ColumnNames(#"Changed Type"))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Consolidate", List.Transform(Table.ColumnNames(#"Expanded Consolidate"), each {_, type text}))
in
    #"Expanded Consolidate"

fwzugrvs

fwzugrvs2#

很棘手。一种方法是将REDUCE()嵌套在另一种方法中:

A8中的公式:

=DROP(REDUCE(0,UNIQUE(A1:A6),LAMBDA(a,b,VSTACK(a,REDUCE(b,SEQUENCE(6),LAMBDA(x,y,HSTACK(x,@SORT(INDEX(FILTER(B1:G6&"",A1:A6=b),,y),,-1))))))),1)

或者,更动态一点:

=LET(r,A1:G6,s,TAKE(r,,1),t,DROP(r,,1)&"",DROP(REDUCE(0,UNIQUE(s),LAMBDA(a,b,VSTACK(a,REDUCE(b,SEQUENCE(COLUMNS(t)),LAMBDA(x,y,HSTACK(x,@SORT(INDEX(FILTER(t,s=b),,y),,-1))))))),1))
iezvtpos

iezvtpos3#

突出显示需要分组的值,然后转到数据,单击groe选项卡

相关问题