BDAstyle

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

ビースウォーム(Bee Swarm Plot)の作成 with Excel

Step 0

はじめに

ここにいうビースウォームは,別頁「箱ひげ図の作成 with Excel ― ヒンジver., 負数・外れ値対応」の「4.バリエーション, 拡張」で追加した,ドットによる1変数の分布の表現(ストリップチャート)と同様の目的で利用するものを指して言います。先の頁では,ドットの重なりを避ける…というか干渉を和らげるアルゴリズムとしてランダムなノイズ(jitter)を加えて解決する方法を採りました(下図青い点の要素,再掲)。

翻ってこの頁での作例は,先のようなランダムなノイズではなく,より規則的な避け方を採るものです。アウトプットとしては3つのスタイル…これを順に"Swarm""Center""Hex"と呼ぶこととしますが,後ろの二者については"Swarm"のシートにプラスアルファの手数を加えるといった手続きをとります。


例示にあたっては,以下のデータを利用します(2群)。それぞれ事前に昇順に並べ替えをすませてあります(必須)。また,ここではこのシート名を“Data”としておきます。

ビースウォーム・サンプルデータ

目的のアウトプットは下図の通りです("Swarm")。

仕様


工程

スタイル “Swarm”

Step 1

“Data”シートで必要な変数の別に最大値・最小値を求めます。

E5 =MAX(A:A)
F5 =MAX(C:C)
E8 =MIN(A:A)
F8 =MIN(C:C)

同様に階級の数を決めます。任意なものでOKですが,ここではビンの数をnの半分程度にして進めます(個人的には,この辺がもっともハマリがよく思うので)。

つづいて,それらの値をもとにしてレンジから階級幅を計算します。ビースウォームはどのみちいろんな意味でアバウトな要素を抱えることになるので,この場合の階級幅も,丸めずそのまま使っていきます。

E11 30
F11 15
E14 =(E5-E8)/E11
F14 =(F5-F8)/F11

横軸上のスウォームの配置位置を決定します。カテゴリを作るだけなので,1群のみを図示する場合はぶっちゃけ何だっていいわけですが,2群以上を扱う場合は左の群とどの程度離せばよいかといった他群との兼ね合いも絡んできます。…実際はそうしたことを試行しながら修正していく必要にも迫られますが,とりあえずここでは順に2,4という値を振っておきます(=横軸の「2」位置を1群目で使用し,「4」位置を2群目で使用する)。

最後に,横軸におけるマーカーのシフト量を指定します。これも実際にはマーカーサイズとの兼ね合いで決めるべきものですが,この辺に大きな労力を割くのも本末転倒なので,だいたいのバランスを予想して0.1と設定しておくことにします(よい結果が出なかったら変えればいいので)。

E18 2
F18 4
E21 0.1

Step 2

“Freq”シートを作成し,ここで度数を求めます。

その準備として,必要な群の数だけ下のような見出しを用意しておきます。また「度数」直下には0を入力(少し後で計算に使います)し,「階級番号」は(見出しの2つ下のセルから)“Data”シートで決めた「ビン」の数(多群の場合は最も多いもの)だけ作っておきます。

最初の群に関して度数を求めます。まず階級の下限を設定し(セルB6のみ下方にコピー),

B5 =Data!E8
B6 =C5

つづいて上限を設定します。

C5 =B5+Data!$E$14

度数を求めます。Frequency関数の場合は,度数の領域を選択し「上限」をもとに配列数式として求めます(cf.ヒストグラムの作成 with Excel 2/3 [Frequency関数による方法])。

2群目以降も同様にして度数を求めます。この作例の場合の2群についての度数分布表は,下図のようになります。

Step 3

データをプロットするための座標を設定します。ここでは,群ごとのx,yといった感じに,1群につき2枚のシートを用意して対処したいと思います。

…ということで,まずは1群目の縦軸に関するシート“V1y”を用意します。最も上の行には,1から当該の群の最大度数(“Freq”シートの「度数」を見て判断)まで連続データを作ります。このとき,後にこの部分を実データと誤認してしまうのもマズいので,それを予防する意味合いで下図のように何らの修飾を加えておくといいかもしれません。

scrollable

セルB1に,各階級の実データ(観測値)を次のような式によって拾ってきます。これを見出しの右限までコピーし,さらに“Data”シートで決めたこの群の階級の数と同じ行だけ(ここでは残り29行)下方にもコピーします。

A2 =IF(COLUMN()>Freq!$D5, "", SMALL(OFFSET(Data!$A$2, SUM(Freq!$D$4:$D4), 0, Freq!$D5), COLUMN()))

