☰

注意/基準付きのランダム選択

tag-icon • tag-icon microsoft-excel random-number-generator
注意/基準付きのランダム選択

6 x 6 のグリッドがあります。36 個のサンプルが 3 つの均等なグループ (A、B、C) に分割されています。

グリッド内のサンプルをランダムに配置する数式が必要です。ただし、各行と各列には各グループが 2 つずつ含まれている必要があります。これを手動で 1 回か 2 回実行するのは簡単ですが、組み合わせの数は膨大です。

フリーウェアまたは Microsoft Office ベースであれば、ソフトウェアの選択に関しては自由です。

ありがとう！

答え1

いくつかの数式と VBA を使用して、ランダムな行/列の順列を生成するツールを作成しました。シートのレイアウトは次のようになります。

Excel スニップ

参照グリッドは、Gary の学生の予備的な回答 (削除された可能性があります) に投稿された有効なマトリックスの簡単な例です。行と列の順列には、6x6 グリッドの順列の可能なすべての一意の組み合わせが組み込まれています。 (必要に応じて、一意でない順列を含めるように簡単に変更できます。) と の値は、E12:E26特定L12:L26の順列を実行するかどうかの基準となるように、ランダムに 0 または 1 にシードされます。列とDは、KVBA 内での処理を簡素化するために、これらをブール値に変換します (以下を参照)。順列グリッドはdoSwap、配列数式として入力されたカスタム関数 によって生成されます。F9を押してシートの再計算をトリガーすると、さまざまなRAND関数によってランダムな値が再生成され、実行される順列のシリーズが変更されます。

この動作を可能にする VBA コードは次のとおりです。

Function doSwap(srcRg As Range, rowSwaps As Range, colSwaps As Range) As Variant
    Dim workVt As Variant
    Dim iter As Long

    workVt = srcRg.Value

    ' Do row swaps
    For iter = 1 To rowSwaps.Rows.Count
        With rowSwaps
            If .Cells(iter, 3).Value Then
                workVt = swapRow(workVt, .Cells(iter, 1), .Cells(iter, 2))
            End If
        End With
    Next iter

    ' Do col swaps
    For iter = 1 To colSwaps.Rows.Count
        With colSwaps
            If .Cells(iter, 3).Value Then
                workVt = swapCol(workVt, .Cells(iter, 1), .Cells(iter, 2))
            End If
        End With
    Next iter

    ' Store and return
    doSwap = workVt

End Function

Function swapCol(ByVal inArr As Variant, idx1 As Long, idx2 As Long) As Variant
    Dim tempVal As Variant, workVt As Variant
    Dim iter As Long

    ' Check if Range or Array input
    If IsObject(inArr) Then
        If TypeOf inArr Is Range Then
            workVt = inArr.Value
        Else
            swapCol = "ERROR"
            Exit Function
        End If
    Else
        workVt = inArr
    End If

    ' Just crash if not correct size
    ' Do swap
    For iter = LBound(workVt, 1) To UBound(workVt, 1)
        tempVal = workVt(iter, idx1)
        workVt(iter, idx1) = workVt(iter, idx2)
        workVt(iter, idx2) = tempVal
    Next iter

    ' Return
    swapCol = workVt

End Function

Function swapRow(ByVal inArr As Variant, idx1 As Long, idx2 As Long) As Variant
   Dim tempVal As Variant, workVt As Variant
   Dim iter As Long

    ' Check if Range or Array input
    If IsObject(inArr) Then
        If TypeOf inArr Is Range Then
            workVt = inArr.Value
        Else
            swapRow = "ERROR"
            Exit Function
        End If
    Else
        workVt = inArr
    End If

    ' Just crash if not correct size
    ' Do swap
    For iter = LBound(workVt, 2) To UBound(workVt, 2)
        tempVal = workVt(idx1, iter)
        workVt(idx1, iter) = workVt(idx2, iter)
        workVt(idx2, iter) = tempVal
    Next iter

    ' Return
    swapRow = workVt

End Function

上記のコードは十分に堅牢化されていませんが、現在の目的には役立ちます。必要に応じて、拡張/一般化は非常に簡単に行えるはずです。特に、正方形でないものも含め、あらゆるサイズの 2D 参照グリッドをそのまま処理する必要があります。重要なことは、順列命令の配列が適切に設定されていることを確認することです。

編集：少し試してみたところ、このソリューションでは可能な順列の完全な空間にアクセスできないことが明らかになりました。そこで、ランダムな「ビットシフト" を使用して、型ラベルを互いに交換します。簡素化のため、ABCラベルから123ラベルに切り替えました。これにより、簡単なMOD操作で実装できるようになり、行と列の合計の形で一目で妥当性をチェックすることもできます。

Excel スニップ

答え2

これを実現するには非常に簡単な方法があります。まず、事前に割り当てますスロット3 つのタイプそれぞれに:

ここに画像の説明を入力してください

次に最初のサンプル（SAMPLE_A_1など）を取り、無作為にA スロットの 1 つに入力します。その後、残りの 35 個のサンプルをそれぞれ処理し続けます。


このアプローチが受け入れられる場合は、マトリックスにデータを入力する短いプログラムを投稿します。このアプローチが受け入れられない場合は、この投稿を削除します。

関連情報