合并Excel中重复项的行 [英] Merging rows for duplicates in excel

问题描述

我有一个Excel工作表，其中包含300个重复项.我需要第一张桌子看起来像第二张桌子.

I have an excel sheet with 300 duplicates. I need the first table to look like second table.

我不知道如何使用vba.

I don't know how to use vba.

我认为在sql中按名称分组可能会有所帮助.我需要有关如何在excel中做到这一点的指导.

I thought grouping by names in sql could help. I need guidance on how I could do this in excel.

我已经尝试过使用数据透视表，但是它并不能完全满足我的需求.

I've tried with a pivot table but it doesn't exactly give me what I want.

关于如何实现这一目标的建议会很棒.

Suggestions on how to achieve this would be great.

推荐答案

使用 powerquery (获取并转换数据).

确保将源数据格式化为表，并在第一列中添加标题名称".然后，您可以使用以下查询:

Make sure your source data is formatted as a table, and give the first column the header "Name". Then you can use a query like:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

编辑，这是一个使用您的数据的有效示例: https://excel.solutions/53186328/

EDIT Here's a a worked example, using your data: https://excel.solutions/53186328/

这篇关于合并Excel中重复项的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！