BDAstyle

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

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

データをコピペするだけで,五数要約の計算から箱ひげ図の描画までをほぼ自動的に実行できるような仕組みをつくる

イントロダクション

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

このブックでは,4枚のシートを利用します。「Data」シート(データの入力先),「1stPRC」シート(前処理),「2ndPRC」シート(本処理),そして「Plot」シート(グラフ用データの作成+グラフの出力)の4つです。

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

  • 利点
    • エクセルで箱ひげ図をつくる際の教科書的なアプローチのひとつ「積み上げ縦棒」を使わないので,パラメータのいずれか またはそのすべてが負数の場合もグラフが描画できます。
    • 外れ値を自動で抽出し,グラフにプロットします。
    • グラフオブジェクトに対するデータ範囲の指定のしかたにより,縦横いずれかの向きを意図してグラフをつくることが可能です。
    • VBAを使用しません(現時点。のち補助的に利用するかもしれません)。
  • 短所
    • ここでは基本的に,環境をひとたびととのえれば データをコピペするだけでアウトプットに到達できるシートの作成を趣旨とします。そのため,かかる趣旨に沿うシート構造 および グラフ設定を用意するため どちらかといえば面倒な手続きを要します。よって「手短にグラフを作成するための」アプローチを必要としている場合,本頁の内容は目的にかないません。この場合,次のような記事で解説されている手続きの方がより適するかと思います。
    • “箱” に関しては原則単色表現のみ可能です。白抜き表現(□)のように外周線・内部で異なる彩色をおこなうことはできません。
  • 特記事項
    • データの入力は必ず「Data」シートのA1からおこなうものとします。
    • 変数名(ラベル)は必須です。
  • 免責事項
    • このテンプレートによるアウトプットが正しいかどうか,いくつかのパターンを生成し「R」を使って確認をおこなっていますが,筆者による数式の構成 ないしは 筆者にとって想定の至らない事象などを原因に不正確が生じる可能性は捨てきれません。筆者はこれによって利用される方がこうむった不利益に対しいかなる責任も負いかねますので,このテンプレートをご利用いただく際は他処理系などで事前にアウトプットを十分検証してくださいますようお願い申し上げます。

作成にあたり参考にしたWebページ・書籍およびPDF文書

工程

箱ひげ図を描くために必要な値を計算する仕組みをつくる

Step 14つのシートを用意する

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

Step 2ダミーのデータをあてがう|「Data」シート

「Data」シートが空のままテンプレートをつくっていくのは,時々の状態を把握しがたくやりづらいかと考えます。したがって,ここでは便宜的にダミーのデータ※を填めてシートをつくっていくことにします。

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

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

DL

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

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

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

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

ことばによる式の説明を以下に加えます。

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

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

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

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

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

※この例示では便宜上サイズを100とします(100行目までコピー)。

コピーをおえたら,そのまま(選択を解除せず)フィルハンドルをひっぱって,同じく想定する変数の最大個分ユニット単位で右方にコピーをつくっていくわけですが ―――――想定する変数の数によっては,どの列までコピーしたらよいかが分かりづらいところかもしれません。

その場合,いったん作業を止め,空いている任意のセルを使って,下の式を使って求めてもいいかと思います。

この例示では3つのユニットを用意したいと考えます(それゆえ,上の式の「変数の数」を3としています)。したがって,=Address(1, 3*6)を求めると“$R$1”が返るので,R列までコピーすればよいことがわかります。

Step 5表組みを用意する|「2ndPRC」シート

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

数値以外の見出し部分は相対的には重要度の劣るパーツなので,任意のものに変更してもOKです(ただしのちにユニットをコピーする以上,自動的に区別可能な一意の見出しとなるような工夫は必要ですが)。

なおタイプする項目が決して少なくはないので,図の下の表をすべてコピーし,セルA1に貼り付けてしまった方が簡単だとは思います。

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

Step 6変数名と参照先の列番号を定義する|「2ndPRC」シート

セルD2,E2,B2の順に次の式を入力します。変数名以外はこれ以降のブロックで使用する参照先アドレスを作成するためのパーツで,D2が「Data」シートの1番目の変数が格納されている列を,E2が「1stPRC」シートの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("Data!" & D2 & 1)="", "", INDIRECT("Data!" & D2 & 1))

Step 7サイズをカウントする|「2ndPRC」シート

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

B5 =COUNT('1stPRC'!A:A)
B6 =B$5

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

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

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

