整理数据透视表中的多列-解网

问：

我正在一个跟踪器中工作，其中的行是按项目组织的。跟踪器包括标识项目的主要和支持经理的列，以及经理每周在项目上花费的 # 小时数：

Project Tracker 表

我正在尝试创建一个数据透视表，其中列出了每个经理、每周在所有项目上工作的总小时数以及支持的项目总数：

所需输出

我在建立关系时遇到了问题，这些关系允许我对 C、E 和 G 列中的值求和，并按经理名称进行筛选。我不认为我可以构建查找或索引/匹配公式，因为经理可以在同一列中多次列出。

示例数据文件：

示例项目跟踪器

Excel 数据透视表

=LET(
     α, WRAPROWS(TOCOL(DROP(A2:G6,,1)),2),
     φ, FILTER(α, TAKE(α,,1)<>"N/A"),
     δ, TAKE(φ,,1),
     ε, MMULT(N(δ=TOROW(δ)),DROP(φ,,1)),
     Ψ, MMULT(N(δ=TOROW(δ)),DROP(φ,,1)^0),
     VSTACK({"Manager","Hours Per Week","Projects Supported"},
     SORT(UNIQUE(HSTACK(δ, ε, Ψ)))))

这也可以使用快速轻松地完成。要使用上述程序实现此目的，请使用窗口执行以下简单步骤：POWER QUERYPOWER QUERYUI

首先将源范围转换为表并相应地命名，在本例中，我将其命名为Table1

接下来，从 Tab --> 打开一个空白查询DataGet & Transform Data --> Get Data --> From Other Sources --> Blank Query

上面让窗口打开，现在从 Tab --> --> 并通过删除您看到的任何内容来粘贴以下内容，然后按Power QueryHomeAdvanced EditorM-CodeDone

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Project Name"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Removed Columns", {"Lead Manager", "Lead Weekly Hours", "First Support Manager", "First Support Weekly Hours", "Second Support Manager", "Second Support Weekly Hours"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Hours", each if Text.Contains([Attribute], "Hours") then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Hours"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each not Text.Contains([Attribute], "Hours")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each [Value] <> null and [Value] <> ""and [Value] <> "N/A"),
    #"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Value"}, {{"Manager", each _, type table [Value=text, Hours=number]}, {"Hours Per Week", each List.Sum([Hours]), type number}, {"Projects Supported", each Table.RowCount(_), Int64.Type}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows",{"Manager"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Value", "Manager"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Manager", Order.Ascending}})
in
    #"Sorted Rows"

最后，要将其导入回 --> 单击或 --> 第一个单击的将创建一个具有所需输出的输出，而后者将提示一个窗口，询问您将结果放置在哪里。ExcelClose & LoadClose & Load ToNew Sheet

0赞 Ron Rosenfeld 11/15/2023 #3

这是另一种Power Query算法，如果你有一个大型数据库，它应该比我测试过的其他算法执行得更快。

此外，它与实际列名无关，要求它们仅按以下顺序排列：

|Project|Manager|Hours|Manager|Hours|...|

注意：如果一个经理可能在同一行上列出两次（即：一个项目中的两个角色），我们将需要修改代码以考虑到这一点

使用 Power Query

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

您的数据

M代码

let

//change next lines to reflect actual table source
    Source = Excel.CurrentWorkbook(){[Name="Table20"]}[Content],

    cn = List.Buffer(Table.ColumnNames(Source)),
    managers = List.Buffer(List.Alternate(cn,1,1)),
    hours = List.Buffer(List.RemoveFirstN(List.Alternate(cn,1,1,1),1)),

    #"Set Data Types" = Table.TransformColumnTypes(Source, 
        {{cn{0}, type text}} 
        & List.Transform(managers, each {_, type text}) 
        & List.Transform(hours, each {_, type number})),

    #"Split Tables" = Table.Combine(List.Generate(
        ()=>[t=Table.FromColumns(
                Table.ToColumns(
                    Table.SelectColumns(#"Set Data Types",{cn{0},managers{0},hours{0}})),
                    type table[Project=text, Manager=text, Hours=number]), idx=0],
        each [idx]<List.Count(managers),
        each [t=Table.FromColumns(
                Table.ToColumns(
                    Table.SelectColumns(#"Set Data Types",{cn{0},managers{[idx]+1},hours{[idx]+1}})),
                    type table[Project=text, Manager=text, Hours=number]),
                     idx=[idx]+1],
        each [t]
    )),
    #"Filtered Rows" = Table.SelectRows(#"Split Tables", each ([Manager] <> "N/A")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Manager"}, {
        {"Hours Per Week", each List.Sum([Hours]), type number}, 
        {"Projects Supported", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

结果

上一个：在AG网格数据透视表中隐藏组总计的任何方法

下一个：多索引数据透视表的 Pandas 减法

整理数据透视表中的多列

Collate Multiple Columns in Pivot Table

评论