BDAstyle

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

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

1.イントロダクション

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

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

ある製品系の強度において重視される指標X,Y2変数)があります。今回,この製品系のうち3つの製品(product 1-3)に,強度に関して科学的有効性をうたう新薬剤をその製造過程で投入可能であることがわかりました。そこで,これを試験的に投入しX,Yの値を観測して次表を得ています。表の左側は投入前における測定値を,同右側は投入後における測定値です(以下これを「マーカー表」と呼びます)。

初期データ

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

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

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

先の表のように,この作例では3項目(ここでは製品の数)で作図していきます。それ以外の場合でも,この作例と同様のシート構成を一度仕上げて,必要に応じて行を足していくなどした方が,シートの修正が容易になります(修正箇所はこのページの最後で触れます)。


2.手順|表部分

Step 1[マーカー表]軌跡パターンの探索

マーカー表右側に下図のような見出しをもつ表をつくります。

下表の計算式を図の対応する色のセルに入力(orコピー。以下に同じ)し,これ(3つのセル)を項目の数(ここでは製品の数=合計3つ)だけ下方にコピーします。

G4 =IF((D4-B4)>0, 1, IF((D4-B4)<0, -1, 0))
H4 =IF((E4-C4)>0, 1, IF((E4-C4)<0, -1, 0))
I4 =IF(AND(G4=1, H4=0), 1, IF(AND(G4=0, H4=1), 2, IF(AND(G4=-1, H4=0), 3, IF(AND(G4=0, H4=-1), 4, IF(AND(G4=1, H4=1), 5, IF(AND(G4=-1, H4=1), 6, IF(AND(G4=-1, H4=-1), 7, IF(AND(G4=1, H4=-1), 8, 9))))))))

なおこの表は,beforeafterで描く軌跡のパターンを判別するために用います。ここで言う「パターン」は,9方向を下図のようにして割り当てます(番号)。

Step 2[アロー表]見出しの作成

マーカー表の下に,下図のような見出しを組み立てます(以下この表を「アロー表」と呼びます)。

アロー表の行見出しを作ります。

すべての項目名(ここでは製品名)と,1から始まる番号とを交互に連続で下方に付加していきます。

Step 3[アロー表]マージンの設定

アロー表"margin"直下にとりあえずの数字として1を入力しておきます。

この値は,軌跡の突端を主に見栄え的な意味において調整する目的で利用します。

つづいて"x2""y2"直下に下表の対応する色の数式を入力し,これを項目の数(ここでは製品の数=合計3つ)だけ下方にコピーします。

この値は,先の軌跡の突端をつくるために必要な,X軸・Y軸に関する具体的なマージンを求めるものです(三角関数などをもとにして)。

G12 =IF(OR(I4=1, I4=3, I4=5, I4=6, I4=7, I4=8), $E$12*COS(ATAN(ABS(E4-C4)/ABS(D4-B4))), 0)
H12 =IF(OR(I4=5, I4=6, I4=7, I4=8), $E$12*SIN(ATAN(ABS(E4-C4)/ABS(D4-B4))), $E$12)

Step 4[アロー表]直交座標の作成

アロー表の下図の各セルに下表の対応する色の数式を入力します。

すべて入力したら,この4つのセルを1ブロックとして,これを項目の数(ここでは製品の数=合計3つ)だけ下方にコピーします。

