TOP

不可透视表 (Unpivot)

描述

汇总表 (pivot tables) 是数据分析的强大工具。然而，有时您需要部署 (unpivo) 聚合数据（也称为“拉直”或展开数据）将其放入常规表格格式中，以便所有相似的值都在同一列中。这是必要的，例如，创建新的或修改的图表或报告。

您可以使用 Power Query 工具在 Excel 中执行此转换，但速度不快且不太方便。让我们尝试一种不同的方法来在不使用 Power Query 的情况下推出汇总数据。

问题的表述

想象一下，我们需要将一些数据集从数据透视表转换为平面表（Unpivot）：

借助宏可以更好地解决此类任务。让我们编写自己的程序，它将能够将带有数据的交叉表（cross table）转换为常规（平面）表格式，这将允许我们再次创建汇总表更改或补充数据。

VBA UnPivotTable 过程的代码

打开编辑器 Visual Basic ( 服务-宏-编辑器Visual Basic ), 插入新模块 (Insert - Module) 并将该宏的文本复制到此处：

Sub UnPivotTable()
  'moonexcel.com.ua
  Dim InVal    As Variant
  Dim OutVal() As Variant
  Dim j, k, i  As Long
  Dim NewSheet
  
  i = 1
  InVal = Selection.Formula
  ReDim OutVal(1 To Selection.Count, 1 To 3)
  
  For j = 2 To UBound(InVal, 1)
    For k = 2 To UBound(InVal, 2)
      If InVal(j, k) <> "" Then
        OutVal(i, 1) = InVal(j, 1)
        OutVal(i, 2) = InVal(1, k)
        OutVal(i, 3) = InVal(j, k)
        i = i + 1
      End If
    Next k
  Next j
  
  Set NewSheet = Worksheets.Add
  NewSheet.Range("A1").Resize(UBound(OutVal, 1), 3).Value = OutVal
End Sub

之后，您可以关闭VBA编辑器，返回Excel，选择源表（完整地，带有标题和带有国家/地区的第一列）并通过菜单运行我们的宏服务 - 宏 - 宏 (Tools - Macro - Macros) (ALT+F8) 。

该宏将在工作簿中插入一个新工作表，并在其上创建所选表的新的重建版本。您可以“使用完整程序”处理这样的表，使用整个 Excel 工具库来处理和分析大型表。