BDAstyle

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

箱ひげ図の作成 with Excel ― ヒンジver., 負数・外れ値対応

データのコピペ → 五数要約の計算と箱ひげ図の描画をおこなうテンプレートを使った方法

1.イントロダクション

テンプレートを作成して,箱ひげ図を描画します(下図作例, 表現細部は任意に調整可能)。なおここでは,グラフの箱部とひげ部の境界に関して,ExcelQuartile関数の戻り値(引数1および3ではなく「ヒンジ」を用いるものとしますTukeyの方法, 「R」のboxplot関数デフォルトの設定で描かれる箱ひげ図)。

ブックにおいて,3枚のシートを利用します。ひとつは「INPUT」シート(データ入力部),ひとつは「PREPRO」シート(前処理部)いまひとつは「OUTPUT」シートです(計算・グラフ出力部)。

以下,このテンプレートに関する利点, 欠点, 特記事項および免責事項です。


作成にあたり参考にしたWebサイト・書籍およびPDFファイル

2.工程

1.“グラフ描画に必要な値” を計算するしくみの作成

Step 13つのシートの用意

「INPUT」(入力),「PREPRO」(前処理),「OUTPUT」(計算・出力)の各シートを用意します。これと異なる名前を使う場合は,以降の式中でデフォルトのシート名から都度変更する必要があります。

Step 2サンプルデータの用意|「INPUT」シート

「INPUT」シートが空の状態からテンプレートをつくるのは時々の状態をつかみにくいと思いますので,ここではダミーのデータ※を用います。

下のDLボタンから別ウインドウをひらき,データをすべて選択(Ctrl+A)・コピー(Ctrl+C)して「INPUT」シートのセルA1に貼り付けます。

※ 同一内容 同サイズの3変数のデータです。

箱ひげ図・サンプルデータ

Step 3転記・ソート・外れ値フラグ(1)|「PREPRO」シート

「PREPRO」シートのA1~B1に,それぞれ次の式を入力(or コピペ。以下に同じ)します。

A1 =IF(OFFSET(INPUT!$A$1, ROW(A1), COLUMN(INDIRECT(OUTPUT!D$2 & OUTPUT!A$2))-1)="", "", OFFSET(INPUT!$A$1, ROW(A1), COLUMN(INDIRECT(OUTPUT!D$2 & OUTPUT!A$2))-1))
B1 =IFERROR(SMALL(A:A, ROW(A1)), "")
C1 =IFERROR(IF(A1="", "", IF(A1>OUTPUT!D$12, ROW(A1), "")), "")
D1 =IFERROR(IF(A1="", "", IF(A1<OUTPUT!D$13, ROW(A1), "")), "")

入力後,A1でエラーが出ます。すべての工程が完了しブックの参照構造が固まるまでエラーは消えません。

ことばによる式の説明は以下のとおりです。

A1 「INPUT」シートの第1変数を見出しを除いてそのまま転記
B1 転記した値を昇順で並べ替え
C1 A列項目から外れ値(上側)の有無を調べる:それがあった場合, 行番号を記録
D1 A列項目から外れ値(下側)の有無を調べる:それがあった場合, 行番号を記録

Step 4転記・ソート・外れ値フラグ(2)|「PREPRO」シート

E1,F1のセルに対し,「使用しない」という視認上の意味づけをおこなっておきます(斜線をひく・濃色で塗りつぶす など)。これは「OUTPUT」シートで1変数に関する計算をおこなうユニットが6列を占有することから,セル参照の上での整合をつくるためです。

つづいてA1~F1までを選択し…

フィルハンドルをひっぱって,運用上想定する最大サイズ(どの程度までのサイズの変数を扱うか)に等しい行までセル範囲の内容をコピーします※。

※ ここでは便宜上100行目までつくるものとします。

コピーをおえたら,そのまま(選択を解除せず)フィルハンドルをひっぱって,同じく想定する変数の最大個分このユニットを右方にコピーします。

