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

前提

ここではExcelによるダッシュボードの作り方を解説する。

前提となるのは

  • 本体のデータが出来上がっていること。
  • Power Pivotがアドインしてあること。
  • 本体からの切り出しが終了していること。
  • Excelにデータモデルとして、本体と切り出しが取り込まれていること。

である。

Excelでは、シートは2枚必要になる。1枚はピボットテーブル用のデータとして、もう1枚はグラフ表示用である。

 

目標とするダッシュボード

事例で示したものであるが、再掲する。

 

年度テーブル

Excelメニュー > power pivot > 管理 を左クリックする

power pivotの画面が開く。

あらかじめデータを取り込んでいる状態

power pivotの画面でデザインを選択する。

デザインを選択 > 日付テーブルを左クリック > 新規作成を左クリック

日付テーブルは日本語のExcelであれば、予定表として落ちるはずであるが、calendarで落ちる場合もある。こだわりがなければこれでもいいが、体裁を整える意味で、calendarを予定表に直す。

calendarの直し方

calendarを右クリック > 名前の変更を左クリック > 予定表と入力 > リターンキーを押す

次にYearなど、それぞれの列の名前を左ダブルクリックして列名を変更する。

Yearを年、Month Numberを月の番号、Monthを月、Day Of Week Numberを曜日の番号、Day Of Weekを曜日に、それぞれ変更する

ただし、年度テーブルの作成なので、月を月年度にする。この作業は体裁を整える意味しかない。追加・変更を加えるのはこれからである。

月年度の直し方

月年度の表記を1月……と直す。

月年度を左クリックすると、=の後ろに

デフォルトのM言語

とでてくるが、Mを3つ消して一つにして、&"月"と入力する。

            =FORMAT([Date],"M"&"月")

あるいは

             =FORMAT([Date],"M月")

でもよい。結果は以下のようになる。

このように変化する
年度の追加方法

power pivotの一番右端にある「列の追加」を左ダブルクリック > 年度と入力

=の後ろに

          if([月の番号]<4,([年]-1&"年度"),([年]&"年度"))

をコピペして、リターンキーを押す。

月並替の追加

「列の追加」を左ダブルクリック > 月並替と入力

し、=の後ろに

     if(MONTH([Date])<4,MONTH([Date])+9,MONTH([Date])-3)

をコピペして、リターンキーを押す。

月年度と月並替をグループ化(重要)

月年度を選択 > power pivot画面のホームを左クリック > 列で並べ替えを左クリック

列で並べ替えを左クリック

グループ化の右端の矢印を左クリック > 月並替を左クリック > OKを押す

これで、年度テーブルの完成である。

リレーション

本体のfマスタDataに切り出した商品・分類・予定表をリレーションする。

power pivotを左クリック > 管理を左クリック > ホーム画面を開く

右端のダイアグラムビューを左クリック

この状態から、上にある黒文字の商品を本体(fマスタData)の商品へドラッグアンドドロップ。同様に、黒文字の分類も本体(fマスタData)の分類へドラッグアンドドロップ、予定表のDateも本体(fマスタData)の年月日へドラッグアンドドロップする

 

リレーションが組まれた状態

これで、右上の×印でpower pivotの画面を閉じて、終了である。

レベルメーター

データ用シートに、あらかじめ、シートA1に【経営指標】、B2に売上指標と書いておき、データ本体から売り上げを落とす。

Excelメニュー>挿入>ピボットテーブル>データモデルから を選択

既存のワークシートを選択し、場所をピボットB3にしてOKを押す

ピボットテーブルのフィールドにデータの本体から売上を値に落とす。

このようになる

C3セルに売上目標値、C4セルに(ここではB4セルの1.6倍の)数値を入力する。

注意:C4セルに数値を入れるときに、=B4*1.6とやるだろうが、そうしたら、必ずC4セルをコピーして、再度C4セルに値貼り付け(左から2番目の123と書いてあるもの)をすること

このような状態になっているはずである

ここで、B3、B4セル(ピボットの部分)は邪魔になるので選択してDeleteを押す。

このような状態になっているはずである
売上目標値をデータモデルに追加

レベルメーターを作成するためにはピボットテーブルを作らなければならないので、売上目標値と値をデータモデルに追加する

Power Pivot  > データモデルに追加 > 範囲指定の画面が出る

この画面の範囲指定で、真ん中の右端の四角をクリックする

この画面でC3セルとC4セルをマウスで選択する

この画面で、先頭行をテーブルの見出しとして使用する、にチェックする

OKを押し、次の画面では何もせずにOKを押すと、Power Pivot画面でテーブル番号となりデータが取り込まれる。

わかりやすくするために、テーブル番号の部分を右クリックする。

名前の変更を左クリックして、売上目標値、とする

注意:個数指標を作る場合は、いままでの売上の部分を個数に変えること

出来上がったPivotテーブルの形

fマスタDataがデータの本体、商品と分類、は本体から切り出したもの、予定表は年度カレンダーであり、これらはすべてリレーションを組んである。売上目標値と個数目標値はいま作ったもので、リレーションは組まない

リレーションの図 Power Pivot > 管理 > 右端のダイアグラムビュー
ピボットテーブルの作成

挿入 > ピボットグラフの下矢印を左クリック > ピボットグラフとピボットテーブルを左クリック

このブックのデータモデルを使用する > 既存のワークシート > 場所をB6セルに指定するためにB6セルを左クリック

ピボットフィールドの値に、本体から売上、いま作った売上目標値を左クリックして出てくる売上目標値を値に入れる
注意:値を入れる順番は必ず売上が上売上目標値を下にすること
ピボットグラフの作成

デフォルトでは縦棒グラフになっている。ここから余分なものを削除する。

ピボットグラフ分析 > 右端にあるフィールドボタンを左クリック > すべて非表示を左クリックする

この段階でグラフは以下のようになる

デフォルトで、すべて非表示にした図
グラフの変更

デザイン > グラフの種類の変更を左クリック

グラフの種類の変更画面 一番下の組み合わせを左クリック

 

この画面で、グラフの種類の集合縦棒の第2軸にチェック > 折れ線の右にある矢印をチェックして集合縦棒に変更する
売上目標値を集合縦棒に変更

この画面で一番上の左端の図を左クリックする

この画面が出てくれば、OKを押す

Excelのシート上にこのグラフが表示される
目盛りを合わせる

この画面から第1軸と第2軸の目盛りを合わせる。
第1軸(左側)の数値のどれかを左クリックし、次に右クリックする。

この画面の一番下、軸の書式設定を左クリックする

軸のオプションの最大値の4.0を3.6に変更してリターンキーを押す

こうすると最小値がズレるので最小値を0にして、リターンキーを押す。

このようなグラフになる。

次に第2軸(右側)も第1軸と同様の手順で変更する。

数値を左クリック > 右クリック > 軸の書式設定 > 最大値を2.5から3.6に変更する > 最小値を0に変更する > リターンキーを押す

このようなグラフになる
レベルメーターの作成

茶色の部分を右クリック > 一番下にあるデータ系列の書式設定を左クリック

一番左のバケツを左クリック

塗りつぶし枠線の左側にある三角を左クリック

上から2番目の塗りつぶし(単色)をチェックし、一番下の色の右端のバケツを白に変える

線(単色)をチェックすると自動で色が青に変わる

幅を3Ptに変更する

この画面から、グラフ以外のものを1つづつ選択(左クリック)してDeleteを押す
(全部で5か所)

この画面の余白を左クリックすると、〇で囲われた部分が2重になる

外側の〇囲みをグラフ領域、内側の〇囲みを描画領域という

グラフ領域の右下にカーソルを合わせて、左クリックを押したままドラックして縦長にする

グラフ内の余白を左クリックすると描画領域が出てくるので、
〇をグラフ領域まで広げる

グラフ領域に描画領域を重ねた図。右下の〇を選択して縦長を再度微調整する
ラベルの表示

青色の部分を右クリックする

データラベルの追加の三角にマウスを合わせる

出てきたメニューで、データラベルの追加を左クリック

ラベルの数値が縦にずらずらと書かれるので、これを調整する
ラベル表示の変更

ピボットテーブルの画面を調整する

数値のセルを選択して右クリック > 上から2番目のセルの書式設定を左クリック

一番下のユーザー定義を左クリック

上から4番目の#,##0を左クリック

この画面がでたら0の直後に  ,,"M"  (すべて半角)と入力し、OKを押す

,"K"は千、,,"M"は百万、,,,"bn"は10億を表す( , の数に注意)

ピボットテーブル、ピボットグラフがこうなった状態で青色の部分を右クリック

下から2番目のデータラベルの書式設定を左クリック

ラベルの位置で、中央にチェックを入れる

この状態で、Excelメニューのホームを開く
ラベルの色の変更