Step 9五数要約を求めるための,セル範囲を作成する|「2ndPRC」シート

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

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

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

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

ことばによる式の説明も加えておきます。

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

Step 10五数要約を求める|「2ndPRC」シート

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

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

Step 11ひげの終端を求める(1)|「2ndPRC」シート

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

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

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

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)|「2ndPRC」シート

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

E12 =COUNT('1stPRC'!C:C)
E13 =COUNT('1stPRC'!D:D)

Step 13ひげの終端を求める(3)|「2ndPRC」シート

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

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

C12 =INDIRECT("'1stPRC'!" & E2 & B5-E12)
C13 =IF(ISERR(E9), E9, INDIRECT("'1stPRC'!" & E2 & 1+E13))

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

Step 14ひげの終端の値とユニット番号とを関連づける|「2ndPRC」シート

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

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

Step 15ヒンジを転記し,ユニット番号を関連づける|「2ndPRC」シート

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

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

Step 16中央値を転記し,ユニット番号を関連づける|「2ndPRC」シート

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

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

Step 17外れ値を抽出し,ユニット番号を関連づける(1)|「2ndPRC」シート

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

A23 =SUM(E12:E13)

Step 18外れ値を抽出し,ユニット番号を関連づける(2)|「2ndPRC」シート

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

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

B23 =IF(ISNA(C23)=TRUE, NA(), A$2)
C23 =IF(ROW('Data'!A1)<=A$23, INDEX('1stPRC'!A:A, SMALL('1stPRC'!C:D, ROW('1stPRC'!A1))), NA())

Step 19ユニットを1つだけコピーする|「2ndPRC」シート

以上で1変数分の計算ユニットが完成です。1変数しか扱わないひな型で十分な場合には,「Plot」シートのパートへジャンプします。

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

※具体的にはL列まで。

Step 20コピーしたユニットを修正する|「2ndPRC」シート

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

G2 =A2+1
I2 =C2+6

Step 21修正を加えた方のユニットを,残りの変数分コピーする|「2ndPRC」シート

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

ここでは最初に3変数分のユニットを用意するとしてシートづくりを進めているので,具体的に =Address(1, 3*6) 式で計算すると,R列までコピーすればよいことがわかります。

Step 22グラフ描画用のシートを準備する|「Plot」シート

描画にあたって過剰な負担となりがちな,グラフの生成にかかる手数を削るため,必要なデータを「Plot」シートにあらかじめ成形したうえまとめておきます。

まずは下図の見出しを配置しておきます。なおこの頁からセルA1にコピペすると簡単です。

scrollable

Step 23ひげ系列のプロット用のデータを配置する|「Plot」シート

下表の式ないし値を,下図の対応する色のセルに入力します。これは「2ndPRC」シートのWHISKERについての,Xvalueのアドレスを指示するものです。

A3 0
B3 =ADDRESS(12, 2+6*A3)
B4 =ADDRESS(13, 2+6*A3)
C3 =ADDRESS(12, 3+6*A3)
C4 =ADDRESS(13, 3+6*A3)

同じく下表の式を,下図の対応する色のセルに入力します。これにより「2ndPRC」シートのWHISKERについての,Xvalueの値を参照します。

D3 =INDIRECT("'2ndPRC'!" & B3)
D4 =INDIRECT("'2ndPRC'!" & B4)
E3 =INDIRECT("'2ndPRC'!" & C3)
E4 =INDIRECT("'2ndPRC'!" & C4)

式を入力した範囲に1行分のマージンを加えた矩形部分を選択し,これを残りのユニットの数だけ下方向にコピーします。具体的に,ここでは計3ユニット分用意したいすでに1ユニットは用意した残りは2ユニット分 といった理解から,コピーは11行目まで必要です。

Step 24箱系列のプロット用のデータを配置する|「Plot」シート

下表の式ないし値を,下図の対応する色のセルに入力します。これは「2ndPRC」シートのIQRについての,Xvalueのアドレスを指示するものです。

G3 0
H3 =ADDRESS(16, 2+6*G3)
H4 =ADDRESS(17, 2+6*G3)
I3 =ADDRESS(16, 3+6*G3)
I4 =ADDRESS(17, 3+6*G3)

同じく下表の式を,下図の対応する色のセルに入力します。これにより「2ndPRC」シートのIQRについての,Xvalueの値を参照します。

J3 =INDIRECT("'2ndPRC'!" & H3)
J4 =INDIRECT("'2ndPRC'!" & H4)
K3 =INDIRECT("'2ndPRC'!" & I3)
K4 =INDIRECT("'2ndPRC'!" & I4)

