エクセル形式のオープンデータを活用する基本-クロス集計形式データを分割してリレーションする方法

エクセル形式のオープンデータを活用する基本-クロス集計形式データを分割してリレーションする方法

数値を目で読むのには適しているクロス集計表ですが、いざ、グラフを作成しようとすると結構むつかしい。エクセルでグラフ化しやすいデータ表へ構造を変更する方法について解説しています。

データについて

<データ出典>
政府統計の総合窓口「e-Stat」(https://www.e-stat.go.jp/)
商業・サービス業>商業動態統計調査>時系列データ>
コンビニエンスストア商品別販売額等及び前年(度、同期、同月)比
オープンデータですので上のリンクからダウンロードできます。

複数のメジャーを含むデータ表
よくあるパタンです

この投稿はエクセル形式のオープンデータを活用する基本-行・列を編集する方法からの続きです。

 データ表は、列A~列Dが売上高(単位は百万円)、列Eは店舗数です。
 このように、売上高と店舗数のような単位が異なるメジャーが列ごとに分かれて一体化したデータ表になっていること自体に問題はありません。というのか、通常のデータ表です。

 このデータ表の課題は、売上高のメジャーが4列に分かれているところにあります。売上高の列は1列だけでよいわけです。

データ表を分割
列ごとコピーします

 店舗数を示す列Eと年月の列Fをコピーします。

新規シートを追加してペーストします。

 新しいシートへペーストします。

列指向形式へピボット
4列ある売上高を1列へまとめます。

 カーソルをA1へ合わせてください。
・「データ」タブ
・「テーブル」をクリックします。

 自動的に「データ範囲」「先頭行をテーブルの見出しとして使用する」にチェックされていると思います。
 範囲がずれているとくきは修正してください。

列を削除します。

 パワークエリが開きます。
 列Eは不要なので削除します。削除する方法は2通りあります。
・列を右クリックして「削除」を選択する方法
・列を選択して「ホーム」タブの「列の削除」をクリックする方法

列から行へピボットします。

・ピボットする列、列A~列Dを選択します。
・「変換」タブの「列のピボット解除」をクリックします。

列を整備
列名を変更します。

 列名を変更する方法は2通りあります。
・列名のセルをダブルクリックする方法
・列名を選択して「変換」タブの「名前の変更」をクリックする方法

年月の列が年月日時間になっています。

 列の表示形式を変更します。

Power Queryにはエクセルほど日付形式のバラエティーはありません。

・YYYY/MM列のカレンダー+時計アイコンを右クリック
・「日付」を選択します。

Power Queryで操作手順を間違えて戻りたいときは「適用したステップ」の「×」をクリックしてステップを消去します。

 「日付」へ変換するとYYYY/MMはYYYY/MM/DDへ変換されます。DDがないとき、DDの部分はすべて「01」になります。
・閉じて読み込みます。

テーブル操作
テーブル名を入力したら必ずエンターキーを押しましょう。

 Power Queryを閉じて読み込むとテーブル形式のシートが作成されます。
 テーブル名を変更します。
・「デザイン」タブ
・「テーブル名」の枠へタイピングします。
・タイピングしたらパソコンのエンターキーを押してください。
 エンターキーを押さないとテーブル名を変更できないことがあります。

カーソルをA1へ合わせておきましょう。

 コピーで作成した「Establishment」(店舗数)のシートもテーブルへ変換します。
・セルA1へカーソルを合わせて
・「挿入」タブ
・「テーブル」をクリックします。

空欄行がなければ自動的にすべての範囲を選択してくれます。

・データの範囲を確認してOKをクリックします。

テーブルへ変換すると自動的にフィルターが設定されます。

 テーブルへ変換されました。
・テーブル名を入力します。

 テーブルのデザインは「デザイン」タブの「テーブルスタイル」で変更することができます。

テーブルのリレーションシップ
リレーションシップ

・「データ」タブ
・「リレーションシップ」をクリックします。

リレーションシップの新規作成

 窓が開きます。
・「新規作成」をクリック

「テーブル」「関連テーブル」(選択する上下)はぢちっがどっちでも構いません。とはいうものの・・・

・リレーションするテーブルをドロップダウンのなかから選択
・列は「YYYY/MM」、両方のテーブルに共通しているKEYになる列をドロップダウンのなかから選択します。

「テーブル」と「関連テーブル」の入れ替え

 「テーブル」「関連テーブル」(選択する上下)を入れ替えてみると何やらワーニングが表示されます。
・このままOKをクリックします。

リレーションシップの内容確認

・「リレーションシップ」をクリック
・開いた窓の「編集」をクリックします。

ワーニングに書かれている「方向」というのは左右のことなのだろうと思います。

 「テーブル」「関連テーブル」(選択する上下)が自動的に入れ替わっています。

「リレーションシップの作成」窓のOKをクリックすると画像のような窓へ戻ります。

・「閉じる」をクリックして完成です。

リレーションしたデータ表を確認する

 残念ながらリレーション完成後のデータ表を見ることはできないようです。

イメージです

 イメージ化すると画像のような感じなのだろうと思います。

・もともとの課題であった売上高を示すメジャーを4列から1列にできた。
・YYYY/MMをKEYにして売上高と店舗数という複数のメジャーを同時に取り扱うことができるようになった。

データ表を分割してリレーションするメリット
売上高と店舗数の2軸グラフ+近似曲線

 データ表をリレーションすると、ピボットグラフで活用できます。
 2軸のグラフなど、サクサク作成できるようになります。

 ピボットグラフは次回の投稿で解説します。