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

操作手順1

ここではピボットテーブルとピボットグラフを同時に設定する手順を示す。

Excelメニューの挿入を左クリック > ピボットグラフの下半分を左クリック > 上から2番目のピボットグラフとピボットテーブルを左クリック

次の画面が表示される。

このブックのデータモデルを使用するにチェック > 既存のワークシートにチェック > シートのセルを1つ左クリック > OKを押す

ここではsheet1という名前のシートのA1セルを選択

この画面がでたら、操作手順1は終わり

以降、ここまでの手順を操作手順1と記述する。例えば「操作手順1を実行後」といった書き方になる。

 

操作手順2

ここではピボットテーブルのみを設定する手順を示す。

Excelメニューの挿入を左クリック > ピボットテーブルの下半分を左クリック > 上から3番目のデータモデルからを左クリック

次の画面が表示される。

既存のワークシート > セルを1つ左クリック > OKを押す

ここではsheet1のA1セルを選択した。

この画面がでたら、操作手順2は終わり

以降、ここまでの手順を操作手順2と記述する。例えば「操作手順2を実行後」といった書き方になる。

 

ピボットテーブルに名前をつける

ピボットテーブルの左上(行ラベル)を左クリック。

Excelメニュー > ピボットテーブル分析 > 左端のピボットテーブル〇を削除して任意の名前をつける

ここでは売上推移とした

グラフ 種類の変更

デフォルトでは、グラフは縦棒である。これを変更する。

Excelメニュー > デザイン > グラフの種類の変更 > 左クリック

目的のグラフを左クリック(ここでは折れ線グラフを選択する)

いくつか種類があるので、適したものを上から選び、OKを押す

以降、これをグラフの種類の変更と呼ぶ。例えば、グラフの種類の変更をして、といった書き方になる。

グラフ 要素の削除

グラフを選択(左クリック)する。

Excelメニュー > ピボットグラフ分析 > フィールドボタン > すべて非表示 

右端の集計、左端の目盛り、横軸の線 を左クリックしてDeleteを押す

集計というタイトルだけが残るので、タイトルを左クリックして、集計を削除して、グラフ名を書く。ここでは、売上推移とする

もし、グラフのタイトルを削除してから、後でタイトルをつけたい場合は次の手順を踏めばいい。

Excelメニュー > デザイン > グラフ要素の追加 > グラフ タイトル > 
グラフの上 をすべて左クリックで行う

以降、これをグラフの要素の削除と呼ぶ。例えば、グラフの要素の削除を実行後、といった書き方になる。

グラフ 色の変更

グラフエリアを選択(左クリック)し、右クリックする。

メニュー全体の下から3番目のグラフエリアの書式設定を左クリック

右端にこの画面がでるので、塗りつぶしの矢印を左クリック

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

線(単色)をチェックし、色は赤、幅を2pt、角を丸くするにチェックを入れる
X軸の色の変更(Y軸の目盛りも同様にできる)

この画面から、X軸の年度と月年度(どれでもよい)を左クリックする

Excelメニュー > ホーム > A(色) > 白 を左クリック
タイトルの色の変更

タイトルの売上推移を左クリックして、Excelメニューのホーム > 色を黄色 > 
Bで太文字 > 11ptにする
線の色を変更する

変えたい線を右クリック > データ系列の書式設定を左クリック > 
バケツを左クリック

線(単色)、色はここでは赤 にする > シートの空き部分を左クリック

この画面になれば色の変更は終了である

以降、これを色の変更あるいは色を変更と記述する。例えば、色の変更を実行すればいい、といった書き方になる。

 

貼り付け

切り取り、コピーはできるだろうが、問題は貼り付け方である。ここではセルからセルへの貼り付け方2種類とグラフの貼り付け1種類いを説明する。

セルからセルへの貼り付け

左端から2番目の123が値貼り付け、右端の∞がリンク貼り付け と呼ぶ
グラフの貼り付け

左端のaと書いてあるもので貼り付けする

グラフ表示シートに貼り付けたら、左クリックを押しながらドラックして、任意の位置へ移動する。

以降、これを貼り付けと呼ぶ。例えば貼り付けをすればいい、といった書き方になる。

年度累計

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

年度、月年度はこの順番で入れる。売上は本体から

ピボットテーブルに名前をつける。(ここではグラフのタイトルと同じにした)

この画面のデータの一番上を左クリックし、右クリックを行う

計算の種類を左クリックし、累計を左クリックする

基準フィールドを月年度にして、OKを押す

データがこのように変わると同時にグラフも変わる

グラフの種類を折れ線に変更し、グラフの要素を削除して、色を変更すると、グラフは次のようになる。

このグラフをグラフ表示シートへ貼り付けすればOK

移動平均

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

年度、月年度はこの順番で入れる。売上は本体から

ピボットテーブルに名前をつける。(ここではグラフのタイトルと同じにした)

グラフの種類を折れ線に変更し、グラフの要素を削除して、色を変更すると、グラフは次のようになる。

このグラフを左クリックする

上の十字を左クリックする

近似曲線を左クリックし、出てくる▶を左クリックして、その他のオプションを左クリック

近似曲線のオプションの一番下の移動平均にチェックを入れ、区間を3にする

青の点線が移動平均線である。これを赤の実線に変更する。

点線を右クリックし、書式設定のバケツを左クリック > 線(単色)にチェック >色を赤 > 実線/点線で一番上の実線を左クリック

このグラフをグラフ表示シートへ貼り付けすればOK

 

前年度同月比

Excelメニュー > Power Pivot > メジャー > 新しいメジャー をすべて左クリック

上から2番目のメジャー1を削除して前年度同月比と入力し、=の後ろに
DIVIDE(SUMX('fマスタData',[売上]),CALCULATE(SUMX('fマスタData',[売上]),SAMEPERIODLASTYEAR('予定表'[Date])))
をコピペしてOKを押す

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

フィールドの行と値の項にこの順序で入れるとピボットテーブルは右のようになる

ピボットテーブルに名前をつける。(ここではグラフのタイトルと同じにした)

前年度同月比をパーセント表示にするため、前年度同月比のデータをすべて選択する。

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

パーセントを左クリックして、小数点以下の桁数を0にする

X軸、Y軸、横軸、タイトルを残して、グラフの要素を削除する。

横軸の線の色を赤に変える

横軸の線を右クリックし、枠線を左クリックして、赤を選択する

このグラフをグラフ表示シートへ貼り付けすればOK

商品伸び率

操作手順1を実行後、各フィールドに値を入れる。

この順序で入力する

出来上がったピボットテーブル

ピボットテーブルに名前をつける。(ここではグラフのタイトルと同じにした)

計算の種類を選択して、基準値に対する比率を求める。

データの左上を右クリック > 計算の種類を左クリック > 基準値に対する比率を左クリック

この画面にしたら、OKを押す

ピボットテーブルはこのように変化する

X軸、Y軸、横軸、タイトルを残して、グラフの要素を削除する。グラフの色の変更は前年度同月比と同様にする。

このグラフをグラフ表示シートへ貼り付ければOK。各線はスライサーで指定する

分類・商品のグラフ

操作手順1を実行後、フィールドに値を入れる。

凡例に商品、軸に分類、値に売上を入れる

個数の場合は、本体から売上の代わりに個数を入れる。

ピボットテーブルは以下のようになる。

空白ができているが、無視して差し支えない

ピボットテーブルに名前をつける。(ここでは、分類と個数とした)

グラフは以下のようになる。

デフォルトの状態

グラフの要素を削除して、色を変更すると以下のようなグラフになる。

ここで、各縦棒に名前をつける

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

ラベルオプションの系列名にチェックを入れ、値のチェックを外す

この画面になったら、ExcelメニューのホームのA(色を変える)で色を白に変える

このようになったら、残りの棒も同様にして商品名を出力する

グラフのタイトルと色を変更すると完成である。

完成した分類・商品棒グラフ

分類売上割合

操作手順1を実行後、フィールドに値を入れる。

行に分類、値に売上を入れる

ピボットテーブルはこのようになる

part2で解説したグラフ 種類の変更を実行し、グラフ 要素の削除を行い、タイトルをここでは分類売上割合とした。

ここに、分類名と割合を表示させる

円グラフ内のどれでもいいから、右クリックしてデータラベルの追加を左クリック

これらの数字の1つを右クリックしする

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

分類名とパーセンテージにチェックを入れ、値のチェックを外す

この状態から、分類名とパーセンテージの色を白に変更する

Excelメニュー>ホーム>Aで白>Bで太文字>10pt

グラフ 色の変更を実行すれば完成である

サイトご利用方法

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