scrollable

参考)コピー後の状態。

scrollable

Step 4

同様にシート“V1x”を作り,先の“V1y”の各座標に対応する横軸位置を求めます。

A2 =IF(Freq!$D5=0, "", IF(V1y!A2="", "", IF(Freq!$D5=1, Data!$E$18, IF(ISODD(A$1)=TRUE, Data!$E$18-((QUOTIENT(A$1, 2))*Data!$E$21), Data!$E$18+(A$1/2*Data!$E$21)))))

上の式では,度数が2以上の場合,見出しに振った値が奇数なら左方向へシフト,偶数なら右方向へシフトさせています。

scrollable

参考)コピー後の状態。

scrollable

Step 5

2群目以降(あれば)も先の手順と同様にして求めます。

この作例の場合,シート“V2y”は具体的に次のように,

scrollable

シート“V2x”は次のようになります。

scrollable

Step 6

シート“Output”を作成し,ここに必要な群の数の次のような見出しを用意しておきます。x,yは散布図を描くために必要な横・縦のそれぞれの座標です。

…ということで,“V1x”“V1y”といったシートから値を拾ってここに転記してやればいいのですが,当該シートの構成が複数列にわたるものである以上,コピペや数式で1列に収める…という所業もなかなかに簡単ではありません。スプレッドシートを知り尽くしている人ならこうした変換も赤子の手をひねるがごとしなのでしょうが,筆者はそうではないので,ここでは別の手段をとりたいと思います。具体的には,テキストエディタの上でデータを成型します。

したがって“V1x”見出しを除くデータ範囲を選択し,このデータをコピーして…

テキストエディタにペーストします(ここではMeryで例示します)。

正規表現を使って次の手順でデータを置換します。

  1. 改行をタブに置換(¥n¥t)
  2. 2つ以上のタブの繰り返しを1つのタブに置換(¥t{2,}¥t)
  3. タブを改行に置換(¥t¥n)

これを終えると,下の状態となります。これをすべて選択し…

“Output”シート,1群目のxにペーストします。

2群目のyまで上記の処理を繰り返し,“Output”シートは下図の状態になりました。

Step 7

この段階でのx,yをそのまま使って散布図を描けばおおむね当初の目的は果たせるのですが,他群との兼ね合いのうえから一部の重なりが上手く解決できない場合に,多少のノイズを入れてみると都合がよくなることもあります(統計グラフとしての視点からのそうした行為の是非については…まぁ考えないことにします…)。

これを「x'」でおこなうと,下表・下図のようになります(ここでは,ノイズを1/50×標準正規乱数としています)。

E18 =A3-NORM.S.INV(RAND())/50
F18 =E3-NORM.S.INV(RAND())/50

「x'」「y」で1群目の散布図を作成してから,

これに2群目を追加すると下図のようになります。

Step 8

書式・サイズを適宜調整し,ビースウォームの完成です。「x'」列により描画した場合は,F9キーを押すたびにマーカーの配置をわずかに変化させることができます。

その他のスタイルへの変更

スタイル “Center”

Step 9

Step 3“V1y”シート計算式を次のように変更します(多群の場合は残りのシートも同様)。

A2 =IF(COLUMN()>Freq!$D5, "", (Freq!$C5+Freq!$B5)/2)

これによりすべてのドットを階級の中心値に打つように変更します。

スタイル “Hex”

Step 10

上の手数に加え,Step 4“V1x”シートを複製し,複製先の計算式を次のように変更します(多群の場合は残りのシートも同様)。

A2 =IFERROR(IF(ISODD(ROW())=TRUE, V1x!A2, V1x!A2-Data!$E$21/2), "")

これにより階級ごとにドットを交互にシフトさせます。加工ないしは複製した“V1x”“V1y”で散布図を作り,別途「図形」機能で正六角形を作成して,これを当該系列にコピペして調整します(マーカーの変更方法の仔細は別頁「層別散布図の作成 with Excel」にて触れています)。

Tips

Step 11

下図赤点の座標に不可視のマーカー(系列)を追加して,これのラベルを「系列名」項目にて表示すれば,見た目上,横軸ラベルに代えることができます。グラフ|プロットエリアのサイズを試行的に調整していくことが必至と言っていいこのグラフにおいては,テキストボックスを貼付するよりこちらの手筈の方が便利かもしれません。

最後に,筆者による読解はケガをするだけなのでここで深入りはしませんが,ビースウォームには次のリンク先のような批判的な言及も見られることを加えておきます。

Beeswarm Boxplot (and plotting it with R) ―"R-statistics blog"

その他の参照