なお具体的にどの列までコピーするかは,

下の式の「変数の数」を変えて求めることもできます。

ここでは便宜的に5つのユニットを用意するものとします。この場合Address(1, 5*6)を求めると“$AD$1”が返るので,AD列までコピーすればよいことがわかります。

=address(1,5*6)

Step 5表組みの作成|「OUTPUT」シート

「OUTPUT」シートのセルA1から下図のような表組みを作成します。

数値以外の見出し部分は,のちに作成するグラフの系列名(A11[ひげ], A15[箱], A19[中央値], A22[外れ値])としての役割をのぞき相対的には重要ではありませんので,任意のものに変更してOKです(ただしのちにユニットをコピーする以上,自動的に区別可能な一意の見出しとなるような工夫は必要ですが)。

なお項目が決して少なくはないので,図の下の表をすべてコピーし,セルA1に貼り付けてしまった方が簡単かもしれません。

no. caption ps.col.num. i.col.ltr. ps.col.ltr.
1 2
="5NUM.S" n odd? address value
最大
上ヒンジ
中央
下ヒンジ
最小
="WHISKER[" & A2 & "]" value no. ="1.5iqr" outlier.cnt.
Upper
Lower
="IQR[" & A2 & "]" value no.
Upper
Lower
="MEDIAN[" & A2 & "]" value no.
Median
="OUTLIER[" & A2 & "]" no.

Step 6変数名・参照列(文字)の設定|「OUTPUT」シート

セルD2,E2,B2の順に次の式を入力します。変数名以外はこれ以降のブロックで使用する参照先アドレスを作成するためのパーツで,D2が「INPUT」シートの1番目の変数が格納されている列を,E2が「PREPRO」シートの1番目の変数のソート列を返します。

D2 =LEFT(ADDRESS(1, A2, 2), FIND("$", ADDRESS(1, A2, 2))-1)
E2 =LEFT(ADDRESS(1, C2, 2), FIND("$", ADDRESS(1, C2, 2))-1)
B2 =IF(INDIRECT("INPUT!" & D2 & 1)="", "", INDIRECT("INPUT!" & D2 & 1))

Step 7サイズのカウント|「OUTPUT」シート

変数1のサイズ(以下n)を「PREPRO」シートのA列をカウントして調べます。

B5 =COUNT(PREPRO!A:A)
B6 =B$5

Step 8偶・奇数個の判断|「OUTPUT」シート

nが奇数か調べます(分析ツールアドインがインストールされている場合,セルC5MOD(B5, 2)=0の部分はIsodd(B5)=falseでも)。

C5 =IF(MOD(B5, 2)=0, FALSE, TRUE)
C6 =C$5

Step 9中央値をとるセル範囲を作成|「OUTPUT」シート

セルD5~D9に次の式を入力します。

D5 ="PREPRO!" & E2 & B5
D6 =IF(C6=FALSE, "PREPRO!" & E2 & B6/2+1 & ":" & E2 & B6, "PREPRO!" & E2 & CEILING.MATH(B6/2) & ":" & E2 & B6)
D7 =IF(C7=FALSE, "PREPRO!" & E2 & B7/2 & ":" & E2 & B7/2+1, "PREPRO!" & E2 & CEILING.MATH(B7/2))
D8 =IF(C8=FALSE, "PREPRO!" & E2 & "1:" & E2 & B8/2, "PREPRO!" & E2 & "1:" & E2 & CEILING.MATH(B8/2))
D9 =IF(AND(ISERR(E5), ISERR(E6), ISERR(E7), ISERR(E8)), D5, "PREPRO!" & E2 & 1)

なおExcel 2010,2007の場合はD6~D8について,上ではなく下の式を利用します(太字は変更箇所)。

D6 =IF(C6=FALSE, "PREPRO!" & E2 & B6/2+1 & ":" & E2 & B6, "PREPRO!" & E2 & CEILING(B6/2, 1) & ":" & E2 & B6)
D7 =IF(C7=FALSE, "PREPRO!" & E2 & B7/2 & ":" & E2 & B7/2+1, "PREPRO!" & E2 & CEILING(B7/2, 1))
D8 =IF(C8=FALSE, "PREPRO!" & E2 & "1:" & E2 & B8/2, "PREPRO!" & E2 & "1:" & E2 & CEILING(B8/2, 1))

ことばによる式の説明は以下のとおりです。

D5 しくみ上「最大」値が格納されている(はずの)セルアドレス
D6 しくみ上「上ヒンジ」計算の対象となる(はずの)セル範囲(nの偶・奇で分岐)
D7 しくみ上「中央」値計算の対象となる(はずの)セル範囲(nの偶・奇で分岐)
D8 しくみ上「下ヒンジ」計算の対象となる(はずの)セル範囲(nの偶・奇で分岐)
D9 しくみ上「最小」値が格納されている(はずの)セルアドレス

Step 10中央値の計算|「OUTPUT」シート

最大・最小値は左のセルアドレスの値をそのまま返し,ヒンジ・中央値は左のセル範囲の中央値をMedian関数で求めます。

E5 =INDIRECT(D5)
E6 =MEDIAN(INDIRECT(D6))
E9 =INDIRECT(D9)

Step 11ひげの終端の計算(1)|「OUTPUT」シート

ここから下のブロックは,グラフ描画のために利用します。

一般的な箱ひげ図の様式と同様に,このテンプレートでも 上下のひげの終端は,上ヒンジ または 下ヒンジ を始点として,箱から離れる方向へそれぞれ[上ヒンジ-下ヒンジ]の1.5倍までの間において観測されるもっとも離れた実データとします(1.5以外の値としたい場合は下の式の中の値[4箇所]を変更します)。

この準備として,セルD12,D13に “上ヒンジ または 下ヒンジ から[上ヒンジ-下ヒンジ]×1.5”を加減した値を求めます。ただし それらが 最大値 または 最小値 を箱から逆の方向に超える場合はそのまま 最大値 または 最小値 を返します。

なおこの値は「PREPRO」シートが外れ値をチェックするため参照します。

D12 =IF(E5<E6+1.5*(E6-E8), E5, E6+1.5*(E6-E8))
D13 =IF(E9>E8-1.5*(E6-E8), E9, E8-1.5*(E6-E8))

Step 12ひげの終端の計算(2)|「OUTPUT」シート

「PREPRO」シートで求められる外れ値を上下方向の別にカウントします。

E12 =COUNT(PREPRO!C:C)
E13 =COUNT(PREPRO!D:D)

Step 13ひげの終端の計算(3)|「OUTPUT」シート

セルB12は「PREPRO」シートでソートされたデータの最下行(最大値)を起点に,(上方向の)外れ値の数だけ行数を遡ったセルの値を返します。

B13は同最も上の行(最小値)を起点として,(下方向の)外れ値の数だけ行数を下ったセルの値を返します。

B12 =INDIRECT("PREPRO!" & E2 & B5-E12)
B13 =IF(ISERR(E9), E9, INDIRECT("PREPRO!" & E2 & 1+E13))

これらがひげの終端となります。

Step 14ID の転記|「OUTPUT」シート

セルC12~C13にこのユニットのID(セルA1の値; カテゴリデータ)を転記します。

C12 =IF(ISERROR(B12), NA(), A$2)

Step 15ヒンジ & ID の転記|「OUTPUT」シート

セルB16~C17に 上ヒンジ, 下ヒンジ およびIDをそれぞれ転記します。

B16 =E6
B17 =E8
C16 =IF(ISERROR(B16), NA(), A$2)

Step 16中央値の転記|「OUTPUT」シート

セルB20~C20に 中央値,IDをそれぞれ転記します。

B20 =E7
C20 =IF(ISERROR(B20), NA(), A$2)

Step 17外れ値の抽出(1)|「OUTPUT」シート