式を入力した範囲に1行分のマージンを加えた矩形部分を選択し,これを残りのユニットの数だけ下方向にコピーします。具体的に,ここでは計3ユニット分用意したいすでに1ユニットは用意した残りは2ユニット分 といった理解から,コピーは11行目まで必要です。

Step 25中央値系列のプロット用のデータを配置する|「Plot」シート

下表の式ないし値を,下図の対応する色のセルに入力します。これは「2ndPRC」シートのMEDIANについてのXvalueのアドレスを指示,および参照するものです。

M3 0
N3 =ADDRESS(20, 2+6*M3)
O3 =ADDRESS(20, 3+6*M3)
P3 =INDIRECT("'2ndPRC'!" & N3)
Q3 =INDIRECT("'2ndPRC'!" & O3)

式を入力した範囲に1行分のマージンを加えた矩形部分を選択し,これを残りのユニットの数だけ下方向にコピーします。具体的に,ここでは計3ユニット分用意したいすでに1ユニットは用意した残りは2ユニット分 といった理解から,コピーは8行目まで必要です。

Step 26外れ値系列のプロット用のデータを配置する|「Plot」シート

まずは下図の2列,厳密には「2ndPRC」シートにて確保しておいた外れ値の行数分を,ユニット単位で埋めていきます。

具体的にはui-1にはユニットごと0から始まる連続データを,wにはユニット0から始まる連続データを用意しておきます。このとき,ユニットの別に1行の空白行を挟んでおきます。

下表の式ないし値を,下図の対応する色のセルに入力し,表最下行までコピーします。これは「2ndPRC」シートのOUTLIERについてのXvalueのアドレスを指示,ないしは参照するものです。

コピーしたのち,ユニットの境界に填まったデータ(下図赤い囲み部分)については,不要なので必ず消去しておきます。

U3 =ADDRESS(23+T3, 2+6*S3)
V3 =ADDRESS(23+T3, 3+6*S3)
W3 =INDIRECT("'2ndPRC'!" & U3)
X3 =INDIRECT("'2ndPRC'!" & V3)

Step 27変数ラベルのプロット用のデータを配置する|「Plot」シート

下表の式ないし値を,下図の対応する色のセルに入力し,表最下行までコピーします。これは「2ndPRC」シートのCaptionを散布図の横軸に表示するために使います(散布図を素地とする場合,その横軸にカテゴリ名を工夫なく表示させることは不可能なので)。

別の言い方をすれば,セルAC3で定める任意の値を,後段のグラフの調整でY座標の「最小値」として据えるので,ここでは「2ndPRC」のすべてのユニットを見渡してハマリのよさそうな最小値を探っておく必要があります(その結果としてこの例示では「-20」を設定したところです)。

Z3 0
AA3 =ADDRESS(2, 2+6*Z3)
AB3 =Z3+1
AC3 =VALUE("-20")
AD3 =INDIRECT("'2ndPRC'!" & AA3)

式を入力した範囲に1行分のマージンを加えた矩形部分を選択し,これを残りのユニットの数だけ下方向にコピーします。具体的に,ここでは計3ユニット分用意したいすでに1ユニットは用意した残りは2ユニット分 といった理解から,コピーは8行目まで必要です。

散布図で素地をつくる

Step 28“WHISKER”系列で散布図を作成する

「Plot」シートのWX, WY列を選択した状態で,挿入タブ「グラフ」グループの散布図ボタンをクリックし,散布図ボタンをクリックしてグラフを作成します。

Step 29系列名を修正する

グラフがアクティブになっている状態からデザインタブ「データ」グループのデータの選択ボタンをクリックし―――

scrollable

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

系列名に「Plot」シートのセルA1を指定します。

Step 30“IQR”系列を追加する

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

系列名系列Xの値系列Yの値に,下図シート上の対応する色のセル範囲またはセルを指定します。

Step 31“MEDIAN”系列を追加する

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

系列名系列Xの値系列Yの値に,下図シート上の対応する色のセル範囲またはセルを指定します。

Step 32“OUTLIER”系列を追加する

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

系列名系列Xの値系列Yの値に,下図シート上の対応する色のセル範囲またはセルを指定します。

Step 33“LABEL”系列を追加する

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

系列名系列Xの値系列Yの値に,下図シート上の対応する色のセル範囲またはセルを指定します。

Step 34ここまでの作業で完成した散布図

