みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

Excel2007で乱数を利用して

乱数を利用して抜き出したいです

0~9の数字記号がありまして、その内のいずれかがセルA~AJの各セルに入ってます。

セルA~AJは1~36番目と言うことなんで数字1~36を乱数で被ることなく6個をとり

その6個を利用してセルA~AJの数字記号を抜き出してセルQ6~V6に表したいです。

マクロボタンを押す度に別の乱数でまた抜き出しては次のQ7~V7へと、違う乱数を使っては下へ下へと表示したいです。

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

投稿日時 - 2019-04-26 11:59:03

QNo.9610714

困ってます

質問者が選んだベストアンサー

とりあえずQ6~V6に乱数の結果を視覚化するために表示して
Q7~V7に2行目のデータから該当するものを取得記載するようにしています。

Sub ボタン1_Click()
Dim MyDic As Object, MyKey As Variant
Dim i As Long
Dim MyRand As Long, LastRow As Long

'6個の重複しない乱数1~36の取得
Set MyDic = CreateObject("Scripting.Dictionary")
For i = 1 To 6
MyRand = WorksheetFunction.RandBetween(1, 36)
If MyDic.Exists(MyRand) = False Then
MyDic.Add MyRand, "Dummy"
Else
i = i - 1
End If
Next i
MyKey = MyDic.keys
Set MyDic = Nothing
'Q6からV6に取得した6個の乱数を記載
Cells(6, "Q").Resize(1, UBound(MyKey) + 1).Value = MyKey
'Q6からV6の数値を列データとして2行目のデータを取得して
'Q7からV7を先頭にして下方向に追加記載
LastRow = Cells(Rows.Count, "Q").End(xlUp).Row + 1
' If LastRow < 6 Then
' LastRow = 6
' End If
For i = 0 To 5
Cells(LastRow, "Q").Offset(0, i).Value = Cells(2, MyKey(i)).Value
Next
End Sub

投稿日時 - 2019-04-29 10:25:44

お礼

いつもご返答ありがとうございます。(Q6:V6)に重複することなく1~36の乱数を表示することに成功しましたが、
それらを利用しての(Q7:V7)への抜き出しコピペは失敗してまして、マクロが作動中に、R7に抜き出してる数値が5回表示されては消えて最後の6回目の数値が表示されるという結果になりました。何処を治せばよろしいでしょうか?

投稿日時 - 2019-04-29 15:42:35

ANo.13

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(18)

ANo.18

一度テスト用のブックを作成して
2行目に
A2からAJ2までを
11から46までの連番(数値)にして試してみてください。
乱数が表示されたセルの値に10を足したものが表示されたら正解です。

投稿日時 - 2019-04-30 07:29:44

補足

kkkkkmさん!!!!!すみません!!!
私の大失敗です。Σ(T▽T;)

ソースの最後辺りのoffset(0,i)のところを(0,1)にしてしまってました。

本当にすみません。

そして治して試したところ滅茶苦茶希望通りにスッキリ作動してくれました。
ありがとうございます。

投稿日時 - 2019-04-30 12:40:27

お礼

こんにちはkkkkkmさん。
試したんですが昨日と変わりがありませんでした。
乱数でV6に9が表示されてまして、R7に19が表示されてます。
今はQ6~V6に乱数で31,5,33,13,1,9と表示されてます。前の様子と変わらず、V6が"9"なんで、R6"5"の下にR7に"19"と表示されてます。う~ん、難しい域なんですかねやっぱり。

投稿日時 - 2019-04-30 12:22:44

ANo.17

>> 抜き出されている数値は確かに0から9の一桁なのでしょうか。
> はい、0~9の数字だけです。

桁についても確認していますよ。7桁でセル幅広げたら表示されたということでいいですね。

> 2回目を試したのですが、下に下にコピペされることもなかったです。

Q列にデータがなければ7行目に記載し続けます。A2~AJ2に必ずデータが入っていればそのような事にはならないと思いますが。

投稿日時 - 2019-04-29 18:31:00

お礼

