Zufällige Auswahl mit Vorbehalt / Kriterien

Question

Ich habe ein 6 x 6-Raster. Ich habe 36 Proben, die in drei gleich große Gruppen aufgeteilt sind (A, B und C).

Ich möchte eine Formel, um die Proben in diesem Raster zufällig anzuordnen. Allerdings muss jede Zeile und jede Spalte zwei Exemplare jeder Gruppe enthalten. Dies ein- oder zweimal manuell zu tun, ist einfach, aber es gibt eine riesige Anzahl von Kombinationen.

Ich bin hinsichtlich der Softwareauswahl offen, solange es sich um Freeware oder Microsoft Office-basiert handelt.

Danke schön!

Answer 1

Ich habe ein Tool zum Generieren zufälliger Zeilen-/Spaltenpermutationen mit einigen Formeln und etwas VBA zusammengestellt. Das Blattlayout sieht folgendermaßen aus:

Das Referenzraster ist ein triviales Beispiel einer gültigen Matrix, wie in Garys Student's vorläufiger Antwort gepostet (möglicherweise inzwischen gelöscht). Die Zeilen- und Spaltenpermutationen umfassen alle möglichen eindeutigen Kombinationen von Permutationen für das 6x6-Raster. (Dies könnte bei Bedarf leicht geändert werden, um nicht eindeutige Permutationen einzuschließen.) Die Werte in E12:E26und L12:L26werden zufällig mit null oder eins gesetzt, um die Grundlage dafür zu bilden, ob eine bestimmte Permutation ausgeführt wird oder nicht. Spalten Dund Kkonvertieren diese einfach in Boolesche Werte, um die Handhabung in VBA zu vereinfachen (siehe unten). Das permutierte Raster wird von der benutzerdefinierten Funktion generiert doSwap, die als Array-Formel eingegeben wird. Wenn Sie drücken, F9um eine Neuberechnung des Blatts auszulösen, generieren die verschiedenen RANDFunktionen ihre Zufallswerte neu, wodurch die Reihe der auszuführenden Permutationen geändert wird.

Der VBA-Code, der dieses Verhalten ermöglicht, ist:

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

Der obige Code ist nicht besonders robust, erfüllt aber den vorliegenden Zweck. Eine Erweiterung/Verallgemeinerung sollte bei Bedarf ziemlich unkompliziert sein. Insbesondere sollte er jede Größe von 2D-Referenzgittern unverändert verarbeiten können, auch solche, die nicht quadratisch sind. Der Schlüssel liegt darin, sicherzustellen, dass die Arrays der Permutationsanweisungen richtig eingerichtet sind.

BEARBEITEN:Nachdem ich ein bisschen damit herumgespielt habe, ist klar, dass diese Lösung keinen Zugriff auf den gesamten Raum möglicher Permutationen bietet. Also habe ich es optimiert, indem ich ein zufälliges "Bitverschiebung", um die Typbezeichnungen untereinander zu vertauschen. Der Vereinfachung halber bin ich von ABCBezeichnungen zu 123Bezeichnungen übergegangen, was eine Implementierung durch eine einfache MODOperation ermöglicht und auch eine schnelle Plausibilitätsprüfung in Form von Zeilen- und Spaltensummen ermöglicht:

Answer 2

Dies lässt sich auf sehr einfache Weise erreichen. ZuerstSchlüsselzu jedem der drei Typen:

Nehmen Sie dann die erste Probe, zum Beispiel SAMPLE_A_1, und platzieren Sie sienach dem Zufallsprinzipin einem der A-Slots. Fahren Sie dann mit der Verarbeitung der verbleibenden 35 Proben fort.


Wenn dieser Ansatz akzeptabel ist, werde ich ein kurzes Programm zum Auffüllen der Matrix veröffentlichen. Wenn der Ansatz nicht akzeptabel ist, werde ich diesen Beitrag löschen.