【欲しいデータベースを作る】クロス集計表をリスト形式の表に変換する方法

Excel

エクセルで分析作業を行う際には、ピボットテーブルが便利です。
ただし、ピボットテーブルを使うためには、データがリスト形式(データベースとかデータテーブルとか呼びます)でなくてはなりません。

しかし、人からもらうデータはクロス集計表である事が多いですよね。
もらったデータを見て、「なんで使いにくいデータなんだろう?」と誰もが思った事があるでしょう。
クロス集計表からのリスト形式のデータに変換するのが面倒で、分析を諦めてしまう方も多いと思います。

そんなときに思い出していただきたいテクニックとして、クロス集計表をリスト形式の表に変換する方法をご紹介したいと思います。

スポンサーリンク

クロス集計表とリスト形式の表はどんな関係?

まず、クロス集計表とリスト形式の表の関係性についておさらいしておきます。
下の図を見てください。
左の表がクロス集計表で、右の表がリスト形式の表です。2つの表のデータの中身は同じですが、見た目が異なります。

 

一般的にクロス集計表はリスト形式の表(データベース)を元に作られます。
例えば、データ分析の際には何らかの意図を分かりやすく伝えるために、リスト形式の表をクロス集計表にまとめなおすのです。リスト形式の表のままではデータの羅列なので、傾向を把握する事は出来ません。

そのため、他人からもらうデータは他人の意図が反映されたクロス集計表となっていて、元データ(データベース)としてのリスト形式の表が記されていない場合が多いです。
特に、「複数部門を経由して入手できるデータはクロス集計表しかない」と考えてよいです。

また、クロス集計表はあってデータの内容は把握できる状態でも、部門の壁、会社の壁などでリスト形式のデータを入手できない事もあります。

 

リスト形式の表に変換する方法

前置きが長くなりましたが、クロス集計表をリスト形式の表に変換する方法について紹介したいと思います。
今回は、VBA(マクロ)は使わず、エクセルの標準機能を使って変換を行います。変換作業は大きく2つの段階に分かれます。

1. セル結合を解除して空白セルに同じ値を入力する

セル結合はデータを整理する上で厄介なので、まずはセル結合を解除して適切な値を入力していきます。

クロス集計表は何らかの成果物として使うことが多いため、成果物をキレイな見た目にしようとするのはDNAに埋め込まれたサガのようなもので、セル結合する事を止める事は出来ないでしょう。潔く作業を進めましょう。

 

まずは、列方向に結合されたセル結合を解除します。
結合を解除したいセル群を選択した状態で、リボンのホームタブから「セル結合の解除」をクリックします。

 

次に結合が解除されて空白になっているセルに適切な値を代入していきます。
「Ctrl + Gキー」でジャンプ機能を呼び出し、「セル選択」をクリックします。

 

選択オプションの中の「空白セル」をチェックし、「OK」ボタンを押します。
これで選択範囲内の空白セルのみが選択された状態になります。

 

結合を解除されて出来た空白セルは、上のセルと同じ値にする必要があります。
そのためのテクニックとして、選択された空白セルに一括で同じ数式を入力します。
現時点では、空白セルが選択されていますが、その中でのアクティブセルはB7セルになります。B7セルに「=B6」と入力し、「Ctrl + Enterキー」を押します。「Ctrl + Enterキー」によって、同じ数式が選択された空白セルすべてに入力されます。
※「Enterキーのみ」の場合には、同じ数式や値がコピーされません。

 

同様に、行方向にも結合されたセルも結合を解除し、「Ctrl + Gキー」のジャンプ機能で空白セルを選択肢、左にあるセルと同じ値になるようにします。
これで、「セル結合を解除して空白セルに同じ値を入力する」パートは完了です。

 

2. OFFSET関数を利用してデータを並べ替える

セル結合のない単純なクロス集計表になったので、あとはデータを並べ替えていきます。

