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!
Antwort1
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:E26
und L12:L26
werden zufällig mit null oder eins gesetzt, um die Grundlage dafür zu bilden, ob eine bestimmte Permutation ausgeführt wird oder nicht. Spalten D
und K
konvertieren 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, F9
um eine Neuberechnung des Blatts auszulösen, generieren die verschiedenen RAND
Funktionen 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 ABC
Bezeichnungen zu 123
Bezeichnungen übergegangen, was eine Implementierung durch eine einfache MOD
Operation ermöglicht und auch eine schnelle Plausibilitätsprüfung in Form von Zeilen- und Spaltensummen ermöglicht:
Antwort2
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.