如何“压平"或“崩溃"将 2D Excel 表格转换为 1D? [英] How to "flatten" or "collapse" a 2D Excel table into 1D?
问题描述
我在 Excel 中有一个包含国家和年份的二维表.例如.
I have a two dimensional table with countries and years in Excel. eg.
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.
Country Year Value
USA 1961 a
USA 1962 x
USA 1963 g
USA 1964 y
France 1961 u
...
我在此展示的示例只是一个 3x4 矩阵,但我拥有的实际数据集要大得多(大约 50x40 左右).
The example I present here is only a 3x4 matrix, but the real dataset i have is significantly larger (roughly 50x40 or so).
对于如何使用 Excel 执行此操作有任何建议吗?
Any suggestions how I can do this using Excel?
推荐答案
您可以使用 excel 数据透视表功能来反转数据透视表(这实际上就是您在此处拥有的):
You can use the excel pivot table feature to reverse a pivot table (which is essentially what you have here):
这里有很好的说明:
http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/
如果您不想手动按照说明操作,可以链接到以下 VBA 代码(将其放入模块中):
Which links to the following VBA code (put it in a module) if you don't want to follow the instructions by hand:
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
-亚当
这篇关于如何“压平"或“崩溃"将 2D Excel 表格转换为 1D?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!