BDAstyle

Business Data Analysis & Visualization with Excel

ヒストグラムの作図Tips 6/7[正規分布曲線, 箱ひげ図, 垂直線をすべて描きこむ]

正規分布曲線だけでなく,垂線と箱ひげもいっしょに描画したい

イントロダクション

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

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

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

エクセルでヒストグラム・正規分布曲線・垂直線・外れ値付き箱ひげ図を同時に描画する

工程

表部分

Step 1シートの作成

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

  • [Data]元データの入力部
  • [Hist]度数の計算部
  • [NC]期待度数の計算部
  • [BP]五数要約他の計算部
  • [VL]平均・標準偏差・規格値等の計算部

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

  • [Hist]ヒストグラム
  • [NC]正規分布曲線
  • [BP]箱ひげ図
  • [VL]垂直線

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

5つのシート:Data, Hist, NC, BP, VL

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

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

A1よりローデータ

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

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

[Hist]見出しの作成

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

[Hist]度数のカウント

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

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

[NC]確率密度関数:正規分布曲線の座標の作成

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

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

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

scrollable

[BP]見出しの作成

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

[BP]見出し部位と箱ひげパーツの対応

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

scrollable

[BP]Q1-Q3,±1.5IQRの計算

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

[BP]U_lim~[BP]L_limの5つの項目が分かればグラフ上のX, Y座標を作成することができます。

[BP]箱ひげパーツの座標の作成

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

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

scrollable

[BP]外れ値パーツの座標の作成

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

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

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

[VL]垂直線の座標の作成

グラフ部分

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

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

正規分布曲線+ヒストグラムまでの具体的な要点は,「Excelによるヒストグラム・作図のためのTips|正規分布曲線を重ねる」頁のtype 2を参照ください。

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

【エクセル】ヒストグラムと正規分布曲線

Step 7垂直線の重ね

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

「データソースの選択」ダイアログを呼び出して,「X」列をX座標,「Y」列をY座標とする任意の名前のあたらしい系列(この例では[VL]とします)を追加します。

(参考:追加後の「データソースの選択」ダイアログの状態)

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

【エクセル】ヒストグラムと正規分布曲線と垂直線

Step 8箱ひげ図の重ね

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

「データソースの選択」ダイアログを呼び出して,先と同様「X」列をX座標,「Y」列をY座標とする任意の名前のあたらしい系列(この例では[BP1]とします)を追加します。

残りの外れ値2系列も同様にして組み入れます(この例では順に[BP2][BP3]とします)。

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

【エクセル】ヒストグラムと正規分布曲線と垂直線と外れ値付きの箱ひげ図

Step 9書式の設定・完成

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

scrollable

ヒストグラムと正規分布曲線と垂直線と外れ値付きの箱ひげ図 完成

拡張, Tips

Step 10その他の加工

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

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

scrollable

エクセルでヒストグラム・正規分布曲線・垂直線・外れ値付き箱ひげ図を同時に描画する

Next

次は,“エクセル感”を除去し“R感”を醸し出すためのテクニックです。

その他の参照