>>桁についても確認していますよ。7桁でセル幅広げたら表示されたということでいいですね。

7桁入るセルに一桁の数字が入ってる状態なんでセル幅に問題はなさそうなんですが。

投稿日時 - 2019-04-29 23:02:48

ANo.16

ここって回答したら削除も訂正もできないから不便で…。
追加です。
とりあえずQからVのセル幅を広げてみてください。

投稿日時 - 2019-04-29 17:27:31

補足

もうkkkkkmさんのソースが完成しそうなんでドキドキしてます。待ち遠しいです。

投稿日時 - 2019-04-29 17:55:10

お礼

しっかり7桁入ります。(^_^)

投稿日時 - 2019-04-29 17:33:25

ANo.15

> マクロが作動中に、R7に抜き出してる数値が5回表示されては消えて最後の6回目の数値が表示される

面白い動作ですね。
R7からU7まで何か表示されてV7に数値が表示された途端にR7からU7までが消えるということだと思いますが。
抜き出されている数値は確かに0から9の一桁なのでしょうか。
セル幅を超えるデータが入っているという事はないですか。
"     9"
みたいな感じで。
一度テスト用のブックを作成して
2行目に
A2からAJ2までを
11から16までの連番(数値)にして試してみてください。
乱数が表示されたセルの値に10を足したものが表示されたら正解です。

投稿日時 - 2019-04-29 17:06:17

お礼

いつもご返答ありがとうございます。

>抜き出されている数値は確かに0から9の一桁なのでしょうか。
はい、0~9の数字だけです。

2回目を試したのですが、下に下にコピペされることもなかったです。

投稿日時 - 2019-04-29 17:42:18

ANo.14

なお、以前から言っていますが
設問の状態だと
たとえば
36個のうち6個の当たりがあるくじを、なるべく公平になるように10人に配って乱数であたりを決めたとしても不公平(4枚もらえるのが6人で3枚もらえるのが4人なので)な状態だと思います。そういうのが関係ないデータでしたらいいのですが。

投稿日時 - 2019-04-29 11:20:46

お礼

助かりました。本当にありがとうございました。

投稿日時 - 2019-04-30 18:17:36

ANo.12

>("6"、"2"、"2"、"0"、"3"、"7")
>("1"、"1"、"8"、"2"、"2"、"5")
>見たいに表示する形になると思われます。
それで良いのでしたらRANDBETWEEN関数で直接配置可能です。
D6セルへ次の数式を設定してI6までオートフィルコピーします。
=TEXT(RANDBETWEEN(0,9),"0") 文字列の場合
=RANDBETWEEN(0,9) 数値で良い場合
D6:I6を選択してコピーでクリップボードへ記憶させてD7を選択し値の貼り付けます。(D6:I6の値は再計算されます)
次にD8を選択して値の貼り付けをします。
以下、D9、D10、D11、・・・・・と順次貼り付ければ良いでしょう。

投稿日時 - 2019-04-28 13:30:18

お礼

何処かに水平型で6個のセル範囲にrandbetweenで1~36の内の6個を並べて重複すればやり直して、重複しなければそれらを利用して(AA2:AJ2)から値を取り出すことは出来ないのでしょうか?
(AA2:AJ2)から絶対に抜き出して、どうなって行くのかが見たいです。

Excel2007で出来ないもんなんでしょうか?Excelの限度なんか全く知らずに質問してばかりですみません。

投稿日時 - 2019-04-29 07:10:29

ANo.11

No10の訂正

Q5からV5までとりあえず乱数で出たものとして手入力します
以下に訂正
Q9からV9までとりあえず乱数で出たものとして手入力します

投稿日時 - 2019-04-28 12:02:35

お礼

ありがとうございました。

投稿日時 - 2019-04-30 18:18:26

ANo.10

> ランダムに選択する以上運任せみたいな感じです。

そうですね。皆さんが回答しても、あなたがその内容を理解しない限り、その結果が正しいのかどうか判断しようがないということですね。

