ソフトウェア活用法

セルの参照・乱数と平均値、サイコロの目
2008/6/9 長島雅裕
１．少し高度なセルの参照の仕方
掛け算の九九の表を、表計算によって作成してみよう。九九とは以下のようなものである。
A B C D EF G H
IJ
112345678

92

1 1x1 2x1 3x1 4x1 5x1 6x1 7x1 8x1 9x1

32 1x2 2x2 3x2 4x2 5x2 6x2 7x2 8x2 9x2

43 1x3 2x3 3x3 4x3 5x3 6x3 7x3 8x3 9x35 4 1x4 2x4 3x4 4x4 5x4 6x4 7x4 8x4 9x46 5 1x5 2x5 3x5 4x5 5x5 6x5 7x5 8x5 9x57 6 1x6 2x6 3x6 4x6 5x6 6x6 7x6 8x6 9x68 7 1x7 2x7 3x7 4x7 5x7 6x7 7x7 8x7 9x79 8 1x8 2x8 3x8 4x8 5x8 6x8 7x8 8x8 9x8
10 9 1x9 2x9 3x9 4x9 5x9 6x9 7x9 8x9 9x9
まず、Ａ列と 1 行目に、掛け算に必要な１～９までの数字を書いておこう。これも最初に１だけ入力し、右下の黒四角をドラッグして「連続データ」にすれば、すぐに入力できる。
次に、B1 セルで計算をしよう。このセルでは、B1 セルの値と A2 セルの値を書ければよいので、=B1*A2 とすれば良さそうである。試しに、これで B2 セルの右下の黒四角をドラッグし、B10 セルまで持っていくことで、「１の段」を計算してみよう。
すると、意図した通りにはなっていないことに気付くだろう。試しに B10 セルの中身を見てみると、=B9*A10 となっているはずである。これは、最初の B2 セルでの計算が、「一つ上のセル＊一つ左のセル」と解釈されてしまったためだ。九九にするためには、「一つ上のセル」ではなくて、「常に１列目のセル」を参照してもらわなければならない。
このための方法がある。”＄”をつけるのである。B2 セルの内容を、=B$1*A2 としてみよう。このセルの値は変わらないはず。これを下までドラッグすると、例えば B10 セルの中身は=B$1*A10 になり、九九の１の段になっていることがわかる。B1 の”１”の部分を、強制的に固定したのである。
さて、１の段をまとめて選択し、右のほうへドラッグすれば、ただちに九九が完成しそうであるがそうはいかない。
今度は、２の段、３の段、…ではＡ列の値を参照したいのに、ここでも「一つ左のセル」の値が参照されているからである。これを避けるためには、最初に B2 セルで=B$1*$A2 としておけばよい。これで、１行目と A 列目というのを固定することができ、簡単に九九の表を完成させることができる。
このテクニックは、今後、少し複雑なことをする際に有用になる。
課題：１９ｘ１９の掛け算の表を作ってみよう。また列の幅も短くして、見やすくしよう（変えたい列を選び、右クリッ
クで「列の幅」を選ぶ）。 最後に印刷して提出すること（番号・名前を忘れずに）。
２．乱数と平均値
サイコロをふると、正しく作られたサイコロならば、１～６の目が等確率で出てくる（確率はいくらでしょう？）。このように、ランダムに発生する数値（の列；数列）のことを「乱数」と呼ぶ。実際に乱数が必要になる場合も多々あるが、統計学を理解する際にも乱数を理解することは大変重要である。
コンピュータはある一定のアルゴリズムに基づいて動いているため、「本当の」乱数を発生させることは不可能である。しかし、「ほぼ」乱数に見える数列を吐き出すプログラムの研究がすすみ、エクセルには乱数を発生させる関数が組み込まれている。rand 関数である。
関数であるのでカッコ()が必要だが、引数はとらない。セル内で、=rand()とすると、区間[0,1)の一様乱数が発生する（0 以上 1 未満でどれも等確率。1 は含まれない）。実際に打ち込んでみよう。1 未満の小数が書かれるはずである。またファンクションキー F9 を押すと、新たな乱数が振り直される。何度か押して、[0,1)の乱数になっていることを体感してください。
次に、平均値を求めてみよう。乱数列[0,1)の平均値は、ちょうど中間の 0.5 である。実際にそうなっているだろうか？
まず、10 個の乱数列を発生させよう。例えば A1 に=rand()と書き、それを A10 までコピーすると、10 個の乱数が発生する（F9 を押して、色々変えてみてください）。この 10 個の数の平均値を求めてみる。
セル A11 に平均値を出そう。やり方はわかりますね？オート SUM ボタンの右側の三角をクリックし、平均値を選ぶ。平均を取る範囲が A1～A10 になっていることを確認し、決定する。値は幾らになってでしょうか？
0.5 付近の人もいれば、0.4 以下だったり 0.6 以上だったりした人もいるでしょう。では、さらに何回か F9 を押してみてください。0.5 のまわりでフラフラとするはずです。どれくらいの範囲でフラフラするかの目安を「標準偏差」とか「分散」と言います。これらの定義は次回の講義でします。
さて、無限に乱数を発生させれば、平均値は 0.5 に近づいていくはずである。それを見てみよう。
左端に 1 列挿入し、A1～A10 まで順に 1～10 の数字を入れていく。これは何回乱数を発生させたかの数になっている。次に平均値を C 列に書いていく。C1 セルは C1:C1 の平均、C2 セルは C1:C2 セルの平均、Cn セルはC1:Cn セルの平均、とすると、乱数をふるごとに平均値がどう変化するかがわかるはずである。ここで、C1 セルに=average(C$1:C1)と”＄”をつけておくと、C1 セルを下にコピーしていっても、自動的に C1 セルからの平均値を取ってくれる。試しに C10 セルの中身を見ると、=average(C$1:C10)となっているはずである。
ここで、A 列を横軸に、C 列を縦軸にしたグラフを描いてみよう。乱数を発生し続けると、平均値がどう変化するかが一目でわかるようにするためである。折れ線グラフにして、グラフが煩雑になるのを避けるため、点のマーカーはないものを選んでおこう（詳細は前回のレジュメを参考のこと）。描けたら、何度か F9 を押して、グラフの変化を味わってみよう。なんとなく、サイコロをふる回数が増えるほど、0.5 に近づくように見えるような気がしないだ
ろうか？
もっとはっきり見るために、100 回乱数を発生させてみよう。まず、A1～C10 セルまで選択しておいて、右下をドラッグして 100 行目まで持っていく。これで、B100 セルの値は 100 回目の乱数、C100 セルの値は 100 回目までの平均値、ということになる。次にグラフを変更する。グラフの中を右クリックし、「元のデータ」を選ぼう。今までと同様にマウスで範囲を選択してもいいのだが、ここではもっと簡単に、「値」と「項目軸ラベルに使用」の中の=Sheet1!$C$1:$C$10 の最後の「10」を「100」に変えよう。この意味は、「値」は、「このシートの C1 から C100 まで」ということだ。両方 100 に変更すれば、それで 100 行目までが範囲になる。
図を眺めてみよう。明らかに、回数が増えるほど、平均値は 0.5 に近づいていっているのがわかるだろう。つまり、理論的に確率分布がわかっていても、その確率分布から予想される平均値に近づくには、十分多くのサンプルが必要、ということだ。この意味は統計解析で重要になるので、次回、ゆっくり実習を行う。
課題：上で描いた 100 回の乱数についてのグラフを印刷して提出すること。
３．サイコロ
最後にサイコロの目を出してみよう。今まで使った rand 関数は[0,1)区間で一様な乱数を発生させるものであった。これを少し変形すると、サイコロにすることができる。
まず、サイコロの目は、1～6 までの整数であることに注意しよう。そこで、方針としては、(1)[0,6)区間で一様乱数を発生（0 以上 6 未満）→rand 関数(2)小数点以下を切り捨て、{0,1,2,3,4,5}をランダムに発生→int 関数(3)1 足して、{1,2,3,4,5,6}にする
これを実現するには、=int(rand()*6)+1 とすればよい。rand()*6 で、[0,1)区間の乱数が 6 倍され、[0,6)区間の一様乱数になる。次に int()を作用させることで、小数点以下を切り捨てる(int 関数は小数点以下を切り捨て、整数に変換する関数）。最後に+1 することで、1～6 までの整数をランダムに発生させることができる。
何度か F9 を押して、1～6 の目が出ることを確かめてみよう。
また２でやったように、沢山のセルでサイコロの目を発生させ、同じくらいの確率で出ているかどうか、見てみよう。
次回はヒストグラムを作り、サイコロの目それぞれが等確率で出ているか視覚的にチェックするところから始めます。
課題（再掲） それぞれ番号・名前を忘れずに(1)１９ｘ１９の掛け算の表を作ってみよう。また列の幅も短くして、見やすくしよう（変えたい列を選び、右クリックで「列の幅」を選ぶ）。 最後に印刷して提出すること。
(2)上で描いた 100 回の乱数についてのグラフを印刷して提出すること。