Excel 2007での箱ひげ図（はこひげず、箱髭図、ボックスプロット、box plot）の作成方法を紹介します。元ネタは、Box Plot for Excel 2007からとなります。
下記が完成した箱ひげ図（box plot）です。株価チャートを使って似たようなグラフを作成できますが、100万倍こちらの方がステキです。
|
| Box Plot（箱ひげ図） |
箱ひげ図（はこひげず、箱髭図、ボックスプロット、box plot）とは
箱ひげ図（box plot）は、データの分布を可視化するのに優れたグラフです。特に幾つかのデータの分布を比較するのに長けており、分布がどのように異なっているかを視覚的に捉えるのに便利です。Wikipediaで下記のように説明されています。
箱ひげ図（はこひげず、箱髭図、box plot）とは、ばらつきのあるデータをわかりやすく表現するための統計学的グラフである。細長い箱と、その両側に出たひげで表現されることからこの名がある。一般的には（ジョン・テューキーの方式）、重要な5種の要約統計量である、最小値、第1四分位点、中央値、第3四分位点と最大値を表現する。母集団は実際には様々なタイプの確率分布に従うわけだが、箱ひげ図はそのような仮定に関係なく、データの分布を表現することができる。箱の各部分の間隔から分散や歪度の程度、また外れ値（これは後述のように箱ひげ図の方式により異なる）を知ることもできる。
箱ひげ図（box plot）は、データを1/4（25%）ずつに分割して、最小値、第一四分位（25%点）、中央値（50%点）、第三四分位（75％）、最大値の５つの要素でデータの分布を可視化します。
 |
| データの分布を５つの要素で可視化 |
Excelで四分位を求める
Excelで四分位を求めるには、=percentile()、若しくは=quartile()で求めることができます。最大値、中央値、最小値は、それぞれ=max、=median()、=min()で求めることもできます。
| 項目名 | 備考 | PERCENTILE | QUARTILE |
| 最大値 | データの最大値 | PERCENTILE(データ範囲,1) | QUARTILE(データ範囲,4) |
| 第3四分位 | データの下から3/4に位置する値（75％点） | PERCENTILE(データ範囲,0.75) | QUARTILE(データ範囲,3) |
| 中央値 | データの中央値（50％点） | PERCENTILE(データ範囲,0.5) | QUARTILE(データ範囲,2) |
| 第1四分位 | データの下から4/1に位置する値（25％点） | PERCENTILE(データ範囲,0.25) | QUARTILE(データ範囲,1) |
| 最小値 | データの最小値 | PERCENTILE(データ範囲,0) | QUARTILE(データ範囲,0) |
Excel 2007で箱ひげ図（はこひげず、箱髭図、ボックスプロット、box plot）を描く
Box Plot for Excel 2007にあるサンプルデータを使用して説明しますが、今回作成したサンプルを下記に置いてありますのでご利用ください。
| A | B | C | D | E | F | G | |
| 1 | sample1 | sample2 | sample3 | sample4 | sample5 | sample6 | |
| 2 | 50.5 | 46.7 | 43.2 | 62.5 | 52.0 | 53.2 | |
| 3 | 51.3 | 45.5 | 45.3 | 64.2 | 52.3 | 58.6 | |
| 4 | 55.3 | 45.6 | 43.2 | 66.1 | 55.0 | 55.4 | |
| 5 | 50.3 | 46.3 | 43.5 | 66.7 | 54.3 | 53.5 | |
| 6 | 55.0 | 49.7 | 45.6 | 63.4 | 52.6 | 56.0 | |
| 7 | 59.6 | 49.8 | 43.1 | 67.7 | 53.9 | 57.6 | |
| 8 | 51.3 | 48.5 | 45.4 | 62.2 | 51.2 | 54.5 | |
| 9 | 56.1 | 48.7 | 46.0 | 68.4 | 52.1 | 58.7 | |
| 10 | 59.7 | 48.8 | 44.1 | 62.7 | 54.5 | 55.7 |
12-16行にそれぞれ最大値、第三四分位、中央値、第一四分位、最小値を求めます。
B12：B16に下の式を代入してC12:C16からG12:G16にコピーします。
=(B2:B10,4)
=(B2:B10,3)
=(B2:B10,2)
=(B2:B10,1)
=(B2:B10,0)
| A | B | C | D | E | F | G | |
| 12 | 最大値 | 59.7 | 49.8 | 46.0 | 68.4 | 55.0 | 58.7 |
| 13 | 第3四分位 | 56.1 | 48.8 | 45.4 | 66.7 | 54.3 | 57.6 |
| 14 | 中央値 | 55.0 | 48.5 | 44.1 | 64.2 | 52.6 | 55.7 |
| 15 | 第1四分位 | 51.3 | 46.3 | 43.2 | 62.7 | 52.1 | 54.5 |
| 16 | 最小値 | 50.3 | 45.5 | 43.1 | 62.2 | 51.2 | 53.2 |
次にグラフを描写するために必要な値を計算して18-22行に代入します。
B18：B22に下の式を代入してC12:C16からG12:G16にコピーします。
=B15-B16
=B15
=B14-B15
=B13-B14
=B12-B13
| A | B | C | D | E | F | G | |
| 18 | 第1四分位-最小値 | 1 | 0.8 | 0.1 | 0.5 | 0.9 | 1.3 |
| 19 | 第1四分位 | 51.3 | 46.3 | 43.2 | 62.7 | 52.1 | 54.5 |
| 20 | 中央値-第1四分位 | 3.7 | 2.2 | 0.9 | 1.5 | 0.5 | 1.2 |
| 21 | 第3四分位-中央値 | 1.1 | 0.3 | 1.3 | 2.5 | 1.7 | 1.9 |
| 22 | 最大値-第3四分位 | 3.6 | 1 | 0.6 | 1.7 | 0.7 | 1.1 |
積み上げ縦棒グラフで基本グラフの作成
A19:G21を選択して、2-D 縦棒から積み上げ縦棒を選択します（下記のイメージを参考）。
 |