セルA23に,E12~E13で求めた外れ値(の数)を合計します(=この変数で観測されたすべての外れ値の数)。

A23 =SUM(E12:E13)

Step 18外れ値の抽出(2)|「OUTPUT」シート

セルB23以下で具体的に外れ値の内容を列挙します。またセルC23にはIDを転記します。

これらのセル範囲の入力をおえたら,これを適当な行数コピーします。ここでは10行分確保するものとしますが,外れ値がそれ以上観測されるようなデータを扱う場合,状況を見て適宜調整(増やす)する必要があります。

B23 =IF(ROW(INPUT!A1)<=A$23, INDEX(PREPRO!A:A, SMALL(PREPRO!C:D, ROW(PREPRO!A1))), NA())
C23 =IF(ISNA(B23)=TRUE, NA(), A$2)

Step 19ユニットのコピー|「OUTPUT」シート

以上で1変数分の計算ユニットが完成です。1変数しか扱わない場合はグラフ作成のパートへジャンプします。

その他の場合は作業を続けます。ユニットの構成範囲(空白列1列含む)を選択し,これを右に1ユニットだけ(フィルハンドルをひっぱって)コピーします。

下の式を求めると,具体的にはL列までコピーすればよいことがわかります。

Step 20コピーしたユニットの修正|「OUTPUT」シート

参照に関して整合がとれていない(またはとれないおそれのある)セルがあるので(セルG2, I2),これを次のように修正します。

G2 =A2+1
I2 =C2+6

Step 21ユニットのコピー|「OUTPUT」シート

(修正を終えた)2番目のユニット右の空白列も含めすべて選択し,これをテンプレートとして必要な変数の数だけ(フィルハンドルをひっぱって)右方にコピーします。

ここでは5変数分のユニットを用意するものとして,具体的に下式で計算すると,AD列までコピーすればよいことがわかります。

2.散布図で “箱ひげ図の体裁” をととのえる

Step 22散布図の準備

「OUTPUT」シートの任意の空白セルをアクティブにした状態で挿入タブ「グラフ」グループの散布図ボタンをクリックし…

散布図ボタンをクリックして空のグラフを作成します。

Step 23系列の追加(1)

そのままデザインタブ「データ」グループのデータの選択ボタンをクリックし…

ダイアログの追加ボタンをクリックします。

Step 24系列の追加(2)

以降,グラフに次々と系列をくわえていきます(第1変数の構成要素から順に)。

最初に「ひげ」部をくわえます。下図のように,ダイアログの項目の色と対応する シートの彩色部分を指定します。

なお,横向きの箱ひげ図が目的の場合, 「系列Xの値」と「系列Yの値」を例示とは逆に指定します(以下に同じ)。

指定後OKを返します。

Step 25系列の追加(3)

ふたたびダイアログの追加ボタンをクリックし…

今度は「箱」部をくわえます。下図のように,ダイアログの項目の色と対応する シートの彩色部分を指定します。

指定後にOKを返し…

ダイアログの追加ボタンをクリックして…

さらに「中央値」部をくわえます。下図のように,ダイアログの項目の色と対応する シートの彩色部分を指定します。

指定後OKを返します。

Step 26系列の追加(4)

つづけてダイアログの追加ボタンをクリックして…

最後に「外れ値」部分を加え(範囲に注意:N/Aエラーも含み指定),OKを返します。

これをユニットの数だけ繰り返します※Step 23 の後段)。

※ ここまでの例示では5つのユニットを作成しています。このリクツから言えば本来は5つの箱ひげをグラフに組み入れることができるのですが,画面上で例示することに制約があるため,ここでのグラフはやむなく3変数 のみ組み入れていくものとします。

Step 27散布図(プレーン)の完成

すべての変数の各要素について設定を終えたのち,ダイアログのOKボタンを返すと…

下図のようなグラフができあがります。

以下の工程では,グラフの各構成要素にさらに設定をくわえ,箱ひげ図の形状的特徴を与えていくことになります。

