エクセルのRAND関数で遊んでみた

【条件１】　普通のコイントス

エクセルのRAND関数は、0以上1未満の乱数を出力する関数である。エクセルのセルA2からA101に

=RAND()

と記入すると、下図のように0から1までの数値がランダムに表示される。

次に、セルB2からB101に、

=IF(A2<0.5,1,0)

と入れる（A2のところには、A2～A101までのセル番号が入る）。これで、A列の値が0.5未満であれば1、0.5以上であれば0が表示される。

そして、B列に出てきた1の総数を、セルC2に表示させる。

これは要するに、100回コインを投げて何回表が出たかを記していることと等しい。

RAND関数の面白いところは、セルC2の値をコピペするたびに違う値が出てくるところだ。この図ではD列に値をコピーしていってる。

では、この要領でC2の値を1万回コピーするとどうなるだろう。

………。

面倒くさいのでマクロを作って1万回コピペを繰り返した。

この計算値1万個がどのような分布を示しているのか、グラフにしてみた。横軸が出力された数値、縦軸がその数値が出てきた回数を表している。

これは要するに、「コイントス100回やって何回表が出るか確認」という操作を1万回繰り返して、出てきた数値の分布を見ていることと等しい。そして、この分布は予想通り、平均約50の正規分布となっていることが分かる。

【条件２】　スマホガチャモデル

今度は、ちょっと特殊なコインについて考える。セルB2からB101に、

=IF(A2<0.05,1,0)

と入力する。つまり、表が5%しか出ないコインである。これも同じように1万回繰り返してみた。

図のようにピークがだいぶ左側に寄ったグラフになった。平均値はもちろん5で、表が10回以上出ることはほとんどない。一度も表が出ないパターンも62回あった。

これは、発生確率の少ない事象を何回も繰り返してるケースなので、スマホゲームのガチャで当たりを引く確率を示しているようなものである。

【条件３】　みんな平等モデル

では次に、もっと変なコインについて考えてみよう。まず、B列の上から1～5番目には、【条件1】と同じように、

=IF(A2<0.5,1,0)

を入れる。重要なのは6番目からで、

=IF(A7<(0.5-(AVERAGE(B$2:B6)-0.5)*0.8),1,0)

と入れる。何のこっちゃと思うかもしれないが、これは要するに、前回までに出た表の数によって表の出る確率が変動するようになっているのである。例えば、コイントス100回のうちn回目（nは6以上100以下の整数）のトスについて、

• 1回からn-1回目までに表が出た確率が0%ならば、n回目に表が出る確率は90%になる。
• 1回からn-1回目までに表が出た確率が50%ならば、n回目に表が出る確率も50%になる。
• 1回からn-1回目までに表が出た確率が100%ならば、n回目に表が出る確率は10%になる。

というような規則で、表が出る確率が10～90%の間で変動するように設定してある。例えば、1回目から20回目までに12回表が出たとすると、AVERAGE関数のところの値は0.6となり、21回目の確率は、

0.5－{（0.6－0.5）× 0.8 } ＝ 0.42

で、42%となるのである。ここで0.8という数値は確率の変動幅を規定する定数で、私が勝手に決めただけなので、今はまだ気にしなくて良い。

この条件で1万回やった時のグラフは下のようになる。なお、表中のBnとは、n回目のコイントスで出てきた数値（表なら1、裏なら0）を表している。

【条件１】と見比べてもらうと分かるが、とてもシャープなピークとなり、標準偏差も小さくなっていることが分かる。どうしてこんな事になるかというと、この【条件3】では、過去に表がいっぱい出ていれば表は出にくくなり、逆に、過去に表が少なかったならば表が出やすくなる。

要するに、みんな平等に計算値が50付近に収束するようになっているので、これは「みんな平等モデル」と命名しておこう。

【条件４】　格差拡大モデル

次は全く逆のパターンについて見てみよう。B列の6～100番目に入れる関数の符号を入れ替えて、

=IF(A7<(0.5+(AVERAGE(B$2:B6)-0.5)*0.8),1,0)

としてみる。この条件だと、

• 1回からn-1回目までに表が出た確率が0%ならば、n回目に表が出る確率は10%になる。
• 1回からn-1回目までに表が出た確率が50%ならば、n回目に表が出る確率も50%になる。
• 1回からn-1回目までに表が出た確率が100%ならば、n回目に表が出る確率は90%になる。

という感じである。例えば、1回目から20回目までに12回表が出たとすると、21回目の確率は、

0.5＋{（0.6－0.5）× 0.8 } ＝ 0.58

さっきは0.5の右横の符号がマイナスだったのに対し、今回はプラスになっていることに注意。これだとどういうグラフができるのだろう。

ご覧のとおり、ピークがめちゃくちゃブロードになった！　グラフの縦軸が先ほどとは全然違うことに注意してほしい。また、標準偏差も【条件１】と比べて3倍以上も大きくなっている。

