Posso produzir uma linha no Excel que seja uma permutação aleatória de outra linha?

Question

Quero gerar uma série de permutações de uma linha no Excel.

A linha de gênese poderia ser, por exemplo,

cat   | dog   | rat   | mouse | rhino | ape   | fish

Gostaria de gerar um número arbitrário de outras linhas com o mesmo conteúdo, mas embaralhadas, por exemplo

dog   | mouse | rhino | ape   | cat   | fish  | rat
rhino | rat   | cat   | mouse | fish  | ape   | dog
...

Isso é possível?

Answer 1

Coloque os valores emA1atravésG1

EmA2atravésG2digitar:

=RAND()

EmA3atravésG3digitar:

=INDEX($A$1:$G$1,MATCH(LARGE($A$2:$G$2,COLUMN()),$A$2:$G$2,0))

Cada vez que a planilha for recalculada, uma nova permutação será gerada.

Answer 2

Eu uso um método semelhante ao que o aluno de Gary postou, mas uso RANKem minha fórmula. Acho que isso simplifica a fórmula e a torna um pouco mais fácil de entender.

Para dados de amostra em A1:G1:

dog    mouse    rhino    ape    cat    fish    rat

Preencha a fórmula =RAND()em A2:G2.

Em seguida, preencha a fórmula abaixo A3:G3.

=INDEX($A$1:$G$1,RANK(A2,$A2:$G2))

Isso é bom para um número único ou pequeno de linhas.

---

Para uma solução mais robusta, eu usaria o VBA. A macro abaixo permitirá que você selecione os valores que deseja embaralhar e especifique o número de permutações que deseja criar. As permutações serão impressas em uma nova planilha, onde você poderá copiá-las e colá-las onde quiser.

Sub nPerm()
Dim ValuesToPermute As Range, arrIn() As Variant, arrTmp() As Variant
Dim pcount As Long
Dim arrOut() As Variant, shtOut As Worksheet

'Get values to permute from user input
Set ValuesToPermute = Application.InputBox("Select values to permute. (Input must be in a single row.)", Type:=8)

'Get number of permutations wanted from user input
pcount = Application.InputBox("How many permutations would you like?", Type:=1)

'Set up array to hold input
arrIn = ValuesToPermute.Value

'Set up array to hold output
ReDim arrOut(1 To pcount, 1 To UBound(arrIn, 2)) As Variant

'Populate output array with n randomly permuted sets
For i = 1 To pcount
    arrTmp = ShuffleArray(arrIn)
    For k = 1 To UBound(arrTmp, 2)
        arrOut(i, k) = arrTmp(1, k)
    Next k
Next i

'Create new sheet and print output there
Set shtOut = Worksheets.Add
shtOut.Name = "nPerm Output"
shtOut.Range("a1").Resize(UBound(arrOut, 1), UBound(arrOut, 2)).Value = arrOut
End Sub


'Modified code from Chip Pearson
'Source: www.cpearson.com/excel/ShuffleArray.aspx Copyright 2018, Charles H. Pearson
Function ShuffleArray(InArray() As Variant) As Variant()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ShuffleArray
' This function returns the values of InArray in random order. The original
' InArray is not modified.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim N As Long
    Dim Temp As Variant
    Dim J As Long
    Dim Arr() As Variant


    Randomize
    L = UBound(InArray, 2) - LBound(InArray, 2) + 1
    ReDim Arr(1 To 1, LBound(InArray, 2) To UBound(InArray, 2))
    For N = LBound(InArray, 2) To UBound(InArray, 2)
        Arr(1, N) = InArray(1, N)
    Next N
    For N = LBound(InArray, 2) To UBound(InArray, 2)
        J = CLng(((UBound(InArray, 2) - N) * Rnd) + N)
        Temp = Arr(1, N)
        Arr(1, N) = Arr(1, J)
        Arr(1, J) = Temp
    Next N
    ShuffleArray = Arr
End Function

A função ShuffleArray não é meu trabalho.
Fonte: www.cpearson.com/excel/ShuffleArray.aspx Direitos autorais 2018, Charles H. Pearson

Answer 3

É absolutamente possível, mas as técnicas para configurar isso estão longe de ser óbvias.

Para cada nova linha, para gerar números apontando para posições na linha original, eu classificaria uma série de números aleatórios por tamanho, do maior para o menor, usando as funções RANK() e RAND().

O Excel não impede que RAND() gere o mesmo número aleatório mais de uma vez. Para contornar esse problema, eu usaria números aleatórios modificados que são forçados a serem diferentes de todos os outros na lista. Minhas expressões arredondam cada número aleatório e, em seguida, adicionam um pequeno número exclusivo (que é menor que o incremento de arredondamento) para fazer isso.

(Minha modificação faz com que algumas permutações sejam trivialmente mais prováveis ​​do que outras, e estou assumindo que você não precisa que a aleatoriedade do documento seja do mais alto calibre, ou então você não usaria o Excel como seu gerador de números aleatórios. )

Depois de fazer as classificações, eu as copiaria e colaria em novas células como valores. Em seguida, eu vincularia cada nova linha reorganizada às células com as classificações coladas e à linha original, usando a função OFFSET() em minhas fórmulas.

Como alternativa, você pode vincular as linhas reorganizadas às classificações sem copiá-las e colá-las como valores. Fazer isso faria com que suas linhas fossem embaralhadas sempre que o Excel recalculasse o documento, porque os números gerados a partir da função RAND() não são fixos no Excel.

As imagens são provavelmente essenciais para entender o que descrevi. Clique abaixo para ver imagens grandes:

Visualização da fórmula:

Visualização normal: