BDAstyle

ビジネスデータ分析ツールの作成 with Excel

ブレットグラフの作成 with Excel

イントロダクション

Step 0

筆者には普遍的な訳語は不詳ですが,ここでは「Bullet Graph」(以下,ブレットグラフ)と呼ばれるところのビジュアライズを扱います。

ブレットグラフの何たるかは

にて詳しいかと思いますので必要であればそちらを参照ください。


たとえば,数ヵ月に1度おこなわれる何らの評価試験のようなものを考えるとわかりやすいかと思いますが,仮の話として,100点満点で採点されるこれは

  • 80点以上Aランク
  • 60点以上Bランク
  • 40点以上Cランク
  • 上の範囲に当てはまらないDランク

といった質的な区分で示される評価の軸を持っていたとします。ブレットグラフは,そうした評価軸の上に,

  • この試験の得点そのもの
  • 前回の試験の得点(比較対象,ないしは目標としての役割を担うもの)

といった量的な情報を重ねて一度に表示します。ここでは,Staff No.1 - No.3の各人のデータを次のように用意しています。

DL

なお,このグラフを作る場合,質的な評価区分(ここではA-D4つ)については上限(あるいは境界)の値にて定義しておきます。

話を戻して「重ねて一度に表示」……と聞けば,もっぱらつめこみが過剰なグラフのように感じられ,見難くなるばかりと憂慮を抱くところです。ところがどうして,このグラフは下図のようになかなかにスッキリを保ったまま情報を盛り込むことが可能です。

参考に,これと同様の情報を,「図形」や「テキストボックス」を駆使して,あえてベタに表現してみたいと思います。

こうして比較すると明瞭かと思いますが,このグラフは同じ情報を表すにしろよりコンパクトになしとげてしまいます。いや,何よりカタチから入る筆者にとってこのグラフの最大の美点は,どこかしら洗練されたイメージが漂ってくるトコにあると思っています。……わりと本気に。

エクセルにおいてこれを作るアプローチとしては,外形から察するに棒グラフ,または散布図を素地にする方法の二択となるかと思われます。素地に応じていくらかの一長一短が考えられますが,やはり,単純な手続きで済ませられるといった利点は何物にも代えがたく思うので,ここでは素地を「棒グラフ」にした方法を選択したいと思います。

―――以下,その手続きです。

工程

素地を準備する

Step 1

下の領域を選択し,

「集合縦棒」グラフを差し込みます。

Step 2

グラフが選択された状態から,デザインタブ「データ」グループの行/列の切り替えボタンをクリックします。

これにより元表にいう列項目によるグルーピングに切り替わります。

Step 3

質的な区分(ここではA-D4クラスのこと)の彩色を任意の色でおこないます。

このグラフが敷く彩色における考え方を尊重して,それを階調で表現します。この場合でも,本来は1系列ずつ様子を見ながらととのえていくことが好ましいとは思いますが,ここでそれを追求するのも主旨を逸らすことになりますので,より簡便に色の変更機能に用意されたデフォルトの配色を適用したところです。

任意の系列を選択し……

デザインタブ「種類」グループのグラフの種類の変更ボタンから「グラフの種類の変更」ダイアログを呼び出します。

質・量の別にグラフの種類を分ける

Step 4

量的データ,ここでいう「得点」および「前回得点」のグラフの種類をマーカー付き折れ線に変更します。

両者の線部位を不可視にします。

上の作業を終えた時点で,グラフの状態は下の通りです。

項目の別に系列を重ねる

Step 5

任意の系列を選択し……

系列が完全に重なり合うよう設定します。「要素の間隔」について,ここでは系列幅と同等の100%としましたが,好みにより調整を施します。

これまでの作業により,グラフは下のような状態となります。

実測値の表現

Step 6

系列の中央の線をエラーバーで描画します。

実測値,ここでいう「得点」系列を選択し……

デザインタブ「グラフのレイアウト」グループのグラフ要素を追加ボタンをクリックします。

