BDAstyle

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

ノードとエッジによる,“つながり”や“ながれ”あるいは“かかわり”のビジュアライゼーション with Excel 1/3

1.無向グラフの場合

1.イントロダクション

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

たとえば,部門A・B・Cを揃える組織において,メンバーの視点から部門のつながりを可視化してみたいと考えたとします。

部門A・B・Cは順に5,4,3名のメンバーを擁します。

各部門のリーダーはメンバーを率い,他部門のリーダーと連携します。

下の図は,上記のことを,エクセルと同じOfficeファミリの一員であるパワーポイントのスライドの上で,「図形」機能を使って表現してみたものです。このとき,描画にあたっては“何となく揃えてみる”こと以外に特段配慮した点はありません。

Officeファミリでこうした図を描くときは,これらに搭載された図形機能の「楕円(または結合子)」と「直線」が役立ちます(一部を除く)。「楕円」はノードを,「直線」はエッジを描くのに利用でき,これらは接続ポイントで容易に接着することができます。これの何が便利かといえば“容易に修正が効く”ことに尽きるでしょう。もちろん,下図でも両者を使用しています。

しかし,いい面があれば悪い面があるのも常で,接続ポイントの少なさに弱点を抱えます。いや,たとえ限られた場所同士でもノードの中央での結線ができたとしたなら,おそらくフローチャートを描くこと以外の,こうした本来の用途の範囲外でもビシバシ使うことができたのでしょうが,残念ながら現状ではノードの中央で結線することは(正攻法では)不可能です。

それゆえ,Officeファミリで図形機能によって無向,ないしは有向のグラフを描くことは,おそらくは誰もが最初に想像するよりはやっかいなものと言っていいかと思います。たとえば下図の青い線の結線箇所を注視すると分かりますが,こうした可視化の方法ではいくらかの不自然な部分を甘受せざるをえなくなります。

加えて,“見た目”を気にするなら,いわゆる「美的基準」(描画規約・描画規則,詳しくは杉山(1993), pp.8-13.)といったなかなかにディープな視座を組み入れる必要が生じます。いくつかの美的基準に忠実であればどうなるかを端的に眺めるには,たとえば

といった頁が好例かと思いますが,個人的には,これらには「端正」といった修辞に近いような美しさを覚えます。

こういったある意味でやっかいな部分は,人物相関図などを念頭に「ただの丸い円をつないだ図」が描ければよかったはずのユーザーを,解決からさらに遠ざけてしまいます。

とまれ筆者は,たとえ知識に欠ける状態ですべてが叶わずとも,若干の美的基準と照らしてみて―――具体的には,同心円・曲線状配置といった施策の容易なわずかな点のみに,ここで配慮を加えてみたいと考えました。

パワーポイントの上でそうした点を実現するため,さしあたり“ものさし”となる円を背面に敷いてみて,これらの円周上の線を目安にしてノード位置の修正を試行錯誤で加えていったところ,下図のような状態となりました(実際には“ものさし”は完成したら消去します)。

筆者が描いたいちばん最初の図と比べ,ちょっとばかり躍動的な印象が出てきた!?ことに気をよくして,より直観的な表現を目指そうと,今度はノードをピクチャに置き換えてみたいといった欲が出てきました。

……しかしながら,実際にただ1つを置き換えてみたところで,どうやらノードとエッジとの接合ポイントがますます絞られてしまい,表現がより窮屈にならざるを得ないことに気づかされました。

「描きたいのはこれじゃない!」のは明白です。おまけに,美的基準を上手に汲めない筆者の前には,もともと高いハードルがそびえたちます。

前置きが長くなりましたが,この頁の趣旨は,そうした難しいことを頭から抜いてツールに任せ,“つながり”のグラフを作ってしまおうといったものです。ここでは無向グラフのみを扱いますが,その描画法としては力指向アプローチに依存します。