乱数を可視化するとどうでしょう。
あまり飛ぶと確認するのが面倒なので結果がでるQ6~V6の近くにデータを仮に入れてみます。
Q5からV5までとりあえず乱数で出たものとして手入力します。(実際に乱数で入れるとやたら飛んで確認しにくくなるので)

Q9に22
R9に15
S9に21
T9に17
U9に24
V9に12

上記で乱数で得たとして仮に入力した数値はそれぞれ上から
V列 O列 U列 Q列X列 L列
を指します。(A列から数えた場合)

Q6に
=INDEX($A$2:$AJ$2,1,Q9)
としてV6までコピーします。

Q6にV2
R6にO2
S6にU2
T6にQ2
U6にX2
V6にL2

の値が表示されます。

で、実際に上記を実行した結果があなたの考えている結果と一致するかどうかですね。

投稿日時 - 2019-04-28 12:01:02

お礼

おはようございます。今読みました。この形であってるんですが、手入力した乱数のところはrand関数を使えば重複しそうなんですが、しないようにするにはどの様な方法がとられる必要がらありますか?

投稿日時 - 2019-04-29 08:21:10

ANo.9

>ご理解されにくい内容なため再度説明させてください。
何回説明されてもあなたが求める結果がどのようになることを望んでいるか読み取れませんのでA2~AJ2のサンプルデータとD6~I6の抽出データのサンプルを提示してください。

>(A2:AJ2)に0~9の数字が入ってます(もちろん数字は重複してます。)
>(A2:AJ2)から被ることなくランダムに選択して6個だけ取り出したいです。
0~9の数字をランダムに36個並べ、そこから6個の数字をランダムに取り出しても重複する数字になる可能性があります。

>(1回のマクロ作業でA2を2回、M2を3回とかは無しでお願いします。)
回答No.4はマクロ(VBA等)ではなくExcelの組み込み関数で処理するものですから値のコピーを実行する毎に自動再計算でコピー元の値が変化します。
考え方としては0~9までの10個の数字から6個をランダムに取り出す方法に比べて0~9までの36個の数字から6個をランダムに取り出す方が同じ数字の再現率が高くなります。
一般的には抽出結果が重複しない方法を採用するのではないでしょうか?

投稿日時 - 2019-04-27 21:26:39

お礼

おはようございます。ご返答ありがとうございます。
Excelでは可能ではなさそうなんでしょうか?乱って重複しますもんね。
(A2:AJ2)から取り出すときに重複することがなく(一回のマクロ作業でD2を3回、P2を2回と選択することなく)取り出し、
(D6:I6)以降に取り出した数字を表示していくことが希望の形です。
("6"、"2"、"2"、"0"、"3"、"7")
("1"、"1"、"8"、"2"、"2"、"5")
見たいに表示する形になると思われます。

投稿日時 - 2019-04-28 10:39:29

ANo.8

> (A2:AJ2)に0~9の数字が入ってます(もちろん数字は重複してます。)
> (A2:AJ2)から被ることなくランダムに選択して6個だけ取り出したいです

0~9の36個のランダムな数値の並びから6個無作為に取り出すとした場合は、4種類の数値は他の6種類の数値より1個少なく配置されていると思いますので、その分偏りますよね。
という結果になるんじゃないですかという事です。(実行するまでもなくわかることとして)
で、思考がどう働くのかを確認してみてください。

投稿日時 - 2019-04-27 19:30:03

お礼

