Excelで文字列ピボット

Pocket

Excelにはピボットテーブルという機能があります。これを知っていると集計作業が簡単で正確にできるので、是非、使えるようになりたい機能です。
行見出しと列見出しを指定するとそれぞれの条件に合致した値を集計したりカウントしたりします。
しかし、まとめられるのは数値だけです。
次のような表があり、入荷予定日と店舗を指定し、その日、その店に入荷する商品をそれぞれまとめて表にしたいとします。

入荷予定日 店舗 商品名
2013/11/1 神奈川店 リンゴ
2013/11/1 千葉店 ミカン
2013/11/1 千葉店 ナシ
2013/11/1 東京店 ブドウ
2013/11/3 神奈川店 リンゴ
2013/11/3 千葉店 ナシ
2013/11/3 東京店 ブドウ
2013/11/3 東京店 ミカン
2013/11/5 神奈川店 リンゴ
2013/11/5 神奈川店 ナシ
2013/11/5 千葉店 ブドウ
2013/11/5 東京店 ミカン
2013/11/7 神奈川店 ブドウ
2013/11/7 千葉店 ミカン
2013/11/7 千葉店 ナシ
2013/11/7 東京店 リンゴ

次のような表になりますが、Excelのピボットテーブルではこれができません。

入荷予定日\店舗 神奈川店 千葉店 東京店
2013/11/1 リンゴ ミカン

ナシ
ブドウ
2013/11/3 リンゴ ナシ ブドウ

ミカン
2013/11/5 リンゴ

ナシ
ブドウ ミカン
2013/11/7 ブドウ ミカン

ナシ
リンゴ

また、その店にその商品が入荷する日をそれぞれまとめて表にしたいとします。次のような表になりますが、やはりピボットテーブルではできません。

店舗\商品名 リンゴ ミカン ナシ ブドウ
神奈川店 2013/11/01

2013/11/03

2013/11/05
2013/11/5 2013/11/7
千葉店 2013/11/01

2013/11/07
2013/11/01

2013/11/03

2013/11/07
2013/11/5
東京店 2013/11/7 2013/11/03

2013/11/05
2013/11/01

2013/11/03

つまり数値の集計(合計、平均、最大値など)や文字列のカウントなどはできますが、文字列の結合ができないのです。
それを行うためのマクロを考えました。

Sub 文字列ピボット()
    Dim rows(1000) '行ラベル全体
    Dim cols(1000) '列ラベル全体
    Dim vals(1000) '値(セルの内容)
    Dim heads(3) '列番号
    options = Array("行ラベル", "列ラベル", "値(セルの内容)")
    '軸の設定(どの列を軸にし、どの列をまとめるか)
    c = 1
    optionText = ""
    Do
        If optionText <> "" Then
            optionText = optionText & Chr(13)
        End If
        optionText = optionText & c & " : " & Cells(1, c)
        c = c + 1
    Loop Until Cells(1, c) = ""
    For i = 1 To 3
        res = InputBox("番号を入力。" & Chr(13) & optionText, options(i - 1), i)
        If res = "" Then
            Exit Sub
        Else
            heads(i) = CInt(res)
        End If
    Next i
    'データの取得
    rowLabel = Cells(1, heads(1))
    colLabel = Cells(1, heads(2))
    x = 0
    Do
        rows(x) = Cells(x + 2, heads(1))
        cols(x) = Cells(x + 2, heads(2))
        vals(x) = Cells(x + 2, heads(3))
        x = x + 1
    Loop Until Cells(x + 2, 1) = ""
    rowsNew = array_unique(rows) '行ラベル(重複なし)
    colsNew = array_unique(cols) '列ラベル(重複なし)
    'ピボットテーブル
    Worksheets.Add
    '軸
    Cells(1, 1) = rowLabel & "\" & colLabel
    '行ラベル
    For r = 0 To UBound(rowsNew)
        Cells(r + 2, 1) = rowsNew(r)
    Next r
    '列ラベル
    For c = 0 To UBound(colsNew)
        Cells(1, c + 2) = colsNew(c)
    Next c
    '値
    For r = 0 To UBound(rowsNew)
        For c = 0 To UBound(colsNew)
            For x = 0 To UBound(vals)
                If rows(x) = rowsNew(r) And cols(x) = colsNew(c) Then
                    temp = vals(x)
                    moto = Cells(r + 2, c + 2)
                    If moto <> "" Then
                        temp = moto & Chr(13) & Chr(10) & temp
                    End If
                    Cells(r + 2, c + 2) = temp
                End If
            Next x
        Next c
    Next r
End Sub
Function array_unique(arrs)
    ReDim temps(UBound(arrs))
    x = 0
    For i = 0 To UBound(arrs)
        If x = 0 Then
            temps(x) = arrs(i)
            x = x + 1
        Else
            flg = 0
            For j = 0 To x
                If temps(j) = arrs(i) Then
                    flg = 1
                    Exit For
                End If
            Next j
            If flg = 0 Then
                temps(x) = arrs(i)
                x = x + 1
            End If
        End If
    Next i
    ReDim rets(x - 1)
    For i = 0 To x - 1
        rets(i) = temps(i)
    Next i
    array_unique = rets
End Function

表はA1セルから作成します。見出しは1行です。セルの結合があると正しく動作しません。
対象の表を表示した状態で、マクロ「文字列ピボット」を実行すると、列見出しを訊かれます。列見出しにしたい列の番号を指定します。A列ならば「1」です。同様に行見出しにしたい列の番号、まとめたい情報(値(セルの内容))の列の番号を指定します。
新しいシートが追加されて「ピボットテーブル」が作られます。
本物のピボットテーブルと違い、更新はできません。

ベースとなるシートを一つ作れば、様々な見せ方の表を自動的に作ることができます。
例えば、世界史と日本史を一つのシートにまとめ、「年」「出来事」「区分」(世界史と日本史など)を入力しておき、行見出しに「年」、列見出しに「区分」、値(セルの内容)に「出来事」を指定すれば、一つのシートから世界史と日本史が分かれた年表を作ることができます。

[ 2013年10月12日 | カテゴリー: Excel | タグ: , ]

« | »

コメント

  1. sako より:

    値の部分が1セルに結合されています。値を結合しないで別々のセルに表示したいのですができますか?
    その際行ラベルが同じものは結合したいです。
    上記マクロを参考にしていますが、なかなかうまくいきません。

  2. stabucky より:

    sako さん
    値を結合しないとセルが複数になり、右に伸ばすか下に伸ばすか、いくつ伸ばすか、など処理が複雑になるので、対応していません。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

送信してください。


タグ

カテゴリー

最近の投稿

最近のコメント

固定ページ

アーカイブ

stabucky

写真

メタ情報