ただし,力指向アプローチは一般的なエクセルの用途と照らしてより重い計算を必要とするため,おそらくすべてをエクセルに投げてしまうのは,“処理速度”を解決できるコードが書けないと難しいかと考えます(筆者には困難です)。したがって,座標の計算に関しては別途「R」の力を借りていきたいと思います。

であれば当然「なら全部Rでやれ」とも思わなくもありませんが,個人的には,やはりエクセルの最大の武器のひとつである,直観的・インタラクティブな操作のみで個性豊かなグラフを描きあげることができる点が,何物にも代えがたく好みなのです。

―――ということでここでは「R」も併用しますが,端正な座標を目測でとるのが得意な方であれば,エクセルのみで完成させることは可能です。

具体的に目的とするものは,次の円形のノードのグラフが1点と

ピクチャ形式のノードのグラフの2点です。以下,その工程です。

illustration: "Girl's Design Materials"

作図にあたり参考にした書籍およびWebサイト


2.工程|前段

Step 1[隣接行列シート]ノードの配置

“つながり”を行列によってあらわします。ここでは,このためのシートを「隣接行列」と名付けます。

「隣接行列」シートの縦方向ないしは横方向にすべてのノードを書き出して,それらを残る一方に対し「行列を入れ替える」でコピーします。

scrollable

Step 2[隣接行列シート]“つながり”の書き出し(1)

各ノードに関し,行方向に“つながり”を示していきます。

たとえばここでの例の場合,Step 0の設定では,ノード「Sarah」は他部門のリーダー2人と自部門のメンバー4人とのエッジを持っていることがわかります。したがって,「Sarah」行に関しては,列見出しの該当するノードとの交点にそれぞれ「1」を立てていきます。

scrollable

残るメンバーに関しても同様の作業をおこないます。これを終えると,下図のようになります。

scrollable

Step 3[隣接行列シート]“つながり”の書き出し(2)

この行列の中の未入力の場所,換言すればエッジを持たないものを明示します。

最初にこの行列以外の,たとえばシートの外れにでも「0」と入力し,これをコピーします。

次に行列の見出しを除くすべての部分を選択し……

「形式を選択して貼り付け」から……

演算「加算」の貼り付けを選びます。

これにより,「1」以外の空のセルが「0」で埋まります。この時点で,一時的に利用する目的で入力した先の0を削除しておきます。

scrollable

Step 4[座標シート]見出しの作成(1)

あたらしい別のシートに,グラフ描画に必要なデータを用意していきます。ここではこのシートを,便宜上「座標」シートと呼ぶことにします。

さしずめ,以下の場所にそれぞれ見出しを入力しておきます。

scrollable

ピクチャノードを利用する場合は,セルB1に,素材を格納してあるフォルダまでのフルパスを入力しておき(したがってすべての素材が同じフォルダの中に準備されている必要があります)……

「隣接行列」シートから,縦に並べたすべてのノード(行見出し)をコピーして,この「座標」シートに貼り付けます。

ピクチャノードを利用する場合には,「File」欄に,各ノードに対応するファイルの名前をそれぞれ指定しておきます。

3.工程|中段

Step 5[R]igraphパッケージの読み込み

Rを起動します。

ここでは別途「igraph」パッケージを必要とします。これがRにインストールされていない場合,インストールを要します。

インストールされているかあいまいな場合,たとえばコンソールに

と入力すれば状態がわかります(TRUE:インストール済み)。

scrollable

未インストールの場合(上でFALSEが返ってきた場合),パッケージのインストールから……

「Japan(Tokyo)」を選び……

「igraph」を選択してインストールをしておきます。

とまれ,インストールされていれば

でパッケージが呼び出せます。

scrollable

Step 6[R]ノード座標の計算

エクセルに戻り「隣接行列」シートの行列を見出しも含めて選択し,クリップボードにコピーしておきます。

再びRに移って,コンソールに

と入力します。これにより,オブジェクトdatにクリップボードの内容を見出し付きの行列として保持させます。

scrollable

続けて

と入力します。これにより,dat“方向性を持たない”エッジリストに転換し,この結果をオブジェクトgに保持させます。

scrollable

