BDAstyle

Business Data Analysis & Visualization with Excel

軌跡付き散布図の作成 with Excel

イントロダクション

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

このグラフは，たとえば追跡調査あるいは処置の有無で対応のある2群を比較したい場合などに利用可能なビジュアライゼーション・メソッドです。

ある製品系の強度において重視される指標X，Y(2変数)があります。今回，この製品系のうち3つの製品(product 1-3)に，強度に関して科学的有効性をうたう新薬剤をその製造過程で投入可能であることがわかりました。そこで，これを試験的に投入し，X，Yの値を観測して次表を得ています。表の左側は投入前における測定値を，同右側は投入後における測定値です(これを「INPUT」シートとします。シートの名称については計算式の中でも同様のものを使用していきますので，特段の理由がなければ同じ名前で作成した方が簡単だとは思います)。

DL

以下，このデータを下図のように可視化するための手続きです。

なお，ビジュアライズのための重要な方針として，ここでは

  • 再計算・再描画が容易であること
  • データを増やす｜減らすが容易であること
  • VBAを使用しないこと
  • 図形(機能)を使用しないこと

を据えています。かかる前提から計算式やグラフの設定に若干の手数が必要な内容となっています。とりわけ計算式については，筆者がここで組み立てた式より洗練された方法もあるだろうな，とは思うところです。

先の表のように，この作例では3項目(ここでは製品の数)で作図していきます。この項目の数については固定的なものではなく，運用上，最大で10個程度まで必要になるかもしれないと見積もったとの仮定を置いて，手続きを進めていくことにします。

工程

表部分

Step 1［MODULE_A表］軌跡のパターンの解析

あたらしく「MODULE_A」シートを作成し，下図のように見出しを配置しておきます。

このシートでは，

  • 軌跡のパターンを解析し【A-C列】，
  • 2点間の直線距離を求め【D列】，
  • 軌跡(矢印付き直線にて描画)の突端をきれいに表現するための，x，yに関する空間的なゆとりを求める【F-G列】

ことを目的とします。

下の式を対応する色のセルに入力(そのままコピペを推奨。以下に同じ)し，これを任意の行だけコピーします。

前段で決めたように，ここでは運用上10程度の項目までを想定していますから，7行余分に12行目までコピーして進めるものとします。

A3 =IF((INPUT!D3-INPUT!B3)>0, 1, IF((INPUT!D3-INPUT!B3)<0, -1, 0))
B3 =IF((INPUT!E3-INPUT!C3)>0, 1, IF((INPUT!E3-INPUT!C3)<0, -1, 0))
C3 =IF(AND(A3=1, B3=0), 1, IF(AND(A3=0, B3=1), 2, IF(AND(A3=-1, B3=0), 3, IF(AND(A3=0, B3=-1), 4, IF(AND(A3=1, B3=1), 5, IF(AND(A3=-1, B3=1), 6, IF(AND(A3=-1, B3=-1), 7, IF(AND(A3=1, B3=-1), 8, 9))))))))
D3 =SQRT(ABS(INPUT!D3-INPUT!B3)^2 + ABS(INPUT!E3-INPUT!C3)^2)

なお参考までに，C列の値は9つの方向についての下図のような割り当てを意味します(番号)。

F，G列についてはこの時点で処理することが叶いませんので，一旦このシートを離れます。

Step 2［MODULE_B表］見出しの作成とアドレスの定義

「MODULE_B」シートをあたらしく作成し，下図のような見出しを配置しておきます。

A1～A3 は順に，「INPUT」シートに関する

  • データ範囲
  • 行見出し
  • 列見出し

のセル番地を，またA4は，

  • ゆとりをつくるための係数(>=0 の実数。結果に鑑み必要があればユーザーにより任意に変更を加える対象。後述)

を意味します。

B1～B4 に以下の式を入力し，先の内容を求めます。

B1 ="INPUT!$B$3:" & ADDRESS(2+COUNTA(INPUT!$A:A), 5)
B2 ="INPUT!$A$3:" & ADDRESS(2+COUNTA(INPUT!$A:A), 1)
B3 INPUT!$B$2:$E$2
B4 =MIN(OFFSET(MODULE_A!D2, 1, 0, COUNTA(INPUT!A:A))) *0.25

Step 3［MODULE_A表］マージンの計算

ふたたび「MODULE_A」シートにもどって，F3，G3 に下式を入力します。

これらを左の表の最下行と同じ行までコピーします。

F3 =IF(OR(C3=1, C3=3, C3=5, C3=6, C3=7, C3=8), MODULE_B!$B$4*COS(ATAN(ABS(INPUT!E3-INPUT!C3) / ABS(INPUT!D3-INPUT!B3))), 0)
G3 =IF(OR(C3=5, C3=6, C3=7, C3=8), MODULE_B!$B$4*SIN(ATAN(ABS(INPUT!E3-INPUT!C3) / ABS(INPUT!D3-INPUT!B3))), MODULE_B!$B$4)

念のため，上の作業をおえた状態を提示します。

Step 4［MODULE_B表］直交座標の作成

「MODULE_B」シートに移り，下表の内容をA8，A9に入力します。

このとき，A9 から入力を始めたほうが混乱を生みません。

A9 1
A8 =IF(OFFSET(INPUT!$A$2, A9, 0)<>"", OFFSET(INPUT!$A$2, A9, 0), "")

つづいて，パターンとゆとりを踏まえたうえでの軌跡の直交座標を求めます(セルB8，C8，B9，C9)。