| 積み上げ縦棒グラフの作成 |
作成したグラフは下から第1四分位、中央値-第1四分位、第3四分位で構成されています。このグラフをよくよく眺めると赤色と緑色の部分が箱ひげ図（Box Plot）の箱の部分になっていることが分かりますでしょうか。
 |
| 積み上げ縦棒グラフ |
箱ひげ図の箱を作成する
作成した積み上げ縦棒グラフの青色（第1四分位）の部分を「塗りつぶしなし」にして、箱ひげ図の箱の部分だけにします。
１）グラフの青色（第1四分位）部分をマウスで選択して、右クリックで「データ系列の書式設定」を選択
 |
| 第1四分位を選択 |
２）「データ系列の書式設定」→「塗りつぶし」で「塗りつぶしなし(N)」を選択
 |
| 第1四分位を塗りつぶしなしに設定 |
これで、箱ひげ図の箱の部分が作成できました。
 |
| 箱ひげ図（box plot）の箱部分 |
箱ひげ図（box plot）の髭（ひげ）を作成する
箱ひげ図（box plot）の髭（ひげ）の部分は、誤差範囲で作成します。
１）グラフエリアを選択してツールバーのレイアウトから誤差範囲の「その他の誤差範囲オプション(M)」を選択
 |
| 誤差範囲の指定 |
２）誤差範囲の追加から「第1四分位」を選択
３）箱ひげ図の下側の髭（ひげ）を作成するため縦軸誤差範囲の表示は「負の方向」「キャップあり」を選択し、誤差範囲はユーザ設定を選択。値の指定で「負の誤差の値(N)」に第1四分位-最小値（B18:G18）を範囲選択して値を入力
 |
| 箱ひげ図（box plot）の下髭を作成 |
これで下側の髭（ひげ）が完成です。
 |
| 箱ひげ図（box plot）の下髭 |
続いて上側の髭（ひげ）を作成します。
４）グラフエリアを選択してツールバーのレイアウトから誤差範囲の「その他の誤差範囲オプション(M)」を選択
 |
| 誤差範囲の指定 |
５）誤差範囲の追加から「第3四分位-中央値」を選択
６）箱ひげ図の上側の髭（ひげ）を作成するため、縦軸誤差範囲の表示は「正の方向」「キャップあり」を選択し、誤差範囲はユーザ設定を選択。値の指定で「正の誤差の値(N)」に最大値-第3四分位（B22:G22）を範囲選択して値を入力
 |
| 箱ひげ図（box plot）の上髭を作成 |
これで髭（ひげ）の部分も作成完了です。
 |
| 箱ひげ図（box plot）の髭（ひげ） |
箱ひげ図（box plot）の見栄えを調整する
箱ひげ図（box plot）はこれで一応完成となります。後は箱ひげ図（box plot）の見栄え（ボックスの縦幅と横幅、色など）を調整します。
ボックス縦幅の調整
１）グラフの縦軸を選んで右クリックから「軸の書式設定(F)」を選択
 |
| 軸の書式設定 |
２）軸のオプションで最小値と最大値を自動から固定に変更して値を入力します。今回のサンプルでは、最小値を40、最大値を70としています
 |
| 軸のオプション |
下記が縦幅を調整した箱ひげ図になります。
 |
| 縦に広がった箱ひげ図（box plot） |
ボックス横幅の調整
次に横幅を調整します。
１）グラフの赤色（中央値-第1四分位）を選択して右クリックで「データ系列の書式設定(F)」を選択
 |
| データ系列の書式設定 |
２）系列のオプションで「要素の間隔(W)」で箱の横幅を調整します。値が小さくなるほど、箱の横幅は広がります。サンプルでは50％としています
横幅を調整したグラフが下記になります。
 |
| 箱の横幅を調整した箱ひげ図（box plot） |
グラフの色の削除
グラフの色を削除する場合に枠線をなしのままにしていると箱が表示されなくなってしまいますので注意してください。
１）グラフの赤色（中央値-第1四分位）部分を選択して右クリックで「データ系列の書式設定(F)」を選択
２）枠線の色で「線（単色）(S)」で枠線の色を指定
３）塗りつぶしで「塗りつぶしなし(N)」を選択
これで箱ひげ図の箱の下側が色がなくなり、枠線だけになりました。
同様にグラフの緑色（第3四分位-中央値）部分についても枠線だけにします。
箱ひげ図の完成
凡例を削除すれば、箱ひげ図の完成です。好みで箱に色を付けたり、目盛り線を調整してください。
 |
| 完成した箱ひげ図 |
今日の一曲
Yellow Magic Orchestra のアルバムで唯一保有していなかったのが、『浮気なぼくら(インストゥルメンタル)』でした。iTunesでYMOのアルバムが公開され、実にリリースから28年経って、2枚組の『浮気なぼくら&インストゥルメンタル』を購入しました。アルバムを聴いてみると、後期の『BGM』、『テクノデリック』で完成されたYMOがしっかりと組み込まれていました。そして細野さんのリズムと歌詞は、いつの時代もステキです。今日の一曲は、ユキヒロと細野さんのハーモニーが至極刺激的な『FOCUS』です。
便利な手法を公開していただきありがとうございます。愛用していたのですが、第1四分位が負の値であるようなデータの場合、正しく処理できないことに気が付きました。
返信削除自分でも考えてみたのですが、どうもいい方法が浮かびません。何かご存知でしたら教えてください。