ひとりマーケティングのためのデータ分析

手順解説 | Excel(エクセル)でおこなうビジネスデータの分析

How-to

ベイジアンネットワークのもとでの
GUIな確率推論

ベイジアンネットワークは,Wikipediaからの引用(外部リンク)に頼れば,

因果関係を確率により記述するグラフィカルモデルの1つで,複雑な因果関係の推論を有向非巡回グラフ構造により表すとともに,個々の変数の関係を条件つき確率で表す確率推論のモデルである。

とされるものです。

深淵な領域ですが,知見の足らないこのサイトでも淵のみなもに足の指の先をわずかに浸し,最も単純な部類のネットワークを触っていきたいと思います。単純―――であれ,ハマればなかなかの恵沢に与れるのがベイジアンネットワークの Good なところでしょう。なおここでいう単純な部類とは,すべて2値の変数少ないノード構造は所与でありかつ簡素―――といった特徴をもつものを言います。

さて,ここでの状況設定としては,13人のメンバーを擁するある会社の審査部門を想定したいと思います。

審査部門・13人のメンバー

審査部門には営業部門より “事案” が投入されます。審査部門は,この事案に対し ACCEPT(以下 “YES”)か REJECT(以下 “NO”)を返すのが仕事です。

事案の投入

審査のルールは次のようになっています。
投入された事案は,最初に下の白いドーナツの部分の「第3層」に位置するおのおのによって,独立して精査されます。

第1層が審査

結果(YES, NO)はすべて直属の上長である「第2層」に上がります。
3人の上長は,直属の部下から上がってきた結果を “自分の裁量のもとに” 参考にしながら,こちらも独立して判断を下します。

第2層が審査

第2層が下した結果は,それぞれ「第1層」に上がります。
この位置にいる「リーダー」もまた,“自分の裁量のもとに” 第2層の下した結果のみを参考にして YES か NO かを量ります。―――これを,部門の最終判断として営業部門に返します。

第1層が最終判断

事案ごとに,審査部門ではメンバーおのおのの判断を記録しています。そこで,ここしばらくのそれらの記録に照らして,YES, NO の割合(事前確率[第3層]あるいは条件付き確率[第2層, 第1層])を下表のように一覧としてまとめてみました。

事前確率およびCPT

これによって,たとえば先のメンバー図の「LEADER」以外の全員が YES を付けたことを(第三者の観測者が)観測したとき,「LEADER」がどう出るかを量る―――といったようなことが可能になります。具体例として,「LEADER」が他の全員とは反対の NO を付ける確率を問うならば,

すべての変数(メンバー)の同時確率は

P(Al,An,J,N,H,B,Sal,Li,T,Sar,C,E,Le)=P(Al)P(An)P(J)P(N)P(H)P(B)P(Sal)P(Li)P(T)P(Sar|Al,An,J,N)P(C|H,B,Sal)P(E|Li,T)P(Le|Sar,C,E)

であるので,右辺に先の一覧を参照して値を代入(茶色は当該メンバーの YES,ピンクは NO の値)していくと

=0.5×0.45×0.6×0.5× 0.35×0.7×0.35× 0.5×0.5× 0.9×0.95×0.1× 0

となります。すなわちこの場合,「LEADER」が NO の判断をつけることは皆無(0%)であることを推測できます。

「お! いろんなケースを柔軟にシミュレーションができそう⁉」
な感触を得たところで,より現実的に,今度は限られたメンバーの結果のみを観測できるような場合(他のメンバーが付した結果は一切わからない!)も試してみたいと思います。

これまた同様に具体的な例を用意して―――たとえば「Hana」が YES をつけたことによって,はたして「LEADER」の YES の確率は変動するのかを探っておきたいとします。ということで,

P(Al,An,J,N,H,B,Sal,Li,T,Sar,C,E,Le)=P(Al)P(An)P(J)P(N)P(H)P(B)P(Sal)P(Li)P(T)P(Sar|Al,An,J,N)P(C|H,B,Sal)P(E|Li,T)P(Le|Sar,C,E)

の右辺に同じく値を代入していくわけですが,今回は困ったことに黒字部分のメンバーがどちらの結果を出したのかが不透明です。ゆえに先のようにすんなりと計算ができそうになく,目的を果たすためにはどうやらさらなる手数が必要となりそうです。

しかしながら,他方でそうした不足を解決するいくつかの優れたツールが存在します。たとえば「Weka」というアプリを使えば,先のメンバー図も GUI で直感的にデザインすることができます。またメンバーのすぐ隣に確率を表示(この場合は下図のメンバーごとの YES, NO。なお数字は 0.いくつ の “いくつ” 部分のこと)させることができるので,状況の理解が何よりも容易になります。

Weka, ベイズネットワークエディタ

もちろん先につまずいた計算も,Weka では設定で一瞬です。「Hana」の上長「Clarice」の動きも含め,最終決定者「LEADER」の YES がどの程度動くのか,下の図とすぐ上の図とを比較すれば判断がつくかと思います(説明を加えるなら,同じグループの「Beth」や「Sally」で同様の計算をしてみると,「LEADER」の YES を増すのは「Hana」が最も大きくなることを掴めます)。

Weka, ベイズネットワークエディタ

このページでは,こうした柔軟に推論をおこなえるしくみをエクセルの上で組み立てていきます。とはいえアプローチとしてはいろいろな手段があると思うので,あくまで一つの例としての領域を超えるものではありません(この例では処理の一部でやむをえずマクロを使用します)。もちろん効率を至上とするなら,エクセルではない他のツールで処理されることが最善であることを加えておきます(また WEKA に限れば,環境によっては意図しない挙動を解決できない場合もあります)。

以下,エクセルを使ったベイジアンネットワークの描画と,推論のために望まれる UI 等の設置に関する,一連の流れについての解説です。ここでは Excel 2016 で手続きを追っています。Excel 2013およびExcel 2010 についても基本的には同じ流れをとることができますが,一部にボタン等の名称が異なる箇所が発生します(この場合,当該箇所に明記しています)。

用途ごとにシートをわける

晴花

1

ひとつのシートにいろいろな要素が混在するとこの場合はちょっと窮屈なので,シートを分けて使いたいと思います。

具体的には

  • NETWORK
  • CPT
  • TABLE

3枚のシートを用意しておきます。

シートの挿入

シート「NETWORK」

晴花

2

しばらくはシートNETWORKの上での処理をすすめます。

まず任意の図形を使って,各メンバー(以下「ノード」)がセルの枠に上手に填まるように調整しつつ(「配置」機能“枠線に合わせる”)ネットワークを描画しておきます。

ここではノードは楕円を使って,親子関係※はコネクタで描いています。


「親」と「子」

有向のネットワークにいう親は矢印の始点のノードが,子は矢印の終点のノードが該当します。ここで扱う組織における職位のようなものに関連付けると混乱しやすい点注意です。

scrollable

ベイジアンネットワーク

晴花

3

では適当なノードから次の作業を始めます。このノードの横にフォームコントロールの方のチェックボックスを配置します【下図上段】。

このとき,コントロールを枠線にピタリとはまるような設定下で調整しておくと,あとのコピペや見栄えの上で好ましいかと思います。

つづいてコントロールのラベルを,とりうる2値の一方(ここでは YES)に変更します【下図中段】。


そして,コントロールの名前をつけ(変え)ます。これはデフォルトの「チェック○○」では識別しにくいゆえです。プロシージャ名を[checkBox]+[メンバーの名前]+[ 2値のいずれか]の,先頭小文字のキャメルケースで書いているので,チェックボックスの名前もこれにしたがってつけることにします【下図下段】。

scrollable

scrollable

scrollable

コントロール名の変更

晴花

4

同様にして2値の残された方のチェックボックスも設置します。

また,コントロールの名前も先の規則で変更しておきます。

scrollable

晴花

5

2 つのチェックボックスを選択コピーして残りのすべてのノードにペーストします。

これらコントロール名の変更も忘れずに済ませておきます。

scrollable

チェックボックスコントロールをすべてのノードの側に配置

晴花

6

ネットワーク図と被らない位置――ここではT列としますが――にチェックボックスの状態を管理するための2元表をつくります。

縦方向にノード名,横方向にとりうる2値を並べます【下図上段】。

表の中には,とりあえずの値としてFALSEを置いておきます【下図下段】。

scrollable

チェックボックスの状態を管理する表

scrollable

晴花

7

上の管理表とチェックボックスとをリンクさせます。

たとえばチェックボックス「checkBoxAliceYes」の場合,上の管理表とリンクさせるのは「Alice」と「YES」の交点,セルU2とします。

これをすべてのコントロールについて繰り返します。

セルとのリンク

シート「CPT」

晴花

8

ここからしばらくCPTシートの処理に移ります(CPT:Conditional Probability Table)。

このシートには,導入で説明した3層ごとに区別して確率表を配置したいと思います。具体的には,下図のような見出しをおいてそれを実行することにします。

scrollable

晴花

9

上のリンクからデータを開き,内容をセルA2にコピペすると第3層から1層まですべての確率を埋めることができます(ただし第2層・第1層は「結合」列を別途埋める必要あり。次のstepで説明)。


ただし,ここで例示するにあたってはコピペでは説明の足らない部分も出てきてしまうので,手元の資料を参照しながらタイプしていくといった状況を設定したうえで,第3層から以下順に配置していくことにします。

ということでまず第3層すべてを埋めたのが下の図です。

第3層の事前確率を配置

晴花

10

つづいて第2層を埋めます。

こちらは親をもつ条件付き確率なので,YES, NO についてすべての組み合わせを埋める必要があります(「Extension:とりうる値のすべての組み合わせをつくる」を参照)。

また2つ以上の条件をもつ場合,のちの工程でこの表を参照する際に複雑な処理が必要になってしまいます。これを単純に済ませるための工夫として,「結合」という名の作業列をあわせて設置し対処します【下図上段】。

この「結合」列を埋めます。

文字通り,左の4つのノードの値をアンパサンド"&"で結合します【下図下段】。たとえばセルH4では=D4&E4&F4&G4となります。

scrollable

第2層の条件付き確率を配置

scrollable

文字列をマージ

Extension :とりうる値のすべての組み合わせをつくる

DEC2BIN関数で可能な場合

晴花

*1

2 値の場合,パターンは親の数をnとしたとき2nあります。

たとえばノードSarahは親を4つもっています。ゆえに組み合わせは

24

パターンとなります。

もっとも,これを愚直に1パターンずつ考えるのもたいへんなので,

2^4=16パターンをどのようにつくる?

晴花

*2

0 から24-1までの値をつくり(下図では降順で作成),

  • =DEC2BIN(A2, n)

2進数に変換します。

[セルB2]=dec2bin(a2,4)

晴花

*3

さらにMID関数などで2進数を1文字ずつにバラします。

ここでノードがとりうる2値はYES, NOなので,たとえば1を YES, 0をNOに置換すればSarah表を埋めるすべてのパターンが用意できます。

10進数→2進数→1文字ずつ分割→置換

DEC2BIN関数では十分でない場合

晴花

*4

もっともDEC2BIN関数は仕様上,1番目の引数で511までしか対応がかないません。

その点に障るような場合,次のリンク先の方法で対応が可能になります。

この方法であれば,後述の結合分布をつくるときでも困りません。

また参考として,上記の方法でも対応できない場合や2値を超える場合,あるいは2値や3値が混在するような場合には,目的にかなうマクロを組んで処理するのがbetterかと思います。これについては,たとえば次のリンク先が参考になります。

晴花

11

2層の残る2人についても同様に表を設置します。

scrollable

晴花

12

最後に第1層を完成させます。

こちらも親をもつノードなので,つくりかたは第2層の場合と同じです。

第1層の条件付き確率を配置

シート「TABLE」

晴花

13

ここからTABLE表での作業に移ります。このシートでは13の変数がとりうるすべての組み合わせすなわち同時分布をつくります。

とまれ,すべてのノード名を先頭行に見出しとして配置して【下図上段】

その末端のセルにつづくかたちで,先の見出しと区別するためP( )で括ったノード名を配置していきます【下図中段】。

その末尾にさらにつづけるかたちで,今度はギリシア文字の“パイ”を置いておきます【下図下段】。

P( ) で括った列はCPTシートから条件を満たす確率を引っぱってくる場所として,“パイ” 列はそれらの同時確率を周辺化する場所として利用するつもりです。

scrollable

左半分の見出しを作成

scrollable

右半分の見出しを作成

Π

晴花

14

13 ノードがとりうる値のすべての組み合わせを用意します。

この例では213=8192パターンと多いので,現実的には機械的に用意するしかありません。これについては「Extension:とりうる値のすべての組み合わせをつくる」で触れています。

あるいはこちらからあらかじめ用意されたデータを開き,セルA2にコピペ(形式を選択して貼り付け:テキスト)してもかまいません。

scrollable

2^13=8192の組み合わせ

晴花

15

この時点で,この表をエクセルの機能にいうテーブル化(先頭行は見出し)しておきます。

こののちの管理の上でいろいろとはかどるゆえ,です。

テーブル化

晴花

16

では見出し直下の行,最初のノードのとる値と対応する確率をCPTシートから引っぱってきます【下図上段】。

このAliceを筆頭とする第3層のノードは,ネットワークにおいて親ノードをもちません【下図中段】。

したがってテーブル上の当該行のAliceの値(セル A2)を参照して,この値の場合の確率をピックアップできるような式をつくります【下図下段】。

N2=IF([@Alice]="YES", CPT!$A$4, CPT!$B$4)

なお上の式の[]の部分は構造化参照と呼ばれるテーブル固有の参照ルールです。だからといってここでの過程では特段に意識する必要もありません。通常の式を組み立てる感覚でA2をクリックした時点で,自然とこのような表示になります。

「Alice」は親ノードをもたない第3層

「Alice」の場合のCPTシートの参照の仕方

晴花

17

下図のとおりテーブルの上で入力された計算式は,この列に自動でフィルされます【下図】。

したがってこれ以降の作業つまり第3層の残るノード,すなわちAnnieTriciaでも,見出しの直下の行のみをターゲットにしていきます。

O2=IF([@Annie]="YES", CPT!$A$8, CPT!$B$8)
P2=IF([@Johanna]="YES", CPT!$A$12, CPT!$B$12)
Q2=IF([@Nickie]="YES", CPT!$A$16, CPT!$B$16)
R2=IF([@Hana]="YES", CPT!$A$20, CPT!$B$20)
S2=IF([@Beth]="YES", CPT!$A$24, CPT!$B$24)
T2=IF([@Sally]="YES", CPT!$A$28, CPT!$B$28)
U2=IF([@Liz]="YES", CPT!$A$32, CPT!$B$32)
V2=IF([@Tricia]="YES", CPT!$A$36, CPT!$B$36)

晴花

18

つづいて第2層の最初のノードSarahを処理します【下図上段】。

この層のノードは,ネットワークにおいて親ノードをもっています【下図中段】。

したがってテーブル上の当該行の親ノードの値(A2, B2, C2, D2)と自身の値(セル J2)をともに参照して,この値の場合の確率をピックアップできるような式をつくります【下図下段】。

W2=INDEX(CPT!$I$4:$J$19, MATCH([@Alice]&[@Annie]&[@Johanna]&[@Nickie], CPT!$H$4:$H$19, 0), MATCH([@Sarah], CPT!$I$3:$J$3, 0))

「Sarah」は親ノードをもつ第2層

「Sarah」の場合のCPTシートの参照の仕方

晴花

19

こちらも同様にフィルされます【下図】。

2層の残るノードと第3層のノード(同様に親をもつノードなので),すなわちClariceEnidおよびLEADERの処理もあわせて済ませておきます。

X2=INDEX(CPT!$H$23:$I$30, MATCH([@Hana]&[@Beth]&[@Sally], CPT!$G$23:$G$30, 0), MATCH([@Clarice], CPT!$H$22:$I$22, 0))
Y2=INDEX(CPT!$G$34:$H$37, MATCH([@Liz]&[@Tricia], CPT!$F$34:$F$37, 0), MATCH([@Enid], CPT!$G$33:$H$33, 0))
Z2=INDEX(CPT!$P$4:$Q$11, MATCH([@Sarah]&[@Clarice]&[@Enid], CPT!$O$4:$O$11, 0), MATCH([@LEADER], CPT!$P$3:$Q$3, 0))

