エクセルで味わう、意外な確率
エクセルは集計するためだけのツールではありません。シミュレーションのためのツールとしても強力です。
直感に合わないかもしれませんが、意外な確率を３つお届けします。
ありふれた偶然
40人のクラスで、同じ誕生日の人がいる確率は、どれくらいでしょうか。
(ア) 10％ (イ) 30％ (ウ) 50％ (エ) 70％ (オ) 90％
n人の誕生日がすべて異なる確率をp(n) ，n人の中の誰かと誰かの誕生日が一致する確率をq(n)とします。このとき、p(n)＋q(n)＝1が成り立ちます。ただし、ここではうるう年を考えずに、誕生日を365種類として計算します。
まず、p(0)＝1 , p(1)＝1 と q(0)＝0 , q(1)＝0 は明らかです。最低でも 2人いないと、誕生日が一致することはあり得ませんから。またn＞365 のとき、p(n)＝0 , q(n)＝1 も明らかです。
p(2) は2人目の誕生日が1人目の誕生日と異なる日であると考えて、p(2)＝p(1)×364／365 で計算できます。同様に、 p(3)＝p(2)×363／365 , p(4)＝p(3)×362／365 , … となります。
一般化すると p(n+1)＝p(n)×(365－n)／365 となります。また、q(n)＝1－p(n) です。
さて、これを手計算するのは現実的ではありませんから、計算はエクセルに任せましょう。エクセルの関数式は、下のエクセル・シートでいうと、
○ セルB4の式： ＝B3*(365－A3)／365
○ セルC4の式： ＝1－B4
となります。これらを入力して、下方向にコピーすれば完成です。
この結果、q(40)＝0.891 となりました。というわけで、冒頭の問いの答えは (オ) です。意外に高い確率だと思いませんか？
コピーをもうちょっと続けましょう。その結果「クラスに同じ誕生日の人がいる確率」は50人のクラスで97％、57人のクラスで99％、68人のクラスで99.9％となりました。
次に、このシートのC列をともに、q(n) のグラフを描いてみました。横軸が「人数」、縦軸が「同じ誕生日の人がいる確率」です。
クラスの中に同じ誕生日の人がいるのは、偶然でも珍しいことでも何でもないんですね。むしろみんなの誕生日が違うことの方がよっぽど珍しいことなんです。
最上位の数はどのような割合で現れるか？
ネットから大きさを表す数をたくさん拾って、最上位の数の個数を数えてみましょう。最上位の数というのは、たとえば日本の人口1億2千万人なら「1」で、バチカン市国の人口798人なら「7」で、日本の面積38万平方キロなら「3」です。 拾う数は最低でも100個以上、できれば1000個、あるいは10000個くらいある方が望ましい。最上位の数字は 1～9 のいずれかですが、それぞれどれくらいの割合で出てくるでしょうか？
ぜひ実際にネットで数を拾って、エクセルで集計してみてください。たくさんの数を探すには、総務省統計局のサイトなどからエクセル・ファイルをダウンロードするのが手っ取り早いでしょう。国別の人口でも面積でもＧＤＰ（国内総生産）でもいいですし、人口を面積で割って「人口密度」の数を作ったり、ＧＤＰを人口で割って「一人当たりのＧＤＰ」の数を作ったりすれば、たくさんの数ができますね。
次にエクセルの関数を使って、最上位の数の個数をカウントしましょう。「ある数の最上位の数」だけを抜き出すには LEFT 関数を、「1～9 の個数を数え上げる」には COUNTIF 関数を使ってみてください。
ところで、１行目に書いたように、拾う数は「大きさを表す数」です。つまり、日付・時間・順序・比率・割合を表す数は除きます。
さて、世界各国の人口をネットで拾って、最上位の数字を数えてみました。同じように、各国の面積とＧＤＰ（国内総生産）も集計してみました。 その結果は右表のとおりです。1 から 9 までの数字がまんべんなく出てくるかと思いきや、数字 1 の個数が最も多く、数字が大きくなるにつれて個数が少なくなっていきます。数字 1 の個数が全体のほぼ30％を占め、1 と 2 と 3 を合わせると全体の約60％に達します。反対に 7 と 8 と 9 を合わせても全体の20％に及びません。
この傾向は、他の数値、たとえば「今日の東証株価の終値」でやっても「通貨の交換レート」でやっても「都道府県別の大根の出荷数」でやっても同じになります。
なぜでしょうか？ そのわけは、数は直線的に変化するのではなく、指数関数的に変化する からです。（ただし、日付・時間・比率・割合に関する数についてはその限りでない。でも、大きさを表す数では確かにそうなんです）
そして、実はそれぞれの数が現れる確率には理論値があるんです。表の右端に書いた数値です。上のデータ数はたかだか574個ですが、全部で10000個くらい数字を集めれば、たぶんもっと理論値に近づくんだろうと思います。
おヒマな方は、新聞でも広げてそこに出てくる数の上１ケタの数字をひたすらカウントしてみてください。あるいは、総務省統計局のサイトなどからエクセル・ファイルをダウンロードして、集計してみてください。
ところで、この理論値とやらは何者なのか？ 最上位の数字が n である確率は「log(10)(n+1)－log(10)n」で求められます。log というのは対数ですね。（高校数学でやったはずですが、覚えてますか？）
ところで、どうしてこんなところで対数が登場するのか？ そのわけは、指数関数の逆関数が対数関数 だからです。下のグラフは、指数関数 y＝10^x のグラフです。まぁそういう（どういう？）わけです。
ウソだと思うんでしたら、エクセルでやってみてください。
席替えしたのにまた同じ席になっちゃう確率
席替えは案外難しい。誰かに任せたら何かしら思惑が入りそうで、みんなが納得できるものになるとは限りません。かといってジャンケンしたら、アイコばかりが続いていつまで経っても決着がつかないでしょう。くじ引きするのも時間がかかります。
さて、こういうときこそエクセルの出番です。乱数を使えば、公平でみんなが納得できる座席表を案外簡単に作れます。
手順はこうです。まずセルA1で乱数を発生させて、セルF1まで右方向にコピーし、さらにセルF7まで下方向にコピーします。こうして42個の乱数を発生させます。次にセルA1～F7の値を大きい順に番号をつけてセルH1～M7の範囲に表示させれば完成です。そこに表示された数字を自分たちの出席番号とみなせばいいわけです。
エクセルの関数式は、
○ セルA1の式： ＝RAND( )
○ セルH1の式： ＝RANK(A1 , $A$1:$F$7)
です。「数式」リボンの「シート再計算」ボタンを押せば何度でも使えますし、ファイルをコピーすれば他のクラスでも使えますからきっと重宝するはず。
ところで、「席替えしたのにまた同じ席になっちゃったよ」という人が、クラスの中に１人以上いる確率はどれくらいになると思いますか？
実は４人以上のクラスであれば人数に関わらず、その確率は約63％に（正確に言うと，その確率を四捨五入して小数第２位までで表すと0.63に）なります。ところで、その状況は次のケースと同じです。「クリスマス会のプレゼント交換で、みんなが持ち寄ったプレゼントを一人に１個ずつ配ったら、自分が用意したものが自分のところに来ちゃう。クリスマス会に参加したうちの誰かがそういう目にあう」、そうなる確率が約63％だということです。
この値、意外と大きいと思うんじゃないでしょうか。しかもそれが人数に因らないということも意外なんじゃないでしょうか。
では、計算してみましょう。「n人の人が席替えしたとき、また同じ席になっちゃう人がいる確率」を pₙ とします。
1人で席替えしたら必ず同じ席になりますから、p₁＝1 です。
2人で席替えする場合、全部で2通りのうちの1通り → p₂＝1/2
3人で席替えする場合、全部で3 ! = 6通りのうちの4通り → p₃＝4/6
と、このように順番に数えていってはキリがないので、ここから先は漸化式を立てましょう。
「n 人の人が席替えしたとき、全員が前と異なる席になる場合の数」を aₙ とします。
このとき「n 人の人が席替えしたとき、全員が前と異なる席になる確率」は qₙ＝aₙ ／n！ で、
「n 人の人が席替えしたとき、また同じ席になっちゃう人がいる確率」は pₙ＝1－qₙ です。
このとき、
aₙ について漸化式 aₙ₊₂＝(n＋1) (aₙ₊₁＋aₙ) が成り立ち、… (1)
qₙ で書き換えると、漸化式 (n＋2)qₙ₊₂＝(n＋1)qₙ₊₁＋qₙ となり、… (2)
pₙ で書き換えると、漸化式 (n＋2)pₙ₊₂＝(n＋1)pₙ₊₁＋pₙ となって、これを解くと … (3)
pₙ の一般項は pₙ＝1／1－1／2!＋1／3!－1／4!＋ … ＋(-1)ⁿ⁺¹／n! となります。 … (4)
※ (1) の計算法や考え方は「完全順列」というワードでネットで検索してください。
(3) から (4) を導くのは難しいが、結果を示されれば数学的帰納法で証明できます。
(4) より「n が大きくなると、減衰振動します」。このことと、p₄＝0.625 , p₅＝0.633... から、４人以上の人が席替えしたとき、また同じ席になっちゃう人がいる確率は、四捨五入して 63 ％ であることが証明できます。//
◇ ◇ ◇
〜 〜
▷
▷
▷