B8 =INDEX(INDIRECT($B$1), MATCH($A8, INDIRECT($B$2), 0), MATCH(B$6, INDIRECT($B$3), 0))
C8 =INDEX(INDIRECT($B$1), MATCH($A8, INDIRECT($B$2), 0), MATCH(C$6, INDIRECT($B$3), 0))
B9 =IF(A8<>"", IF(OR(OFFSET(MODULE_A!$C$2,A9,0)=1, OFFSET(MODULE_A!$C$2,A9,0)=5, OFFSET(MODULE_A!$C$2,A9,0)=8), INDEX(INDIRECT($B$1), MATCH($A8,INDIRECT($B$2),0), MATCH(B$7,INDIRECT($B$3),0))-OFFSET(MODULE_A!$F$2,A9,0), IF(OR(OFFSET(MODULE_A!$C$2,A9,0)=3, OFFSET(MODULE_A!$C$2,A9,0)=6, OFFSET(MODULE_A!$C$2,A9,0)=7), INDEX(INDIRECT($B$1), MATCH($A8,INDIRECT($B$2),0), MATCH(B$7,INDIRECT($B$3),0))+OFFSET(MODULE_A!$F$2,A9,0), IF(OR(OFFSET(MODULE_A!$C$2,A9,0)=2, OFFSET(MODULE_A!$C$2,A9,0)=4), INDEX(INDIRECT($B$1), MATCH($A8,INDIRECT($B$2),0), MATCH(B$7,INDIRECT($B$3),0)), IF(OFFSET(MODULE_A!$C$2,A9,0)=9,B8)))), NA())
C9 =IF(A8<>"", IF(OR(OFFSET(MODULE_A!$C$2,A9,0)=2, OFFSET(MODULE_A!$C$2,A9,0)=5, OFFSET(MODULE_A!$C$2,A9,0)=6), INDEX(INDIRECT($B$1), MATCH($A8,INDIRECT($B$2),0), MATCH(C$7,INDIRECT($B$3),0))-OFFSET(MODULE_A!$G$2,A9,0), IF(OR(OFFSET(MODULE_A!$C$2,A9,0)=4, OFFSET(MODULE_A!$C$2,A9,0)=7, OFFSET(MODULE_A!$C$2,A9,0)=8), INDEX(INDIRECT($B$1), MATCH($A8,INDIRECT($B$2),0), MATCH(C$7,INDIRECT($B$3),0))+OFFSET(MODULE_A!$G$2,A9,0), IF(OR(OFFSET(MODULE_A!$C$2,A9,0)=1, OFFSET(MODULE_A!$C$2,A9,0)=3), INDEX(INDIRECT($B$1), MATCH($A8,INDIRECT($B$2),0), MATCH(C$7,INDIRECT($B$3),0)), IF(OFFSET(MODULE_A!$C$2,A9,0)=9,C8)))), NA())

Step 5［MODULE_B表］シートの仕上げ

最初の1項目(ここでは"product1")のデータ範囲に空白行をひとつ加えた領域(これを「ユニット」と呼ぶことにします)を選択して，このユニットを最初の想定の項目の数だけコピーします。

具体的に，ここでは最大で10個の項目を想定していますので，7+3×10=37行目までフィルハンドルを引っ張れば条件を満たすことができます。

グラフ部分

Step 6［グラフ］before, after 系列の設定

「INPUT」シートの下図パープルの領域(10個の項目を想定した都合上，実際に入力された範囲より広く選択したもの。以下に同じ)をデータソース(「系列のX」をB列，「系列のY」をC列)にして散布図をつくります。このとき，系列の名称として黄色のセルを当てておきます。

このまま処理をつづけてもいいかとも思いますが，やはり「INPUT」シートと成果物たるグラフが同居するのは少しきまりの悪さも感じるところですので，ここではこのグラフを切り取って，あたらしいシート「OUTPUT」に移動させたいと考えます。

このグラフに，先と同様「INPUT」シートの下図パープルの領域をあたらしい系列として加えます(系列名には黄色のセルを指定)。

上述の作業をおえると，グラフは下のような状態となります。ここでの例にいう“before”が青いマーカーで，“after”がオレンジのマーカーによって示されています。

Step 7［グラフ］アロー系列(軌跡)の作成

つづいて「MODULE_B」シートの下の領域(この例では37行目まで)をあたらしい系列として先のグラフに追加します。系列の名称は任意のものでかまいませんが，ここでは「Arrow」としてすすめたいと思います。

「Arrow」系列のグラフの種類を「散布図(直線)」に変更します。

「Arrow」系列の線の終端を任意の矢印に変更します。

Step 8［グラフ］その他の書式設定

マーカーのサイズを任意のものに設定し，

いずれかの系列に「INPUT」シートのセルA3以下をソースとするデータラベルを振っておくと，項目の判別が容易となります。

なおversion2010で項目名のデータラベルを設定するには，個人的にはこちら(外部リンク: 散布図の作成―"初心者のためのOffice講座")の解説での方法が容易かと思います。

Step 9軌跡付き散布図の完成｜マージンの調整

残りの細かな部分の書式設定を任意におこなって，軌跡付き散布図の完成です。

このとき，「MODULE_B」シートの「Margin」の値を既定のものから任意のものに変更(大きく)することで，マーカーと軌跡の突端との間隙を拡げることが可能です(下図にいうパープルの部分の幅。ただしこの場合，マーカーが最も近接する組み合わせの項目に関して，軌跡が破たんしない程度の値にとどめておく必要はあります)。

Step 10項目を増やす場合

参考までに，最初に想定した最大項目数で「INPUT」シートを埋めたとき，グラフは次のように変化します。

限られた範囲にマーカーが凝集する場合，バージョン2013以降に限っては，番号によるラベルをマーカーの中央に配置した方が良好な見栄えを生むかもしれません。

このように，ここで作成したグラフは「INPUT」シートの内容の変化に追従するので，別のデータを随時あてがう“使いまわし”が可能です。

その他の参照