[[20091215131742]]　『重複しないグループ分け』（きたみ）　ページの最後に飛ぶ

『重複しないグループ分け』（きたみ）
　
　excel2003を使用しています

  1.1～２４の数値を６つのグループに分け（最初はベタ打ちでかまいません）
　2.その後１回目に同じグループにいた数字同士が重ならないようにグループ分けをして
　3.１回目と２回目に同じグループにいた数字同士が重ならないようグループ分け
　4.１、２、３回目に同じグループにいた数字同士が重ならないようグループ分け
　ということは可能でしょうか
　取っ掛かりすらわからず困っていますどなたかお助けください

 G1に =RAND() としてG24までフィルダウン

      A         B          C          D           E          F          G
1 Aグループ  Bグループ  Cグループ  Dグループ  Eグループ  Fグループ    =RAND()
2                                                                       ↓
3
4

 グループ名が横でしたら
 A2=MATCH(SMALL($G$1:$G$24,ROW(A1)+4*(COLUMN(A1)-1)),$G$1:$G$24,0)
 F2までオートフィルして、A2:B2をフィルダウン

       A        B      C      D     E
1  Aグループ
2  Bグループ
3  Cグループ
4  Dグループ
5  Eグループ
6  Fグループ

 グループ名が縦でしたら
 B1=MATCH(SMALL($G$1:$G$24,ROW(A1)+6*(COLUMN(A1)-1)),$G$1:$G$24,0)
 E1までオートフィルして、B1:E1をフィルダウン

 などでいかがでしょう。。。(gon-2)

(gon-2)さん回答ありがとうございます。
解りづらい説明ですいません

1回目

 　 A       B        C        D         E        F         
1 　1       5        9        13        17      21
2　 2       6        10       14        18      22                                
3 　3       7        11       15        19      23
4 　4       8        12       16        20      24
２回目
 　 A       B        C        D         E        F         
6　 1       2        3        4         5        6
7　 7       8        9       10         11      12                               
8　 13      14       15      16         17      18
9　 19      20       21      22         23      24
３回目
・・・
４回目
・・・
のようにしたいんです。
そもそも４回シャッフルして重ならないようにできるかすらわかりません
４回実験を行う際に同じ生徒どうしが同じグループにしたくないんです。
よろしくお願いします。（きたみ）

 こんにちは。
＞ ４回シャッフルして重ならないようにできるか
結論から書くと、可能でした。
下の表の、H～M列の6列に含まれる、5つのブロックが回答です。
（2ブロック以降は、1～6を省略しています）
 かなりノープランで作ったので、数式も小刻みなものになってしまいましたが。
（コタ）

 大まかな考え方としては、
