【Excel】ピボットテーブルやクロス集計をリスト型の整然データにする(De-Pivot)

テーブル型のデータをリストに変換する

Excelを使っているとテーブル型のデータによく遭遇します。マトリクスとも呼ばれますし、場合によってはクロス集計表とも呼ばれます。ピボットテーブルを一度でも使ったことがある方にとっては見慣れたものでしょうか。

f:id:ekanoh:20180817231844p:plain

われわれ人間にとっては見やすくていいのですが、Excelにとってはなかなか難のあるデータの形です。たとえば、テーブル型のデータが複数のシートに跨って複数存在するときなどはデータをマージ(結合)するのに一苦労です。

こういう場合は、テーブル型のデータをリスト型に変換できれば話が早いでしょう。

f:id:ekanoh:20180817231606p:plain

なぜなら、右のようなリスト型のデータであれば、末尾にどんどん追加していくだけで簡単にデータをマージすることができるからです。また、ピボットテーブルもすぐにかけることができるので大変都合のよい形です。 (なお、このようなリスト型のデータを「整然データ」と呼ぶことがありますので、興味のある方は検索してみてください。)

De-Pivotの機能を使う

Excelでこのような操作を行う場合、いくつか方法がありますがDe-Pivotの機能を使うのが楽ちんです。以下の手順で行ってみましょう。

1. ピボットテーブルウィザードを開く

変換したいテーブルのあるシートを開いて、Alt -> D -> PDe-Pivotと覚えましょう!)の順でキーを押すと、ピボットテーブルウィザードが開きます。

3つ目の[Muliple Consolidation Ranges]を選んで、先に進んでください。

f:id:ekanoh:20180817232306p:plain

ここもそのまま[Create a single page field for me]でOKです。

f:id:ekanoh:20180817232416p:plain

2. 範囲を指定する

変換したいテーブルの範囲を指定して先に進みます。

f:id:ekanoh:20180817232618p:plain

ここもそのまま[New worksheet]でOKです。

f:id:ekanoh:20180817232705p:plain

3. ピボットテーブルからリストに戻す

変換したかったテーブルを元にしたピボットテーブルが出来ているので、右下のセル(ここでは492と入っている)をダブルクリックしてください。

f:id:ekanoh:20180817232745p:plain

すると、求めていたリスト型のデータが表示されます。

f:id:ekanoh:20180817232925p:plain

3-2. 空欄が要らない場合

f:id:ekanoh:20180817233134p:plain

上図のように元となるデータに空欄が入っていると、リストに変換した後のデータにも空欄が入ったままになります。

f:id:ekanoh:20180817233233p:plain

このような場合は、フィルタ機能を使って空欄セルを省いてあげればOKです。

f:id:ekanoh:20180817233315p:plain