☰

使用 Excel 公式根据分层选择填充单元格中的名称

tag-icon • tag-icon microsoft-excel worksheet-function google-spreadsheets
使用 Excel 公式根据分层选择填充单元格中的名称

我有 2 张桌子

第一个表格是项目列表，另外 3 列表示选择层级

Items                             Choice 1          Choice 2          Choice 3

item1
item2
item3
item4

第二张表列出了用户及其商品选择。商品选择各不相同。

User                             Choice 1          Choice 2          Choice 3

user1                            item3             item1             item2
user2                            item1             item2             item4
user3                            item4             item3             item2

我想根据用户的选择填充第一个表中的用户。在上述情况下，第一个表应按如下所示填充。

Items                             Choice 1          Choice 2          Choice 3

item1                              user2             user1
item2                                                user2            user1 & user3
item3                              user1             user3            
item4                              user3                              user2

如果他们的选择与另一个用户共享，我将需要在一个单元格中拥有多个用户

答案1

在 Excel 2010+ 中，您可以使用 Power Query 从第二个表生成所需的表。

在此处输入图片描述

  • 选择用户列并Unpivot other columns
    • 结果如下图所示：

在此处输入图片描述

  • 然后选择属性列并以此为依据进行数据透视，高级选项为Don't aggregate

在此处输入图片描述

  • 但是，这将导致任何具有多个项目内容的单元格出现错误。
    • 请注意Choice 3包含Item2两个不同的用户
    • 我们通过添加自定义聚合函数来解决这个问题，以连接结果（此选项在 UI 中不可用）
    • 在高级编辑器或公式栏中，我们更改：
Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "User")

通过添加： each Text.Combine(List.Sort(_)," & ")作为可选的聚合函数。

经过一些其他步骤，设置数据类型，删除多余的列，并重命名列Item，最终使用的代码是：

M 代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"Choice 1", type text}, {"Choice 2", type text}, {"Choice 3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"User"}, "Attribute", "Value"),
    
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", 
        List.Distinct(#"Unpivoted Other Columns"[Attribute]), 
        "Attribute", 
        "User",
        each Text.Combine(List.Sort(_)," & ")),
    
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Value", "Item"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Choice 1", type text}, {"Choice 2", type text}, {"Choice 3", type text}})
in
    #"Changed Type1"

导致：

在此处输入图片描述

笔记：如果您决定将 M 代码粘贴到高级编辑器中，则需要确保第 2 行中的表名与工作簿中数据表的表名相同

相关内容