EXCEL - 如何将多行（“子行”）分组到链接到同一“父行”的列中？

Question

我正在尝试为我们学校的家长建立邮件列表，但我不确定如何在 Excel 中执行公式。

我有一长串孩子名单（姓名和班级）以及每个孩子的父母名单（姓名和电子邮件地址）

看起来像这张表 子女名单

我想根据父母将其转换为邮件列表。唯一 ID 将成为父母的电子邮件地址，而不再是孩子的名字。

每个父母占一行，孩子占另外几列。（如果父母有 4 个孩子，我需要有 4 倍的 3 列姓名/姓氏/班级）

在此处输入图片描述

非常感谢你的帮助 Thierry

Answer 1

以下是使用 Power Query 执行此操作的一种方法。

使用 Power Query

• 在数据表中选择一些单元格
• Data => Get&Transform => from Table/Range
• 当 PQ 编辑器打开时：Home => Advanced Editor
• 记下表格姓名在第 2 行
• 将下面的 M 代码粘贴到您所看到的位置
• 将第 2 行的表名改回最初生成的表名。
• 阅读评论并探索Applied Steps以了解算法

如果可行，请参阅我的注释，了解有关动态生成最终列名称列表的最后一步。如果基本算法可行并且您需要灵活性，则可以添加此项。

M 代码

let

//Read in the data and set data types
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Child Name", type text}, {"Child Surname", type text}, {"Class", Int64.Type}, 
        {"Parent Name", type text}, {"Parent email address", type text}}),

//Unpivot columns other than the two Parent columns
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", 
        {"Parent email address", "Parent Name"}, "Attribute", "Value"),

//Group by the Parent Columns
//Then add an index colum to each sub-table
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Parent Name", "Parent email address"}, {
        {"idx", each  Table.AddIndexColumn(_,"idx"),Int64.Type}}),

//Add integer/divide by 3 column to each subtable to differentiate the different children
//Note that we set the data type to Text for later combining with the Attributes
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "int/div", 
        each Table.TransformColumnTypes(
            Table.AddColumn([idx], "Integer-Division", 
                each Number.IntegerDivide([idx], 3)),{"Integer-Division",Text.Type})),

//Combine Attribute and int/divide column to create unique names for each child/column
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "childNum", 
        each Table.CombineColumns([#"int/div"],
            {"Attribute","Integer-Division"},
                Combiner.CombineTextByDelimiter("."),"Attribute")),

//Remove unneeded columns
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"idx", "int/div"}),

//Pivot each sub-table
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "pivot subtable", each 
        let 
            //Remove columns we don't want when we pivot
            x=Table.RemoveColumns([childNum],{"Parent Name", "Parent email address", "idx"})
        in 
            Table.Pivot(x,x[Attribute],"Attribute","Value")),

//Remove unneeded columns and expand the pivot subtables
//May need to generate the list of expanded column names dynamically
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"childNum"}),
    #"Expanded pivot subtable" = Table.ExpandTableColumn(#"Removed Columns1", "pivot subtable", 
        {"Child Name.0", "Child Surname.0", "Class.0", "Child Name.1", "Child Surname.1", "Class.1", "Child Name.2", "Child Surname.2", "Class.2"}, 
        {"Child Name.0", "Child Surname.0", "Class.0", "Child Name.1", "Child Surname.1", "Class.1", "Child Name.2", "Child Surname.2", "Class.2"})
in
    #"Expanded pivot subtable"

来源

结果