BDAstyle

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

範囲棒グラフの作成 with Excel

イントロダクション

Step 02つの描画法

Range Bar Graph,ここでいうところの範囲棒グラフは,定まった時間を切るなどして観測値のレンジ(あるいはその推移も含め)を可視化したい場合に便利なツールです。

さて,ここでの例示にあたっては次のデータを利用したいと思います。ある事象に影響を受ける,指標Aの観測値に関するMinMaxを,過去30日の期間にわたって記録したデータです。

DL

範囲棒グラフ・元データ

以下このページでは,2つの異なる“型”をとりあげます(type 1 ― type 2)。用途に照らして,適した方法を任意に選択してください。

  • type 1: 1群しか扱わない場合の範囲棒グラフ
  • type1:範囲棒グラフ(1群)

  • type 2: 2群(ときにそれ以上)を扱う場合の範囲棒グラフ
  • type2:範囲棒グラフ(2群)

工程

type1|1群のみの範囲棒グラフ

type1- Step 1前提

type 1 では折れ線グラフを素地とします。ただし,横軸だけは見栄え的な意味から数値軸ライクに扱いたいと思うので,下図,デフォルトの状態で設定される番でなく2番の形式を利用することを前提として話をすすめます。

かといって,グラフの縦軸と被るような位置からいきなり折れ線を描くのもスマートさに欠けるかと思うので,元データにも若干の余裕を持たせたいと思います。具体的には,下図のようにDay1の前にDay0を差し込みます。

type1- Step 2ソースの選択

見出し「Day」を削除(Delete)します。グラフを描く段階で,見出しが残ったままだと見出し以下が数値と認識されてしまいます。数値と認識されるとグラフの中に単独の系列として顔をのぞかせることになる(もちろん歓迎されない)ので,それを避けるためのhackです。

下図範囲を選択します。このとき,表の最終行にも1行の余裕を見て選択しておきます。これは先のStep 1と同様の理由からです。

type1- Step 3折れ線グラフの挿入

挿入タブ「グラフ」グループの折れ線/面グラフの挿入ボタンをクリックします。

階層メニューの「2D折れ線」より折れ線を選択します。

これにより,シートの上に下図のようなグラフが出力されます。

type1- Step 4ローソクの追加

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

階層メニューのローソクよりローソクを選択し,これをレンジバーとして利用します。

scrollable

type1- Step 5系列の不可視化と彩色

“折れ線” 部分を2本とも不可視(線の色を「なし」)にし,「ローソク」だけを残します。

「ローソク」は枠線・塗りとも独立して彩色等の設定が可能です。これを任意のものに設定します。

type1- Step 6X軸

Step 1 で前提としたとおり,ここで「軸位置」を目盛に変更します。また,その他目盛りに関する設定も加えます。

たとえばこの時点のグラフを見ると,横軸の「Day」が0から30まで間断なく振られています。これをいささか過剰なように思ったとして0から5日間隔で目盛りを振り直そうと考えた場合,下図のような設定で対処します。

この設定をおえた時点で,グラフは下図のとおりです。

type1- Step 7Y軸

縦軸「境界値」の最小値最大値を設定します。step 6 のグラフを見ると前者は 60,後者は180で十分かとも思われるので,ここではこの値を指定することにします。

この設定をおえた時点で,グラフは下図のとおりです。

type1- Step 8ローソクの幅

ローソクの幅は必要に応じて自由に変更することができます。この場合,具体的には折れ線のいずれかの系列(「最低」or「最高」)の「要素の間隔」を狭めることで可能です。

たとえば幅を広げた場合,下図のようなグラフになります。

type1- Step 9type 1・完成

その他の任意の書式設定を加え,範囲棒グラフの完成です。

範囲棒グラフ(1群)

type2|2群を扱う層別範囲棒グラフ

type2- Step 1元データと別表

この作例では下図のデータを使用します。

DL

type 2 では散布図を素地とします。ゆえにできるだけシンプルな手数でグラフを描くには,若干の下準備があったほうがbetterです。そこで,まずはその「下準備」用の見出しをシートの上に用意しておきます。

scrollable

type2- Step 2グラフ描画用にデータを最適化

type 2 を描くうえで中心となるのが,直線散布図を土台に2点間をむすぶ直線を描く作業です。type 2 では,この直線をレンジバーとして利用します。

