ダッシュボード 作り方 Excel編 part3

はじめに

part1,part2とピボットテーブルからピボットグラフを作ってきたのだが、このピボットグラフには制限があり、以下のものだけである。

すべての棒グラフ、折れ線グラフ、円グラフ、ドーナツグラフ、面グラフ、レーダーチャート

ここで作成していくヒストグラムと箱ひげ図、散布図そしてパレート図はピボットグラフとしてピボットテーブルから直接作ることはできない。

さらに、ピボットグラフのように、グラフ自体の角を丸くすることはできない。(グラフを表示したときに角を丸くする表示方法はあるのだが……)

なお、操作手順2を実行後、色の変更などはpart2で解説したものを使う。

ヒストグラムと箱ひげ図

ヒストグラムと箱ひげ図は同じデータを使う。

操作手順2を実行後、ピボットテーブルのフィールドに値を入れる。

行に年度と月年度、値に売上を入れる。個数の場合は値に本体の個数を入れる

売上を1列置いた右にpart2で解説した値貼り付けをする。このとき、セルが年度ごとに空白となるので、空白部分ができないように貼り付けること。さらにpart1ラベル表示の変更で解説した売上の表示の単位をM(百万)にする。

ここから、ヒストグラムと箱ひげ図の作り方が変わってくる。

ヒストグラム

セルをすべて選択した後、Excelメニュー>挿入>図のグラフを左クリック>ヒストグラムを選択

このようなグラフになる。ここでpart2で解説したグラフ 要素の削除し、グラフ 色の変更を行い、タイトルを売上ヒストグラムに変える

このようなグラフになったら、X軸の項目を左ダブルクリックする

右側に軸の書式設定という画面が現れるので、ビンの数(日本では柱の数)を8に変更する

このグラフに個数のラベルをつける

ラベルを左クリックして、part2で解説したX軸の色の変更と同様の作業をして、ラベルの色を白に変更する。ラベルの合計値が36になっていればOK
お断り

ヒストグラムの階級の幅は、スタージェスの公式や、身長なら10㎝刻みといったことがあるが、ここでは、3年分すべてのデータの動き(最頻値とデータの散らばり具合)を見たいだけなので、ビンの数を8本に指定しただけであり、8本あれば十分だと考えている。
なお、統計でいうところの分布ということは、一切考えていないことをお断りしておく。

箱ひげ図

セルをすべて選択した後、Excelメニュー>挿入>図のグラフを左クリック>箱ひげ図を左クリック

このようなグラフになる。ここでpart2で解説したグラフ 要素の削除を実行し、グラフ 色の変更を行い、タイトルを売上箱ひげ図に変える。箱ひげ図ではX軸の項目も削除するが、この段階ではY軸の目盛りは残しておく

縦軸の数字のどれかを左クリックし、次に右クリックをする。

一番下の軸の書式設定を左クリックすると、右側に軸の書式設定の画面が出てくる

最小値を4.0E6に、最大値を9.5E6に変更すると、描画領域にグラフが広がる

ここでY軸の目盛りを削除して、グラフを右クリックし、データラベルの追加を左クリック

ラベルを左クリックして、part2で解説したX軸の色の変更と同様の作業をして、ラベルの色を白に変更する
注意

ヒストグラムは単独で用いることができるが、箱ひげ図は単独で用いることはできず、必ずヒストグラムと一緒に使わなければならない。理由はヒストグラムの山がふた山、三山になっていても、中央値(データの半分の値)は変わらないからである。
ここで箱ひげ図を使用したのはデータの半分がどの辺に固まっているか(これはヒストグラムからもわかるが)、平均値(ここでは×印)と中央値の乖離があるのかどうかを確認するためである。

散布図

操作手順2を実行後、ピボットフィールドに値を入れる。

行に商品、値に個数、売上をこの順序で入れる

行ラベルを商品、合計/個数を個数、合計/売上を売上に変える

Excelの場合、左端からX軸、Y軸、バブルの大きさとなる

総計を除き、すべてを選択したあと、1列置いた右端にリンク貼り付けを行う。理由は年度スライサーをかますためである。次にpart2のピボットテーブルに名前をつけるを実行する。ここでは、散布図とした。
part1で説明した通り、スライサーはピボットテーブルにしか作用しないのだが、このリンク貼り付けを行うことで、ピボットテーブルにスライサーが作用し、リンク貼り付けをした値も自動的に変化し、グラフもそれに従って変わるという仕掛けである。

このようになったら、貼り付けした範囲をすべて選択し、グラフをバブルチャートにする

