エクセル クロス集計形式を列指向形式へ変換する方法

エクセル クロス集計形式を列指向形式へ変換する方法

エクセルでイメージ通りのグラフを描くことができない!そんなときはデータ表の形式を変更することで解決できるかも。クロス集計形式表を列指向形式表へ変換するPower Query エディターの手順を解説しています。

動画編はこちら

もとデータについて
政府統計の総合窓口「e-Stat」より

<データ出典>
政府統計の総合窓口「e-Stat」(https://www.e-stat.go.jp/)
国勢調査>人口推計>各月1日現在人口>平成27年国勢調査結果確定人口に基づく改定数値(平成27年10月~28年6月)>年齢(5歳階級),男女別人口-総人口,日本人人口を加工して作成

 もとデータの表です。今回は、「年齢」の列、「平成27年10月1日・11月1日」の列、「男女合計・男・女」の別です。

 使用する部分だけを取り出して、ヘッダーが2行に分かれている部分を結合して1行にまとまています。

 データ表はクロス集計形式、いわゆるマトリクス型です。

 このデータ表から
・横軸に「日付」、縦軸に「人口」、系列を「男・女」に設定した棒グラフを作成したいと思っています。

 ちょっと難しい感じがします。その原因はデータ表がクロス集計形式になっているからです。このデータ表を列指向形式へ変換すれば、イメージしたグラフを描画することができるようになります。

Power Query エディター

エクセル クロス集計形式を列指向形式へ変換する方法 トレーニングデータ_1はこちら

 データ表の「A1」へカーソルを合わせます。
「データ」タブ→「テーブルから」の順でクリックします。

 必要なデータの範囲が選択されていれば「OK」です。

 Power Query エディターが開きます。

 シフトキーを押したまま「人口」が記載された6列すべてを選択します。

 「変換」タブへ移動して「列のピボット解除」をクリックします。

 これで列指向形式へ変換されました。
 「属性」の列を移動します。「移動」→「左へ移動」

 属性のデータを「日付」と性別に分割します。
 「列の分割」→「区切り記号による分割」

 データは「YYYY/MM/DD」、「-」(半角バー)、「性別」の構造ですから「-」(半角バー)で分割します。

 区切り記号へ「-」(半角バー)、分割は「一番左の区切り記号」、「OK」です。

 分割したら、列名を整えます。
 列を選択した状態で「名前の変更」

 列名の左側にあるマークはデータの表示形式をあらわしています。カレンダーは「日付」、ABCは「文字列」、123は「数値」です。

 画面右側の「クエリの設定」にここまで実行してきたステップが表示されます。間違えてやり直したいときは、ステップをクリックして左側の「×」を押すとそのステップを取り消すことができます。

 「ファイル」タブの「閉じて読み込む」をクリックします。

 データテーブル形式の新規シートが生成されます。「ブッククエリ」の「×」マークをクリックします。

 Power Query エディターへ戻りたいときは、データ表内のどこかへカーソルを合わせて、「クエリ」タブ→「編集」の順でクリックします。

 「挿入」タブから「ピボットグラフ」をクリックします。

ピボットグラフ

 イメージ通りのグラフができたら完成です。