BDAstyle

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

ヒストグラムの作成 with Excel Tips6 [Normal Curve, Box Plot, 垂直線をひとつのグラフに描き入れる]

1.イントロダクション

Step 0シチュエーションの設定

ここでは,ヒストグラムに関するTips1, 2, 3のコンテンツ,および「箱ひげ図」コンテンツにおけるいくつかの処理法を組み合わせ,3つの要素――正規分布曲線・箱ひげ図・垂直線を「ヒストグラム」に合成してみたいと思います。

ただし単回の効率を至上とするタスク下ならば,これは「他のツールでやれ」案件なのかもしれません。その点を鑑みれば,(ここでは特に意識はしていきませんが)本来は繰り返しのデータ処理にも耐えうるシートの構成を考えていくべきテーマかな,とも思います。

ヒストグラム+正規分布曲線・箱ひげ図・垂直線


2.手順|表部分

Step 1シートの作成

シートを5枚用意します。意味付けは,順に

としています。グラフの要素の上では

を描くために利用します。

ここでは,標準正規乱数を元データに据えて例示を進めたいと思います(N=10000, ただし1/1000位までを有効とする)。

元データは,シート「Data」のA列に用意します。

Step 2シート「Hist」:ヒストグラム

シート「Hist」でN, Min, Max, μ(x bar), σ(s), 階級幅hを入力or求めます。なおこの例示でのhScottによるアルゴリズムを利用しています。

階級の下上限,および度数を求めます。この例示では上限を含むFrequency関数で求めています。

Step 3シート「NC」:正規分布曲線

シート「NC」で,ヒストグラムの第1階級下限から最後の階級の上限までをカバーするレンジで確率密度(「Density」)を求め,それをもとに期待度数(「[NC]Ex」)を計算します。これについての仔細が必要な場合は,「ヒストグラムの作成 with Excel Tips3[正規分布曲線を重ねて描画する]」頁のMethod 2を参照ください。

Step 4シート「BP」:箱ひげ図

シート「BP」に次の見出しおよびデータを用意します。

ここにいう「上辺の度数換算値」は,箱部の上辺を描く位置を,ヒストグラムにおける度数(グラフでいえば第1縦軸の目盛り)を基準に指定するものです。「箱の高さ」も同様に度数換算値です。

scrollable

A列に連なる各項目は,下の箱ひげ図にいういくつかの強調個所を描画するために利用します。

具体的には,Q1, Q2, Q3を求め(この例示ではQuartile.Exeを使用しています),これらをもとに右方でQ3+1.5IQR点(内壁。以下略)およびQ1-1.5IQR点を求めます。そしてQ3+1.5IQR, Q1-1.5IQRをもとにして,元データに外れ値がいくつ存在するか,上下方の別にCountif関数で調べています(「度数」)。

scrollable

先の処理を受け,大きな順に度数+1番目の観測値,つまりQ3+1.5IQR点直下の観測値がわかります([BP]U_lim;Large関数によって)。Q1+1.5IQR点直上の観測値も同様の考え方で確認することができます([BP]L_lim;small関数によって)。

[BP]U_lim~[BP]L_lim5つの項目が分かればグラフ上のX, Y座標を作成することができます。なおここでY座標に一部空欄があるのは,同値の他のセルの値を使いまわせばいいかな…といった趣旨によるものです。

方向の別に,それぞれの外れ値を「度数」個分元データより抽出します。

具体的には度数の分だけ連番を振り,これを引数にして上方向はLarge,下方向はSamll関数で値をそのまま各個抜き出し,これをX座標としています。Y座標についてはすべて同値とします(「上辺の度数換算値」-「箱の高さ」/2)。

scrollable

Step 5シート「VL」:垂直線

シート「VL」に垂直線用の座標を設定します。ここでは平均と平均±σ3つの位置を示すために利用するものとします(X座標)。

また重ねて,これらの線を「箱ひげに重ねて上下目いっぱいに描きたい」という設定を設けます。このため,シート「Hist」の最大度数と箱ひげ図の箱の高さ,加えてヒストグラムとの若干のマージンを考慮したグラフの第1縦軸の上限を下図「第1縦軸Max」のように見積もり,これをY座標の始点として据えています。

3.手順|グラフ部分

Step 6正規分布曲線の描画→ヒストグラムの重ね

シート「NC」のデータを起点に,作図のプロセスを開始します。

正規分布曲線+ヒストグラムまでの具体的な要点は,「ヒストグラムの作成 with Excel Tips3[正規分布曲線を重ねて描画する]」頁のMethod 2を参照ください。

先のリンク先の手続きを終えると,下図のような状態のグラフが出来上がります。

Step 7垂直線の重ね

シート「VL」から垂直線を組み入れます。

「データソースの選択」ダイアログを呼び出して,必要な数の系列(ここでは3つ)を,X, Y座標の対応に注意しながら次(下図下段)のように順に追加していきます。

先の手続きを終えると,下図のような状態に遷移します。

Step 8箱ひげ図の重ね

シート「BP」から箱ひげ図を組み入れます。

「データソースの選択」ダイアログを呼び出して,9つの系列をX, Y座標の対応に注意しながら次(下図下段)のように順に追加していきます。

残りの外れ値2系列も同様にして組み入れます。

両手続きを終え,外れ値2系列のグラフの種類を「散布図」(注:マーカーのみ)に変更すると,グラフは下図のような状態となります。

Step 9書式の設定

必要に応じて軸位置等を調整して,最初に目的としたグラフの完成です。

scrollable

4.拡張, Tips

Step 10その他の加工(1)

下図では箱ひげ図や垂線にデータラベルを付加しています。

また,水平線に関するシートを加え必要なデータを用意してやることで,下図のように箱部の彩色(線幅の太い線)や任意の度数目盛り線の不可視化(白色線を重ねる)が可能です。

scrollable

Step 11その他の加工(2)

少し話が逸れますが,上のグラフしかり,縦軸と横軸の始点が重なる棒グラフまたは散布図のような他のグラフを “間借り” して描かざるを得ないといった(エクセルにおける)ヒストグラム作図のための根底にある要素は,筆者の個人的な感覚としては,いくつかの「痒いところに手が届かない」部分を生み出す主要因となっているように感じられるところです。

とりわけここでの作図にあたっては “階級幅アルゴリズムによる戻り値を調整しないこと”,“平均線を柱の間に配置できるような第1階級の下限を設定すること” を優先としています。そうした場合にトレードオフとしてあきらめざるを得ない要素を内包してしまうのが惜しまれるところで,この例に即して言えば,とにもかくにも半端な目盛りの読みづらさや,軸の始点まわりの要素の判別の難さにて顕著かと思います。

こうした要素の並立を矛盾なく実現するツールが個人的には好きなのですが,エクセルにおいて所与の設定を崩せないとする状況下なら,前者は整合を保った状態でラベルの開始値を任意に設定することが仕様として制限されるため,いわゆるバッドノウハウと呼ばれる変則的なアイデアに依らなければ解決は困難なように思います。ただ後者については,横軸をグラフ要素から多少の手順で距離をとってやることによっていくばくかは軽減できるかもしれません。

下図は,直前の過程からかかる処置をさらに加えてみたものです。参考までにこの場合の手続きの要点を示しておくと,第2横軸を追加した上で第2縦軸が(第2横軸と)最小値で交わるように交点の設定を変更し,その上で第2横軸に関するいくつかの書式の設定を重ねることで可能となります(cf.「Excelのヒストグラムの外形をRのそれに似せるためのあれこれCase1)。

scrollable

その他の参照