この条件では、過去に表の出た回数が多ければ多いほど、表が出る確率は大きくなり、逆に、表が出ていなかったら表が出る確率がどんどん小さくなる。要は、裕福な人ははますます裕福になり、貧乏人はますます貧乏になるというパターンであり、これによって計算値は大きくばらつくことになるのである。

【条件5A】　生まれ重視モデル（左右対称）

では次に、B列の6～100番目に入れる関数を、

=IF(A7<(0.5+(AVERAGE(B$2:B$6)-0.5)*0.8),1,0)

にしてみよう。先ほどの条件では「B6」となっていたところが「B$6」と変わっている。つまり、6～100番目の表が出る確率は全て、1回目から5回目までに何回表が出たかによって規定される。

• 1～5回目で表0回ならば、6回目以降に表が出る確率は10%
• 1～5回目で表1回ならば、6回目以降に表が出る確率は26%
• 1～5回目で表2回ならば、6回目以降に表が出る確率は42%
• 1～5回目で表3回ならば、6回目以降に表が出る確率は58%
• 1～5回目で表4回ならば、6回目以降に表が出る確率は74%
• 1～5回目で表5回ならば、6回目以降に表が出る確率は90%

要するにこれは、生まれた時に備わっている条件（親の年収、健康な体、才能、etc.）によって、その後の人生が全部決まってしまうようなケースを表しているので、これを「生まれ重視モデル」と命名しよう。では、1万回繰り返した後の計算値はどうなっているかというと、

なんかピークが6つに分かれている！

これはよく考えてみれば当たり前で、表が出る確率は、1～5回目のコイントス結果によって6タイプに分類されている。そして、この6タイプの確率で6～100回目のコイントスが行われた結果、値は(a)10%、(b)26%、(c)42%、(d)58%、(e)74%、(f)90%を中心とする6山に寄って分布することとなるのである。

（もし、計算回数が1万回ではなく、2万、5万、10万回と増えていったら、もっと滑らかな6つのピークが確認できたであろう。しかし、今回は1万回でご容赦いただきたい。回数をこれ以上増やすと私のパソコンがフリーズしてしまうのである。）

上図では、1～5回目までのコイントス結果、つまり5つの因子がその後の人生を規定しているわけだが、この数を色々変えていったらどうなるだろう。生まれ重視モデルで因子の数を4、3、2、1と減らしていった場合の結果が下図である。条件名の右端にある枝番が因子数を示している。

要は、初期の因子数がn個の時、n+1個のピークが出てくるわけである（化学の研究でNMRを使った事がある人には馴染み深いピーク形状であろう）。最も極端なのが【条件5A-1】である。これはつまり、最初のコイントスが表か裏か（セルB2の値が0か1か）によって、その後の人生がガラッと変わってしまうというケースである。

逆に、因子の数を7、10に増やしてみた。すると、下図のように、ピークどうしが重なり合って効果が相殺された結果、1まとまりのブロードなピークが出てくるという形状になった。

【条件5B,5C,5D,5E】　生まれ重視モデル（非対称）

では今度は、上で見た【条件5A-4】をさらに変形してみよう。【条件5A-4】ではB列の1～4番目が、

=IF(A2<0.5,1,0)

となっていたが、これを、

=IF(A2<0.4,1,0)

に変更する。つまり、最初の４回だけコインの表が出る確率が40%になったのである。すると、予想通り、5つのピークの大きさは非対称になり、全体の平均値も50からずれる結果となった。

同じようにB列1～4番目の確率を30%、20%、10%という風に変えると、分布はますます左側に偏っていく。

例えば、【条件5E-4】では、右側のピーク２つが完全に消えて、真ん中のピークもかろうじで見える程度である。そして、大部分のデータが計算値10か30の付近（つまり、最初の５回のトスで表が出た回数0か1のデータ群）に集中している。

【条件6】　生まれ&育ち重視モデル

【条件5】は、最初の数回のコイントスの結果によってその後の結果が大きく左右されるというモデル、つまり、生まれた時の環境によってその後の人生が決まってしまうというモデルであった。しかし、現実の人生では、「生まれ」と同時に「育ち」もまた重要である。以降では、そのようなケースについて考えてみたい。

そのためにまず、上で見た【条件5E-4】をさらに変形してみよう。B列の上から1～4番目までは、

=IF(A2<0.5,1,0)

とし、5～19番目を

=IF(A6<(0.5+(AVERAGE(B$2:B$5)-0.5)*0.8),1,0)

とする。ここまでは前と変わらないのだが、20～100番目を

=IF(A21<(0.5+(AVERAGE(B$6:B$20)-0.5)*0.8),1,0)

とするのである。つまり、20回目からラストまでの表が出る確率は、5～19番目までに表が出た回数によって規定される。模式図で表すと次のようになる。