余分な系列を消費せずにグラフを描くための工夫として,まずは元データを下図のような構成に整形する必要が生まれます。

1日分1ユニット(3x3表)

手作業では面倒なので,これを次式

G3 =IF(MOD(ROW(),3)<2, QUOTIENT(ROW(), 3), "")
H3 =IF(MOD(ROW(), 3)=0, OFFSET($B$2, QUOTIENT(ROW(), 3), 0), IF(MOD(ROW(), 3)=1, OFFSET($C$2, QUOTIENT(ROW(), 3), 0), ""))
I3 =IF(MOD(ROW(), 3)=0, OFFSET($D$2, QUOTIENT(ROW(), 3), 0), IF(MOD(ROW(), 3)=1, OFFSET($E$2, QUOTIENT(ROW(), 3), 0), ""))

で機械的に用意しすることにします。これらセルG3~I3の内容は,見出しが2行1ユニット3行×30日92行までコピーします。

scrollable

type2- Step 3長さ0の文字を飛ばす作業(1)

「データの整形(2)」表の見出し「Day」直下に,0 をふたつ用意しておきます。これを置くのは,type 1 と同様,グラフに見栄え的な意味での余裕を加えるためです。

「データの整形(1)」表の見出し直下より下図の範囲を選択し,この内容をクリップボードにコピーしておきます。

type2- Step 4長さ0の文字を飛ばす作業(2)

「データの整形(1)」表の“なにも入力されていない”セルには,実際には空白処理で返ってきた長さ0の文字が入っています。これを残しておくと意図した散布図が描けないので,これへの対処を加えていきます。

Windows 組み込みアプリの「メモ帳」などテキストエディタを立ち上げて,現在のクリップボードの内容をペーストします。

間髪を入れずテキストエディタの内容をすべてクリップボードにコピーし―――

この時点でのクリップボードの内容を,「データの整形(2)」表の「0」より下1行の空行を開けて(=1ユニットが3行なので,その整合をとるため)ペーストします。これで“空白のように見えていた”セルの内容を,文字どおりの空白に修正できます。

注:「メモ帳」も利用できない環境の場合,このstepの作業はエクセルの機能だけでも実現することは可能です(cf. 実際空白に見えますが空白になりません ――"エクセルの学校")。

type2- Step 5ソースを選択してグラフを描画

「データの整形(2)」表の左から3列を選択(下図参照)し,

これをソースに「散布図(直線)」を出力しておきます。

type2- Step 6系列の書式設定

群ごとにレンジバーの彩色を異なるものに変え,あわせて線幅,透明度などの設定を加えます。

レンジバーの両端の扱いを,すべての系列についてフラットに変更します。

type2- Step 7軸の書式設定

縦軸範囲を適切なものに明示的に変更し(この例では最小:60-最大:180),

横軸も同様に設定します。ただし表示の欠けを防ぐため,グラフの右境界は 1 つ分の余裕が必要です(この例では最小:0-最大:31)。

type2- Step 8type 2・完成

残る各所に任意に書式設定を加え,完成です。

なおこの場合,凡例は直接描き加えたほうが明瞭かと思います。

範囲棒グラフ(2群)

type2- Step 9拡張1

他方,横座標を共有する形式を避ける手もあります。この場合,元表の「Day」に微小値を加減算して別個の座標を作成し(下図),

グラフをアクティブにしたとき表示されるソースの範囲を,系列ごとにマウスで適宜指定しなおしてやれば下図のような形式に転換できます。

範囲棒グラフ(2群・異なるX座標)

type2- Step 10拡張2

さらに,横軸をカテゴリにしたい場合の対応例です。

具体的には,ラベルの表示を第3の系列を使っておこなうので,ラベルのY位置(つまり,縦軸の表示範囲の最小値)と「Day」と対応する文字列(=実際に表示させたいもの)をシート上に指定しておき(下図),

上図でいう「ラベルの位置」列の内容をクリップボードにコピーしたうえで,グラフに直接ペーストします。

これにより作成した第3の系列に対し,データラベルを表示させ,その内容を上図にいう「ラベルの内容」列の内容に書き換えれば,下図の赤字部分のように任意の文字列でグラフを描画することも可能となります。

範囲棒グラフ(2群・カテゴリ軸)

その他の参照