つづけて,誤差範囲パーセンテージとたどっていきます。

Step 7

上で描画したエラーバーを選択します。

エラーバーの形態を目的にかなうようととのえ,

負方向・キャップなし・パーセンテージ100

加えて次のような設定を加えます。……線色・幅は任意で構いません。

実測値のマーカーはこの時点で不可視にしてOKです(ただし,目指すところの成果物によってはこれを残しておいたほうがいい場合もあります。これについては「拡張」にて触れていきます)。

比較値の表現

Step 8

グラフエリア,プロットエリア,あるいは軸・凡例などに関して,この時点で大枠の調整を終えておきます。

―――少し話が逸れますが,下図の最も左の柱に内包されたパーツは,名称通り射出された弾丸……を想起させるに十分です。

残る比較値,ここでいう「前回得点」を仕上げます。

グラフの枠外で図形機能を利用して任意の色・幅・長さの直線を描き,これをクリップボードにコピーします。

「前回得点」系列を選択し,

クリップボードの内容をペースト(Ctrl+V)します。

必要ならば,最後にひと手間

Step 9

凡例が不要な場合はそれを削除して完成です。

凡例が必要な場合……上図にて明らかなことに,「得点」と「前回得点」に限ってマーカーが見当たりません。マーカーを不可視にしたとき,あるいは図形を充てたときは凡例からも脱落する(あるいは無視されるほど小さくなる)ので,可能なら何とかしたい部分ではあります。

これについての最も明快な解決策は―――やはり図形機能を使って相応の場所に直接描き加えることでしょう。下の図が,まさにそれをおこなった結果です。

拡張

Step 10

ここに至るまでの手続きによるアウトプットで不都合を挙げるとして,グラフサイズの変更に脆弱な点があります。

たとえば,グラフのハンドルを右方に引っ張ってサイズを拡大しても,「前回得点」の線長は下のように追従しません。また凡例に至っては,描き入れた部分がズレてしまいます。

ときに前者は許容できたとしても,後者の状態を放置することは難しく,したがってサイズ変更の都度,当該箇所の修正に迫られることになります。

この仕様は,より簡便な工程たらんことを指向したゆえの帰結ですが,“使いまわし”をどうしても視野に入れていかなければならないときには,解決が望まれる部分です。

以下,さらなる手数を対価にこれらの解決を望む場合の要点だけを記します。

同じマーカーでも散布図のそれは性質上エラーバーを左右方向に表示することが可能です。この性質を利用して,「前回得点」をマーカーのみの散布図に替え,

それによって自動的にこの系列が2軸側に振られるので,左の軸との整合を保つため目盛りを一致させておきます。

不要な第2横軸をデリートすると,

グラフはこのようになります。

Step 11

エラーバーを加え,(横方向のエラーバーと)棒との兼ね合いをはかりながら(横方向のエラーバーの)「固定値」の値を調整します。

線色・幅を設定し,

残る縦方向のエラーバーを選択してデリートします。

グラフ部分はこれでサイズ変更にも耐えうる状態にはなったのですが,いかんせんこれまでと同様,残る「前回得点」のマーカーを「なし」にした瞬間に凡例のそれも文字だけを残して消滅します(下図)。

Step 12

幸いというべきか,これについてはhack的な対処で一応の解決が可能です。

その端緒として,「得点」「前回の得点」それぞれの系列の形状・色を象徴するマーカーを設定し,グラフの上に表示させた状態にしておきます(凡例にマーカーが再表示されたことを確認しておきます)。

“系列の”マーカーを1つずつ“要素ごとに”「なし」にしていくと,どうしたことか凡例上のマーカーが脱落することがありません。この性質を利用して,STAFF #1の「前回得点」のマーカーのみを選択マーカーなしSTAFF #2の……といった具合に設定をつづけます。

「得点」についても同じくhackで賄って,いくつかの不都合を解決したブレットグラフの完成です。

その他の参照