B12 =INDEX($B$4:$E$6, MATCH($A12,$A$4:$A$6,0), MATCH(B$10,$B$3:$E$3,0))
C12 =INDEX($B$4:$E$6, MATCH($A12,$A$4:$A$6,0), MATCH(C$10, $B$3:$E$3, 0))
B13 =IF(OR(OFFSET($I$3,A13,0)=1, OFFSET($I$3,A13,0)=5, OFFSET($I$3,A13,0)=8), INDEX($B$4:$E$6, MATCH($A12,$A$4:$A$6,0), MATCH(B$11,$B$3:$E$3,0))-OFFSET($G$11,A13,0), IF(OR(OFFSET($I$3,A13,0)=3, OFFSET($I$3,A13,0)=6, OFFSET($I$3,A13,0)=7), INDEX($B$4:$E$6, MATCH($A12,$A$4:$A$6,0), MATCH(B$11,$B$3:$E$3,0))+OFFSET($G$11,A13,0), IF(OR(OFFSET($I$3,A13,0)=2, OFFSET($I$3,A13,0)=4), INDEX($B$4:$E$6, MATCH($A12,$A$4:$A$6,0), MATCH(B$11,$B$3:$E$3,0)), IF(OFFSET($I$3,A13,0)=9,B12))))
C13 =IF(OR(OFFSET($I$3,A13,0)=2, OFFSET($I$3,A13,0)=5, OFFSET($I$3,A13,0)=6), INDEX($B$4:$E$6, MATCH($A12,$A$4:$A$6,0), MATCH(C$11,$B$3:$E$3,0))-OFFSET($H$11,A13,0), IF(OR(OFFSET($I$3,A13,0)=4, OFFSET($I$3,A13,0)=7, OFFSET($I$3,A13,0)=8), INDEX($B$4:$E$6, MATCH($A12,$A$4:$A$6,0), MATCH(C$11,$B$3:$E$3,0))+OFFSET($H$11,A13,0), IF(OR(OFFSET($I$3,A13,0)=1, OFFSET($I$3,A13,0)=3), INDEX($B$4:$E$6, MATCH($A12,$A$4:$A$6,0), MATCH(C$11,$B$3:$E$3,0)), IF(OFFSET($I$3,A13,0)=9,C12))))

この式により,パターンとマージンを踏まえたうえでの軌跡の直交座標を求めます。

3.手順|グラフ部分

Step 5[グラフ]散布図の作成

最初の1群(ここでは"before")のデータ範囲のみを選択して,散布図を描きます。

Step 6[グラフ]before, after 系列の設定

「データソースの選択」ダイアログを呼び出して,設定したばかりのY1系列の系列名を下図のように変更します("Y1"ではメンテナンス時に分かりにくいので"before"に変更)。

ダイアログをそのままに,2群目(ここでは"after")を下図のように追加します。

念のため,シート上で図示すると「系列名」「Xの値」「Yの値」が示すセルないしセル範囲は下図のとおりです。

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

1つ目のアロー系列を追加します。

具体的には,下図の設定のとおりです。

こちらも念のためシート上で図示すると,「系列名」「Xの値」「Yの値」が示すセルないしセル範囲は下図のようになります。

残りの項目の数のアローを繰り返し追加(ここでは製品の数=合計3つ)して,下図のような状態の散布図を用意します。

Step 8[グラフ]グラフの種類の変更と軌跡の作成

任意の系列を選択したうえで,「グラフの種類の変更」ダイアログを呼び出します。

アロー("before""after"以外のすべての要素)に関して,「散布図」から「散布図(直線)」に変更します。

アローの「終点矢印の種類」(必要により「サイズ」もあわせて)を任意のものにそれぞれ変更し…

マーカー他の書式を任意に設定します。必要により,マーカーのラベル(データラベル)も設定します※。

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

Step 9[グラフ]凡例の作成

凡例を追加し2群の区別を容易にします。

ただアロー単体で1系列を消費している都合上,凡例にもそれらが表示されてしまいます。この場合これは好ましくないので,別途凡例上のアロー系列を個別に選択削除(DELETE)を繰り返し…

下図のように修正します。

Step 10軌跡付き散布図の完成

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

Step 11マージンの使い方と値の修正

"margin"の値を増減することによって,下図の青い丸によって示される間隙について柔軟に調整できます。

値を増やせば間隙は広がります。どの程度増やせばよいかは扱うデータないしはグラフの大きさによって変わります(相対的な目安です)。

もちろん,マーカー表を直接修正することも可能です。値にあわせて,表の再計算及びグラフの再描画が自動的になされます。

Step 12項目の数を変えるときのTips

項目を増やしたり減らしたりする場合は式の修正が必要です。

この場合,Step 4式の強調部分で修正が必要です。ただ正攻法での修正は量が多くて大変なので,式の強調部分(異なる2つの領域の指定)に対してテキストエディタやWordなどで正しい範囲に文字列として直接置換してしまった方が簡単だと思います(修正したものを再度貼り付けコピー)。

参考)マーカー表に1行挿入して1項目を追加した場合

その他の参照