うーん(^-^;
おそらく推察されてることとは違うことを私はしてます。が、違うとまでは言いきれません。ランダムに選択する以上運任せみたいな感じです。
あのときのご協力ありがとうございます。
あなたもですし、皆さん本当に凄い。何かを作りたいと思わない以上パソコンに触れる機会さえなかったし未だにパソコンにインターネットも
繋げてなくて本当に初心者で助けて頂いて
有り難く思います。いつも気をかけて頂いてありがとうございます。

投稿日時 - 2019-04-27 20:36:15

ANo.7

> あなたの仰る通り私もそうなんだと思います。が、どうなんでしょうw

もちろん単に0~9までの数値をランダムに6個表示したいのであればという話です。
設問のように
0~9の36個のランダムな数値の並びから6個無作為に取り出すとした場合は、4種類の数値は他の6種類の数値より1個少なく配置されていると思いますので、その分偏りますよね。

投稿日時 - 2019-04-27 18:31:54

お礼

ご返答ありがとうございます。ご理解されにくい内容なため再度説明させてください。(私の説明下手がいけないのですが)

(A2:AJ2)に0~9の数字が入ってます(もちろん数字は重複してます。)

(A2:AJ2)から被ることなくランダムに選択して6個だけ取り出したいです。(1回のマクロ作業でA2を2回、M2を3回とかは無しでお願いします。)

取り出した6個の値は(D6:I6)にコピペします(その時の値は重複してたりしてます)。

そして次のマクロ作業の時にはまたランダムに選択をし、(D6:I6)の次の(D7:I7)にコピペして行きたいです。

と、この様な質問内容でした。いつも質問し始めは頭の中が混乱してる最中で上手く伝えれてなかったりしてすみません。

投稿日時 - 2019-04-27 18:59:30

ANo.6

>(A2:AJ2)から被ることなく
>ランダムに選択して6個だけ取り出したいです。

例えば、
A2=3
J2=3
L2=3
Q2=3
U2=3
X2=3
と埋まっており
これらのセル6個がヒットしたときに
拾い出す値は
3,3,3,3,3,3 となるわけですが、
この動作が正しいということであれば
以下のコードになると思います。


Option Explicit

Dim xNum(6) As Long  '重複しない6個の列番号


Sub Sample3()
 
 Const sCol = 17   '出力セル開始列番号
 Const RowAdd = 5     '6行目から出力
 Dim wkCounter As Long
 Static RowCounter As Long
 
 Get6Num
 
 RowCounter = RowCounter + 1 '出力行をカウントアップ
 
 With ThisWorkbook.Sheets(1)
  For wkCounter = 1 To 6
   .Cells(RowCounter + RowAdd, wkCounter + sCol - 1).Value = _
    .Cells(2, xNum(wkCounter)).Value
  Next wkCounter
 End With

End Sub

Sub Get6Num()

 xNum(1) = GetRandomiz
 Do
  xNum(2) = GetRandomiz
  If xNum(2) <> xNum(1) Then Exit Do
 Loop
 Do
  xNum(3) = GetRandomiz
  If ((xNum(3) <> xNum(1)) And _
    (xNum(3) <> xNum(2))) Then Exit Do
 Loop
 Do
  xNum(4) = GetRandomiz
  If ((xNum(4) <> xNum(1)) And _
    (xNum(4) <> xNum(2)) And _
    (xNum(4) <> xNum(3))) Then Exit Do
 Loop
 Do
  xNum(5) = GetRandomiz
  If ((xNum(5) <> xNum(1)) And _
    (xNum(5) <> xNum(2)) And _
    (xNum(5) <> xNum(3)) And _
    (xNum(5) <> xNum(4))) Then Exit Do
 Loop
 Do
  xNum(6) = GetRandomiz
  If ((xNum(6) <> xNum(1)) And _
    (xNum(6) <> xNum(2)) And _
    (xNum(6) <> xNum(3)) And _
    (xNum(6) <> xNum(4)) And _
    (xNum(6) <> xNum(5))) Then Exit Do
 Loop

End Sub


'整数な乱数発生関数
Function GetRandomiz()
 Const iMin = 1  '開始範囲From
 Const iMax = 36  '開始範囲To
 Call Randomize
 GetRandomiz = Int((iMax - iMin + 1) * Rnd + iMin)
End Function

投稿日時 - 2019-04-27 17:17:26

補足

これらのソースが希望の形になるんでしょうか?
凄いですね。

投稿日時 - 2019-04-27 17:52:32

お礼

ありがとうございました。ご協力頂いて気持ちが良かったです。

投稿日時 - 2019-04-30 18:21:00

ANo.5

・A2:AJ2の36個のセルに0~9の任意の整数がランダムに埋まっている
・ここから乱数を使って、重複しない任意な6個の値を拾い出す。
と理解しました。

A2:AJ2の35個のセルを使うことで、
拾う値(0から9)に重みづけが可能かもしれませんが
それを除けば
既に他の方から指摘がありますように
単に、
0~9の任意の整数から重複しない6個を拾い出すのと同等に思えます。

ともあれ、期待のことを素直に受け取れば
つぎのようなコードになると思います。

Option Explicit

Sub Sample2()
 
 Const RowAdd = 5     '6行目から出力
 Dim wkCounter As Long
 Dim ColData(6) As Long  '出力個数
 
 Static RowCounter As Long
 RowCounter = RowCounter + 1 '出力行をカウントアップ
 Const sCol = 17   '出力セル開始列番号
 
 With ThisWorkbook.Sheets(1)
  
  Do
   For wkCounter = 1 To 6
    ColData(wkCounter) = .Cells(2, GetRandomiz).Value
   Next wkCounter
   
   If ( _
    (ColData(1) <> ColData(2)) And _
    (ColData(1) <> ColData(3)) And _
    (ColData(1) <> ColData(4)) And _
    (ColData(1) <> ColData(5)) And _
    (ColData(1) <> ColData(6)) And _
    (ColData(2) <> ColData(3)) And _
    (ColData(2) <> ColData(4)) And _
    (ColData(2) <> ColData(5)) And _
    (ColData(2) <> ColData(6)) And _
    (ColData(3) <> ColData(4)) And _
    (ColData(3) <> ColData(5)) And _
    (ColData(3) <> ColData(6)) And _
    (ColData(4) <> ColData(5)) And _
    (ColData(4) <> ColData(6)) And _
    (ColData(5) <> ColData(6)) _
      ) Then
    Exit Do
   End If
  Loop
 
  For wkCounter = 1 To 6
   .Cells(RowCounter + RowAdd, wkCounter + sCol - 1).Value = _
    ColData(wkCounter)
  Next wkCounter
 
 End With

End Sub


'整数な乱数発生関数
Function GetRandomiz()
 Const iMin = 1  '開始範囲From
 Const iMax = 36  '開始範囲To
 Call Randomize
 GetRandomiz = Int((iMax - iMin + 1) * Rnd + iMin)
End Function

投稿日時 - 2019-04-27 15:25:40

お礼

ご返答ありがとうございます。ご理解されにくい内容なため再度説明させてください。(私の説明下手がいけないのですが)

(A2:AJ2)に0~9の数字が入ってます(もちろん数字は重複してます。)

(A2:AJ2)から被ることなくランダムに選択して6個だけ取り出したいです。(1回のマクロ作業でA2を2回、M2を3回とかは無しでお願いします。)

取り出した6個の値は(D6:I6)にコピペします(その時の値は重複してたりしてます)。

そして次のマクロ作業の時にはまたランダムに選択をし、(D6:I6)の次の(D7:I7)にコピペして行きたいです。

この様に質問を訂正させて頂きました。この内容とホホパパさんに作って頂いたソースは整合してますでしょうか?
質問で返す様な形で申し訳ありません。

投稿日時 - 2019-04-27 15:41:32

ANo.4

回答No.1への補足より
>A2:AJ2の各セルに数字記号である0~9のいずれかがランダムに入ってます。
A2:AJ2の各セルには文字列として"0"~"9"の1文字の数字が入力されているのですか?
セルの数が36ですから重複することになりますよね?

>A2~AJ2の数字記号を6個だけ抜き出してD6~I6に表したいです。
>なのでD6~I6は0~9の数字記号になります。
D6~I6へ"0"~"9"の文字列としての1文字をランダムに配置できれば良いのでしたら質問の中で述べている「セルA~AJは1~36番目と言うことなんで数字1~36を乱数で被ることなく6個をとり」は無意味かと思います。
A2~J2へ重複のない乱数を発生させて小さい順(大きい順でも可)に列番号を6個取り出せば良いことになります。但し、取り出した列番号から1を減じないと0~9になりません。
A2:J2=RAND()
D6=TEXT(MATCH(SMALL($A$2:$J$2,COLUMN(A6)),$A$2:$J$2,0)-1,"0")
D6セルを右へI6までコピーします。

この回答はExcelの組み込み関数による処理なので「マクロボタンを押す度に別の乱数でまた抜き出して」という操作はできませんがD6:I6をコピーしてD7以降へ順次「値の貼り付け」を実行すれば目的通りになるでしょう。

投稿日時 - 2019-04-27 12:45:17

お礼

ご返答ありがとうございます。ご理解されにくい内容なため再度説明させてください。(私の説明下手がいけないのですが)

(A2:AJ2)に0~9の数字が入ってます(もちろん数字は重複してます。)

(A2:AJ2)から被ることなくランダムに選択して6個だけ取り出したいです。(1回のマクロ作業でA2を2回、M2を3回とかは無しでお願いします。)

取り出した6個の値は(D6:I6)にコピペします(その時の値は重複してたりしてます)。

そして次のマクロ作業の時にはまたランダムに選択をし、(D6:I6)の次の(D7:I7)にコピペして行きたいです。
よろしくお願いいたします。

投稿日時 - 2019-04-27 15:31:45

ANo.3

ANo.1です。

> >セルA2:AJ2に=RAND()と入れて、を
> A3:AJ3に=RAND()に変えまして、シート関数でよろしいですか?

その場合、マクロ側も変更が必要です
  .Formula = "=OFFSET($A$1,0,MATCH(LARGE(2:2,COLUMN(A1)),2:2,0)-1)"
    ↓
  .Formula = "=OFFSET($A$1,1,MATCH(LARGE(3:3,COLUMN(A1)),3:3,0)-1)"

お試しください。

投稿日時 - 2019-04-26 22:24:06

お礼

ありがとうございました。ご協力感謝します。

投稿日時 - 2019-04-30 18:22:20

ANo.2

結果として0~9までの数値をランダムに表示したいという動作でしょうか
関数で
=RANDBETWEEN(0,9)
でいいと思いますが。

投稿日時 - 2019-04-26 21:37:06

お礼

凄いところを突いてきてらっしゃるんですが、あなたの仰る通り私もそうなんだと思います。が、どうなんでしょうw
自己満足の世界にひたりたいのか、結果をみて次に思考がどう働くのかを経験したいのです。
なんか滅茶苦茶楽しいんですw
次に自分の頭がどう働くのかがw

投稿日時 - 2019-04-26 22:28:50

ANo.1

「A~AJの各セル」ってのはA1:AJ1の各セルと言う認識で良いですよね?

以下の様にします。
セルA2:AJ2に=RAND() と入れて、フォント色を白にでもしてください。
マクロはこんな感じ。
Sub Sample()
  nRow = WorksheetFunction.Max(6, Cells(Rows.Count, 17).End(xlUp).Row + 1)
  With Range("Q" & nRow & ":V" & nRow)
    .Formula = "=OFFSET($A$1,0,MATCH(LARGE(2:2,COLUMN(A1)),2:2,0)-1)"
    .Value = .Value
  End With
End Sub

添付の図は、解りやすくするため、A1:AJ1に「1~36」までの数値を入れていますし、A2:AJ2のフォント色を黒にしています。

投稿日時 - 2019-04-26 15:51:41

お礼

上手くいきませんでした。
ごめんなさい!書き損じてました。A2:AJ2の各セルに数字記号である0~9のいずれか
がランダムに入ってます。1~36の乱数を使って
A2~AJ2の数字記号を6個だけ抜き出してD6~I6に表したいです。
なのでD6~I6は0~9の数字記号になります。

>セルA2:AJ2に=RAND()と入れて、を
A3:AJ3に=RAND()に変えまして、シート関数でよろしいですか?

投稿日時 - 2019-04-26 20:05:44

あなたにオススメの質問