如何将2D Excel表格"展平"或"折叠"为1D？

我有一个二维表,包含Excel中的国家和年份.例如.

        1961        1962        1963        1964
USA      a           x            g           y
France   u           e            h           a
Germany  o           x            n           p

我想"扁平"它,这样我在第一个col中有Country,在第二个col中有Year,然后在第三个col中有值.例如.

Country      Year       Value
USA          1961       a
USA          1962       x
USA          1963       g
USA          1964       y
France       1961       u
              ...

我在这里给出的例子只是一个3x4矩阵,但我拥有的真实数据集要大得多(大约50x40左右).

有关如何使用Excel执行此操作的任何建议吗？

1> Adam Davis..：

---

您可以使用excel数据透视表功能来反转数据透视表(这基本上就是您在此处所拥有的):

这里有好的指示:

http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

如果您不想手动按照说明操作,请链接到以下VBA代码(将其放入模块中):

Sub ReversePivotTable()
'   Before running this, make sure you have a summary table with column headers.
'   The output table will have three columns.
    Dim SummaryTable As Range, OutputRange As Range
    Dim OutRow As Long
    Dim r As Long, c As Long

    On Error Resume Next
    Set SummaryTable = ActiveCell.CurrentRegion
    If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
        MsgBox "Select a cell within the summary table.", vbCritical
        Exit Sub
    End If
    SummaryTable.Select
    Set OutputRange = Application.InputBox(prompt:="Select a cell for the 3-column output", Type:=8)
'   Convert the range
    OutRow = 2
    Application.ScreenUpdating = False
    OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")
    For r = 2 To SummaryTable.Rows.Count
        For c = 2 To SummaryTable.Columns.Count
            OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)
            OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)
            OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)
            OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
            OutRow = OutRow + 1
        Next c
    Next r
End Sub

-亚当

---

神奇的资源,这正是我想要的.谢谢亚当!

2> Michael La V..：

---

@Adam Davis的答案是完美的,但为了防止你像我对Excel VBA一样无能为力,这就是我在Excel 2007中使用代码所做的工作:

使用需要展平为表格的Matrix打开工作簿并导航到该工作表

按Alt-F11打开VBA代码编辑器.

在左窗格的"项目"框中,您将看到表示excel对象的树结构以及已存在的任何代码(称为模块).右键单击框中的任意位置,然后选择"插入 - >模块"以创建空白模块文件.

将上面的@Adman Davis代码复制并粘贴到打开的空白页面并保存.

关闭VBA编辑器窗口并返回电子表格.

单击矩阵中的任何单元格以指示您将使用的矩阵.

现在您需要运行宏.此选项的位置将根据您的Excel版本而有所不同.当我使用2007时,我可以告诉你,它将其宏保存在"视图"功能区中作为最右边的控件.点击它,你会看到一个宏的清单,只需双击一个名为"ReversePivotTable"的宏来运行它.

然后它会显示一个弹出窗口,要求您告诉它在哪里创建扁平表.只需将其指向电子表格的任何空白区域,然后单击"确定"即可

你完成了!第一列是行,第二列是列,第三列是数据.

3> vladimir..：

---

在Excel 2013中需要按照以下步骤操作:

选择数据并转换为表格(插入 - >表格)

调用表的查询编辑器(Power Query - > From Table)

选择包含年份的列

在上下文菜单中选择" Unpivot Columns "命令.

支持Office:Unpivot列(Power Query)

---

在Excel 2016中,**Power Query**被称为**Get&Transform**,它位于**Data**选项卡中.否则此解决方案完美无缺.

4> 小智..：

---

平坦化数据矩阵(又名表)可以与一个阵列formula¹和两个标准公式来完成.

      

数组公式¹和G3中的两个标准公式:I3是,

=IFERROR(INDEX(A$2:A$4, MATCH(0, IF(COUNTIF(G$2:G2, A$2:A$4&"")

必要时填写.

虽然阵列公式可能会因循环计算而对性能产生负面影响,但您所描述的40行×50列的工作环境不应过度影响性能和计算滞后.



¹ 数组公式需要用Ctrl+ Shift+ 完成Enter↵.一旦正确进入第一个单元格,就可以像任何其他公式一样向下或向右填充或复制它们.尝试并减少对列表的全列引用,这些范围更接近地表示实际数据的范围.数组公式以对数方式计算计算周期,因此将参考范围缩小到最小值是一种好习惯.有关更多信息,请参阅数组公式的指南和示例.