BDAstyle

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

水平方向のブレットグラフによる目標と実績のビジュアライゼーション with Excel

イントロダクション

Step 0

次のような特徴をもつ,ある会社の営業部門を想定します。

  • 子部門として,1課3課が存在
  • 10名の人員により構成

この部門では,月も下旬に差し掛かる頃になると,部門の長の要請を受けた部付きの事務かかりAさんによって,当該時点での営業実績がまとめらています。

これまでAさんは,下表(※)

DL

のような序列をもつ,目標と実績(単位:円)を記した表組みに達成率(実績に対する目標の比)を加えて(ペーパーで)報告を上げていました(※念のため,これはまったくの架空のデータです。個人名はWebサービス「すごい名前生成器」にて作成したランダムな組み合わせであって,実在する同姓同名の個人とは何ら関係のないものです)。

あるときこの部門の長は,業務の都合から従来のペーパーとは別に,序列を保ったまま“グラフ”による報告も寄こしてほしい旨をAさんに伝えます。

とはいえどんな情報がグラフとして欲しいのか,多忙な部門長はいちいち具体的な構想を得ているわけではないようです。当然,Aさんは困惑しますがどうしようもありません。そこで,Aさんはいくつかの思案を試行してみることにしました。

最初に思いついたのが,次の図です。

やはり達成率はインセンティブに大きく関わってくる指標なので,Aさんは部門長の最大の関心もここにあるかと考えました。したがって,各人に関して円グラフで達成率を表現してみようと思い至ったところです。

…が,最初の4,5人分ほどこれを用意したところで,何だかこの形態は,指し示すところを閲覧者に嚙み砕いてもらうのにしばしの間を必要とする……そんな性格の図となるようにも思われました。最初に期待したほど,これに今回の用途での適合を求めることは難しそうです。

「始点と終点が一緒(0%100%が同じ場所)だから読みづらいのでは」―――こう仮説を作ったAさんは,その解決策としてゲージグラフに頼ることを思いつきます。

日常で目に触れることの多い,多くの人にとって何らかの形で身近なアナログゲージを模しただけに,確かにパッと見のわかりやすさは段違いと言っていいかと思います。

…しかしエクセルでこれを複数個用意するのは作業過多で現実的な選択肢とはなりえません。重ねて円グラフでも同様ですが,すべてのデータを表示するためのスペースが,その内容とは不釣り合いなほど過剰に要求されてしまいます。仮にA4のペーパーに出力可能な体裁ですべての情報を突っ込むとしても,おそらく明視性が犠牲になってしまいます。

A4一枚で済むレイアウトは維持しなければ」―――次にこう考えたAさんは,その解決策として帯グラフ(エクセルの機能にいう「100%積み上げ横棒」)に頼ろうと考えました。確かにこれなら,同じ情報を表すにしろ要求されるスペースを減らせるはずです。

「悪くないかも」―――Aさんは結果にポジティブな感想を抱きます。

これでいこうかと傾きつつあったとき,あらためて図を見返しながら,ぼんやりと思うところがありました。

「いくらか消極的すぎる選択じゃ?」

Aさんは,この形態をもってしても

  • 達成率
  • 達成したか否か

しかわからないこと,つまり,情報量が少し貧弱であるところをあらたに気にしはじめました。

自身が情報を受け取る立場だったら……を顧みたAさんは,“率”への執着の是非を考えます。

Aさんは異なる長さの2つの横棒をてきとうな紙に描き,長いほうを「目標」,短いほうを「実績」とみなしました。このとき,「目標」を基準にしたアバウトなところでの「実績」の割合を目視によって読み取る作業が,閲覧者に対して過度な負担を強いるものともならないことを確認すると,“率”から“実数”への転換に意思を傾けていきました。

―――実数ベースで下図のように作りなおしてみたところ,なかなか悪くないようです。これならば目標値を超えてからもどの程度伸びたかの追跡が可能です。

―――ところがどうして,Aさんは幾度かこれを眺めるにつれ,色の異なる棒を間断なく並べていくことが,自分が思う以上に閲覧者に窮屈な印象を与えるんじゃないかといった懸念を抱きます。

「何も同じ記号で表現しなきゃならない理由もないな」―――Aさんは“目標”を線グラフに置換することを思いつきます。

垂直方向の線グラフをエクセルで作るのはひと工夫必要ですが,Aさんは何とかしてこれを下のように成し遂げました。

「縦方向はカテゴリ(個人)だから,それらをつなぐラインに意味はない,か」―――要らぬ誤読を防ぐ意味で,Aさんは当該系列より線を削除します。

そんなことで,当初とくらべかなりスッキリとしたグラフができあがりました。Aさんはこの結果にほぼ満足を得ましたが,自身が情報を受け取る立場だったら……をあらためて顧みると,加えて次のようなことが気になるだろうと見積もりました。おそらく部門の長が関心を寄せるのは,

  1. 誰誰が達成できそうかどうか
  2. 達成したか否か

であって,現状ではこのあたりの判別には弱点を抱えています。先に見た通りだいたいの達成率は点・棒の突端それぞれの原点からの距離を比較すればわかるところですが,つまるところはっきりとした線引きがあったとしたなら1.についての判断はさらに容易となります。

2.については棒の先端と点が近接するような場合だと,あらためて(数字記載の)別紙での確認が必要となってしまいます。閲覧者からしたら,この作業は面倒です。

そこでAさんはそれぞれに次善の策を用意しました。まずは上述の2.について,達成した場合はを別途白抜きで表現することにします。

