離れたセルに重複せず整数の乱数を発生させたいです

前提・実現したいこと

エクセルマクロで、離れたセルに重複せず整数の乱数を発生させたいです。
初学者なため、どのようにコードを書けばいいのか、ネット上のものを見てもイマイチわからない状態です。
このコードは、座席表作成のために使います。
皆さんのお力、お貸しいただけないでしょうか。よろしくお願い致します。

表示させたいセル位置
N11,N15,N19,N23,N31,N35,N39,N43
R11,R15,R19,R23,R31,R35,R39,R43
W11,W15,W19,W23,W31,W35,W39,W43
AF11,AF15,AF19,AF23,AF31,AF35,AF39,AF43
AF11,AF15,AF19,AF23,AF31,AF35,AF39,AG43
AP19,AP23,AP31,AP35
AI19,AI23,AI31,AI35
AT11,AT15,AT19,AT23,AT31,AT35,AT39,AT43
AY11,AY15,AY19,AY23,AY31,AY35,AY39,AY43
BC11,BC15,BC19,BC23,BC31,BC35,BC39,BC43
BH11,BH15,BH19,BH23,BH31,BH35,BH39,BH43
BL11,BL15,BL19,BL23,BL31,BL35,BL39,BL43

行動規範の内容に同意します

回答3件

シート上で数式を入れた方が後でメンテナンスがしやすいかと。

席順が決まったら、シート丸ごと新規ブックにコピーし、
セル全体を選択して（行番号と列番号の欄の重なる角を選択）コピーし、
そのまま値のみ貼り付けで数式を消したらいいかと思います。

F9キー押下で再計算ができるので、
何度か再計算してみて、目視で確認し、
ある程度意図的に選ぶことも可能かと思います。

投稿2020/09/26 02:16

mattuwan

総合スコア2136

Takateratail

2020/09/26 03:16

ご親切に回答ありがとうございます。mattuwanさんのご教授頂いた方法も一度試してみようと思います。

行動規範の内容に同意します

ベストアンサー

「重複しない乱数」は乱数とはいいません。
この場合は、「ランダムに並べ替える」と考えましょう。プログラム界ではシャッフルといいます。

シャッフルするには、いろいろ方法はありますが、エクセルなら、
作業用シートに縦一列にセル番地を記入しておきます。

N11
N15
N19
N23
・
・

これをシャッフルするには、下記で紹介しているような方法を使います。

エクセルランダムに並び替えをする

シャッフルができたら、この列のとなりに順に席を割り当てたい人の名前を入力する（どこかに名前の一覧があるならそれをコピーする）。
名前をセル番地をもとに、座席表に転記しておく。
というようなマクロ（VBA）を組めばいいでしょう。

作業シートを使わない別案

下記で配列をシャッフルする関数を紹介しています。

配列をランダムに並べ替える関数 - hatena chips

配列にセル番地を格納しておいて、上記の関数でシャッフルします。
Forループ処理で順にセル番地に名前を転記していきます。

これだと作業シートを使わずできます。

この方法でコードを書いてみたのでご参考に。

vba
1Public Sub SetSeatingChart()
2    Dim SeatList As String 'セル番地をカンマ区切りで格納
3    SeatList = _
4    "N11,N15,N19,N23,N31,N35,N39,N43," & _
5               "R11,R15,R19,R23,R31,R35,R39,R43," & _
6               "W11,W15,W19,W23,W31,W35,W39,W43," & _
7               "AF11,AF15,AF19,AF23,AF31,AF35,AF39,AF43," & _
8               "AF11,AF15,AF19,AF23,AF31,AF35,AF39,AG43," & _
9               "AP19,AP23,AP31,AP35," & _
10               "AI19,AI23,AI31,AI35," & _
11               "AT11,AT15,AT19,AT23,AT31,AT35,AT39,AT43," & _
12               "AY11,AY15,AY19,AY23,AY31,AY35,AY39,AY43," & _
13               "BC11,BC15,BC19,BC23,BC31,BC35,BC39,BC43," & _
14               "BH11,BH15,BH19,BH23,BH31,BH35,BH39,BH43," & _
15               "BL11,BL15,BL19,BL23,BL31,BL35,BL39,BL43"
16
17    Dim ArySeat
18    ArySeat = Split(SeatList, ",") '配列に変換
19    Call AryShuffle(ArySeat)       '配列をシャッフル
20
21    Dim AryName
22    AryName = Range("A1:A88").Value  'A1:A87セルに名前が記入されているとする
23
24    Dim i As Long
25    For i = LBound(ArySeat) To UBound(ArySeat)
26        Range(ArySeat(i)).Value = AryName(i + 1, 1)
27    Next
28End Sub
29
30'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
31' 引数      : バリアント配列、動的配列、配列                    '
32'               宣言例 Dim MyArray As Variant                   '
33' 目的      : 配列をランダムに並べ替える　　　　　　　          '
34' 戻り値    : なし                                              '
35' 使用例  　: Call AryShuffle(MyArray)                               '
36'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
37Public Sub AryShuffle(ByRef MyAry)
38Dim i As Integer, buf, UB As Integer, P As Integer
39    If IsNull(MyAry) Then Exit Sub
40     
41    Randomize
42    UB = UBound(MyAry)
43    For i = UB To 1 Step -1
44        P = Int((i + 1) * Rnd)
45        buf = MyAry(P)
46        MyAry(P) = MyAry(i)
47        MyAry(i) = buf
48    Next
49End Sub