マイクロソフトワードエクセル

百ます計算

マイクロソフトエクセル

2014-04-27 19:20:21

エクセルで九九表が意外と検索されています。 九九表とは違いますが百ます計算の表もエクセルで作ってみましょう。百ます計算についてはWikipediaの百ます計算をご覧ください。 九九の表は1から9まで順に並んでいますが百ます計算の表は0から9の数値がバラバラでしかも同じ数値が出ないように並んでいます。 もちろんエクセルで百ます計算を作りますのでいちいちダブらない数字を手入力するようなことは避けたいと思います。 ただ、百ます計算もいろいろなサイトの問題を見ていると0(ゼロ)が入っているパターンと入っていないパターンがあるようです。特に掛け算の場合は0(ゼロ)は答えがすべてゼロになってしまうので１から9にするか1から9の数値のどれかが2回出てくるのが多いようです。 まず、0から9までのパターン 上図のようにA3のセルから百ます計算の表を始めることにします。3行目やA列に直接数値を入れてしまいますと変更するのが大変です。 K列から1列以上必ず離して0から9の数値を並べます。今回は2列離してN列とQ列に0から9の数値を配置します。 A列はN列の値を参照し、3行目はQ列の値を参照するようにします。セルA4には =N4 とすれば常にセルN4の値が表示されますのでセルA13まで式をオートフィルでコピーします。同様に3行目にはQ列の値を参照するようにB3のセルから順に =Q3 とします。面倒ですがセルC3には =Q4 とセルK3まで一つずつQ列の値を参照するように数式を入れます。（図は参照関係がわかりやすいように背景色を付けてあります。同じ色同士で参照しています。） あとはN列の値（とQ列の値）をランダムに並べ替えられればセルA3から始まる百ます計算表の出来上がりです。ランダムな並べ替えでもやったようにrand関数をN列の隣とQ列の隣に入れます。セルO4とセルR4に =rand()と入力します。オートフィルでコピーしておきます。 数式は以上で完成です。この状態までできるとO列とR列にランダムな数字並んでいますのでO列で並べ替え、R列で並べ替えをするとそれぞれ左隣N列とQ列も一緒に並べ変わります。するとN列とQ列を参照している百ます計算の表も変更されます。（下図はまず、O列で並べ替えた状態、次にR列で並べ替えます） あと余裕があれば2度並べ替えが面倒ならマクロの登録をして図形に張り付けておけばいいでしょう。（マクロはまた別の機会にご紹介の予定。） 0(ゼロ)がいらないパターン これは単純に先ほどの手順で0から始めるところ1から始めて9までに変更すれば完成です。 これは9×9なので81ます計算となってしまいますのでやはりもう1列、もう1行追加して10×10にしておきます。0(ゼロ)を使わずに1から9までの数値で10このセルを埋めるので何かの数値を2回使うことになります。何かの数値が固定でいつでも7だとかなら初めの0から9までのパターンの0を7だとか９だとかに変更すればいいでしょう。 しかし、0の代わりの数を毎度ランダムにしたければ1から9までの数値の中でランダムな1つを自動で選択するようにしなければなりません。 N列Q列の0の数値を1から9のランダムな数値にする方法があります。 aからbまでのランダムな正の整数を導く場合は =int(rand()*(b-a+1))+a というのがあります。rand関数は『0 以上で 1 より小さい実数の乱数を発生させます。』ので10倍して小数点以下切り捨てると0から9までの正の整数が得られます。rand関数は1より小さいので10倍しても小数点で切り捨てると10にはなりません。 0  　~  0.09999… 0.1  ~  0.19999… 0.2  ~  0.29999… 0.3  ~  0.39999… 0.4  ~  0.49999… 0.5  ~  0.59999… 0.6  ~  0.69999… 0.7  ~  0.79999… 0.8  ~  0.89999… 0.9  ~  0.99999… rand関数で得られる数値偏りがないとして0から0.1未満、0.1から0.2未満と10個に分類していった場合どの分類に属する値が出現するかは同じものと考えられます。（上は便宜上小数点以下5桁としていますがrand関数が小数点以下5桁までというわけではありません。） 例えば1（ゼロ）から４までのでたらめな正の整数値（乱数）が必要なら 0  　   ~  0.24999… 0.25  ~  0.49999… 0.5     ~  0.74999… 0.75  ~  0.99999… この4つの範囲は同じ間隔で分けられていますのでrand関数がこの範囲のどの数値を出力するかは同じ割合と考えられます。1番目の0  ~  0.24999…はこの範囲の数値は4倍しても1以上になることはありません。同様に2番目の0.25  ~  0.49999…は4倍しても1以上2未満の数値しか得られません。同様に3番目は4倍すると2以上3未満4番は3以上4未満となります。そして小数点以下を切り捨てると、 4倍する　　　切り捨てる 0  　   ~  0.24999…　　0 ~ 1未満　　　0 0.25  ~  0.49999…　　1 ~ 2未満　　　1 0.5     ~  0.74999…　　2 ~ 3未満　　　2 0.75  ~  0.99999…　　3 ~ 4未満　　　3 rand関数の結果を4倍し小数点以下を切り捨てると0から3まで整数が得られます。それに1を足すと1から4までの整数がランダムに生成することが可能です。 それを一般的にしたものが =int(rand()*(b-a+1))+a という式になります。 長々と乱数から任意の範囲の正の整数の取得を説明しました。結論は0から9までの百ます計算のN列Q列の0(ゼロ)の値のセルに =INT(RAND()*9)+1 という式を入れてあげれば0の代わりに1から9までの何かの数値が入ります。 ところが困ったことが発生します。結構な頻度で隣りどうしもしくは上下で同じ数字が並んでしまうことがあります。隣もしくは上下だと全く同じ答えが続いてしまうのでそこだけ単調になってしまいます。 ある列、は必ず特定のほかの列と同じ数字にする。という方法で解決したいと思います。 今回は最終行（13行目）は7行目と同じ、最終列（K列）はE列と同じに固定します。それぞれは離れていますので同じ数字が連続することはありません。 まず1から9のパターンの百ます計算の表を作成します。 セルK1はN2のセルを参照するよう =N2 という式を入力し、K3のセルはQ2のセルを参照するように =Q2 という式を入れます。 さらにセルN2にはセルN7を参照する =N7 という式を入力し、セルQ2にはセルQ7を参照する =Q7 という式を入れます。 セルN2にはセルN7を参照するために=N7という式を入力するときにoffset関数とrand関数を組み合わせて参照するセルを都度変更することも可能ですが、それほど効果もないと思いますので完成にしておきたいと思います。 ちなみに任意の範囲の整数の乱数はRANDBETWEEN関数で得ることができます。 RANDBETWEEN(最小値, 最大値) ただし、RANDBETWEEN関数はエクセル2003まではアドイン関数でしたので別途分析ツールアドインをインストールする必要があります。