如何将2D Excel表“平铺”或“折叠”到1D?

I have a two dimensional table with countries and years in Excel. eg.

我有一张两维的表格，上面是国家和年份的Excel表格。如。

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

I'd like to "flatten" it, such that I have Country in the first col, Year in the second col, and then value in the third col. eg.

我想要把它“拉平”，这样我就有了一个国家在第一个col，一年在第二个col，然后值在第三个上校。

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

The example I present here is only a 3x4 matrix, but the real dataset i have is significantly larger (roughly 50x40 or so).

我在这里展示的示例只是一个3x4矩阵，但实际的数据集要大得多(大约50x40左右)。

Any suggestions how I can do this using Excel?

有什么建议我可以用Excel来做吗?

8 个解决方案

#1

You can use the excel pivot table feature to reverse a pivot table (which is essentially what you have here):

您可以使用excel pivot表特性来反转一个数据透视表(它本质上就是您在这里所拥有的):

Good instructions here:

良好的指令:

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

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

Which links to the following VBA code (put it in a module) if you don't want to follow the instructions by hand:

如果你不想按手操作的话，那么下面的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

-Adam

亚当

#2

@Adam Davis's answer is perfect, but just in case you're as clueless as I am about Excel VBA, here's what I did to get the code working in Excel 2007:

@Adam Davis的答案是完美的，但如果你像我一样对Excel VBA一无所知，下面是我在Excel 2007中所做的工作:

1. Open the workbook with the Matrix that needs to be flattened to a table and navigate to that worksheet
2. 打开需要被压平的矩阵的工作簿，并导航到该工作表。
3. Press Alt-F11 to open the VBA code editor.
4. 按Alt-F11打开VBA代码编辑器。
5. On the left pane, in the Project box, you'll see a tree structure representing the excel objects and any code (called modules) that already exist. Right click anywhere in the box and select "Insert->Module" to create a blank module file.
6. 在左边的窗格中，在项目框中，您将看到一个树结构，它表示excel对象和已经存在的任何代码(称为模块)。右键点击框中的任意位置，选择“插入->模块”创建一个空白模块文件。
7. Copy and paste @Adman Davis's code from above as is into the blank page the opens and save it.
8. 复制和粘贴@Adman Davis的代码从上面到空白页打开和保存它。
9. Close the VBA editor window and return to the spreadsheet.
10. 关闭VBA编辑器窗口并返回到电子表格。
11. Click on any cell in the matrix to indicate the matrix you'll be working with.
12. 点击矩阵中的任意一个单元格来表示你将要使用的矩阵。
13. Now you need to run the macro. Where this option is will vary based on your version of Excel. As I'm using 2007, I can tell you that it keeps its macros in the "View" ribbon as the farthest right control. Click it and you'll see a laundry list of macros, just double click on the one called "ReversePivotTable" to run it.
14. 现在需要运行宏。此选项将根据您的Excel版本变化。在我使用2007年的时候，我可以告诉你，它把它的宏保存在“视图”的丝带中，作为最右边的控件。点击它，你会看到一个宏的列表，双击“ReversePivotTable”来运行它。
15. It will then show a popup asking you to tell it where to create the flattened table. Just point it to any empty space an your spreadsheet and click "ok"
16. 然后，它将显示一个弹出窗口，请求您告诉它在哪里创建扁平的表。只要把它指向任何空的空间你的电子表格就可以点击“确定”

You're done! The first column will be the rows, the second column will be the columns, the third column will be the data.

你已经完成了!第一列是行，第二列是列，第三列是数据。

#3

In Excel 2013 need to follow next steps:

在Excel 2013需要遵循以下步骤:

• select data and convert to table (Insert -> Table)
• 选择数据并转换为表(Insert ->表)
• call Query Editor for table (Power Query -> From Table)
• 调用表的查询编辑器(Power Query ->来自表)
• select columns that contain years
• 选择包含年份的列。
• in context menu select 'Unpivot Columns'-command.
• 在上下文菜单中选择“Unpivot列”-命令。

