Eu quero gerar uma série de permutações de uma linha no Excel.
A linha genesis poderia ser, por exemplo,
cat | dog | rat | mouse | rhino | ape | fish
Eu gostaria de gerar um número arbitrário de outras linhas com o mesmo conteúdo, mas embaralhei, por exemplo
dog | mouse | rhino | ape | cat | fish | rat
rhino | rat | cat | mouse | fish | ape | dog
...
Isso é possível?
Eu uso um método semelhante ao que o Gary's Student postou, mas eu uso RANK em minha fórmula. Eu acho que isso simplifica a fórmula e 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() entre A2:G2 .
Em seguida, preencha a fórmula abaixo em A3:G3 .
=INDEX($A$1:$G$1,RANK(A2,$A2:$G2))
Issoébomparaumúnicoouumpequenonúmerodelinhas.
Paraumasoluçãomaisrobusta,euusariaoVBA.Amacroabaixopermitiráquevocêselecioneosvaloresquedesejamisturareespecifiqueonúmerodepermutaçõesquedesejacriar.Aspermutaçõesserãoimpressasemumanovaplanilha,ondevocêpodecopiá-lasecolá-lasondequiser.
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 Copyright 2018, Charles H. Pearson
É absolutamente possível, mas as técnicas de configuração 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 em torno de cada número aleatório e, em seguida, adicionar um número pequeno único (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 estaria usando o Excel como seu aleatório gerador de números.)
Depois de fazer as classificações, eu copiava e colava as novas células como valores. Em seguida, eu ligaria 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 amarrar as linhas reorganizadas nos rankings sem copiá-las e colá-las como valores. 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 provavelmente são essenciais para entender o que descrevi. Clique no link abaixo para ver imagens grandes:
Visualização de Fórmula:
Vistaregular: