条件付Excel乱数の作成について

質問

次のような形の表に以下の条件にあった乱数を作成したいと思っています。

A列　問題番号（No.1～50）
B列　正解数　（1または2）
C列　選択肢の数
D列　正解番号　※ここに乱数で以下の条件に合う数式を入れたい

<条件>

・列方向で見たとき、は同じ正解番号が3回以上続かないようにしたい　（例：2,2,2・・・というのはNG）
・同じ正解番号が14個以上発生しないようにしたい（MAX50問くらいです）
・選択肢の数が複数の場合は「n,n」と同一セルに表示されるようにしたい。
　（難しい場合は隣の列でも可）

現在、正解番号列に「=INT(RAND()*(選択肢の数)+1)」と入れるところで停滞してしまっています。

VBA等、いろいろネットで調べたのですが、具体的な解決に繋がるものがなくこちらに駆け込みました。

どうぞお知恵を貸して下さい。

よろしくお願い致します。

回答No.2

mshr1962

問題の選択肢をランダムに入れ替えるということですよね。
まず選択肢の数は4か5で固定してください。
そうでなければ他の条件を満たすことは不可能です。
例として選択肢が3と5の問題がある場合、選択肢が5の答えは4か5に偏ることになります。
選択肢数を固定として作ることが可能なVBAは2通りあり
正解数を1に固定するか、14個以上の正解があるのはOKのどちらかになります。

正解数が1に固定の場合なら、50問分の正解パターンを用意して
それをランダムで選ぶ方式が考えられます。
QA(1)="12334324245323323223323423424"
QA(2)="12325432235423424353224233223"
のように200パターン位用意してこれを各問題の正解番号にセットする

逆に正解数を1or2で問題ごとに変えるなら、
3回以上続かないのと14個以上同じ正解番号がないは難しいです。
3回以上続かないのは条件に合致するまでLoopさせれば不可能ではないですが
14個以上同じ正解番号がない条件を加えると全体の番号を割り当ててから個数を確認して
NGならやり直しとなり無限ループに近い計算となりCPUに負荷がかかります。
正解数を1or2とする場合は少なくとも14個以上同じ正解番号がない条件は外してください。

上記の条件でよければVBAを組んでみますけど．．．

補足

回答ありがとうございます。
条件を全て満たすのは難しいのですね。

＞正解数を1or2とする場合は少なくとも14個以上同じ正解番号がない条件は外してください。
＞上記の条件でよければVBAを組んでみますけど．．．

正解数を1or2にする方を優先したいので、参考にさせて頂けますか？
文字数がオーバーする場合は、条件設定の部分だけでも結構です。
よろしくお願い致します。

回答No.1

chie65536

正解番号は、乱数関数を含む数式で求めてはいけません。

＞同じ正解番号が3回以上続かない
＞同じ正解番号が14個以上発生しない

という条件を判定する為に、一度、そのセルに正解番号が入れられたら、２度と値が変わってはいけません。

しかし
＞=INT(RAND()*(選択肢の数)+1)
のような乱数関数を含む数式を書くと「評価するたびに、正解が変化してしまう」ので、条件を正しく判定できません。

例えば、以下のような事が起きます。

１問目の回答が乱数で「2」になりました。
２問目の回答が乱数で「2」になりました。
３問目で、乱数が「2」になったので、１問目と２問目の回答が「2」かどうか調べました。すると１問目と２問目の式が再評価され、それぞれ新しい乱数で「1」と「3」が返って来て「2が３つ連続してないから大丈夫」と判断され、回答が「2」になりました。

ここで「最初に求めた値を採用する」と、３問連続で「2」が出てしまいます。

つまり「乱数関数は、番号を最初に新規に作る時だけ使用し、番号を再評価する場合は、最初に作られた値が返って来る」ようにしないとなりません。

言い換えれば「数式では無理。マクロを組むか、ＶＢコードで書く必要がある」という事。

例えば、シート上に「回答成作」のコマンドボタンを置き、ボタンを押したらＶＢコードが呼び出され、正解番号列のセルに「乱数で作られた、正解番号を表す文字列」を書き込んで行くとか。

なお「正解が２個あるとき」に「1,2」とか「1,3」とか「2,5」とかにならないといけないので、正解番号列は「文字列」になっていなければなりません。

数式で書くのは「理論的に不可能」なので、今すぐＶＢコードの参考書を買って来ましょう。

因みに「ＶＢのコードは、ここの解答欄の文字数では、どうやっても書き切れないくらいの文字数」になるので、回答不可能です。

お礼

回答ありがとうございます。
乱数の特性を理解していなかったので大変勉強になりました。
ご丁寧な解説、助かりました。

＞例えば、シート上に「回答成作」のコマンドボタンを置き、ボタンを押したらＶＢコードが呼び出され、正解番号列のセルに「乱数で作られた、正解番号を表す文字列」を書き込んで行くとか。

なるほど！こちらのヒントを頂けただけでも質問させて頂いた収穫がありました。

VB初心者ですが、もう少し試行錯誤してみます。