Support Office: Unpivot columns (Power Query)

支持办公室:非主列(电源查询)

#4

Flattening a data matrix (aka Table) can be accomplished with one array formula¹ and two standard formulas.

压扁一个数据矩阵(即表)可以用一个数组公式完成¹公式和两个标准。

      

      

The array formula¹ and two standard formulas in G3:I3 are is,

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

=IFERROR(INDEX(A$2:A$4, MATCH(0, IF(COUNTIF(G$2:G2, A$2:A$4&"")<COUNT($1:$1), 0, 1), 0)), "")
=IF(LEN(G3), INDEX($B$1:INDEX($1:$1, MATCH(1E+99,$1:$1 )), , COUNTIF(G$3:G3, G3)), "")
=INDEX(A:J,MATCH(G3,A:A,0),MATCH(H3,$1:$1,0))

Fill down as necessary.

填满了。

While array formulas can negatively impact performance due to their cyclic calculation, your described working environment of 40 rows × 50 columns should not overly impact performance with a calculation lag.

虽然数组公式可以负面影响性能由于其循环计算,描述你的工作环境40行×50列不应该过于滞后影响性能的计算。

---

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.}

¹数组公式需要用Ctrl + Shift + Enter↵定稿。一旦进入第一个单元格，就可以像其他公式一样填充或复制。尝试减少全列引用，以更紧密地表示实际数据的范围。数组公式对计算周期进行对数运算，因此，将引用范围缩小到最小值是很好的练习。有关更多信息，请参见参考指南和数组公式示例。

#5

For anyone who wants to use the PivotTable to do this and is following the below guide: http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

对于任何想要使用数据透视表的人，请遵循以下指南:http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/。

If you want to do it in Excel 2007 or 2010 then you first need to enable the PivotTable Wizard.

如果您想在Excel 2007或2010中完成，那么您首先需要启用数据透视表向导。

To find the option you need to go to "Excel Options" via the Main Excel Window icon, and see the options selected in the "customize" section, then select "Commands Not in the Ribbon" from the "Choose Commands from:" dropdown and "PivotTable and PivotChart Wizard" needs to be added to the right.. see the image below.

找到你所需要的选择去“Excel选项”通过主Excel窗口图标,看看选项中选择“自定义”部分,然后选择“命令不是丝带”“选择命令:“下拉和“数据透视表和PivotChart向导”需要被添加到正确的. .见下面的图片。

Once that is done there should be a small pivottable wizard icon in the quickbar menu at the top of the Excel window, you can then follow the same process as shown in the link above.

完成后，在Excel窗口顶部的quickbar菜单中应该有一个小的数据透视表向导图标，然后您可以按照上面的链接所示的流程执行相同的流程。

#6

VBA solution may not be acceptable under some situations (e.g. cannot embed macro due to security reasons, etc.). For these situations, and otherwise too in general, I prefer using formulae over macro.

在某些情况下，VBA解决方案可能无法接受(例如由于安全原因不能嵌入宏等)。对于这些情况，通常情况下，我更喜欢使用公式而不是宏。

I am trying to describe my solution below.

我试着描述下我的解决方案。