・番号1～6をグループ固定とする（例えば1番のいるグループがグループ1）
・そうすると、残りの18人（7～24）の振り分けになる
・各グループ内での順番も関係ないので、それぞれのグループでの番号順位毎に「組」として考える
　（1組：7～12、2組：13～18、3組：19～24）
・重複しないためには、各組内でグループをシフト移動すればいい
　（2行目の7～12を、6行目では1つ右にシフトして、12,7～11としている
　　このとき、7番は1グループから2グループに移るので、N列の「移動」を2と定義する）
・全組の移動先を変えてやれば、全てのグループで重複しない
　（6～8行目では、1組を2で移動、2組を3で、3組を4で移動している）
・このような考え方で移動パターンを決めていくと、N列の
　　234
　　325
　　452
　　543
　の4パターンなら、お互い重複しない（縦で見ても重複していない）ことが分かります。
　※↑これは結果からの判断で、途中までは、O～X列を使って、使用済番号を外したりして
　　作っていました（実はO～X列は不要だったということですね）。

  ※「_」セルは未入力または""
  [R/C]  [A]  [B]  [C]  [D]  [E]  [F]  [G]  [H]  [I]  [J]  [K]  [L]  [M]   [N]  [O]  [P]  [Q]  [R]  [S]  [T]  [U]  [V]  [W]  [X]
   [1]    組   OK   OK   OK   OK   OK   OK    1    2    3    4    5    6  移動   使   用   可   番   号   使   用   済   番   号
   [2]     1    7    8    9   10   11   12    7    8    9   10   11   12     _    _    _    _    _    _    2    3    4    5    6
   [3]     2   13   14   15   16   17   18   13   14   15   16   17   18     _    _    _    _    _    _    2    3    4    5    6
   [4]     3   19   20   21   22   23   24   19   20   21   22   23   24     _    _    _    _    _    _    2    3    4    5    6
   [5]     _    _    _    _    _    _    _    _    _    _    _    _    _     _    _    _    _    _    _    _    _    _    _    _
   [6]     1   12    7    8    9   10   11   12    7    8    9   10   11     2    2    3    4    5    6    x    3    4    5    6
   [7]     2   17   18   13   14   15   16   17   18   13   14   15   16     3    x    3    4    5    6    2    x    4    5    6
   [8]     3   22   23   24   19   20   21   22   23   24   19   20   21     4    x    x    4    5    6    2    3    x    5    6
   [9]     _    _    _    _    _    _    _    _    _    _    _    _    _     _    _    _    _    _    _    _    _    _    _    _
  [10]     1   11   12    7    8    9   10   11   12    7    8    9   10     3    x    3    4    5    6    x    x    4    5    6
  [11]     2   18   13   14   15   16   17   18   13   14   15   16   17     2    2    x    4    5    6    x    x    4    5    6
  [12]     3   21   22   23   24   19   20   21   22   23   24   19   20     5    x    x    x    5    6    2    3    x    x    6
  [13]     _    _    _    _    _    _    _    _    _    _    _    _    _     _    _    _    _    _    _    _    _    _    _    _
  [14]     1   10   11   12    7    8    9   10   11   12    7    8    9     4    x    x    4    5    6    x    x    x    5    6
  [15]     2   15   16   17   18   13   14   15   16   17   18   13   14     5    x    x    x    5    6    x    x    4    x    6
  [16]     3   24   19   20   21   22   23   24   19   20   21   22   23     2    2    3    x    x    6    x    3    x    x    6
  [17]     _    _    _    _    _    _    _    _    _    _    _    _    _     _    _    _    _    _    _    _    _    _    _    _
  [18]     1    9   10   11   12    7    8    9   10   11   12    7    8     5    x    x    x    5    6    x    x    x    x    6
  [19]     2   16   17   18   13   14   15   16   17   18   13   14   15     4    x    x    4    x    6    x    x    x    x    6
  [20]     3   23   24   19   20   21   22   23   24   19   20   21   22     3    x    3    x    x    6    x    x    x    x    6

 ☆1ブロック目
 B1 =IF(SUMPRODUCT((COUNTIF(H1:H30,H1:H30)>1)*1)=0,"OK","NG") →B1:G1フィルコピー
 H1 : 入力（H1:M1の範囲内）
 A2 : 入力（A2:A4の範囲内）
 B2 =H2 →B2:G4フィルコピー
 H2 =M1+1 →H2:M4フィルコピー
 T2 =I$1 →T2:X4フィルコピー

 ☆2ブロック目～
 A6 : 入力（A6:A8の範囲内）
 B6 =H6 →B6:G8フィルコピー
 H6 =OFFSET(H$2,,1-$N6) →H6:M8フィルコピー
 N6 =MIN($O6:$S6) →N6:N8フィルコピー
 O6 =T2 →O6:S6フィルコピー
 O7 =IF($N6=T3,"x",T3) →O7:S7フィルコピー
 O8 =IF(OR($N6=T4,$N7=T4),"x",T4) →O8:S8フィルコピー
 T6 =IF($N6=T2,"x",T2) →T6:X8フィルコピー
 ※H6:M8を選択後、条件付書式「数式が」=MOD(H6,6)=1 を設定しておくと移動状況がみやすい
 以下、2ブロック目（A6:X8）を1行ずつ空けてコピペ

 連投で失礼します。
2点ありまして、1点目は上の回答の訂正です。

 ☆2ブロック目～
 H6 =OFFSET(H$2,,1-$N6) →H6:M8フィルコピー
の箇所ですが、各行で数式を変えないといけませんでしたので、
 H6 =OFFSET(H$2,,1-$N6) →H6:M6フィルコピー
 H7 =OFFSET(H$3,,1-$N7) →H7:M7フィルコピー
 H8 =OFFSET(H$4,,1-$N8) →H8:M8フィルコピー
と訂正します。失礼しました。

 2点目ですが、ごめんなさい、実は重なっていました。
  [R/C]  [A]  [I]  [J]     [N]  
   [1]    組    2    3    移動  
   [6]     1    7    8       2  
   [7]     2   18   13       3  
   [8]     3   23   24       4  
   [9]     _    _    _      _  
  [10]     1   12    7       3  
  [11]     2   13   14       2  
  [12]     3   22   23       5  
 2グループ（I列）の7・23が、3グループ（J列）でも現れています。
原因は、1組と3組の移動の差が、上は2と4で差2、下も3と5で差2と同じになっているので、
相対的な移動量が変わらないためです。

 もう少し考えてみますが、なんとなく不可能が気がしてきました。。
（コタ）

 こんにちは。