最初に1～4番目のコイントスが行われ、表が出た回数によって5～19番目の確率が5パターンに分かれる。その状態で5～19番目のコイントスが行われ、そこでたくさん表が出れば出るほど、20番目以降に表が出る確率が上がる。

「表が出る確率」を「人生で成功する確率」に置き換えて考えてみれば分かりやすいだろう。20番目以降に上手く表を引き当てるためには、5～19番目でたくさん表を出しておかなければならない。そして、そのためには1～4番目で「スタートダッシュ」に成功していることが重要なのである。だが、仮に「スタートダッシュ」に失敗したとしても、5～19番目である程度までは挽回可能。逆に言えば、1～4回目で上手くいっていても、5～19回目に油断してるとヤバい、というモデルである。

さあ、グラフはどんな感じになっているだろう。

「生まれ」に由来するピークは、5～19番目の「育ち」の期間でならされて、右側に大きくテーリングした１本のピークとなった。

（本記事では詳しく述べないが、このピーク形状とそれが得られるまでの過程は、HPLCなどの分離カラムでピークがテーリングしてしまう理由を実に簡潔に説明しているものである。）

この形状を一番最初の【条件１】と比較してみると興味深い。実測値（ここではコイントスで表が出た回数）を横軸に、分布が正規分布に従っていると仮定した場合の期待値を縦軸にとったグラフを、Q-Qプロットと言う。詳細な説明は省くが、このプロットがy=xの直線状に並んでいればいるほど、その分布は正規分布に近いということが言える。

【条件1】と【条件6E-4】でQ-Qプロットを比較してみると、前者はほぼ正規分布に従っていることが分かるのに対し、後者は直線y=xの下側にズレている。このような形状は、各値が対数正規分布に従って分布している時に見られるものである。

現実世界で正規分布を示すものには以下のようなものがある。

• サイコロで出た目
• ブラウン運動による粒子の移動距離
• 大人の身長

一方、現実世界で対数正規分布に従う関係性には、次のようなものがある。

• 人の所得
• 市町村の人口
• 各国のGDP

【条件6E-4】のy軸に、国数・市町村数・人口などを置き、x軸にGDP・市町村人口・年収などを置いてみるとなるほどと思うであろう。

この日本でたくさんの賃金を得るためには、良い教育を受けて知識や技術を身につける必要がある（育ち）。そしてそのためには、生まれた時の環境、つまり親の年収・遺伝子・才能等（生まれ）が重要となる。

国の経済活動が盛んになるためには、その国が建国してから今日まで常に発展し豊かになっていなければならない（育ち）。そのためには、その国の置かれた環境、つまり、肥沃な土壌・温和な天候・河川や山や海の配置・周辺国との位置関係など（生まれ）が重要となってくる。

「生まれ」は絶対ではなく、「育ち」で挽回することもできるが、あまりにも生まれた時の環境が悪いと挽回するのは難しい。「生まれ」が良くてもその後に没落することもあるが、やはり「生まれ」が良いほどその後の物事が上手くいく可能性が高いこともまた事実である。

【条件7】　教育機会均等モデル

生まれによって生じた格差を解消するためにはどうすれば良いだろう。１つ目の方法として、どんな家庭で育ったとしても同じように教育機会を平等に与えるということが考えられる。

【条件6E-4】のB列5～19番目を

=IF(A6<(0.5+(AVERAGE(B$2:B$5)-0.5)*0.6),1,0)

に変更する。【条件３】のところでチラッと述べた「確率の変動幅を規定する定数」を0.8から0.6に変えたのである。

この値を小さくすると、１番目から４番目までに出た表の数に関わらず、5～19番目の確率はだいたい同じくらいになる。

実際にグラフを見てみると、標準偏差が小さくなり、格差が解消していることが分かる。

【条件8E-4】　累進課税モデル

もう一つ、格差を解消するやり方として累進課税が考えられる。

【条件6E-4】のB列20番目以降を

=IF(A21<(0.5+(AVERAGE(B$6:B$20)-0.5)*0.6),1,0)

に変更する。こうすることで、「生まれ」と「育ち」に起因する確率の差はある程度小さくなるので、所得の多い人ほど所得税率を上げる累進課税が行われていることと同じ効果がある。

この場合もやはり、ピーク幅が狭まり、格差が小さくなっていることが分かる。

まとめ

このように、エクセルのRAND関数で乱数を作っておき、それを色々な関数で処理してやることで、様々なタイプのデータ分布を作り出すことができるのが面白い。

ただし、本記事の内容は私がエクセルを使って色々遊んでみた結果を考察しているだけであって、統計学的に厳密な議論をしているわけではないので、注意願いたい。

もし、何か内容に不備、あるいは他にもこういうモデルがあるんじゃないかといったアイディアがあれば、コメント欄かブックマークで教えてほしい。

• 参考文献：古田徹也著『統計分布を知れば世界が分かる』（中公新書）