フォントメニューのAで色を、Bで太文字、フォントのサイズを12Ptにする

ここでは、フォントの色をオレンジ、太文字、12Ptとした。

この画面で数字を左クリックする

数字の〇囲みの真ん中の〇を左右に広げる

この画面がでたらグラフエリア(右上の〇の付近)へカーソルを移動して右クリック
グラフエリアの色の変更

下から3番目のグラフエリアの書式設定を左クリック

塗りつぶし(単色)をチェックし、色を黒(上から3番目)を左クリックする

線(単色)をチェック、色を赤、幅を2pt、角を丸くするにチェック

Excelの余白をクリックすると、レベルメーターの完成である。

完成したレベルメーター

スライサー

ここで一度、作成したレベルメーターが正常に動作するかを確認しておく。その機能がスライサーである。また、スライサーはグラフではなくピボットテーブルのみに作用するものだから、まずはレベルメーターのピボットテーブルに名前をつける。

 

ピボットテーブルの左上をクリック

Excelメニューのピボットテーブル分析を左クリックする

左端のピボットテーブルの下矢印を左クリックして、ピボットテーブル〇となっているところを削除して、ここでは売上指標と入力する

この画面がでたら、ワークシートの余白を左クリックする

これで名前は完了である。

スライサーの設定

Excelメニューの挿入を左クリックする

右端から4番目のフィルターにあるスライサーを左クリックする

すべてを左クリックする

予定表のその他のフィールドを左クリックして年度にチェックをいれてOKを押す

ここまでの作業の間は、Excelメニューにピボットテーブル分析デザインが常に表示されていること。

この画面がでると、Excelメニューのピボットテーブル分析とデザインは消える
Excelメニューのスライサーを左クリックする

左端から2番目のレポートの接続をクリックする

売上指標にチェックを入れて、OKを押す

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

これで、スライサーの設定は完了である。

スライサーの動作確認

あとは実際に年度をクリックしてレベルメーターが変化するかどうかを確認する。

複数選択をしたければ、右上のバツ印がついたじょうごの右隣にチェックを入れる。

チェックを入れた状態。この画面ではすべてが選択されている

例えば、2018年度と2019年度の合算値を見たければ、2020年度と2021年度を左クリックする。

すべて解除する場合は、バツ印のじょうご左隣の複数選択を左クリックする。

動作確認が終了したところで、再びレベルメーターに戻る。

再びレベルメーター(達成率)

Excelシート上でB9セルに達成率、B10セルに =B7/C7 と入力する

B10セルを右クリック > セルの書式設定を左クリック

パーセンテージを左クリック > 小数点以下の桁数は2に設定する > OKを押す

完成した達成率


ここで、注意するのは、B10セルをコピーして値貼り付けをしないことである。

完成した経営指標(達成率の数値は小数点以下0としてある)

ここで、先ほどのスライサーを使って、達成率が変化することを確認する。レベルメーターと連動して動作すればOK。

表示画面の設定

グラフ用のシートを作成 > A1セルを左クリックする

Shift+Ctrl を押しっぱなしにしながら、キーボードの右矢印を押した後下矢印を押す。

この状態を維持しながら、Excelメニューのホームを押す。

Excelメニュー > ホーム > フォントの右下斜め矢印を左クリック

フォントの2つ右隣りの塗りつぶしを左クリックする

一番下の右端の紫色を左クリック > OKを押す

これでシート全体が紫色に変化するので、グラフ用シートのA1セルに必要な事柄を記入する。

A1セルに売上指標と表題をつけて、期限と年月日、目標値と数字はべた書き。
達成率はべた書きでいいが、数値はリンク貼り付け
リンク貼り付けの手順

データ用ピボットテーブルのB10セルを右クリック > コピー > 表示用のシートへ移動する > A7セルを右クリック

一番右端の∞のマークがついているものを左クリック
経営指標の色の変更

B2セルからB7セルをマウスの左を押しながら選択 > 右クリック

バケツを選択して、黒の上から3番目を選択して、OKを押す
グラフを表示シートへ移動・貼り付け

グラフは赤い枠線にカーソルを合わせて右クリック。

切り取りを左クリックする

表示用のシートへ移動したら、B1セルを右クリック

貼り付けのオプションで、一番左のものを左クリック

あとはグラフとその位置を微調整して完成である。

完成した売上指標

これで、レベルメーターは終了である。

 

サイトご利用方法

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