リレーションしたテーブルからピボットグラフを作成する方法

投稿日: 2021年1月19日 2021年1月19日
投稿者: morita
カテゴリー: data, エクセル
タグ: データ編集

さらばVLOOKUP！リレーションテーブルからピボットグラフを作成する方法、新規メジャーを作成する方法などについて解説しています。

ピボットグラフ

データについて

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

ピボットグラフを挿入

データは前回の投稿をつかいます＞エクセル形式のオープンデータを活用する基本-クロス集計形式データを分割してリレーションする方法

　新規シートを追加します。
・「挿入」タブ
・「ピボットグラフ」をクリックします。

　窓が開きます。
・「外部データソースを使用」のラジオボタンをチェック
・「接続の選択」をクリックします。

　窓が開きます。
・「テーブル」のタブをクリックします。
・「２つのテーブル」キューブ型のアイコンを選択
・「開く」をクリックします。

基本的なグラフ

　テーブル名が表示されます。
・テーブル名の左側にある白三角形をクリックします。
　テーブル内のデータフィールド（データ列）が開きます。

＜テーブル「Establishments」＞
・YYYY/MM(年)をチェック
・自動的に「軸（分類項目）」へ追加されます。
　自動的に追加されないときは、ドラッグしてドロップしてください。

＜テーブル「Value」＞
・Value（百万円）をチェック
・自動的に「Σ（値）」へ追加されます。
　自動的に追加されないときは、ドラッグしてドロップしてください。

　たちまち完成しました。

ピボットグラフの操作

系列ごとに表示する

＜テーブル「Value」＞
・「category」を「凡例」へドラッグ＆ドロップで追加します。
　チェックを入れるだけの操作では「軸」へ追加されることがあります。

・グラフエリアを右クリック
・「グラフの種類の変更」を選択します。

・「縦棒」から
・「積み上げ縦棒」を選択します。

　折線にしてみます。

二重軸グラフ

　categoryを削除します。方法は２通りあります。
・ドラッグ＆ドロップで戻す方法
・右側の黒い▼をクリックし、「フィールドの削除」を選択する方法

テーブル「Establishments」の「YYYY/MM」の横軸へテーブル「Value」のメジャーをプロットすることができます。

　categoryを削除して、テーブル「Establishments」のEstablishments」(店舗数)を「Σ(値)]へ追加します。

　現在のグラフは
・横軸＝テーブル「Establishments」の「YYYY/MM」
・グラフ青線の値＝テーブル「Value」のValue」(カテゴリ売上高の合計)の年間合計
・グラフオレンジ線の値＝テーブル「Establishments」の「Establishments」(店舗数)の年間合計

ポイントは、
テーブル「Establishments」の「YYYY/MM」の横軸へテーブル「Value」のメジャーをプロットすることができることです。
　これがリレーションのメリットです。

・グラフエリアを右クリック
・「グラフの種類の変更」を選択します。

・「組み合わせグラフ」
・どちらかのメジャーを「集合縦棒」
・一方のメジャーを「折線」
・「第２軸」をチェックした軸が右側の軸になります。

・スケールを右クリック
・「軸の書式設定」を選択します。

　軸の書式設定が開きます。
・グラフのアイコンをクリック
・「表示形式」(折りたたまれているときは三角を押してください)
・「カテゴリ」のドロップダウンのなかから「数値」を選択します。

集計方法の編集

値の集計方法を変更する

　Establishments(店舗数)の値が70万とかになっています。

　グラフの横軸は「年」です。ここがポイントです。

　Establishments(店舗数)の値を月別で確認すると5万6千中盤の値で推移しています。
　12か月を合計すると「679,003」です。

　グラフの横軸が「年」ということは、グラフの値は「年」の合計値を示すことになります。つまり、横軸に従い表計算をした合計値がグラフへプロットされるしくみになっています。

　もちろん、横軸を「年月」に設定すればデータ表通りの値になります。

　2109年の合計は「679,003」、データの12か月合計値と一致しています。

・「Σ(値)」にある「Establishments」の▼三角をクリック
・「値フィールドの設定」を選択します。

　窓が開きます。
・「集計方法」タブ
・「平均」を選択
・OKで窓を閉じます。

　表示が「平均」になっていれば完成です。

集計方法「平均」とは

　2019年のデータは12か月あります。
・平均＝値の12か月合計÷12

　2020年のデータは10か月です。
・平均＝10か月合計÷10

結論は、
・平均＝メジャーの行の値の合計÷メジャーの行数

計算メジャーの作成

新規メジャー作成

　現在のデータ表にあるメジャーは、
・テーブル「Value」の「Value」(カテゴリ売上高の合計)
・テーブル「Establishments」の「Establishments」(店舗数)
　これら２メジャーです。

　既存のメジャーから新規メジャーを新規作成します。
　作成するのは、
・1店舗あたりの売上高＝売上高÷店舗数

・テーブル名を右クリックします。テーブル「Value」、テーブル「Establishments」のどちらでも構いませんが、今回はテーブル「Establishments」で実践します。
・「メジャーの追加」を選択します。

通常のエクセルシートはセル計算ですが、テーブルではカラム(列フィールド)計算になります。

　窓が開きます。
　テーブル「Establishments」を右クリックしたので「テーブル名」は「Establishments」になっていると思います。(ドロップダウンから選択できます)
・「メジャーの名前」を入力します。
・必要に応じて「値の説明」を入力してください。

#数式の書き方
SUM('テーブル名'[メジャーA])/AVERAGE('テーブル名'[メジャーB])

#今回の数式
=SUM('Value'[Value (百万円)])/AVERAGE([Establishments])

　数式をタイプします。

・「SUM(」のあたりまでタイプすると候補カラムの窓が開きます。
・’Value'[Value (百万円)]をダブルクリックします。

・「AVERAGE(」のあたりまでタイプすると候補カラムの窓が開きます。
・[Establishments]、Establishments[Establishments]どちらかをダブルクリックします。
　テーブル名でテーブル「Establishments」を指定しているので、カラムのテーブル名の部分(Establishments)は省略できます。’ ‘で囲まれるテーブル名は、あってもなくても構わないということです。