BDAstyle

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

ゲージグラフ(メーターグラフ)の作成 with Excel


イントロダクション


Step 03つの形式

ゲージグラフあるいはメーターグラフは,おおむね定まった範囲で観測可能な値をアナログゲージやアナログメータに模した盤面の上で表現するグラフです。

とりわけ,値そのものを表示したり帯グラフで明示したりするのはありきたりで平板すぎて,他者の関心や注意を惹くことができないと感じるとき…過剰な表現を使えば,ある瞬間を切り取ったに過ぎないデータを他者に印象づける必要のある場合には,インパクトの面では有効なこともありえます。

筆者の浅慮か,もしビジネスシーンにおける管理者として先のような考え方をバックグラウンドにおくならば,このグラフは達成率や進捗状況といったKPIまわりの行動評価と相性がいいはずです。実際のと…いや,とまれ知見に足らないこのサイトが用途を掘り下げても墓穴を掘るだけのような気もするので,ここでは単に集合 U における要素 A, A bar に関する A の比率のビジュアライゼーションを目的に据えて例示していきたいと思います。

さて,これに関して,3つの異なるタイプのグラフを定義します。具体的には,

です。type 1が最も手軽で,type 2,type 3はやや工程を要します。またいずれのタイプに関しても,

といった方針で縛りを与えています。

また,目的からは少し外れますが,ある意味ではより現実的な?形式であろうtype 4について最後で別途触れています。


type1. 半ドーナツ型ゲージ

type1- Step 1

集合Uに関する情報を構成します。Aに直接任意の比率(0≦rA≦1)を入力し,rA barrUを計算によって求めます。

B6 =1-B5
B7 =SUM(B5:B6)

type1- Step 2

次の領域を選択し…

「ドーナツ」グラフを挿入します。

rA=0.1のとき,出力されるグラフは以下の通りです。

type1- Step 3

グラフの系列(ドーナツ部分)を選択した状態から,「データ系列の書式設定」ウィンドウを呼び出しておきます。

デフォルトの状態では円の最上部から要素が順番に配置されます。目的とする水平の半円ゲージにおいてこれは不都合なので,最初の要素が配置される位置を90°反時計回りにずらします。具体的には,

「グラフの基線位置」を270°に指定すればこれが叶います。

type1- Step 4

この状態で,つづいては下半円が不要なことがわかります(データとしては必要です)。そこでこの要素のみ選択(系列が選択された状態からこの要素の部分のみ再度クリック)し

塗りつぶしを「なし」にします。

type1- Step 5

その他の任意の書式設定を加え,完成です。

なお,このグラフは仕組み上どうしてもグラフオブジェクトの下半分が空いてしまうので,エクセルシートの上での可視化法として利用する場合は,下の図のように空いた個所にデータラベルやタイトルをいれるなどして不均衡を和らげるとbetterかと思います。


type2. 自由な中心角のドーナツ+針式ゲージ

type2- Step 1

集合Uに関する情報を構成します。Aに直接任意の比率(0≦rA≦1)を入力し,rA barrUを計算によって求めます。

また,これとは別にデータラベル用の文字列も(使うか使わないかは任意ですが)用意しておくと,場合によっては便利かと思います。

B6 =1-B5
B7 =SUM(B5:B6)
B10 =TEXT(B5,"0%")

type2- Step 2

次のデータを入力ないし計算します。下表※印のあるセル以外は,運用時にはすべてユーザーによる任意の値を指定します。下表はこの例示での便宜的な設定値です。

なお具体的な意味づけは,

となっています。

D2 216
F2※ =180+(360-D2)/2
G2 0.4
H2 0.85

ユーザーのθ指定にともなって,適切なドーナツを描画できるようrを調整します。

C5 =B5*$D$2/360
C6 =B6*$D$2/360
C7 =1-B7*$D$2/360
D5 =(180-$D$2)/2+$D$2*B5

軸については固定座標とします。また針については,θ値のもとでユーザー指定の比率を正しく指示するために必要となる座標を求めます。

G5 =-($G$2*COS(RADIANS($D$5)))
H5 =$G$2*SIN(RADIANS($D$5))
G6 0
H6 0

type2- Step 3

下図の領域を選択し散布図を作成すると…

次の状態でグラフが出力されます。

グラフをアクティブにした状態で「データソースの選択」ダイアログを呼び出し,まずは「軸座標」系列を次のような対応で追加し,

つづいて「針座標」系列を次のような対応で追加します。

type2- Step 4

この時点でのグラフ(散布図)は下の通りです。これの任意の系列を選択した状態で

「グラフの種類の変更」ダイアログを呼び出し,系列「r'」をドーナツに,系列「針座標」を散布図(直線)に変更します。

出力されたグラフに対し,若干の調整を加えます。

グラフの系列「r'」(ドーナツ部分)を選択した状態から,「データ系列の書式設定」ウィンドウを呼び出し,「グラフの基線位置」をシートの最上部の「基線位置」に表示されている値(この例では252°)に設定します。

type1 step 4と同様にしてドーナツの余分な要素(上の図にいうグレーの部分)を不可視化(塗りつぶし「なし」)し,

横・縦軸ともに軸の最小値を-1,最大値を1に設定します。

type2- Step 5

各所に任意に書式設定を加えて完成です。step 1で作ったラベル表示用のデータを使って,針の先端などにデータラベルを加えてやると使い勝手がいいかと思います。

このグラフでは,比率rAはもちろんのこと,θおよび針の長さの変更も可能です。ただし,θを変更した場合は,ドーナツに関する書式設定「基線位置」を(step 4と同様にして)シート上の値と同期させる必要があります。


type3. 円弧に沿って目盛を振った針式ケージ