だいぶ間が空いてしまいましたが、結果が出ました。
また結論から書くと（今度はミスってないといいですが^^;）、可能でした。
下の表の、A～F列の6列に含まれる、5つのブロックが回答です。
（コタ）

  ※「_」セルは未入力または""
  [R/C]  [A]  [B]  [C]  [D]  [E]  [F]  [G]  [H]  [I]  [J]  [K]  [L]  [M]  [N]  [O]  [P]  [Q]  [R]  [S]  [T]  [U]  [V]  [W]  [X]  [Y]  [Z] [AA]  [AB] [AC] [AD] [AE] [AF]
   [1]     1    5    9   13   17   21    _    0    1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24
   [2]     2    6   10   14   18   22    _    1    _    1    1    1    2    3    4    5    2    4    3    _    2    5    4    3    _    _    _    5    _    _    _    _
   [3]     3    7   11   15   19   23    _    2    _    _    1    1    3    2    5    4    4    2    _    3    5    2    3    4    _    _    5    _    _    _    _    _
   [4]     4    8   12   16   20   24    _    3    _    _    _    1    4    5    2    3    _    _    _    5    _    _    _    _    2    4    3    _    2    5    4    3
   [5]     _    _    _    _    _    _    _    4    _    _    _    _    5    4    3    2    _    _    5    _    _    _    _    _    4    2    _    3    5    2    3    4
   [6]     1    2    3    4   11   12    _    5    _    _    _    _    _    1    1    1    2    _    5    3    2    _    3    _    _    4    _    _    5    _    4    _
   [7]     5    6    7    8   15   16    _    6    _    _    _    _    _    _    1    1    _    2    3    5    _    2    _    3    4    _    _    _    _    5    _    4
   [8]     9   10   17   18   19   20    _    7    _    _    _    _    _    _    _    1    _    4    _    _    5    _    4    _    2    _    5    3    2    _    3    _
   [9]    13   14   21   22   23   24    _    8    _    _    _    _    _    _    _    _    4    _    _    _    _    5    _    4    _    2    3    5    _    2    _    3
  [10]     _    _    _    _    _    _    _    9    _    _    _    _    _    _    _    _    _    1    1    1    2    3    5    4    3    5    _    _    _    3    _    5
  [11]     1    2    3    4    9   10    _   10    _    _    _    _    _    _    _    _    _    _    1    1    3    2    4    5    5    3    _    _    3    _    5    _
  [12]     6    5    8    7   14   13    _   11    _    _    _    _    _    _    _    _    _    _    _    1    4    _    2    3    _    _    2    4    5    4    2    _
  [13]    11   12   19   20   17   18    _   12    _    _    _    _    _    _    _    _    _    _    _    _    _    4    3    2    _    _    4    2    4    5    _    2
  [14]    16   15   24   23   22   21    _   13    _    _    _    _    _    _    _    _    _    _    _    _    _    1    1    1    _    3    5    4    3    4    _    _
  [15]     _    _    _    _    _    _    _   14    _    _    _    _    _    _    _    _    _    _    _    _    _    _    1    1    3    _    4    5    4    3    _    _
  [16]     1    2    3    4   11   12    _   15    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    1    _    5    2    _    _    _    2    5
  [17]     7    8    5    6   13   14    _   16    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    5    _    _    2    _    _    5    2
  [18]    10    9   18   17   20   19    _   17    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    1    1    1    2    3    5    4
  [19]    15   16   23   24   22   21    _   18    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    1    1    3    2    4    5
  [20]     _    _    _    _    _    _    _   19    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    1    4    _    2    3
  [21]     1    2    3    4    9   10    _   20    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    4    3    2
  [22]     8    7    6    5   15   16    _   21    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    1    1    1
  [23]    14   13   12   11   18   17    _   22    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    1    1
  [24]    20   19   22   21   24   23    _   23    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    1
  [25]     _    _    _    _    _    _    _   24    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _
  [26]     _    _    _    _    _    _    _   組    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _
  [27]     _    _    _    _    _    _    _    1   36    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _
  [28]     _    _    _    _    _    _    _    2   36    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _
  [29]     _    _    _    _    _    _    _    3   36    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _
  [30]     _    _    _    _    _    _    _    4   36    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _
  [31]     _    _    _    _    _    _    _    5   36    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _    _

 右側の24×24表は、2つ数値ペアが、同グループになっているかどうかを調べたものです。
・例えば1と2は、1組目の1グループでペアになっているので、J2セル（縦が1で横が2）が1となり、
・4と5は、5組目の4グループでペアになっているので、M5セル（縦が4で横が5）が1となっています。
同様にペアになっている数値について、全てうめていった結果、各組のペア数がそれぞれ36ずつと
なりました（H27:I31）。
1つの組から作られるペアの数は、(3+2+1)×6=36なので、このことから各組どうしでも重複して
いないことが分かります。
（長くなったので分割します）

 （続き）
 ところで、上の組合せはマクロで作成しましたので、一応コードを挙げておきます。