このようになれば左クリックして確定する

選択範囲とグラフはこのようになる

このグラフからpart2で解説したグラフ 要素の削除グラフ 色の変更を行い、タイトルはここでは個数・売上散布図とした。

グラフがこのようになったら、各円の色を変更する

どれか1つ円を左クリックし、次に右クリックする

データ系列の書式設定を左クリックして、バケツを左クリックする。

要素を塗り分けるにチェックを入れる

この状態のまま、右クリックをして、データラベルの追加を左クリック

左クリックすればデータラベル(値)がでるので、それを右クリックする

データラベルの書式設定を左クリックする

セルの値にチェックを入れる

この画面から、商品のセルをすべて選択する

ここでは、sheet1のE2からE13までを選択。OKを押す

Y値のチェックを外す

この画面から、Excelメニュー>ホーム>Aで白>9ptに設定する

この画面がでたら、重なり合った文字を左クリックする

左クリックして、白〇でかこまれたら、ドラックして文字をずらす

このようになればOK

完成した散布図

パレート図

操作手順2を実行後、ピボットフィールドに値を入れる。

本体から売上を値に、行に商品を入れる。個数の場合は本体から個数を値に入れる

総計以外を選択し、1列置いて右側にリンク貼り付けを行う

散布図と同様に、年度スライサーをかますためにリンク貼り付けを行う。part2のピボットテーブルに名前をつけるを実行する。ここでは、売上パレート図とした。

図のように、パレート図を選択したら、左クリックをする

ここで、左側のY軸を削除して、part2で解説したグラフ 色の変更を行う。

完成したパレート図

スライサー

part1ではピボットテーブルを選択した状態でスライサーを作成したが、ここでは、グラフ表示シート上でスライサーを設定する手順とスライサーの1列表示を変更する方法を説明する。

Excelメニュー>挿入>スライサーを左クリック

この画面は無視していいので、OKを押す

データモデルを左クリックし、開くを押す

分類と商品にチェックを入れて、OKを押す

本体の商品、分類ではなく、切り出したものを選択すること

このような画面になるので、後は列を変更する

Excelメニュー>スライサー>列数の右にある上下の矢印をクリックして、画面にあうようにスライサーの列数と大きさを調整する

スライサー レポートの接続とスライサーの連携

図1

図2

図1と図2を見比べて欲しい。

図1では、イベントを選択しても商品はすべて選択された状態になっているのに対して、図2ではイベントを選択するとイベントに関する商品のみが選択できる状態になっている。

これは商品のレポートの接続で、売上指標と個数指標、そして商品伸び率をレポート接続したかどうかの違いである。(図1は接続したもの、図2は接続しないもの

どちらがいいかは使い手の好みである。

個別の商品の売上と個数そして達成率と商品伸び率まで知りたければ図1となるが、この状態でイベントに無関係な商品を選択すると、年度累計、移動平均、前年度同月比のグラフは消え、移動平均は再度書き直す必要がでてくる。

図2を選択すれば図1の危険は避けることはできるが、個別の商品の売上と個数そして達成率と商品伸び率は表示できない。

(このような現象が起こる原因は不明。おそらくExcelのバグかも?)

ここでは図1を使用し、分類のスライサーをかければ、商品が上段の左から3つが自動的に分類に仕分けされている商品になるので、他をクリックしないように注意して欲しい。

また、作り方の注意点としては、スライサーの作成画面から、同時に商品と分類を落とすこと(この2つにチェックを入れてからOKを押すこと)である。スライサーの接続は別々になるのは当然である。

なお、「年度と分類」「年度と商品」のスライサーの組み合わせは図1・2ともに可能である。問題なのは「分類と商品」のスライサーの組み合わせ方のみである。

さらに、注意喚起をしておくと、一度スライサーの項目を解除してから(右上の端のバツ印のじょうごをクリックする)、次のスライサーを仕掛けることである。こうすれば変にグラフが消滅したりはしない。


以上でExcelによる経営ダッシュボードの作成は完了である。

サイトご利用方法

次のページ・前のページを利用するよりも、グローバルメニュー(ヘッダー部分にある項目)をクリックしていただければ、その項目の全体像が一目でみることができ、クリックすればそのサイトへ飛びます。

google、yahoo、Bingなどで検索する場合、検索ワードは先頭に、孤立じじい、と入力しその後に、ダッシュボード or インテリア or 統計 or 談話室、とどれかひとつを入力すると、その検索サイトが上位表示されます。