アンピボットテーブル (Unpivot)

説明

ピボットテーブル (pivot tables) は、データ分析における強力なツールです。ただし、場合によっては、ピボットされたデータをアンピボット (unpivo) して、すべての同様の値が同じ列に収まるように、ピボットされたデータを通常のテーブル形式に置く必要があります。これは、たとえば、新規または変更されたグラフやレポートを作成する場合に必要です。

この変換は、Power Query ツールを使用して Excel で実行できますが、高速ではなく、あまり便利ではありません。 Power Query を使用せずに概要データを展開する別のアプローチを試してみましょう。

問題の定式化

一部のデータセットをピボットテーブルからフラットテーブル (Unpivot) に変換する必要があると想像してください。

このようなタスクは、マクロを使用するとより適切に解決されます。データを含むクロステーブル (クロステーブル) を通常の (フラット) テーブル形式に変換できる独自のプロシージャを作成してみましょう。これにより、再び可能になります。テーブルを作成するデータが変更または追加されたもの。

UnPivotTable プロシージャの VBA コード

エディター 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 ツールの武器全体を使用して、このようなテーブルを「完全なプログラムで」操作できます。

アンピボットテーブル (Unpivot)

説明

問題の定式化

UnPivotTable プロシージャの VBA コード

関連記事：