• input data as shown in question (B2:F5)
• 输入数据如有问题(B2:F5)
• column_header (C2:F2)
• column_header(C2:F2)
• row_header (B3:B5)
• row_header(B3:B5)
• data_matrix (C3:F5)
• data_matrix(C3:F5)
• no_of_data_rows (I2) = COUNTA(row_header) + COUNTBLANK(row_header)
• no_of_data_rows (I2) = COUNTA(row_header) + COUNTBLANK(row_header)
• no_of_data_columns (I3) = COUNTA(column_header) + COUNTBLANK(column_header)
• no_of_data_columns (I3) = COUNTA(column_header) + COUNTBLANK(column_header)
• no_output_rows (I4) = no_of_data_rows*no_of_data_columns
• no_output_rows = no_of_data_rows * no_of_data_columns(预告)
• seed area is K2:M2, which is blank but referenced, hence not to be deleted
• 种子面积为K2:M2，为空白，但被引用，因此不被删除。
• K3 (drag through say K100, see comments description) = ROW()-ROW($K$2) <= no_output_rows
• K3(拖出K100，参见注释描述)= ROW()-ROW($K$2) <= no_output_rows。
• L3 (drag through say L100, see comments description) = IF(K3,IF(COUNTIF($L$2:L2,L2)
• L3(拖动到L100，见注释说明)= IF(K3,IF(COUNTIF) ($ 2:L2,L2)
• M3 (drag through say M100, see comments description) = IF(K3,IF(M2 < no_of_data_columns,M2+1,1),"-")
• M3 (drag through say M100，见注释说明)= IF(K3,IF(M2 < no_of_data_columns,M2+1,1)，“-”)
• N3 (drag through say N100, see comments description) = INDEX(row_header,L3)
• N3(通过N100拖拽，参见注释描述)=索引(row_header,L3)
• O3 (drag through say O100, see comments description) = INDEX(column_header,M3)
• O3(通过O100拖拽，见注释描述)=索引(column_header,M3)
• P3 (drag through say P100, see comments description) = INDEX(data_matrix,L3,M3)
• P3 (drag through say P100，见注释说明)=索引(data_matrix,L3,M3)
• Comment in K3: Optional: Check if expected no. of output rows has been achieved. Not required, if one only prepares this table limited to no. of output rows.
• 在K3中注释:可选:检查是否需要。已经实现了输出行。不需要，如果只准备这张表，限制为不。输出行。
• Comment in L3: Goal: Each RowIndex (1 .. no_of_data_rows) must repeat no_of_data_columns times. This will provide index lookup for row_header values. In this example, each RowIndex (1 .. 3) must repeat 4 times. Algorithm: Check how many times RowIndex has occurred yet. If it less than no_of_data_columns times, continue using that RowIndex, else increment the RowIndex. Optional: Check if expected no. of output rows has been achieved.
• L3:目标:每个RowIndex (1 ..)no_of_data_rows)必须重复no_of_data_columns时间。这将为row_header值提供索引查找。在本例中，每个RowIndex (1 ..)3)重复4次。算法:检查RowIndex已经发生了多少次。如果它小于no_of_data_columns，则继续使用该RowIndex，否则将增加RowIndex。可选:检查是否需要。已经实现了输出行。
• Comment in M3: Goal: Each ColumnIndex (1 .. no_of_data_columns) must repeat in a cycle. This will provide index lookup for column_header values. In this example, each ColumnIndex (1 .. 4) must repeat in a cycle. Algorithm: If ColumnIndex exceeds no_of_data_columns, restart the cycle at 1, else increment the ColumnIndex. Optional: Check if expected no. of output rows has been achieved.
• M3的注释:目标:每个ColumnIndex (1 ..)no_of_data_columns必须在循环中重复。这将为column_header值提供索引查找。在本例中，每个ColumnIndex (1 ..)4)必须在循环中重复。算法:如果ColumnIndex超过no_of_data_columns，则在1处重新启动循环，否则将增加ColumnIndex。可选:检查是否需要。已经实现了输出行。
• Comment in R4: Optional: Use column K for error handling, as shown in column L and column M. Check if looked up value IsBlank to avoid incorrect "0" in the output because of blank input in data_matrix.
• 在R4中注释:可选:使用列K进行错误处理，如列L和M. Check中所示，如果查找值IsBlank，则由于data_matrix中的空白输入，在输出中避免不正确的“0”。

#7

I developed another macro because I needed to refresh the output table quite often (input table was filled by other) and I wanted to have more info in my output table (more copied column and some formulas)

我开发了另一个宏，因为我需要经常刷新输出表(输入表由其他表填充)，我希望在输出表中有更多的信息(更多的复制列和一些公式)

Sub TableConvert()

Dim tbl As ListObject 
Dim t
Rows As Long
Dim tCols As Long
Dim userCalculateSetting As XlCalculation
Dim wrksht_in As Worksheet
Dim wrksht_out As Worksheet

