☰

根据行值创建表？

tag-icon • tag-icon microsoft-excel
根据行值创建表？

我有一张像这样的桌子，

场景 1A 场景 2 场景 3
功能 1 功能 1 功能 2
功能 2 功能 3
功能 3

我想要一个按功能列出场景的表格，以便

功能 1 功能 2 功能 3
场景 1A 场景 1A 场景 1A
场景 2 场景 3 场景 2

我正在使用 Office 365。

有什么方法可以自动实现这一点？我已经花了一个小时研究 INDEX、FILTER 等各种组合，但还是搞不懂。

我很感激任何帮助！

答案1

屏幕截图上的另一种可能的解决方案。
场景

答案2

发布更多更简单易行的方法，当使用MS365-->Fill Right方法！

在此处输入图片描述

• 单元格中使用的公式E1

=UNIQUE(TOROW(A2:C4,1),1)

&

• 单元格中使用的公式E2--> 填充右侧！

=SORT(TOCOL(IFS(E$1=$A$2:$C$4,$A$1:$C$1),3))

上面的解决方案需要填写正确，而下面发布的解决方案是动态的。这可以通过使用Excel Formulas以及使用POWER QUERY，但POWER QUERY我认为最推荐使用。

使用Excel Formulas仅适用于MS365。

在此处输入图片描述

• 单元格中使用的公式E1

=LET(
     α, HSTACK(TOCOL(IFS(A2:C4<>"",A1:C1),3),TOCOL(A2:C4,1)),
     φ, TAKE(α,,-1),
     δ, TOROW(UNIQUE(φ)),
     VSTACK(δ,DROP(REDUCE("",δ, LAMBDA(x,y,HSTACK(x,FILTER(TAKE(α,,1),φ=y)))),,1)))
  • 变量α-->有助于垂直堆叠数据并将各组合并在一起。
  • 变量φ-->提取Function组。
  • 变量δ——> 获取唯一值并水平对齐Function组
  • 最后，使用REDUCE()函数循环遍历每个唯一的函数组以按行返回相应的场景。

或者使用Windows 和 MAC 以上版本POWER QUERY可用的版本。Excel 2010+

在此处输入图片描述

  • 首先将源范围转换为表格并进行相应命名，在此示例中，我将其命名为Table_1
  • Data接下来，从Tab --> Get & Transform Data--> Get Data--> From Other Sources-->打开一个空白查询Blank Query
  • 上面的命令可以Power Query打开窗口，现在从HomeTab --> Advanced Editor--> 删除您看到的任何内容并粘贴以下内容M-Code，然后按Done
let
    Source = Excel.CurrentWorkbook(){[Name="Table_1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Value"}, {{"All", each _, type table [Attribute=text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Attribute", "Value", "Index"}, {"Attribute", "Value", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Value]), "Value", "Attribute"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

在此处输入图片描述

  • 最后，将其导回Excel--> 单击Close & Load或Close & Load To--> 第一个单击的将创建一个New Sheet具有所需输出的，而后者将提示一个窗口询问您将结果放在何处。

另一种替代方法是使用MAKEARRAY()

在此处输入图片描述

=LET(
     α, A2:C4,
     φ, UNIQUE(TOROW(α,1),1),
     VSTACK(φ, MAKEARRAY(MAX(MAP(φ,LAMBDA(s,SUM(--(α=s))))),3,LAMBDA(r,c,
     INDEX(TOCOL(IF(INDEX(UNIQUE(TOROW(α,1),1),,c)=α,A1:C1,z),3),r,)))))

相关内容