☰

如何在 Excel 中“取消透视”或“反向透视”？

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 pivot-table
如何在 Excel 中“取消透视”或“反向透视”？

我有如下数据：

Id | Loc1 | Loc2 | Loc3 | Loc4
---+------+------+------+-----
1  | NY   | CA   | TX   | IL
2  | WA   | OR   | NH   | RI

我想将其转换为：

Id | LocNum | Loc
---+--------+----
1  |   1    | NY
1  |   2    | CA
1  |   3    | TX
1  |   4    | IL
2  |   1    | WA
2  |   2    | OR
2  |   3    | NH
2  |   4    | RI

在 Excel 2007 中执行此操作最简单的方法是什么？

答案1

您可以使用数据透视表来完成此操作。

  1. 创建“多个合并区域数据透视表”。 （仅适用于数据透视表向导。在 Excel 2007 中，使用ALT+调用）DP
  2. 选择“我将创建自己的页面字段”。
  3. 选择您的数据。
  4. 双击总计值 - 位于罗格兰德和大柱，一直到数据透视表的右下角。

您应该会看到一个新工作表，其中包含数据透视表中的所有数据，并按照您想要的方式进行转置。

Datapig 技术提供分步说明实际上比你需要的更复杂——他的例子只转置了部分数据集，并使用了枢轴技术与文本转列。但它确实有很多图片。

请注意，数据透视表将对数据进行分组。如果您希望取消分组，唯一的方法是复制数据透视表，然后“选择性粘贴”为值。然后，您可以使用以下技巧填写空白：http://www.contextures.com/xlDataEntry02.html

答案2

如果您的数据不是 Excel 数据透视表而只是数据，您可能希望使用一些简单的 VBA 代码对其进行“取消透视”。代码取决于两个命名范围，即源和目标。源是您要取消透视的数据（不包括列/行标题，例如示例中的 NY-RI），目标是您要放置结果的第一个单元格。

Sub unPivot()
Dim oTarget As Range
Dim oSource As Range
Dim oCell As Range

Set oSource = Names("Source").RefersToRange
Set oTarget = Names("Target").RefersToRange

For Each oCell In oSource
    If oCell.Value <> "" Then
        oTarget.Activate
      ' get the column header
        oTarget.Value = oCell.Offset(-(oCell.Row - oSource.Row + 1), 0).Text 
      ' get the row header
         oTarget.Offset(0, 1).Value = oCell.Offset(0, _
           -(oCell.Column - oSource.Column + 1)).Text 
      ' get the value
        oTarget.Offset(0, 2).Value = oCell.Text 
      ' move the target pointer to the next row
        Set oTarget = oTarget.Offset(1, 0) 
    End If
Next
Beep
End Sub

答案3

我已经创建了一个插件，可以让你做到这一点，并且可以轻松适应不同的情况。请在此处查看：http://tduhameau.wordpress.com/2012/09/24/the-unpivot-add-in/

答案4

到目前为止我想到的最好的办法是：

Id   LocNum  Loc
---------------------------------
1    1       =INDEX(Data,A6,B6)
1    2       =INDEX(Data,A7,B7)
1    3       =INDEX(Data,A8,B8)
1    4       =INDEX(Data,A9,B9)
2    1       =INDEX(Data,A10,B10)
2    2       =INDEX(Data,A11,B11)
2    3       =INDEX(Data,A12,B12)
2    4       =INDEX(Data,A13,B13)

这可行，但我必须手动生成 Id 和 LocNum。如果有更自动化的解决方案（除了编写宏），请在单独的答案中告诉我。

相关内容