Seleção aleatória com advertência/critério

Question

Eu tenho uma grade 6 x 6. Tenho 36 amostras divididas em três grupos iguais (A, B e C).

Quero uma fórmula para organizar as amostras nessa grade aleatoriamente. No entanto, cada linha e cada coluna devem conter dois de cada grupo. Fazer isso manualmente uma ou duas vezes é fácil, mas há um grande número de combinações.

Estou aberto em termos de opções de software, desde que seja freeware ou baseado no Microsoft Office.

Obrigado!

Answer 1

Eu montei uma ferramenta para gerar permutações aleatórias de linhas/colunas com algumas fórmulas e um pouco de VBA. O layout da folha é assim:

A grade de referência é um exemplo trivial de uma matriz válida, conforme postado na resposta preliminar de Gary's Student (possivelmente excluída). As permutações de linhas e colunas incorporam todas as combinações únicas possíveis de permutações para a grade 6x6. (Isso pode ser facilmente modificado para incluir permutações não exclusivas, se desejado.) Os valores em E12:E26e L12:L26são propagados aleatoriamente para zero ou um, para fornecer a base para realizar ou não uma determinada permutação. Colunas De Kapenas convertê-los em valores booleanos para manipulação simplificada dentro do VBA (veja abaixo). A grade permutada é gerada pela função personalizada doSwap, inserida como uma fórmula de matriz. Pressionar F9para acionar o recálculo da planilha faz com que as diversas RANDfunções gerem novamente seus valores aleatórios, alterando a série de permutações a serem executadas.

O código VBA que permite esse comportamento é:

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

O código acima não está bem robusto, mas serve ao presente propósito. A extensão/generalização deve ser bastante direta, se necessário. Em particular, ele deve lidar como está com qualquer tamanho de grade de referência 2-D, mesmo uma que não seja quadrada. O principal é garantir que as matrizes de instruções de permutação estejam configuradas corretamente.

EDITAR:Depois de brincar um pouco, fica claro que esta solução não fornece acesso a todo o espaço de permutações possíveis. Então, eu ajustei adicionando um "mudança de bits" para trocar os rótulos de tipo entre si. Para simplificar as coisas, mudei de ABCrótulos para 123rótulos, o que permite a implementação por meio de uma MODoperação simples, e também uma verificação rápida de integridade na forma de somas de linhas e colunas:

Answer 2

Existe uma maneira muito simples de fazer isso. Primeira pré-atribuiçãoslotspara cada um dos três tipos:

Em seguida, pegue a primeira amostra, por exemplo SAMPLE_A_1, e coloque-aaleatoriamenteem um dos slots A. Em seguida, continue a processar cada uma das 35 amostras restantes.


Se esta abordagem for aceitável, postarei um pequeno programa para preencher a matriz. Se a abordagem não for aceitável, excluirei esta postagem.