答案1
答案2
如果您对 VBA 解决方案感兴趣,您可以尝试一下,只需插入一个模块并链接到 Sheet2 上的按钮(修改 Sheet1 和 Sheet2 以适应代码)-其中 Sheet 2 是空白的:
Sub MyTpose()
Application.Screenupdating = False
Dim ws1 As Worksheet: Set ws1 = Sheet1
Dim ws2 As Worksheet: Set ws2 = Sheet2
Dim MyINT As Long, i As Long, MyCOUNT As Long, LstRW As Long
'\\ MyINT reflects 1970 to 2017 in this case, modify to suit.
MyINT = 47
ws2.Cells(4, 2) = ws1.Cells(1, 1)
ws2.Cells(4, 1) = ws1.Cells(1, 2)
ws2.Cells(4, 3) = "INDICATOR X"
ws2.Cells(4, 4) = "INDICATOR Y"
ws2.Cells(1, 1) = "Data Source"
ws2.Cells(1, 2) = "World Development Indicators"
ws2.Cells(2, 1) = "Last Updated"
ws2.Cells(2, 2) = Now
ws2.Cells(4, 5).Resize(1, MyINT) = Application.WorksheetFunction.Transpose(ws1.Range(ws1.Cells(2, 3), ws1.Cells(2 + MyINT, 3)))
MyCOUNT = (ws1.Cells(1, 1).CurrentRegion.Rows.Count - 1) / MyINT
For i = 2 To ws1.Cells(1, 1).CurrentRegion.Rows.Count - 1 Step MyINT
LstRW = ws2.Cells(4, 1).CurrentRegion.Rows.Count + 4
ws2.Cells(LstRW, 1) = ws1.Cells(i, 2)
ws2.Cells(LstRW, 2) = ws1.Cells(i, 1)
ws2.Cells(LstRW, 3) = "AGE"
ws2.Cells(LstRW, 4) = "SP.POP.DPND"
ws2.Cells(LstRW, 5).Resize(1, MyINT) = Application.WorksheetFunction.Transpose(ws1.Range(ws1.Cells(i, 4), ws1.Cells(i + MyINT, 4)))
Next i
End Sub
请注意,MyINT 变量反映了您要转置的年数,并假设所有条目的年数相同。如果不是这种情况,那么将有一种方法可以在循环内计算偏移量,但我现在假设了一致性。其他假设是输出表的第 3 行中始终会有一个空白行(否则 currentregion.rows.count 将失败)。我不确定您从哪里获得 AGE 和其他指标,所以我只是将它们作为字符串插入。
答案3
如果您拥有 Windows Excel 2010+,则可以使用Power Query
。它包含在更高版本的Get & Transform
数据选项卡中,并在早期版本中作为 Microsoft 的免费插件。
在后续版本中,选择数据表中的单个单元格即可导航至:
Data → Get & Transform Data → From Table/Range
- 确保虚线仅包含表格,而不包含任何多余的行/列
- PQ 编辑器将打开,显示您的原始表,并且可能
Changed Type
在右侧显示“应用步骤”窗口。 Year
按升序对列进行排序- 选择
Year
列,然后- 枢
- 值列 = KOFGI
- 高级选项:不聚合
- 枢
- 按照输出所示重新排列
Country Name
和列。Country Code
您已经完成了!
这是生成的M 代码,但由于表名不同,以及您似乎没有在数据源中包含的额外列,因此在您的机器上会有所不同
let
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Country Code", type text}, {"Country Name", type text}, {" Year", Int64.Type}, {"KOFGI", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{" Year", Order.Ascending}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{" Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{" Year", type text}}, "en-US")[#" Year"]), " Year", "KOFGI"),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Country Name", "Country Code", "2010", "2011", "2012", "2013", "2014", "2015", "2016"})
in
#"Reordered Columns"