晴花

20

“パイ” 列を処理します。

ここはパターンごとにすべてのPを掛け合わせた値が欲しいので,

AA2=PRODUCT(テーブル1[@[P(Alice)]:[P(LEADER)]])

で対処します。

P()で括ったすべての確率を掛け合わす

Extension :確率をどのように求めるか

条件付き確率の定義式

晴花

*1

頁頭の設定すなわちHana(H)がYESをつけたことによって,LEADER(Le)のYESの確率はどうなるかは,下式の条件付き確率で求めます。

\[P(Le|H)=\frac{P(Le, H)}{P(H)}\]

晴花

*2

このとき,分子のほうは下図上段の式で,分母のほうは下図下段の式で計算できます。

\[P(Le, H)=\sum_{Al,An,J,N,B,Sal,Li,T,Sar,C,E=YES,NO}^{} P(Al,An,J,N,H,B,Sal,Li,T,Sar,C,E,Le)\]

\[P(H)=\sum_{Al,An,J,N,B,Sal,Li,T,Sar,C,E,Le=YES,NO}^{} P(Al,An,J,N,H,B,Sal,Li,T,Sar,C,E,Le)\]

テーブルにフィルタをかけて確率を積み上げる

晴花

*3

先につくったテーブルは,これらの式を利用することを前提として設置したものです。

どういった処理をおこなうのかを示すため,とりあえず先のテーブルに集計行を用意して話を進めるものとします。このテーブルはとりうるすべての値の組み合わせをカバーしているはずなので,このとき“パイ”列の合計も1となっているはずです。

scrollable

テーブルに集計行を加える

晴花

*4

分子のほうの式はLEADERHanaの同時確率,題意に沿えばLEADERHanaがともにYESを返す確率です。

これは,テーブルのHanaLEADER列( Pで括っていない方!)のフィルタをともに「YES」でONにしたときの「集計」値と一致します【下図上段】。

分母のほうの式はHanaのみの確率,題意に沿えばHanaYESを返す確率です。

これは,テーブルのHanaLEADERに掛かっている2つのフィルタのうちのひとつ,LEADERのフィルタを解除したときの「集計」値と一致します【下図下段】。

つまり,0.16997 / 0.35 = 0.4856 とこの場合の解答を導くことができます。

scrollable

テーブルの「Hana」「LEADER」のフィルタをYESでチェック

scrollable

テーブルの「LEADER」のフィルタを解除

ふたたび,シート「NETWORK」

晴花

21

すぐ上の「Extension」で見た計算の一部をシートNETWORKの上に実装していきます。

すでにつくったチェックボックスの管理表と少し離して,ノードと同じ行数 × 変数のとりうる値の列数 をセル範囲をとする以下のグレーの区画をその場所に充てたいと思います。

セルU17:V29を利用する

晴花

22

セルU17, V17に次式を入力し,これらをノードの数だけ下方にコピーします。

U17=IF(U2, "テーブル1[" & T2 & "]," & CHAR(34) & $U$1 & CHAR(34), "")
V17=IF(V2, "テーブル1[" & T2 & "]," & CHAR(34) & $V$1 & CHAR(34), "")

これは,上の管理表の当該セルの内容がTRUEになったときのみ文字列(SUMIFS関数の引数として利用)を表示するものです。この文字列をマクロで読み取って,「Extension」でのたとえでいう自身のフィルタをONにする目的で使用します。

SUMIFS関数の中身を文字列で作成

晴花

23

チェックボックスの横に確率を表示するパーツをつくっていきます。

任意のノードの横にまず1セット,文字色・有効桁など書式を設定したとりあえずの値「0.5」を設置します【下図上段】。

これを残りのすべてのノードにコピペします【下図下段】。

チェックボックスコントロールの横に確率を暫定的に配置

晴花

24

確率パーツを配置したセルすべてを選択します【下図上段】。

これらにエクセルの機能にいう条件付き書式のデータバーを,min:0max:1の設定で加えます【下図下段】。