Step 28散布図の書式設定(1)

最初に断っておかないといけないですが,多系列のグラフゆえ,以下書式設定がすこぶる面倒です………。

そうした方法を選択した身から言及するのも なかなかにはばかられるところではありますが……負担をできるだけ軽くするコツは,“流れ作業” 的処理法に徹することにあると思います。直前の操作を繰り返し別系列に適用させるF4キーを使って,系列(≒「箱ひげ」の部位)ごとにまとめて書式を適用していくと負担を減らすことはできると思います(たとえば,変数1のひげ 変数2のひげ 変数3のひげ で1サイクル。2サイクル目は 変数1の箱 変数2の箱 変数3の箱,3サイクル目… といった具合)。


…さて具体的に設定をはじめるものとして,まずは「ひげ」系列(ここでの系列名は “WHISKER [x]”。以下略)に対し,グラフに適用した変数の分(ここでは3変数)次の諸点を設定します。

「ひげ」系列
R 単色
R 任意
R 任意(ただし細め)

※ 表中 “R” 文字のついた要素は 繰り返し操作(F4キー)の可能な項目です(以下に同じ)。

Step 29散布図の書式設定(2)

「ひげ」の終端をグラフに適用した変数の分 設定していきます(繰り返し可)。グラフオブジェクトの外のてきとうな場所で,終端として適度な長さ および 線幅をもった直線を(図形で)描き…

これを選択してコピーします。

「ひげ」系列終端のマーカーを選択し(片方だけ選択されていないか要注意[上端・下端ともにハンドルが表示されている状態であることを確認])…

先の直線をペースト(貼り付け[Ctrl+V])します。

これをすべての変数に適用をおえたとき,グラフは下のようになっているかと思います。

Step 30散布図の書式設定(3)

「箱」系列(ここでの系列名は “IQR [x]”。以下略)に対し,次の流れですべての変数に対し設定をくわえます。

「箱」系列に直線を加え(線「単色」:繰り返し可)…

任意の線幅(箱の太さ)を設定します(繰り返し可。ポイント数が高いほど箱の幅が大きくなる)。

なおExcel 2013以外はこの設定を施すとマーカーまで大きくなってしまうので,線が背後に隠れてしまいます。この場合,裏ではおそらく設定が反映されているはずですので,気にせずそのまま作業を追っていけばいいかと思います。

つづいて先端を「フラット」(繰り返し可)に変え…

不要なマーカーを削ります(繰り返し不可)。

Step 31散布図の書式設定(4)

「中央値」系列(ここでの系列名は “MEDIAN [x]”。以下略)に対し,次の諸点を設定します。

「中央値」系列
マーカーのオプション 種類:―, サイズ:任意
塗りつぶしR 任意の色
枠線R なし

Step 32散布図の書式設定(5)

「外れ値」系列(ここでの系列名は “OUTLIER [x]”。以下略)に対し設定をおこないます。

(場合により適当な形状のマーカーに変更し)マーカーの塗りつぶしを「塗りつぶしなし※」(繰り返し可)に設定し…

※ マーカーが近接した場合でもできるだけ判別可能にするための処置です。

つづいてマーカーの枠線を「線(単色)」(繰り返し可)にし,任意の色を指定します。

Step 33散布図の書式設定(6)

散布図の横軸を設定します。散布図本来の性格に照らせば,横軸に項目そのものを振るような使い方は当然想定されていないはずなので,“らしくみせかける” ための細工を施します。

まず,ここではカテゴリ番号(1, 2, 3…)の間にその中間値(0.5, 1.5, 2.5…)が挿入されてしまいました。それゆえ目盛間隔を1にし,中間値が表示されないよう設定します(中間値が挿入されない場合でも,図の意図せぬ崩れを防ぐため設定しておくことをおすすめします)。

さらに横軸の最小値・最大値をそれぞれ0,グラフに組み込んだ変数の数+1 に固定し…