まずはリスト形式の表(データベース)の箱を作ります。
今回はデータの並べ替えを簡単にするために、1行(1レコード)に対して、1つの値が記載されるようにしました。クロス集計表の項目名に対して、リスト形式の表の1行目に対して適切な属性名を付けていきます。
今回の例では、「果物/菓子  →  分類」「りんご/すいか/・・・ → 品目」「数量/売上 → データ名」「8月/9月/10月 → 計上月」「50/70/・・・ → 値」というように、それぞれの名称の上位概念になるような単語を属性名に割り当てます。
※一般的には数量と売上はセットで1レコードにしていると思いますが、データの並べ替え方法の説明をするためにう数量と売上は別のレコードにしました。

そして、データ並べ替えの際に使うための連番をリスト形式の表の横に記載しておきます。
※1列分の空白列を取っておくと無難です。

 

並べ替えは下の図のように行います。
クロス集計表では1行あたり6個の値(数量:3個、売上:3個)が記載されているので、それらをリスト形式の表では6行(6レコード)に並べ替えます。

 

データの並べ替えはOFFSET関数に、MOD関数とQUOTIENT関数を組み合わせて使います。

OFFSET(基準, 行数, 列数, 高さ, 幅)
基準のセルまたはセルの範囲から指定した行数と列数を移動した先のセル参照を取得します。参照する範囲の大きさを高さと幅で指定できます。

QUOTIENT(分子,分母)
除算(割り算)の商の整数部を求めます。

MOD(数値,除数)
除算(割り算)の余りを求めます。

まず、下の図の黄色枠で囲った部分の数式例を示します。
M5=OFFSET($B$6,QUOTIENT($K5,6),M$2,1,1)
「分類」属性のデータ参照先を、B6セルを基準として6データ毎にOFFSETする行数を1行増やすことを意味しています。周期的に値を増やしていく関数としてQUOTIENT関数を使っています。
OFFSET関数の列数の引数は、「分類:0」「品目:1」となるようにしています。
M5セルの式を1つ作れば、黄色枠のセルは数式をコピペする事で対応できます。

次に、オレンジ色枠で囲った部分の数式例を示します。
O5=OFFSET($D$4,O$2,MOD($K5,6),1,1)
「データ名」属性のデータ参照先を、D4セルを基準として1データ毎にOFFSETする列数を1列増やしつつ、6データ周期でOFFSETする列数をリセットすることを意味しています。周期的に値をリセット出来る関数としてMOD関数を使っています。
OFFSET関数の行数の引数は、「データ名:0」「計上月:1」となるようにしています。
O5セルの式を1つ作れば、オレンジ色枠のセルは数式をコピペする事で対応できます。

最後に、緑色枠で囲った部分の数式例を示します。
Q5=OFFSET($D$6,QUOTIENT($K5,6),MOD($K5,6),1,1)
「値」属性のデータ参照先を、D6セルを基準として1データ毎にOFFSETする列数を1列増やしつつ、6データ周期でOFFSETする列数をリセットした上で行数を1行増やすことを意味しています。QUOTIENT関数とMOD関数を組み合わせる事が重要です。
OFFSET関数の行数の引数は、「データ名:0」「計上月:1」となるようにしています。
Q5セルの式を1つ作れば、緑色枠のセルは数式をコピペする事で対応できます。

あとは必要なデータ数分が転記できるまでリスト形式の表をコピペで下に伸ばして完成です。

 

まとめ

以上、クロス集計表をリスト形式の表に変換する方法をご紹介しました。

OFFSET関数をベースとして使いつつ、QUOTIENT関数とMOD関数を組み合わせる事が重要です。
割り算系の関数は使い慣れないとイメージが湧きにくいですが、使い慣れてくると便利です。なぜならば、エクセルのデータは、何らかの周期を持っている事が多いからです。

リスト形式の表さえ作れれば、分析作業が非常に楽になります。今回の方法を是非マスターしてみてください。

コメント

タイトルとURLをコピーしました