試しにコンソールでgの内容を見てみると,この例では下の図のような,「AからB」といったかたちで示されるエッジが,先の行列からすべて拾われていることがわかります。

scrollable

このgの内容がエクセルで利用したいもののうちの1つですので,これをテキストファイルに書き出します。

ここではテキストファイルを作業ディレクトリにそのまま書き出します。したがってファイル名(edgelist.txt)のみしか記していませんが,任意の場所に出力したい場合,その場所までをフルパスで明示する必要があります。

scrollable

gをもとに,任意のアルゴリズムを使ってノードの座標を求めます。結果はオブジェクトlayに渡します。

さて,そのアルゴリズムですが,ここではKamada-Kawai layout algorithmを使用したいと思います(その他のアルゴリズムに関しては後述)。

scrollable

試しにコンソールでlayの内容を見てみると,すべてのノードの座標が返ってきていることがわかります。

scrollable

このlayの内容がエクセルで利用したいものの残る1つですので,これをテキストファイルに書き出します。

ここでも同様,テキストファイルを作業ディレクトリに書き出すのでファイル名(node.txt)のみしか記していません。任意の場所に出力したい場合,その場所までフルパスで明示することが必要です。

scrollable

Step 7[座標シート]計算結果をエクセルへ(1)

先のStepで作成したnode.txtを開きます。

このファイルの内容をすべて選択し,クリップボードにコピーします。

「座標」シートのセルB4をアクティブにして,貼り付け――テキストファイルウィザードを使用,とたどります。

ダイアログの「元のデータ形式」が「カンマやタブなど――」側が選択されていることを確認して,完了を返します。

同様の作業をedgelist.txtにおいても繰り返し,セルF4にデータを貼り付けます。

scrollable

このとき,G列の内容を行位置を保ったままI列に移します(有向グラフの場合[次頁以降]との兼ね合いによる処理。次に同じ)。

F列とI列のノード名の間を,マイナス記号(-)で埋めておきます。

scrollable

Step 8[座標シート]計算結果をエクセルへ(2)

エッジに関する「ex1」「ex2」「ey1」「ey2」を埋めていきます。

具体的に,これらは左のノード表から拾います。

J4 =VLOOKUP(F4, $A$4:$C$15, 2, FALSE)
K4 =VLOOKUP(I4, $A$4:$C$15, 2, FALSE)
L4 =VLOOKUP(F4, $A$4:$C$15, 3, FALSE)
M4 =VLOOKUP(I4, $A$4:$C$15, 3, FALSE)

scrollable

4.工程|後段

Step 9[座標シート]散布図で“つながり”を描く

散布図を使って,下図「座標」シートの左側の彩色部分をソースにノード(「散布図」)を,右側のそれをソースにエッジ(「散布図(直線)」)を描きます。

ただし,エッジについては,エッジの別に1系列を使って描く必要があります。たとえば,「Sarah--Clarice」エッジでは,セルJ4:K4Xに,セルL4:M4Yに割り当てこれを1つの系列とします。つまりこの例では,ノードに1系列,エッジに12系列の計13系列を消費してグラフを描画することとなります。

scrollable

当然,エッジの数がより大きくなったときは割の合わない手間を要するので,可能な環境であればマクロで処理したほうがbetterです。

下のコードは,上述のシート構成の場合のみ機能します。

Sub DrawUndirectedGraph()
' "つながり" 描写のための無向グラフ ver.1.0.0
' ***http://bdastyle.net/tools/scatterplot/network-visualization-based-on-force-directed-layout-1.html
' ***by hawcas 2017

Dim tgtNode As Range ' Nodeのデータ範囲
Dim tgtEdge As Range ' Edgeのデータ範囲
Dim serNo As Integer ' 系列番号
Dim serName As String ' 系列名
Dim myDir As String ' 画像ディレクトリ
Dim adLabel As String ' ラベルのアドレス
Dim i As Long ' カウンタ
Dim j As Long

Application.ScreenUpdating = False