現時点での散布図は下図のようになっています。

ざっくりと言えば,これ以降の過程で下図濃い青の点の間を“ひげ”として,同じくオレンジの点の間を“箱”として,それぞれの見た目をととのえていきます。

箱ひげ図の “体裁” をととのえる

Step 35グラフの種類を変更する

グラフの任意の系列をアクティブにした状態から―――

「グラフの種類の変更」ダイアログを呼び出して,WHISKERおよびIQR系列をそれぞれ順に散布図(直線とマーカー)散布図(直線)に変更します。

Step 36線端の突出を無くす

WHISKERおよびIQR系列の線の先端をいずれもフラットに変更します。

Step 37箱を太く

IQR系列の線のを任意のサイズに拡げます。

この時点で,グラフは下図のとおりです。

Step 38ひげの突端を描く

WHISKER系列のマーカーの種類を下図のように横棒に変え,大きさを任意のものに調整します。

これにより,グラフは下図のようになります。

Step 39外れ値マーカーの外形を決める

OUTLIER系列の外形を書式において設定します。マーカーの形状以外に塗り色・線色によって特徴をあたえることができます。

たとえば,この例示ではマーカーが近接するケースを考慮して,下図のように枠線色のみを残しておきたいと思います。

Step 40変数名を表示する

LABEL系列のマーカー直下にデータラベルを挿入し―――

その内容を下図の色の対応で指定して書き換えます。ver.2010の場合は,(面倒ですが)個別にラベルにAD3AD5といったセルアドレスを指定してやれば対処は可能です。

これにより,グラフは下図のようになります。

Step 41表示範囲を調整する

グラフの軸のMin, Maxを設定して適度な表示範囲を探っていきます。

この例示では,たとえば横軸は[0.5, 3.5]のレンジで―――

縦軸はラベル位置で決めたようにMin:-20で描けば程良いところかと思います。

具体的にそれらの値を各軸に設定すると,グラフは下図のようになります。

Step 42その他の書式設定(1)

箱ひげ図に直接関わる要素(LABEL系列以外のもの)を任意の配色ルールで彩色し,

LABEL系列のマーカーを不可視とします。また加えて,データラベルが違和感なく表示されるよう,グラフエリアやプロットエリアを適当な大きさにととのえていきます。

Step 43その他の書式設定(2)

その他任意の書式設定を続けます。

たとえば,過剰な目盛線を削ったり,

全体のバランスを見ながら,箱の太さやデータラベルの大きさに再度の調整を加えていくなどの作業を重ねます。

これにより,シートの上での仕組みづくりは完了です。

動作の確認と使い方

Step 44「Data」シートのデータを置き換える

ここで動作確認も兼ね,制作過程で使ったダミーデータとは別のデータを実際に適用してみたいと思います。

最初に定義したように,元データは「Data」シートにコピペする仕様です。したがって「Data」シートをクリアすることから始めます。

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

※他シートでセルの参照構造が崩壊するため,「クリア」でなく行または列ごと削除してシートを空にすることはNGです

下のDLボタンからあたらしいダミーをひらき,

内容をすべて選択・コピーして「Data」シートのセルA1に貼り付けます。

DL

これによりグラフは下図のように変化します。もっともラベルを組み込んだ手前,軸の最小値を設定していますので,TEST2TEST3の箱ひげ図が半端に切れてしまっています。

この点については手作業で修正します。たとえば,あたらしいダミーデータの場合,「2ndPRC」シートの全ユニットの最小値を見比べて勘定した結果,ラベルを-100位置に移動させています(「Plot」シートの「LY」列の値を-100に,グラフの縦軸の最小値を同じく-100に設定)。

Tips

Step 45横向きの箱ひげ図とデータマーカーの追加

横向きで箱ひげ図を描画したいとき,たとえば下のようなアウトプットをつくることができます。この場合,ひげの突端と中央値のマーカーで使用したい(組み込みマーカー用の)縦向きのラインが用意されていませんので,直接図形(直線)をペーストすることで解決を図ります。

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

だだ上のようにドットを加えるとき,データのサイズが大きい場合など,状況によっては読みやすさを失うこともあろうかと考えます。そうした場合の解決策のひとつとして,下図のように適宜Xrand関数で散らしてやってもいいかもしれません(jitterを加える)。

また,さらなる手数を対価にストリップチャートではなくビースウォームを組み込む方法もあります。ビースウォームについては次のリンク先などを参照してください。

その他の参照