'##block calculate and screen refresh
Application.ScreenUpdating = False
userCalculateSetting = Application.Calculation
Application.Calculation = xlCalculationManual

'## get the input and output worksheet
Set wrksht_in = ActiveWorkbook.Worksheets("ressource_entry")'## input
Set wrksht_out = ActiveWorkbook.Worksheets("data")'## output.


'## get the table object from the worksheet
Set tbl = wrksht_in.ListObjects("Table14")  '## input
Set tb2 = wrksht_out.ListObjects("Table2") '## output.

'## delete output table data
If Not tb2.DataBodyRange Is Nothing Then
    tb2.DataBodyRange.Delete
End If

'## count the row and col of input table

With tbl.DataBodyRange
     tRows = .Rows.Count
     tCols = .Columns.Count
End With

'## check every case of the input table (only the data part)
For j = 2 To tRows '## parse all row from row 2 (header are not checked)
    For i = 5 To tCols '## parse all column from col 5 (first col will be copied in each record)
        If IsEmpty(tbl.Range.Cells(j, i).Value) = False Then
            '## if there is time enetered create a new row in table2 by using the first colmn of the selected cell row and cell header plus some formula
            Set oNewRow = tb2.ListRows.Add(AlwaysInsert:=True)
            oNewRow.Range.Cells(1, 1).Value = tbl.Range.Cells(j, 1).Value
            oNewRow.Range.Cells(1, 2).Value = tbl.Range.Cells(j, 2).Value
            oNewRow.Range.Cells(1, 3).Value = tbl.Range.Cells(j, 3).Value
            oNewRow.Range.Cells(1, 4).Value = tbl.Range.Cells(1, i).Value
            oNewRow.Range.Cells(1, 5).Value = tbl.Range.Cells(j, i).Value
            oNewRow.Range.Cells(1, 6).Formula = "=WEEKNUM([@Date])"
            oNewRow.Range.Cells(1, 7).Formula = "=YEAR([@Date])"
            oNewRow.Range.Cells(1, 8).Formula = "=MONTH([@Date])"
        End If
   Next i
Next j
ThisWorkbook.RefreshAll

'##unblock calculate and screen refresh
Application.ScreenUpdating = True 
Application.Calculate
Application.Calculation = userCalculateSetting

End Sub

#8

updated ReversePivotTable function so i can specify number of header columns and rows

更新了ReversePivotTable函数，因此我可以指定标题列和行数。

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

    Dim lngHeaderColumns As Long, lngHeaderRows As Long, lngHeaderLoop 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)
    lngHeaderColumns = Application.InputBox(prompt:="Header Columns")
    lngHeaderRows = Application.InputBox(prompt:="Header Rows")
'   Convert the range
    OutRow = 2
    Application.ScreenUpdating = False
    'OutputRange.Range("A1:D3") = Array("Column1", "Column2", "Column3", "Column4")
    For r = lngHeaderRows + 1 To SummaryTable.Rows.Count
        For c = lngHeaderColumns + 1 To SummaryTable.Columns.Count
            ' loop through all header columns and add to output
            For lngHeaderLoop = 1 To lngHeaderColumns
                OutputRange.Cells(OutRow, lngHeaderLoop) = SummaryTable.Cells(r, lngHeaderLoop)
            Next lngHeaderLoop
            ' loop through all header rows and add to output
            For lngHeaderLoop = 1 To lngHeaderRows
                OutputRange.Cells(OutRow, lngHeaderColumns + lngHeaderLoop) = SummaryTable.Cells(lngHeaderLoop, c)
            Next lngHeaderLoop

            OutputRange.Cells(OutRow, lngHeaderColumns + lngHeaderRows + 1) = SummaryTable.Cells(r, c)
            OutputRange.Cells(OutRow, lngHeaderColumns + lngHeaderRows + 1).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
            OutRow = OutRow + 1
        Next c
    Next r
End Sub