条件付き書式のデータバーを加えて分かりやすく

マクロを割り当てる

晴花

25

下のコードを標準モジュールにコピペします。

なお頭の〓印のついたConstステートメント群やFunction群は修正の負担を減らす意味合いから設けてあります。このマクロは変数のとりうる値にして2値までしか対応していませんが,ここでの作例以外の適用可能な環境で利用する場合には,必要によって上記を状況に合ったものに変更すれば動作するかと思います。

ただしその場合,のちにチェックボックスとリンクさせるプロシージャ群の名前もそれぞれに変更する必要があります。

Option Explicit

' https://hitorimarketing.net/tools/bayesian-network-gui.html
' by hawcas, b20180901

Dim CheckBoxID As Integer
Dim CheckBoxBIN As Integer
Dim Numerator As String
Dim Denominator As String

' 〓"NETWORK"表の構造
Const TOTALNODE As Integer = 13 ' ノードの数を指定
Const TRUEFALSELIST As String = "U2:V14" ' チェックボックスの管理表を範囲で指定
Const FORMULASTRINGSLIST As String = "U17" ' 式を作るための表を左上角のセルで指定
' 〓"TABLE"表の構造
Const TABLENAME As String = "テーブル1" ' テーブル名
Const HEADWORDOFSUBTOTAL As String = "Π" ' 総積を計算している列の名前

Private Function GetCheckBoxOwner(ByVal checkbox_id As Integer) As String
' 〓ノード名
GetCheckBoxOwner = Application.WorksheetFunction.Choose(checkbox_id, _
    "Alice", "Annie", "Johanna", "Nickie", _
    "Hana", "Beth", "Sally", _
    "Liz", "Tricia", _
    "Sarah", "Clarice", "Enid", _
    "LEADER")

End Function

Private Function GetMarginsAddress(ByVal checkbox_id As Integer) As String
' 〓確率の表示位置(ノード名と対応)
GetMarginsAddress = Application.WorksheetFunction.Choose(checkbox_id, _
    "M3", "J1", "G3", "E6", _
    "D14", "E19", "H22", _
    "O21", "Q13", _
    "J8", "H16", "N16", _
    "K13")

End Function

Private Function GetCheckBoxValue(ByVal checkbox_bin As Integer) As String
' 〓2値の内容
GetCheckBoxValue = Application.WorksheetFunction.Choose(checkbox_bin, _
    "YES", "NO")

End Function

Private Function GetTextStrings(ByVal checkbox_id As String, _
    ByVal checkbox_bin As String) As String
' 計算式を作成(自ノード含む:分子部分)
Dim listofFormulaStrings As Range
Set listofFormulaStrings = Range(FORMULASTRINGSLIST)
Dim arraySize As Integer
    arraySize = Application.WorksheetFunction.CountIf(Range(TRUEFALSELIST), _
        True)
Dim textArray()
ReDim textArray(arraySize)
Dim arrayCounter As Integer
    arrayCounter = 1
Dim x As Integer, y As Integer
For x = 0 To 1 ' range(u17:v29)の空白でないセルの内容を配列にそのまま格納
    For y = 0 To TOTALNODE - 1
        If listofFormulaStrings.Offset(y, x).Value <> "" Then
            textArray(arrayCounter) = listofFormulaStrings.Offset(y, x).Value
            arrayCounter = arrayCounter + 1
        End If
    Next y
