エクセルで以下の条件付きの乱数 (整数) を求める関数を教えてください．

エクセルで1～3の216個の乱数 (整数) を作りだす際，

・各整数は同じ確率で出現する (1も72回，2も72回，3も72回)
・同じ整数は連続しない (１⇒1，2⇒2，3⇒3，とならない)
・不規則である (1⇒2⇒3という小さなまとまりが72回出現とはならない)

以上の条件を満たす乱数 (整数) を求める関数を教えてください．

No.6 ベストアンサー 回答者： GooUserラック 回答日時： 2018/06/07 14:30

とりあえずマクロを作成してみました。

指定行の A列から HH列に条件付きの乱数を表示します。

Sub 条件付き乱数発生()

Const 行 As Long = 1
Dim データ() As Integer
Dim 個数() As Integer
Dim フラグ As Boolean
Dim 列 As Long

　Randomize
　Do
　　DoEvents
　　For 列 = 1 To 216
　　　If 列 = 1 Then
　　　　フラグ = False
　　　　ReDim データ(219)
　　　　ReDim 個数(3)
　　　　データ(1) = Int(Rnd() * 3) + 1
　　　　個数(データ(1)) = 1
　　　Else
　　　　データ(列) = Int(Rnd() * 2)
　　　　Select Case データ(列 - 1)
　　　　　Case 1
　　　　　　If データ(列) = 0 Then
　　　　　　　データ(列) = 2
　　　　　　Else
　　　　　　　データ(列) = 3
　　　　　　End If
　　　　　Case 2
　　　　　　If データ(列) = 0 Then
　　　　　　　データ(列) = 3
　　　　　　End If
　　　　　Case 3
　　　　　　If データ(列) = 0 Then
　　　　　　　データ(列) = 2
　　　　　　End If
　　　　End Select
　　　　個数(データ(列)) = 個数(データ(列)) + 1
　　　　If 個数(データ(列)) > 72 Then 列 = 0
　　　　If 列 > 3 Then
　　　　　If データ(列) <> データ(列 - 3) Then フラグ = True
　　　　End If
　　　End If
　　Next
　Loop Until フラグ
　For 列 = 1 To 216
　　Cells(行, 列).Value = データ(列)
　Next

End Sub

この回答へのお礼

ご回答ありがとうございます．
作成していただいたマクロで行ってみたところ求めていたものができました．
大変助かりました．
お力を貸していただいてありがとうございました．

お礼日時：2018/06/07 16:19

No.7 回答者： WindFaller 回答日時： 2018/06/07 14:46

#5のGooUserラック さんへ

どうもありがとうございます。

おしゃった方法は、まだ、#5の内容の全部は理解していませんが、加算方式だと思います。一応、組み合わせの考え方は、そのままで、ずっと進めていました。それは、組み合わせとしては、6通りしかないからです。

>３つ目の条件チェックですが任意の２つの数または３つの数が３つごとに現れなくなった時点でクリアしたことになるのではないかと思います。

この内、1つの数が72を越えると、残りの数字は、2つですが、その内、当てはまるのは1組になります。同じものを続かせる場合は、少し違ってきます。