type3- Step 1

前提として,type2 の step 1-2を仕上げておきます。

このシートに,データがとる値の下限から上限までの任意の値を1セルおきに入力します。

ここでは比率を扱っているので,下限0上限100(比率×100)の範囲の,目盛として刻みたい任意の値を入力していきます。ゲージとして意味をなす場合には,等間隔でなくてもかまいません。

最初の目盛りのすぐ右のセルで,「基線位置」設定にいう270°位置を基点にした,目盛りを描画するために利用する角度を求めます。つづいてこれと直下の空白のセルの2つを選択して,フィルハンドルを引っ張って最後の目盛りを1つ超えるセルまでコピーしてしまいます。

G9 =(180-$D$2)/2+$D$2*F9/100

先の角度を使って,各目盛り線の座標を求めます。4セルにつきこれを求めたら,当該セル範囲(4つのセル)を選択して,先と同じようにフィルハンドルを引っ張って最後の目盛りを1つ超えるセルまでコピーします。

H9 =-(COS(RADIANS(G9)))
I9 =SIN(RADIANS(G9))
H10 =$H$2*H9
I10 =$H$2*I9

type3- Step 2

シート上の任意の空白のセルをアクティブにして,散布図を用意します。これにより“何もないカラのグラフ”が出来上がります。

グラフをアクティブにした状態で「データソースの選択」ダイアログを呼び出し,まずは「軸座標」系列を次のような対応で追加します。

つづいて「針座標」系列を次のような対応で追加します。

これにより,散布図は下図のように形を変えます。

type3- Step 3

先と同じようにして,今度は目盛りを作っていきます。最初の目盛りを追加するときの対応は,下図のようになります。

データとして用意したすべての目盛りを組み入れる場合は,すべての目盛りに関して上の操作を繰り返します。

それを終えて横・縦軸ともに軸の最小値を-1,最大値を1に設定したら,散布図は下図の状態となっているかと思います。

type3- Step 4

グラフ(散布図)の任意の系列を選択した状態で,「グラフの種類の変更」ダイアログを呼び出します。

系列「軸座標」以外のすべての系列を散布図(直線)に変更します。

type3- Step 5

各所に任意に書式設定を加えて完成です。ラベル表示用のデータを使って,針の先端や軸の付近,あるいは目盛り線の終点などにデータラベルを加えてやると判読に易いかもしれません。

Font: Squared Display by Vikas Kumar(目盛り部)
Font: DSEG by Keshikan(液晶部)

このtype3の場合でも,比率rAはもちろんのこと,θおよび針の長さに加え,「刻みの内限」によって目盛り線の長さを変更することが可能です。なおこちらはドーナツを使用していないので,θを変更した場合でも「基線位置」の同期作業は発生しません。


type4. 比率,ではなく実数で管理する

type4- Step 1

type2,type3では比率を扱ってきましたが,最後にここでは売上などの“ナマの値”を対象とした場合を考えてみたいと思います。たとえば月次目標下における販売実績の到達点を管理する場合には,金額をそのまま入力できた方がラクでしょうし,同時に,目標到達後に“陸上トラックでいうフィニッシュラインを超えた減速区間”のようなマージンがあった方が資料としては使い勝手がいい場合もあります。以下,この場合の手続きです。

なお技法およびシートの構成はtype2,type3でのそれらを踏襲していますので,ここではそのあたりの説明は省きます。必要があれば,type2,type3の一連の手順を参照ください。

…ということで,早速元データを用意します。ここにいう「到達点」は現時点での実績,「マージン」は余裕区間,「計測上限」はメーターの最大計測値でこれのみ計算により求めます。

B8 =B6+B7

type4- Step 2

「θ」「基線位置」「針の長さ」「刻みの内限」を入力ないし計算します。

D2 216
F2 =180+(360-D2)/2
G2 0.8
H2 0.5

ユーザーのθ指定にともなって,適切なドーナツを描画できるようr'をつくります。type4では,ドーナツは目標値までの範囲とマージンを表現するために利用します。すなわち,ここでのドーナツはそれらの値を変えない限り固定的な利用のされ方となります。

C5 =B5/B6
C6 =B6/B8*$D$2/360
C7 =B7/B8*$D$2/360
C8 =1-$D$2/360
D5 =(180-$D$2)/2+$D$2*B5/B8

軸座標,針座標をつくります。

G5 =-($G$2*COS(RADIANS($D$5)))
H5 =$G$2*SIN(RADIANS($D$5))
G6 0
H6 0

ドーナツにおける開始(=0%点)・目標・計測上限(=100%点)の3つの位置に線を引くための座標をつくります。まずは「刻み」の3個所を埋め(上と下は定数),

F9 0
F11 =B6/B8*100
F13 100

3つのパーセント点に関する座標を求めます(1ブロック仕上げたらそれを残り2ブロックにコピー)。

G9 =(180-$D$2)/2+$D$2*F9/100
H9 =-(COS(RADIANS(G9)))
I9 =SIN(RADIANS(G9))
H10 =$H$2*H9
I10 =$H$2*I9

type4- Step 3

以下の領域を選択し散布図を作成します。具体的な手順はtype2 step3-4と同様です。

これにより,グラフは次の状態となります。

type4- Step 4

type3 step3-4の手続きを適用します。これにより,ドーナツの3個所に境界が引かれます。

type4- Step 5

各所に任意に書式設定を加え,重ねて針の長さ等を必要に応じて調整すれば完成です。この場合も,重要なポイントにデータラベルなどを加えてやると使い勝手がよくなるかと思います。また,θを変更した場合は,ドーナツに関する書式設定「基線位置」をシート上の値と同期させる必要があります。

その他の参照