Next x
Dim temporaryOne As String
    temporaryOne = TABLENAME & "[" & checkbox_id & "],""" & checkbox_bin & """"
Dim myExistence As Boolean
    myExistence = CheckMyExistenceInArray(textArray, arraySize, temporaryOne)
Dim selectAll As String
    selectAll = ""
If myExistence = True Then ' 自分を含む場合
    For x = 1 To arraySize
        If textArray(x) <> temporaryOne Then
            selectAll = selectAll & "," & textArray(x) ' 自分以外で文字列を作成
        End If
    Next
Else ' 自分を含まない場合
    For x = 1 To arraySize
        selectAll = selectAll & "," & textArray(x) ' すべての配列で文字列を作成
    Next
End If
selectAll = selectAll & "," & temporaryOne ' 末尾に自分を強制的に加える

GetTextStrings = selectAll

End Function

Private Function GetTextStringswithoutMe(ByVal select_all As String, _
    ByVal checkbox_id As String, ByVal checkbox_bin As String) As String
' 計算式を作成(自ノード含まず:分母部分)
Dim selectAllwithoutMe As String
    selectAllwithoutMe = select_all
Dim temporaryOne As String
    temporaryOne = "," & TABLENAME & "[" & checkbox_id & "],""" & checkbox_bin & _
        """"
selectAllwithoutMe = Replace(select_all, temporaryOne, "")

GetTextStringswithoutMe = selectAllwithoutMe

End Function

Private Function CheckMyExistenceInArray(ByVal text_array, _
    ByVal array_size As Integer, ByVal temporary_one As String) As Boolean

Dim i As Integer
For i = 1 To array_size
    If text_array(i) = temporary_one Then
        CheckMyExistenceInArray = True
    End If
Next

End Function

Sub checkBoxAliceYes_Click()
CheckBoxID = 1: CheckBoxBIN = 1
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxAliceNo_Click()
CheckBoxID = 1: CheckBoxBIN = 2
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxAnnieYes_Click()
CheckBoxID = 2: CheckBoxBIN = 1
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxAnnieNo_Click()
CheckBoxID = 2: CheckBoxBIN = 2
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxJohannaYes_Click()
CheckBoxID = 3: CheckBoxBIN = 1
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxJohannaNo_Click()
CheckBoxID = 3: CheckBoxBIN = 2
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxNickieYes_Click()
CheckBoxID = 4: CheckBoxBIN = 1
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxNickieNo_Click()
CheckBoxID = 4: CheckBoxBIN = 2
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxHanaYes_Click()
CheckBoxID = 5: CheckBoxBIN = 1
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxHanaNo_Click()
CheckBoxID = 5: CheckBoxBIN = 2
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxBethYes_Click()
CheckBoxID = 6: CheckBoxBIN = 1
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxBethNo_Click()
CheckBoxID = 6: CheckBoxBIN = 2
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxSallyYes_Click()
CheckBoxID = 7: CheckBoxBIN = 1
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxSallyNo_Click()
CheckBoxID = 7: CheckBoxBIN = 2
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxLizYes_Click()
CheckBoxID = 8: CheckBoxBIN = 1
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxLizNo_Click()
CheckBoxID = 8: CheckBoxBIN = 2
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxTriciaYes_Click()
CheckBoxID = 9: CheckBoxBIN = 1
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxTriciaNo_Click()
CheckBoxID = 9: CheckBoxBIN = 2
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxSarahYes_Click()
CheckBoxID = 10: CheckBoxBIN = 1
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxSarahNo_Click()
CheckBoxID = 10: CheckBoxBIN = 2
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxClariceYes_Click()
CheckBoxID = 11: CheckBoxBIN = 1
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxClariceNo_Click()
CheckBoxID = 11: CheckBoxBIN = 2
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxEnidYes_Click()
CheckBoxID = 12: CheckBoxBIN = 1
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxEnidNo_Click()
CheckBoxID = 12: CheckBoxBIN = 2
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxLEADERYes_Click()
CheckBoxID = 13: CheckBoxBIN = 1
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Sub checkBoxLEADERNo_Click()
CheckBoxID = 13: CheckBoxBIN = 2
Call ControllCheckBox(CheckBoxID, CheckBoxBIN)
End Sub

Private Sub ControllCheckBox(ByVal checkbox_id As Integer, _
    checkbox_bin As Integer)
' [PreviousAction]→"ControllCheckBox"→"SetFromula"→"ConvertTextToFormula"
Dim partnerCheckboxBin As Integer
If checkbox_bin = 1 Then
    partnerCheckboxBin = 2
Else
    partnerCheckboxBin = 1
End If
Dim myownObjectName As String
    myownObjectName = "checkBox" & GetCheckBoxOwner(checkbox_id) & _
        GetCheckBoxValue(checkbox_bin)
Dim partnerObjectName As String
    partnerObjectName = "checkBox" & GetCheckBoxOwner(checkbox_id) & _
        GetCheckBoxValue(partnerCheckboxBin)
If ActiveSheet.CheckBoxes(myownObjectName).Value = xlOn And _
    ActiveSheet.CheckBoxes(partnerObjectName).Value = xlOn Then
    ActiveSheet.CheckBoxes(partnerObjectName).Value = xlOff
End If

Call SetFormula

End Sub

Private Sub SetFormula()

Dim formulaTail, formulaHeadYES, formulaHeadNO, myFormula
Dim targetOne As Range
Dim targetTwo As Range
Set targetTwo = Range(TRUEFALSELIST).Range("A1").Offset(-1, -1)

Dim x As Integer, y As Integer
For y = 1 To TOTALNODE
    Set targetOne = Range(GetMarginsAddress(y))
    For x = 1 To 2
        Select Case x
        Case 1
            formulaHeadYES = "=IF(AND(" & targetTwo.Offset(y, 1).Address & _
                ",NOT(" & targetTwo.Offset(y, 2).Address & _
                ")),1,IF(AND(NOT(" & targetTwo.Offset(y, 1).Address & ")," _
                & targetTwo.Offset(y, 2).Address & "),0,"
            myFormula = formulaHeadYES
        Case 2
            formulaHeadNO = "=IF(AND(NOT(" & targetTwo.Offset(y, 1).Address & _
                ")," & targetTwo.Offset(y, 2).Address & _
                "),1,IF(AND(" & targetTwo.Offset(y, 1).Address & ",NOT(" _
                & targetTwo.Offset(y, 2).Address & ")),0,"
            myFormula = formulaHeadNO
        End Select
        Call ConvertTextToFormula(GetCheckBoxOwner(y), GetCheckBoxValue(x)) ' ex."Alice","YES"
        formulaTail = Numerator & " / " & Denominator
        myFormula = myFormula & formulaTail & "))"
        targetOne.Offset(x - 1, 0).Formula = myFormula
        formulaHeadYES = "": formulaHeadNO = "": formulaTail = "": myFormula = ""
    Next x
Next y
Numerator = "": Denominator = ""

End Sub

Private Sub ConvertTextToFormula(ByVal checkbox_id As String, _
    ByVal checkbox_bin As String)

Dim temporaryNumerator As String
temporaryNumerator = "SUMIFS(" & TABLENAME & "[" & HEADWORDOFSUBTOTAL & "]" & _
    GetTextStrings(checkbox_id, checkbox_bin) & ")"
Dim temporaryDenominator As String
temporaryDenominator = "SUMIFS(" & TABLENAME & "[" & HEADWORDOFSUBTOTAL & "]" & _
    GetTextStringswithoutMe(GetTextStrings(checkbox_id, checkbox_bin), _
    checkbox_id, checkbox_bin) & ")"
If temporaryDenominator = "SUMIFS(" & TABLENAME & "[" & HEADWORDOFSUBTOTAL & "])" Then
    temporaryDenominator = "SUM(" & TABLENAME & "[" & HEADWORDOFSUBTOTAL & "])"
End If

Numerator = temporaryNumerator
Denominator = temporaryDenominator

End Sub

晴花

26

マクロをすべてのチェックボックスコントロールに割り当てます。

たとえばALICEYESには,checkBoxAliceYesを割り当てます。

これをコントロールの数だけ繰り返します。

マクロの割り当て

完成

晴花

27

ベイジアンネットワークの完成です。

任意のチェックをはじめて動作させたタイミングで,確率が再計算されます。

FINISHED

ベイジアンネットワーク Excel 完成

晴花

28

たとえば第1層・第2層の4人にそれぞれ観測した値を置く(=チェックを入れる)と,第3層のそれぞれのメンバーの確率は次のように示されます。

このように,チェックのON・OFFのみであれこれシミュレーションが可能です。

scrollable

第1層・第2層にエビデンスを付けた時のネットワーク

  • 本頁で使用したデータはすべて架空のものです。また特定の会社等に実在する人物名,および同場所で実際に観測されたデータ群などを根拠にしたものでもありません。
.

LastUpdate

2018.9.1

.
このページの先頭へ