' ノードに関するセル範囲を格納
Set tgtNode = Range("a3").CurrentRegion
Set tgtNode = tgtNode.Range("a2").Resize(RowSize:=tgtNode.Rows.Count - 1, columnsize:=4)

' エッジに関するセル範囲を格納
Set tgtEdge = Range("f3").CurrentRegion
Set tgtEdge = tgtEdge.Range("a2").Resize(RowSize:=tgtEdge.Rows.Count - 1, columnsize:=8)

' 散布図を挿入
Range(tgtNode.Range("b1"), tgtNode.Range("b1").Offset(tgtNode.Rows.Count - 1, 1)).Select
ActiveSheet.Shapes.AddChart(xlXYScatter).Select

' ノード系列の基本的な書式設定
serNo = 1
With ActiveChart
    .HasLegend = False
    With .SeriesCollection(serNo)
        .Name = "Node"
        .MarkerStyle = xlMarkerStyleCircle
        .MarkerForegroundColorIndex = xlColorIndexNone ' 枠線なし
        .MarkerBackgroundColor = RGB(80, 80, 80) ' マーカーの色
    End With
End With

' エッジ系列の挿入
For i = 0 To tgtEdge.Rows.Count - 1
    serName = ""
    For j = 0 To 3
        serName = serName & tgtEdge.Range("a1").Offset(i, j)
    Next
    With ActiveChart.SeriesCollection.NewSeries
        .XValues = Range(tgtEdge.Range("e1").Offset(i, 0), tgtEdge.Range("e1").Offset(i, 1))
        .Values = Range(tgtEdge.Range("g1").Offset(i, 0), tgtEdge.Range("g1").Offset(i, 1))
        .Name = serName
        .Border.Color = RGB(170, 170, 170) ' 線色
        .Format.Line.Weight = 2 'pt, 線幅
    End With
Next

' エッジを直線に
For serNo = 2 To tgtEdge.Rows.Count + 1
    With ActiveChart
        .SeriesCollection(serNo).ChartType = xlXYScatterLinesNoMarkers ' 散布図→直線
    End With
Next

' ピクチャノードか否か
For i = 0 To tgtNode.Rows.Count - 1
    Select Case tgtNode.Range("d1").Offset(i, 0).Value
    Case "" ' File未指定の場合→円形ノード
        With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(i + 1)
            .MarkerSize = 30 ' pt, マーカーサイズ
        End With
    Case Else ' File指定の場合→ピクチャノード
        myDir = Range("b1").Value & "\" & tgtNode.Range("d1").Offset(i, 0).Value
        With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(i + 1)
            .MarkerStyle = xlMarkerStylePicture
            .Fill.UserPicture (myDir)
        End With
    End Select
Next

' データラベルを挿入
adLabel = "'" & ActiveSheet.Name & "'!" & _
    Range(tgtNode.Range("a1"), tgtNode.Range("a1").Offset(tgtNode.Rows.Count - 1, 0)).Address
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
    .HasDataLabels = True
    With .DataLabels
        .Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, (adLabel), 0
        .ShowRange = True
        .ShowValue = False
        .Position = xlLabelPositionBelow
    End With
End With

Application.ScreenUpdating = True

End Sub

Step 10グラフの完成

いずれのアプローチにしろ,円形のノードの場合は下図のような,

ピクチャノードの場合は下図のようなアウトプットが導けます。あとは,縦横軸の表示範囲を調整する,あるいはデータラベルに書式設定を加えるなどして任意の加工を続け,グラフを仕上げます。

下,別系列を加えた内線番号図としての展開です。あたらしく組織にjoinした人でも,これなら中の人の力関係を察(ry

Step 11[R:付記]その他のアルゴリズム

力指向に関する他のアルゴリズムは

を利用することができます。これらを選択する場合のコマンドは,順に

となります。参考までに,下図はKamada-Kawai layoutに代えてそれらを適用しています。

cf. Graph layouts ―"R igraph manual pages"

Next

次頁では矢印付きのエッジで“ながれ”を表現します。

その他の参照