Tengo una cuadrícula de 6 x 6. Tengo 36 muestras divididas en tres grupos iguales (A, B y C).
Quiero una fórmula para organizar las muestras en esa cuadrícula al azar. Sin embargo, cada fila y cada columna debe contener dos de cada grupo. Hacer esto manualmente una o dos veces es fácil, pero existe una gran cantidad de combinaciones.
Estoy abierto en términos de opciones de software siempre que sea gratuito o esté basado en Microsoft Office.
¡Gracias!
Respuesta1
Creé una herramienta para generar permutaciones aleatorias de filas/columnas con un par de fórmulas y algo de VBA. El diseño de la hoja se ve así:
La cuadrícula de referencia es un ejemplo trivial de una matriz válida, como se publicó en la respuesta preliminar de Gary's Student (posiblemente desde que se eliminó). Las permutaciones de filas y columnas incorporan todas las combinaciones únicas posibles de permutaciones para la cuadrícula de 6x6. (Esto podría modificarse fácilmente para incluir permutaciones no únicas, si se desea). Los valores en E12:E26
y L12:L26
se siembran aleatoriamente en cero o uno, para proporcionar la base sobre si se debe realizar o no una permutación determinada. Columnas D
y K
simplemente conviértalas a valores booleanos para un manejo simplificado dentro de VBA (ver más abajo). La cuadrícula permutada es generada por la función personalizada doSwap
, ingresada como una fórmula matricial. Al presionar F9
para activar el recálculo de la hoja, las diversas RAND
funciones regeneran sus valores aleatorios, cambiando la serie de permutaciones que se realizarán.
El código VBA que permite este comportamiento es:
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
El código anterior no está bien robusto, pero sirve para el presente propósito. La extensión/generalización debería ser bastante sencilla, si es necesario. En particular, debe manejar tal cual cualquier tamaño de cuadrícula de referencia 2-D, incluso una que no sea cuadrada. La clave es garantizar que las matrices de instrucciones de permutación estén configuradas correctamente.
EDITAR:Después de jugar un poco con ella, queda claro que esta solución no proporciona acceso al espacio completo de posibles permutaciones. Así que lo modifiqué añadiendo un "al azar".cambio de bits" para intercambiar las etiquetas de tipo entre sí. Para simplificar las cosas, cambié de ABC
etiquetas a 123
etiquetas, lo que permite la implementación mediante una MOD
operación simple, y también una verificación rápida de cordura en forma de sumas de filas y columnas:
Respuesta2
Hay una manera muy sencilla de lograr esto. Primera preasignacióntragamonedasa cada uno de los tres tipos:
Luego tome la primera muestra, por ejemplo SAMPLE_A_1, y colóquelaal azaren una de las ranuras A. Luego continúe procesando cada una de las 35 muestras restantes.
Si este enfoque es aceptable, publicaré un programa breve para completar la matriz. Si el enfoque no es aceptable, eliminaré esta publicación.