【プレゼント交換】Excelで確率の問題を解く！②
本シリーズでは、Excelの関数を用いて使って確率の問題を解いていきます。コードをコピペしながら読み進めていただければ幸いです。最後に、この記事をもとに作成したExcelファイルをご用意しています！サクッと試したい方、きれいなレイアウトで確認したい方はぜひご利用ください！
【問題】5人でプレゼント交換をするとき、すべての人が自分以外のプレゼントをもらえる確率
5人の名前をそれぞれAさんからEさんとしましょう。受け取るプレゼントはランダムで決まり、自分のプレゼントを受け取る可能性もあるとします。つまり、次のようなパターンがありえます。
パターン①:すべての人が自分以外のプレゼントをもらえる
名前 : A B C D E
プレゼント:B C A E D
パターン②:1人以上が、自分のプレゼントをもらう
名前 :A B C D E
プレゼント:B A C E D
パターン②の例では、Cさんが自分のプレゼントを受け取ってしまっています。今回の問題は、パターン①に該当するケースの発生回数を数えることで解くことができそうです。
①モデルを考える
参加者それぞれが、誰からのプレゼントを貰うかはランダムです。AさんがAさんのプレゼントを貰う確率も、他の人から貰う確率も等しく1/5となります。前回はさいころを複数回振る試行を考えましたが、今回はそれとはモデルが少し異なります。それは、同じプレゼントを複数の人が受け取ることはありえない、という点です。
さいころでは、1回1の目が出たらもう2度と同じ目は出ない、なんてことはありません。一方で、プレゼント交換では誰かがAさんのプレゼントを受け取ったら、他の4人はAさんのプレゼントを受け取ることはありません。
ですので、「重複しない乱数」をExcelで表現する必要があります。
A10セル
=ROW()-9B10セルからF10セル
=RAND()G10セル
=CHOOSE(RANK(B10, $B10:$F10), "A", "B", "C", "D", "E")G10セルを選択し、右下をK10セルまでびーっと引っ張るRAND関数は、0から1での実数をランダムに返します。RANK関数は、範囲の中で特定のセルが何番目に大きいかを返します。この2つの関数を組み合わせることで、「重複しない乱数」を表現しています。
②判定する！
次に、発生させた乱数を判定しましょう。今回は「全員が自分のではないプレゼントを受け取っているか」、そうではないかで分類していきます。
まずは、判定用の数字を準備しましょう。
G9セル
A
H9セル
B
I9セル
C
J9セル
D
K9セル
EL10セル
=AND($G$9<>G10,$H$9<>H10,$I$9<>I10,$J$9<>J10,$K$9<>K10)AND関数は、条件式をいくつも引数にとることができます。全ての条件式がTRUEの場合、AND関数がTRUEを返します。条件式の中に１つでもFALSEがあれば、AND関数の戻り値もFALSEになります。今回の場合、判定用のG9セルからK9セルと、乱数のG10セルからK10セルを比較しています。５つ全て一致しない場合（<>はノットイコールを表しています）に、TRUEを返します。
③集計する！
N3セル
=COUNTIF(L:L, TRUE)N4セル
=MAX(A:A)N5セル
=N3/N4N3セルは、全員が自分以外のプレゼントをもらえた回数、N4セルは試行回数を表しています。N3セルをN4セルでわることで、確率を表現しているのがN5セルです。
前回同様、次の操作をすることで試行回数をどんどん増やすことができます！
A10セルからL10セルの範囲を選択し、
選択範囲の右下の角をぐいーっと下にひっぱる！それでは、最後に前回と同様理論値との比較を行いましょう。
N6セル
=1/2 -1/6 + 1/24 - 1/120N7セル
=N6-N5N6セルが理論値を表し、N6セルとN5セルの差分を表すのがN7セルになります。
【数学的な話】確率について少し解説
この確率は、完全順列や撹乱順列という名前で呼ばれます。今回は5人のプレゼント交換を扱いましたが、参加人数をどんどん増やしていくと、全員が自分以外のプレゼントをもらえる確率はe(ネイピア数)の逆数にどんどん近づいていきます！
【完成品】きれいなエクセルファイル
今回の手順をもとにしたExcelファイルはこちらです！サクッと試してみたい方、時間の取れない方、きれいなレイアウトで見たい方はぜひご活用ください！
サンプルは100回の試行をした状態ですので、いろいろな回数で試してみてください！
いただいたサポートは、主に本の購入費用になります。まとまった金額になりましたら、noteの投稿などを通じて、皆様に還元していければと思っております。