☰

使用 Excel 分离复杂的多对多关系

tag-icon • tag-icon microsoft-excel database pivot-table power-query
使用 Excel 分离复杂的多对多关系

我在 Excel 中有一组数据，其格式如下： 显示数据及其属性/关系的表格 显示高级、中级和低级属性之间关系的图像

我需要帮助找到一种方法来显示此信息：

  1. 一种方法是Excel 工作簿中的半交互原始数据所在的位置（类似数据透视表，使用切片器作为可点击的过滤器）
  2. 原始数据一旦发生变化，即可轻松/快速更新表。（有超过 350 个应用程序，每个都有自己的应用程序名称和各种高/中/低级属性）

我发现了一种解决方案（但效率极低且繁琐），即手动为每个低级属性创建一个新行，并将其逐一匹配到相关的中级属性、高级属性和应用程序名称。但是，这必须在未链接到当前工作表的单独工作表中完成，而且由于低级属性的数量分布在所有 360 个应用程序中，因此在可行的时间范围内根本不可能实现。此解决方案的一个示例如下（全部手动完成）：

2 张表格演示了手动分离解决方案

我曾尝试使用 Power Query 手动分隔并以逗号作为分隔符创建新行，但它对具有多个高/中/低级属性的应用程序无效，因为它会（错误地）将信息复制到多行中。

任何建议/帮助都将不胜感激，谢谢:)

答案1

您可以在 Power Query 中使用辅助列和过滤器执行此操作

查看代码注释并按照应用步骤来理解算法。

该代码仅针对三个级别的属性进行“硬编码”，并显示其名称。

如果有可变数量的子属性，则可以更改代码以适应使用List.Accumulate或List.Generate允许循环。

原始数据
在此处输入图片描述

M 代码

let

//Change next line to reflect data source
    Source = Excel.CurrentWorkbook(){[Name="Table29"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(Table.ColumnNames(Source), each {_, type text})),

//split the columns by delimiters into rows
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"High Level Attribute/s", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "High Level Attribute/s"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"Medium Level Attribute/s", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Medium Level Attribute/s"),
    #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter1", {{"Low Level Attribute/s", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Low Level Attribute/s"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter2",{{"High Level Attribute/s", Text.Trim, type text}, {"Medium Level Attribute/s", Text.Trim, type text}, {"Low Level Attribute/s", Text.Trim, type text}}),

//Add "grouper columns"
    #"Add Med Grouper" = Table.AddColumn(#"Trimmed Text", "Medium Grouper", each Text.BeforeDelimiter([#"Medium Level Attribute/s"],".",0), type text),
    #"Add Low Grouper" = Table.AddColumn(#"Add Med Grouper", "Low Grouper", each Text.BeforeDelimiter([#"Low Level Attribute/s"],".",1), type text),

//Filter the table based on equality between attribute and next lower lever grouper column
    filtered = Table.SelectRows(#"Add Low Grouper", each 
            [#"High Level Attribute/s"] = [Medium Grouper]
            and [#"Medium Level Attribute/s"] = [Low Grouper]),
            
    #"Removed Columns" = Table.RemoveColumns(filtered,{"Medium Grouper", "Low Grouper"})
in
    #"Removed Columns"

结果
在此处输入图片描述

答案2

Power Query 有一个“拆分列”功能，可以设置分隔符（例如逗号），然后为每个逗号拆分成单独的行（其他列保留相同的信息）。这应该会根据每个属性为您分离行，然后您可以根据分离出的行基于新数据创建数据透视表。这意味着您可以保留交互性，同时还可以快速更新信息。

答案3

使用公式的方法怎么样（Office 365）：

=IFNA(TEXTAFTER(TEXTBEFORE("."&TEXTSPLIT(E4,,", ")&".",".",SEQUENCE(,4)),"."),B4)

在此处输入图片描述

或者对于多个单元格：

=LET(range,B4:E5,
DROP(REDUCE(0,SEQUENCE(ROWS(range)),LAMBDA(x,y,VSTACK(x,IFNA(TEXTAFTER(TEXTBEFORE("."&TEXTSPLIT(INDEX(TAKE(range,,-1),y),,", ")&".",".",SEQUENCE(,4)),"."),INDEX(TAKE(range,,1),y))))),1))

相关内容