最後は、3つの数の1つが欠けることだから、1組(2つの数字）だけ許されるわけです。
それで、組み合わせのピースが埋まればよいのですが、ランダムはランダムゆえに、数合わせはピッタリとは行かないわけです。

>・各整数は同じ確率で出現する (1も72回，2も72回，3も72回)

ランダムで発生させると、どうやら、上記の条件に完成する確率は低いのではないかということが分かりました。

すでに改良版のコードは出来上がってはいますが、条件にはマッチしないし、乱数にこだわれば、可能性はかなり低いのではないかというところです。ジグソーパズルのように、定数から引いていく方法があるのは想像つくのですが、囲碁の持碁と同じで、みえみえに数合わせしたらだめだと思うのです。そこらへんは、今の所、私には無理なのかなって思います。

この回答へのお礼

ご回答ありがとうございます．
私も初めは3つの数字の並び替えの組み合わせでやることになるのかと思っていました．
お力を貸していただいてありがとうございます．

お礼日時：2018/06/07 16:26

No.5 回答者： GooUserラック 回答日時： 2018/06/07 11:20

質問者からマクロの使用しても良いかの返事が無いので、チョット面白そうな課題なのですが１歩進めません。

なので WindFaller さんへの提案みたいになってしまいますが、３つ目の条件チェックですが任意の２つの数または３つの数が３つごとに現れなくなった時点でクリアしたことになるのではないかと思います。
なので考え方は次のような感じで良いのでは
①乱数で１～３の数を決める。
②それぞれの出現数をカウントして７２回を超えたらクリアして ①からやり直す。
③前回の出現位置から３つ後でなければ３つ目の条件クリアのフラグを立てる。
④乱数で、１つ前の数字以外の数を決める。
⑤２１５回 ②～④を繰り返す。
⑥３つ目の条件クリアのフラグが立っていなかったら ①からやり直す。

No.4 回答者： WindFaller 回答日時： 2018/06/07 09:56

スレ主さん、ちょっとトピオフすみません。

No.3 のGooUserラックさんへ
貴重なアドバイスありがとう御座います。

そのとおりですね。そうすると、3つの数の6組ではなくて、2つの数の6組になるということでしょうか。ただ、#2の場合は、必ず3つ使うから考えなくて済んだものが、そうすると、3つの数字の偏りをチェックする項目が増えるということになります。

そのまま、組み合わせを2つにして、代入させると

1- 73個 * 1 over
2- 73個 * 1 over
3 -70個 * 2 less
72個には、数が合いません。
ただし、そう大きくブレているわけではなさそうです。

最初に各数の回数72までを、カウントを減らしていくのか、増やしていくほうがよいかです。たぶん、定量なので、減らして、rnd 関数で選ばせる方法がよいのではないかと考えています。(言葉ではなんとも言えますが、実際は分かっていません）

ここの掲示板では、最後に、だいたい帳尻合わせするやり方が目立つのですが、これほどガッカリさせられる回答はないと思います。仮に結果として同じでも、それはしたくないのです。今は、頭で考えて分からないので、もう一度、VBAにまかせてみようかと思います。

>せっかく作ってもマクロはダメとか言われると思いっきり疲れるのでまだ作っていません
見る人は見ています。以前、我ながら傑作だと思ったものが、スレ主さんから、無視するなら無視してもよいのに、自分宛ての回答ではないと言われるほど、酷い返事はありません。

No.3 回答者： GooUserラック 回答日時： 2018/06/06 22:55

No.2 の WindFaller さんへ

「6組の組み合わせ」ではないと思います。以下のようなものもOKなはずなので…
1->2->1
1->3->1
2->1->2
2->3->2
3->1->3
3->2->3
なので、３つ目の条件チェックが面倒ですが２組ごとの組み合わせで考えないといけないと思います。
（偉そうに言っていますが、せっかく作ってもマクロはダメとか言われると思いっきり疲れるのでまだ作っていません）

No.2 回答者： WindFaller 回答日時： 2018/06/06 21:22

こんばんは。

ご質問の場合は、3の並べ替えの数の6組の数字群を、隣り合わないように、組み合わせパターンを繰り返さないように、72回出力するものではないでしょうか。つまり、2重のチェック項目が必要になるわけです。

1->2->3
1->3->2
2->1->3
2->3->1
3->1->2
3->2->1
の6組の組み合わせ

関数には、以下のような器用なことはできないような気がします。

'//標準モジュール
Sub AsortedNumbers()
Dim i As Long, j As Long, k As Long, m As Long
Dim olj As Long
Dim AsrtSets(1 To 6, 0) '組み合わせセット
AsrtSets(1, 0) = Array(1, 2, 3)
AsrtSets(2, 0) = Array(1, 3, 2)
AsrtSets(3, 0) = Array(2, 1, 3)
AsrtSets(4, 0) = Array(2, 3, 1)
AsrtSets(5, 0) = Array(3, 1, 2)
AsrtSets(6, 0) = Array(3, 2, 1)

Dim arBuf As Variant '出力バッファ
Columns(1).ClearContents '1列目のクリア
Randomize '乱数のおまじない
k = 1 'スタート
olj = 0 'jの前の数字は、最初は0
For i = 1 To 216 \ 3 '3つの数字の組み合わせ回数／216は全体の回数
　Do
　 Do
　 j = Int(Rnd() * 6) + 1
　 Loop While olj = j '前の組み合わせセットを出さない
　 If i = 1 Then
　　arBuf = AsrtSets(j, 0)
　　Exit Do
　 End If
　 arBuf = AsrtSets(j, 0)
　 DoEvents '万が一の暴走を止める
　Loop Until arBuf(0) <> Cells(k - 1, 1).Value '前セットの後尾と重ならない
　Cells(k, 1).Resize(3).Value = Application.Transpose(arBuf)
　k = k + 3
　olj = j 'jを繰り返すことないようにする
Next
End Sub

'//
私なりのアイデアなのですが、いかがでしょうか?
もっと単純に書く人はいらっしゃるとは思います。

216という数は安全圏内ですが、それ以上の極端な数は未知、つまりいづれは出来上がるかもしれませんが、いつになるのか分からないということです。そのためのDoEventsで、メモリを全部使いきらないための命令です。専門的になりますが、その時は、固定の乱数パレットが必要になるのだろうと思います。

No.1 回答者： GooUserラック 回答日時： 2018/06/06 17:27

ある意味乱数ではないですよね。

また単純ではないので関数では多分無理でしょう。
プログラム的なものが必要なのでマクロ（ＶＢＡ）などを使い②の条件の物を何度か作成して①や③の条件に当てはまらなければ自動で作り直す感じになるのだと思いますがそれでも良いのでしょうか？