今日を乗り切るExcel研究所

Excel に働かされていませんか

Excelのマトリクス表をリスト表へ変換したい

Excelで作成したマトリクス形式の表を、データとして扱い易くなるよう、リスト形式の表に並び替える手順とそれを自動処理するマクロを検討します。

f:id:shego:20170520141509p:plain

マトリクス表をデータ化したい

要は下図の左の表から右の表へ表の形式を変換したいです。

f:id:shego:20170505132608p:plain

これらの表形式を何と呼んだものか、日本語の標準的な呼び名が良く分からないのですが、 左の表形式はマトリクス、集計表、クロステーブル、ピボットテーブル、多次元テーブルなどの呼び名が使われているようです。

一方、上図右の表形式は、単にテーブル、リスト、一覧表、フラットデータなどと呼ばれているようです。

この記事ではとりあえず、各々「マトリクス」と「リスト」と呼ぶようにします。

マトリクス表は、公表される統計データや、各部署の営業実績表、データ連携用コードマッピング表など、いたるところで使われています。

マトリクスは人が見る分には良くても、このままデータベースに取り込む元データ(CSVなど)としては使えず、いったんリスト形式に変換・加工する必要があります。

列ごとに配置換えするコピー&ペーストの単純作業を無心に繰り返せばいいのですが、データ量が多いと大変な作業になります。

手作業でマトリクス表をリスト表に変換するには

マトリクス-リスト変換は古くからある問題のようで、Web を検索してみると様々な方法があるようです。

ここではその中のひとつ、[ピボットテーブル/ピボットグラフ ウィザード]を使った手順を検証します。

[ピボットテーブル/ピボットグラフ ウィザード]コマンドは、標準のリボンに置かれていないので(Excel 2013)、ボタンをクイックツールバーなどに登録するか、ショートカットAltDP(Data Pivot)で直接呼び出します。

【手順例】

  1. ピボットテーブルウィーザードのダイアログを開きます
    • キー操作: AltDP
  2. 最初の画面(1/3)
    1. [分析するデータのある場所]に、[複数のワークシート範囲]を選択します
    2. [次へ]ボタンを押します
      f:id:shego:20170504141013p:plain:w245
  3. 次の画面(2a/3)
    1. [ページフィールドの作成方法]に、[指定]を選択します
    2. [次へ]ボタンを押します
      f:id:shego:20170504141029p:plain:w255
  4. 次の画面(2b/3)
    1. [結合するワークシートの範囲]でマトリクスの範囲(見出しを含む)を指定します
    2. [追加]ボタンを押します
    3. [次へ]ボタンを押します
      f:id:shego:20170504141059p:plain:w176
  5. 次の画面(3/3)
    1. [ピボットテーブルレポートの作成先]に、[新規ワークシート]を選択します
    2. [完了]を押します
      f:id:shego:20170504141151p:plain:w271
  6. ワークシートが新規追加されます
    • ピボットテーブルに変換されています f:id:shego:20170505132644p:plain
  7. 作成された表の一番右下のセルを「ダブルクリック」します
  8. ワークシートが新規追加されます
    • 変換されたリスト表が作成されています
      f:id:shego:20170505132714p:plain

ちょっとした魔法のようですが(ウィザードだけに)、何をやっているのかサッパリ分かりません。

個人的にこれまで何回もやった事があるのですが、全然覚えられないので、この機会に手順を詳細に記録しておきました。

しかしこの方法では、見出しが複数階層になったマトリクス表には対応できないようです。



マトリクスデータをリストに変換するマクロ

ピボットテーブルウィザードを使った手順では行見出しと列見出しが1階層である2次元のマトリクスしか対応できません。

ここでは、複数の見出しが階層的にグルーピングされた(多次元)マトリクス表も、リストに変換するマクロを考えます。

まず、マトリクス表の範囲と、その見出しの位置をマクロに指定出来なければなりませんが、入力ダイアログなど使わずに、セルの選択だけで指定できるようにしたいところです。

そこで、本マクロでは、選択された範囲の左上隅のセルの位置をデータ領域の最初のデータとみなし、これを基準としてマトリクス表の構造を自動判断することにします。

f:id:shego:20170510131149p:plain

セルを一つ選択すると、それが基準セルとなり、それを含むアクティブセル領域(空行と空列で囲まれる領域)を見出しを含むマトリクス表の範囲として使用します。

