如何将垂直重复的数据重新组织为水平数据?

如何将垂直重复的数据重新组织为水平数据?

我有 1970 年至 2017 年来自世界各国的 1 个变量的数据。此数据组织为一个大型垂直列表,第一列为(重复的)国家名称,然后是 1970 年至 2017 年的重复列,第三列为变量值。但是,我需要以另一种格式组织它(以便能够将变量与 spss 中的其他文件合并)。我需要左列包含所有国家/地区,但只有一个。然后水平排列一行,内容为 1970-2017 年,其下方是包含每个国家/地区变量值的所有行

我添加了 2 张现在的照片和 1 张应该如何组织的照片。我该如何实现这一点而不用分别选择所有国家并翻转这些数据?

现在的情况:
现在看起来

现在的样子:
现在看起来怎么样

它看起来应该是这样的:
它应该是什么样子!

答案1

为此,您需要一个数据透视表。选择所有列,有一个选项可以启动数据透视表向导(具体位置取决于您的 Excel 版本):

在此处输入图片描述

指定列(“年份”)、行(“代码”和“国家”)和值(“KOFGI”)中所需的字段:

在此处输入图片描述

您几乎已经完成了;您只需要一个选项“以表格形式显示”并禁用行中的小计。(同样,这些小计的位置取决于您的 Excel 版本。)最终结果如下:

在此处输入图片描述

答案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"

相关内容