Excelで文字列ピボット
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 |
つまり数値の集計(合計、平均、最大値など)や文字列のカウントなどはできますが、文字列の結合ができないのです。
それを行うためのマクロを考えました。
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 | タグ: VBA , ピボット ]
« 複数のテキストボックスの値を結合 | 町田商店マックス »
値の部分が1セルに結合されています。値を結合しないで別々のセルに表示したいのですができますか?
その際行ラベルが同じものは結合したいです。
上記マクロを参考にしていますが、なかなかうまくいきません。