マトリクス表の範囲のうち、基準セルより上の行と左の列を見出しデータとみなします。

それ以外の、基準セル以降右下の領域を値データと見なします。

また、1セルではく、特定の領域を範囲選択して指定することもできます。

集計行を除外したい場合など、特定の領域のみをデータ領域としたい場合は、そこだけ範囲選択後(見出しは含めない)、このマクロを実行します。

見出しに、階層を表す空欄があったりセル結合されていても自動で補完します。

パフォーマンスを優先するため書式を捨てているので、数値などでは元データと表示が異なることがあり、日付など数値になってしまいますので注意してください。

f:id:shego:20170510145114p:plain:w282

Option Explicit

Sub マトリクスをリストに変換する()
    Dim table As Range
    Dim matrix As Range
    Dim target As Range
    
    If Selection.Cells.Count > 1 Then
        Set matrix = Selection.Cells
        Set table = Range(matrix.CurrentRegion.Cells(1, 1), matrix.Cells(matrix.Rows.Count, matrix.Columns.Count))
    Else
        Set table = ActiveCell.CurrentRegion
        Set matrix = Range(ActiveCell, table.Cells(table.Rows.Count, table.Columns.Count))
        matrix.Select
    End If
    
    If table.Row = matrix.Row Or table.Column = matrix.Column Then
       Beep
       Exit Sub
    End If
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set target = ActiveWorkbook.Worksheets.Add.Range("A1")
    matrixToList table, matrix, target
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Private Sub matrixToList(srcTable As Range, srcMatrix As Range, dstList As Range)
    Dim rowHSize As Integer
    Dim colHSize As Integer
    Dim lineSize As Integer
    rowHSize = srcMatrix.Column - srcTable.Column
    colHSize = srcMatrix.Row - srcTable.Row
    lineSize = srcMatrix.Columns.Count
    
    Dim srcRowHeader As Range
    Dim srcColHeader As Range
    With srcMatrix
        Set srcRowHeader = .Offset(0, -rowHSize).Resize(, rowHSize)
        Set srcColHeader = .Offset(-colHSize, 0).Resize(colHSize)
    End With
    
    Dim dstKey1 As Range
    Dim dstKey2 As Range
    Dim dstVals As Range
    With dstList.Cells(1, 1)
        Set dstKey1 = .Offset(0, 0).Resize(lineSize, rowHSize)
        Set dstKey2 = .Offset(0, rowHSize).Resize(lineSize, colHSize)
        Set dstVals = .Offset(0, rowHSize + colHSize).Resize(lineSize, 1)
    End With
    
    Dim srcKey2Arr As Variant
    srcKey2Arr = WorksheetFunction.Transpose(srcColHeader.Value)
    
    Dim srcLine As Range
    For Each srcLine In srcMatrix.Rows
        dstKey1.Value = srcRowHeader.Rows(1).Value
        dstKey2.Value = srcKey2Arr
        dstVals.Value = WorksheetFunction.Transpose(srcLine.Value)
        
        Set dstKey1 = dstKey1.Offset(lineSize, 0)
        Set dstKey2 = dstKey2.Offset(lineSize, 0)
        Set dstVals = dstVals.Offset(lineSize, 0)
        Set srcRowHeader = srcRowHeader.Offset(1, 0)
    Next
    
    fillDownBlanks Range(dstList.Cells(1, 1), dstKey2.Rows(1).Offset(-1, 0))
End Sub

Private Sub fillDownBlanks(rng As Range)
    Dim blanks As Range
    If rng.Cells.Count > 1 Then
        On Error Resume Next
        For Each blanks In rng.SpecialCells(xlCellTypeBlanks).Areas
            If blanks.Row > 1 Then
                blanks.Value = blanks.Rows(1).Offset(-1, 0).Value
            End If
        Next
        On Error GoTo 0
    End If
End Sub

【使い方】

  1. 標準モジュールに上記 VBA プログラムをコピー&ペーストします
  2. マトリクス表のデータ領域、あるいはその左上隅のセルを選択します
  3. マクロを実行します
  4. 新規シートにテーブル形式の表が出力されます

※ 複数行・列の見出しも展開したい場合には、表全体ではなく、実データ範囲の最初(左上)のセルのみを選択します。見出しの内容によっては期待通りならないかもしれません。

【注意】 本マクロの動作は期待する結果の確実性・正確性を保証するものではありません。

本記事の内容は Windows 10 の Excel 2013 で動作確認しました。