☰

将列转换为行

tag-icon • tag-icon microsoft-excel conversion
将列转换为行

因此，我在 Excel 上有一个如下所示的表格：

年 简 二月 行进
2000 1 四十四 1
2001 33 55 2
2002 四十四 3 1
2003 55 22 1
2004 22 三十四 3
2005 33 23 3
2006 43 四十五 3

因此，最后 3 列代表 1，第一列代表年份。我想创建一个时间序列图，我需要将此表转换为只有 2 列的表。第一列代表日期（2000 年 1 月，2001 年 2 月），第二列代表值。你能告诉我怎么做吗？我尝试了“转置”功能，但它没有创建我需要的内容。

答案1

使用 Power Query，您可以选择Year列并逆透视其他列。

  • 选择数据表中的一个单元格
  • `数据 => 获取并转换 => 来自表/范围
  • 在 PQ UI 中，选择Home => Advanced Editor
  • 记下第 2 行中的表名称
  • 粘贴下面的 M 代码以替换该窗口中的代码。
  • 检查注释以及“应用步骤”窗口选择，以了解正在发生的事情
  • 年/月将转换为真实的等于每月第一天的日期
    • 按照您在 Excel 中想要的格式进行显示。
  • 关闭并加载（或加载至）
  • 请注意，所有步骤都可以直接从 UI 完成

M 代码

let

//change table name to your actual table name
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

//Unpivot the columns other than the year
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"year"}, "Attribute", "Value"),

//merge the year and month ("Attribute") columns
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {
        {"year", type text}}, "en-US"),{"year", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"month/year"),

//change to a real date by changing the Type => date
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"month/year", type date}, {"Value", Int64.Type}})
    
in
    #"Changed Type"

在此处输入图片描述

答案2

以下是基于动态数组 (DA) 并使用 LET 函数的方法，因此需要 Excel 2016 或 Microsoft 365。由于它基于 DA，因此它将在 Excel for Mac 上运行，并且不需要数据刷新或其他数据透视表操作 - 它是纯粹基于 Excel 公式的。

假设数据位于单元格 A1 至 D8 中，如上所示。按照 Power Query 的说法，我们会说您想要“逆透视B1:D8经过A1:A8"。但您想要从 By 列和 Unpiv 矩阵的相对列位置计算日期。

以下是对常规 unpiv 数组公式的修改，它遵循 Power Query Unpivot 的逻辑，但将计算日期作为组合列而不是按列和属性提供。

=LET( unPivMatrix, B1:D8,
      byYears, A1:A8,
       upC, COLUMNS( unPivMatrix ),
       byC, COLUMNS( byYears ),
       dmxR, MIN( ROWS( unPivMatrix ), ROWS( byYears ) ) - 1,
       upCells, dmxR * upC,
       upSeq, SEQUENCE( upCells,, 0 ),
       upHdr, INDEX( INDEX( unPivMatrix, 1, ),  1,  SEQUENCE( upC ) ),
       upBody, INDEX( unPivMatrix,  SEQUENCE( dmxR ) + 1,  SEQUENCE( 1, upC ) ),
       byBody, INDEX( byYears,  SEQUENCE( dmxR ) + 1,  SEQUENCE( 1, byC ) ),
       mux, INDEX( upBody, upSeq/upC + 1, MOD( upSeq, upC ) + 1 ),
       demux, IFERROR( INDEX(
                             INDEX( byBody,
                                    IFERROR( INT( SEQUENCE( upCells, byC,0 )/byC/upC ) + 1, MOD( upSeq, upC ) + 1 ),
                                             SEQUENCE( 1, byC + 1 ) ),
                             upSeq + 1, SEQUENCE( 1, byC + 2 ) ),
                       mux ),
       IFERROR( INDEX( DATE( INDEX( demux,,1), MOD( upSeq, upC ) + 1, 1 ),
                       SEQUENCE( upCells ),
                       {1,2}),
                mux )
 )

输出结果如下： 在此处输入图片描述

工作原理 - 读取输入

输入是你想要取消透视的范围，我称之为矩阵B1:D8（可以是您需要的任何尺寸）以及您想要取消透视的列，我称之为按年份A1：A8。

该公式首先计算每个矩阵的列数：上皮细胞逆透视列和来自C通过列计算韓國（解复用行）：通过取 unPivMatrix 和 byMatrix 行中的最小值并减去 1（因为 unPivMatrix 有一个标题），在 unpivot 中将传递多少行值。如果输入的行数不同（这在定义上是一种错误），则取最小值是一种错误预防步骤。

这些值用于创建稍后将用于形成输出的塑造变量。上层细胞是将被反透视的值的数量，用于生成名为上行序列它从 0 开始计数值的数量，稍后将在 INDEX 函数中使用。我们从 0 开始，因为 upSeq 将被调制以形成正确的输入和输出索引。

现在我们将分解矩阵的各个部分，以便对其进行复用。各部分如下所示：

在此处输入图片描述

有一个反透视标题（上行) 包含值数据的标识符（在本例中为 Jan、Feb、March）。这些将被多路复用到一列中，稍后将放置在每个未透视的值旁边。upHdr 是通过将整个 unPivMatrix 放入 INDEX 函数并读取第 1 行和所有列来创建的。我将该 INDEX 函数嵌套到另一个 INDEX 中，该 INDEX 使用大小为 upC 的垂直 SEQUENCE 将水平数组重塑为垂直数组。

上身包含我们想要通过多路复用取消透视的值。它是通过将 upMatrix 放入 INDEX 并根据要输出的行数进行整形来创建的SEQUENCE( dmxR ) + 1 §针对每个要读取的列 SEQUENCE(upC)。 身体包含将与 upBody 中每个值进行复用的数据。其创建方式与 upBody 相同。

§ - 添加 1 以跳过标题行

工作原理 - 塑造和编写输出

输出结果如下：

在此处输入图片描述

我们现在创建一个名为多路复用器的值（使用 Power Query 术语）将针对每个 month 列和 byBody 行进行多路复用。它是通过将 upBody 放入 INDEX 中，然后将其重塑为由行创建的多路复用模式来创建的

upSeq/upC + 1产生一行{1,1,1,1,1,1,1,1,2,2,...}

和列

MOD( upSeq, upC )+ 1 产生一列{1;2;3;4;5;6;7;8;1;...}。

mux 的输出将是 unPivMatrix 的内容。

工作原理 - 将各部分解复用到输出中

现在简单的部分已经完成，是时候开始做困难的事情了——将所有这些组合成一个动态数组。将多个数组组合在一起需要一个技巧，而且这个技巧必须应用两次，因为正如您所见，我们要将三个表组合在一起。这个技巧就像有一个 APPEND 函数，如下所示：

APPEND( APPEND( table1, table2 ), table3 )。

要合并两个数组，请将第一个数组放入 INDEX，然后引用数组外部的单元格以强制出现 #REF! 错误。例如，如果我有一个包含字母 A 到 F 的 3 x 2 数组，并且我引用单元格 3, 3，它将引发引用错误。

在此处输入图片描述

现在，您可以通过将 INDEX 包装在 IFERROR 中，用要附加的表替换它们来利用这些错误。这有点像：

IFERROR( INDEX( table1,
                SEQUENCE( table1.rows ),
                SEQUENCE( 1, table1.columns + table2.columns ) ),
         table2 )

从这个意义上讲，上面的公式等同于 APPEND(table1, table2)，其中 APPEND 是两个表按行附加，这正是我们想要的。（注意：切换序列模式，您可以按列附加。）

所以，希望这个解释能清楚地说明变量最后阶段发生了什么解复用它传递结果。我给结果命名，然后引用它，以便您可以轻松地探索、修改或优化公式。所以，demux 实际上是这样的：

APPEND( byBody, mux )

然后最终输出使用它将 byBody 转换为日期，因此它有效地生成了类似以下内容的内容：

APPEND( DATE( demux Col1, Column Index of upMatrix, 1 ), mux )

我已经测试过这个，但我没有简化它。最后几个步骤可以更有效地组合起来，但我根本没有时间去清理它。原始代码来自我为另一个类似用途构建的动态数组 unpiv。

答案3

这解决了这个问题：

：警告：

当您使用 F85:H87 中的数据制作图表时，Excel 将显示年份、月份以及相关数据。

在此处输入图片描述

  • 单元格 F86 中的公式并填充：

    =IF(ISBLANK(F$85),"",IF(XOR($A86=2000,$A86=2001),INDEX(A$86:A$92,SUMPRODUCT(($A$86:$A$92=2000)*($A$86:$A$92=2001)*ROW($A$86:$A$92)),0),""))

注意：

  • 您可以用单元格引用替换 2000 年和 2001 年，使公式动态化。
  • 根据需要调整公式中的单元格引用。

相关内容