動かすと5分以上かかりますし、再帰的に組んでいるので非常に分かりにくいですが、もし試すなら
・新規ブックの標準モジュールに以下のコードをコピペして、
・Test1を実行
してみてください。
なお、このコードでは、6組目が作成できませんが、5組がMAXかどうかは分かりません。
ペア数上限（276=COMBIN(24,2)）から考えると、276÷36=7.66なので、8組以上は不可能とは言えますが。

 【Module1】標準モジュール
Option Explicit
Dim v, pea, use
'
Sub test1()
    Dim L As Long, flg As Long
    Dim r As Range
    With ActiveSheet
        .Rows(1).ColumnWidth = 2
        Set r = .Range("A1:F4")
        .Cells.ClearContents
    End With
    v = r.Value
    ReDim pea(1 To 24, 1 To 24)
    '移動
    Do
        flg = 0
        ReDim use(1 To 24)
        v = r.Offset(L * 5).Value
        v(1, 1) = 1
        use(1) = 1
        Call NumIdo(v, pea, use, 2, flg)
        Application.StatusBar = False
        If flg = 0 Then Exit Do
        r.Offset(L * 5).Value = v
        L = L + 1
    Loop
    Call peaPut
    End
End Sub
'
Private Sub NumIdo(ByVal v2, ByVal pea2, ByVal use2, _
        ByRef h As Long, ByRef flg As Long)
    Dim i As Long, j As Long, k As Long, L As Long
    If h > 24 Then
        v = v2: pea = pea2: use = use2
        flg = 1 '成功
        Exit Sub
    End If
    i = (h - 1) Mod 4 + 1
    j = (h - 1) \ 4 + 1
    L = 2
    If i > 1 Then L = v2(i - 1, j) + 1 'Grp内昇順
    For k = L To 24
        If use2(k) = 0 Then
            If peaCheck(v2, pea2, i, j, k) Then
                v2(i, j) = k
                use2(k) = 1
                Call peaSet(v2, pea2, i, j, 1)
                Call NumIdo(v2, pea2, use2, h + 1, flg)
                If flg = 1 Then
                    Exit Sub
                Else '失敗→元に戻す
                    Call peaSet(v2, pea2, i, j, 0)
                    v2(i, j) = 0
                    use2(k) = 0
                End If
            End If
        End If
    Next
End Sub
'
Private Function peaCheck(ByRef v2, ByRef pea2, _
        ByVal i As Long, ByVal j As Long, ByVal k As Long) As Boolean
    Dim L As Long, mn As Long, mx As Long
    peaCheck = True
    For L = i - 1 To 1 Step -1
        mn = Application.Min(k, v2(L, j))
        mx = Application.Max(k, v2(L, j))
        If pea2(mn, mx) = 1 Then peaCheck = False
    Next
End Function
'
Private Sub peaSet(ByRef v2, ByRef pea2, _
        ByVal i As Long, ByVal j As Long, ByVal b As Long)
    Dim L As Long, mn As Long, mx As Long
    For L = i - 1 To 1 Step -1
        If v2(i, j) > 0 And v2(L, j) > 0 Then
            mn = Application.Min(v2(i, j), v2(L, j))
            mx = Application.Max(v2(i, j), v2(L, j))
            If pea2(mn, mx) = b Then Stop
            pea2(mn, mx) = b
            pea2(mn, mx) = b
        End If
    Next
End Sub
'
Sub peaPut()
    Dim i As Long, j As Long, L As Long
    Dim r As Range
    ReDim pea(1 To 24, 1 To 24)
    With ActiveSheet
        Set r = .Range("A1:F4")
        For L = 0 To .Range("A" & Rows.Count).End(xlUp).Row \ 5
            For j = 1 To 6
                For i = 1 To 4
                    Call peaSet(r.Offset(L * 5).Value, pea, i, j, L + 1)
                Next
            Next
        Next
        With .Range("H1")
            .CurrentRegion.ClearContents
            .Value = 0
            .Resize(, 25).DataSeries
            .Resize(25).DataSeries
            .Offset(1, 1).Resize(24, 24).Value = pea
        End With
        With .Range("H26")
            .Value = "組"
            .Offset(1).Value = 1
            .Offset(1).Resize(5).DataSeries
            .Offset(1, 1).Resize(5).Formula = "=COUNTIF(R2C9:R25C32,RC[-1])"
        End With
    End With
End Sub

コメント返信：

[ 一覧(最新更新順) ]

[[20091215131742]] 『重複しないグループ分け』（きたみ） ページの最後に飛ぶ

[[20091215131742]]　『重複しないグループ分け』（きたみ）　ページの最後に飛ぶ