1.については棒をまたぐラインを引くことで対応します。「ゴールが見えてきた」―――Aさんは,自身であればそう感じ始めるだろうラインが,目標の80%くらいじゃないかと考えます。そこでAさんは,プリントしたグラフの上にひとまずの80%ラインを下図のようにペンで描きこんでみました。

「ゴタゴタ見にくい…」―――Aさんは自身の案に失望します。


―――結局,このAさんの失望を最後に解決したのは,「ブレットグラフ」と呼ばれる下の表現となりました。

帯の中央の黒い横線が実績,白または赤の縦線が目標です。

進行状況のいくつかの区分となる階調によって帯を作り,階調の境界で「80%ライン」をあわせて表現しています。また到達に至った目標値については,目立たせるため帯を分断するかたちでの表現としています。

―――以下,上図のような水平方向のブレットグラフを作成するための手続きです。

工程

帯の構造と元表の加工

Step 1

直上のグラフから,一人分の帯部分のみ抜粋します。

これを作るに,色の異なる3本の棒を重ねる方法が最も単純で簡単です。

この棒は淡いほうから順に,目標以降のゾーン,目標まで残り20%のラインを超えたゾーン,およびそれ以外のゾーンを明示するために使います。それぞれに名前があったほうが管理が容易なので,ここでは順に「到達」「接近」「進行」の棒と呼びたいと思います。

「到達」棒が目標以降のゾーンを示すので,これと「接近」棒との境界が目標と同義となります。

それぞれを棒グラフに置き換えて考えると,これを描くためには,各棒の上限を指定してやればよいことがわかります。

というわけで,元表の見出し「目標」を「接近」に書き換え,その両端に「到達」と「進行」の両列をさしこみました。

ここで「到達」列は個々の実績をパッと眺めて1.5Mあたりで決め,「進行」については「接近」との境界なので個々の目標の80%となる値を据えています。

Step 2

「達成」「未達」の列を加え,これに次の式を入れて表の最下行までコピーします。

これはstep 0の最後で示した図にいうところの,赤線と白線の区別を作るための処置です。

G2 =IF(F2>=D2, D2, NA())
H2 =IF(F2<D2, D2, NA())

Step 3

「Dummy」列を加え下式を入力します。入力後,表の最下行までコピーします。

これは帯を除くパーツをエクセルの機能にいう散布図で代用するために求められるY値で,表における下方の人より0.5刻みでこれを拵えると,棒との兼ね合いのもと,グラフにきれいにハマります。下式はこの値を作るための式ですが,シートの使いまわしを考えなければ連続データで用意しても構いません。

I2 =(COUNTA(B:B)-0.5)-COUNTA($B$1:B1)

グラフを作るために必要なシートの完成です。

横棒グラフの作成

Step 4

下の領域を選択し集合横棒グラフを作成します。

デフォルトの状態での軸の並びが希望したものと異なっているので,これを反転させます(下図は処理前の状態)。

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

Step 5

任意の系列を選択し……

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

つづけて少なくとも「到達」「接近」「進行」の3つの棒に対し階調をつくります。細かい調整は後々にでも時間をとっておこなえばよいようには思うので,ここでは機能として用意されたパレットからすべての系列に対して彩色を一度に済ませています。

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

散布図の組み込み

Step 6

「実績」「達成」「未達」の系列をエラーバーで描画します。上下および左方向にバーの表示が必要なことを考えると,素地には散布図が必要です。

すでにグラフに組み込まれている「実績」系列を散布図に替え……

この系列のデータ範囲をXYについて下図のように修正します。

上の作業をおえると,グラフは下のような状態となります。この時点ではマーカーの位置と帯とがズレているので,第2軸の下限を0,上限をn(グラフに登場するメンバーの数;この例では30)として合わせてやります。

Step 7

未だ組み込んでいない「達成」系列をあらたに追加します。XYについては下図のとおりです。

同様に,未だ組み込んでいない「未達」系列をあらたに追加します。XYについては下図のとおりです。

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

エラーバーの利用

Step 8

「実績」「達成」「未達」の3つの系列それぞれに,エラーバーを(「パーセンテージ」で)描画します。

上の作業をおえると,グラフは下のような状態となります。

Step 9

この時点での系列の存在は下図の通りです。

図の強調部分,すなわち「実績」の縦方向のエラーバーと,「達成」「未達」の横方向のそれはこの用途において不要ですので,各個選択して削除(DELETE)しておきます。

上の作業をおえると,グラフは下のような状態となります。

Step 10

「実績」のエラーバーから調整を加えます。

下図のように指定して負方向へ100%の長さを持つ終端を描かないエラーバーに変更します。

つづいて「達成」「未達」のエラーバーの調整を加えます。

こちらは下図のような構成をベースとして,誤差範囲のみ固定値にして,いくらかの値をテストしてみながらグラフ上での適合を探っていきます。下図の値は「達成」のもので,帯を切る白線を描くため,若干長めに設定しています。

3つの系列それぞれのマーカーを不可視にします。

…結果,グラフはこのようになります。

書式設定

Step 11

「実績」「達成」「未達」の3つの系列に加えたエラーバーのそれぞれに対して,線幅・彩色など任意の書式設定を加えてやります。

Step 12

完成が見えてきたところで,第2縦軸のラベルを非表示にしてしまいます。

その他配色のバランスなど必要に応じた調整を加えて,ブレットグラフによる目標と実績の描写を完了とします。

その他の参照