☰

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

tag-icon • tag-icon microsoft-excel formatting database format
如何将垂直重复的数据重新组织为水平数据？

我有 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"

相关内容