Selección aleatoria con una advertencia/criterio

Question

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!

Answer 1

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:E26y L12:L26se siembran aleatoriamente en cero o uno, para proporcionar la base sobre si se debe realizar o no una permutación determinada. Columnas Dy Ksimplemente 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 F9para activar el recálculo de la hoja, las diversas RANDfunciones 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 ABCetiquetas a 123etiquetas, lo que permite la implementación mediante una MODoperación simple, y también una verificación rápida de cordura en forma de sumas de filas y columnas:

Answer 2

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.