用定界列展开表格 [英] Expand Table with Delimited Column

问题描述

我经常看到这个问题，所以我创建了这个问题并回答，以便我(和其他贡献者)将来可以指出它.

I see this question a lot, so I'm creating this question and answer so I (and other contributors) can just point to it in the future.

比方说，我们遇到的情况是有一个看起来像这样的表:

Let's say we have a situation where there is a table that looks like this:

Category    Items
Fruit       Apple,Orange
Vegetable   Carrot,Potato

我们想将它变成一个看起来像这样的表:

And we want to turn it into a table that looks like this instead:

Category    Items
Fruit       Apple
Fruit       Orange
Vegetable   Carrot
Vegetable   Potato

在此示例中，我们希望扩展表，以便每个项目都有自己的行，而不是在分隔列中每个类别的同一行.我们如何使用Excel VBA做到这一点?

In this example, we want to expand the table so that each item gets its own row, instead of being on the same row per category in a delimited column. How can we accomplish this using Excel VBA?

推荐答案

另一个选择是使用 Power Query ；现在命名为获取&转换.它是一个自Excel 2010版本以来发布的加载项，用于ETL(提取，转换，加载)/是为数据分析开发的.在那里，您可以连接多个源并根据需要转换数据.

Another option is using Power Query; now named Get & Transform. It is an Add-in, released since Excel 2010 version, for ETL (Extract, Transform, Load)/ developed for Data Analysis. There you can connect multiple sources and transform the data as you want.

我们可以在 Applied Steps 中逐步检查，并且它还有自己的代码，称为 Power M语言；我们可以在首页标签的高级编辑器中找到它，在这里我们可以逐行查看和编辑您的转换步骤.

We can check step by step in Applied Steps and it also has its own code known as Power M Language; We can find it in Advanced Editor , in Home tab, where we can see and edit, line by line, your transformation steps.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Items", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Items.1", "Items.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Items.1", type text}, {"Items.2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Category"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Item"}})
in
    #"Renamed Columns"

这篇关于用定界列展开表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！