横軸の線を「なし」にします。

さらに負のパラメータの出現に対応させるため,ラベルの表示位置を「下端/左端」に変更します。

Step 34散布図の書式設定(7)

(場合によって ひげ が判別しづらくなるおそれがあることから)縦目盛り線は不要なので,これを消去します。

横軸ラベルの文字色を白にして背景と同化させます(ここではメンテナンスの観点[下でおこなうラベルの位置合わせなどの際にガイドとして利用できる]からラベル自体は維持させますが,場合によっては表示そのものを切ってしまってもグラフの表示に影響はありません)。

グラフをアクティブにし

図形(四角形)をグラフ内横軸・第1変数のラベル(見えませんが)付近に挿入します。

図形の書式設定で背景と同化(線・塗り色:なし)させ,図形を選択した状態から数式バーで第1変数の名称が入っているセル(B2)を指定します。このとき,Enterキーを押した時点でグラフに変数名が反映されます。

これをグラフにくわえた変数の数だけ繰り返します。


―――以上の作業をおえると,下のような箱ひげ図ができあがります。

3.動作のテスト

Step 35「INPUT」シートに別データをペーストして結果を確認

3枚のシートが連携して正しく処理をなすかどうか,別のデータを入れて確認してみます。

最初に定義したように,元データは「INPUT」シートにコピペする仕様ですので,まず「INPUT」シートをクリアします。

下のように「INPUT」シートのすべてのセルを選択し,ショートカットメニュー(右クリック)から「数式と値のクリア」を選択します

※ 他シートでセルの参照構造が崩壊するため,「クリア」でなく行または列ごと削除してシートを空にすることはNGです。その意味ではシートを保護して行・列の削除メニューをグレーアウトしておくべきかもしれません。

下のDLボタンからサンプルデータをひらき,内容をすべて選択・コピーして「INPUT」シートのセルA1に貼り付けます。

箱ひげ図・サンプルデータ2

このとき「OUTPUT」シートのグラフは下のような形状を返すか確認し…

同シート・ユニット1の内容は下のような…

同シート・ユニット2の内容は下のような…

同シート・ユニット3の内容は下のような値を返すか確認してすべての工程は完了です。

4.バリエーション, 拡張

Step 36横向きの箱ひげ図, データの “ムラ” を並列図示する箱ひげ図

横向きで箱ひげ図を描画した場合,下のようなアウトプットになります。

また,各変数のカテゴリ値から若干ずらした値・各変数が得た値(「INPUT」シートで入力した値)の計2列からなる別シートを作成し,(縦向きの箱ひげ図の場合)前者を横軸,後者を縦軸にしてこれをグラフの系列にくわえると下のようなストリップチャート付きの箱ひげ図を描くことができます(マーカーのみ, 線なし, 塗り色の透明度: 下の図は75%に設定)。いうなればビースウォーム的なはたらきを期待してのものですが,点の有無 および 色の濃淡で分布における “ムラ” もアバウトに確認することができます。

だだ上のようにドットをくわえるとき,データのサイズが大きいときなど,状況によっては判読性を失うこともあるかと思います。そうした場合の解決策のひとつとして,下図のように適宜x軸をrand関数で散らしてやってもいいかもしれません。

また,さらなる手数を対価にストリップチャートではなくビースウォームを組み込みたい場合は,リンク先などを参照ください。

最後に,系列を増やす手間を許容できるなら,下のように「箱」要素の外周線および中央値もすべて直線で描画してしまうのも方法のひとつとしてはアリかな…とも思います。この場合,1変数につき 各辺(4系列)+あたらしい中央値の計5系列を追加する必要があります(最初に組み入れた「MEDIAN[x]」系列は不要につき外します)※。

※参考:1つのグラフに含めることのできるデータ系列の数:255(Excel(2013)の仕様および制限)。上の仕様の場合,1変数につき最低7系列必要なので グラフへ組み込める上限は36変数となります。

その他の参照