¿Puedo generar una fila en Excel que sea una permutación aleatoria de otra fila?

Question

Quiero generar una serie de permutaciones de una fila en Excel.

La fila de génesis podría ser, por ejemplo,

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

Me gustaría generar un número arbitrario de otras filas con el mismo contenido pero mezcladas, por ejemplo

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

es posible?

Answer 1

Coloca los valores enA1a través deG1

EnA2a través deG2ingresar:

=RAND()

EnA3a través deG3ingresar:

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

Cada vez que se vuelve a calcular la hoja de trabajo, se generará una nueva permutación.

Answer 2

Utilizo un método similar al que publicó Gary's Student, pero RANKen su lugar lo uso en mi fórmula. Creo que esto simplifica la fórmula y la hace un poco más fácil de entender.

Para datos de muestra en A1:G1:

dog    mouse    rhino    ape    cat    fish    rat

Complete la fórmula =RAND()a través de A2:G2.

Luego complete la siguiente fórmula a lo largo de A3:G3.

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

Esto es bueno para una sola vez o para una pequeña cantidad de filas.

---

Para una solución más sólida, usaría VBA. La siguiente macro le permitirá seleccionar los valores que desea mezclar y especificar la cantidad de permutaciones que desea crear. Las permutaciones se imprimirán en una hoja nueva, donde podrás copiarlas y pegarlas donde quieras.

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

La función ShuffleArray no es mi trabajo.
Fuente: www.cpearson.com/excel/ShuffleArray.aspx Copyright 2018, Charles H. Pearson

Answer 3

Es absolutamente posible, pero las técnicas para configurarlo están lejos de ser obvias.

Para cada nueva fila, para generar números que apunten a posiciones en la fila original, clasificaría una serie de números aleatorios por tamaño, de mayor a menor, usando las funciones RANK() y RAND().

Excel no impide que RAND() genere el mismo número aleatorio más de una vez. Para evitar este problema, usaría números aleatorios modificados que deben ser diferentes de todos los demás en la lista. Mis expresiones redondean cada número aleatorio y luego agregan un número pequeño único (que es más pequeño que el incremento de redondeo) para hacer esto.

(Mi modificación hace que algunas permutaciones sean trivialmente más probables que otras, y supongo que no necesitas que la aleatoriedad del documento sea del más alto calibre, o de lo contrario no estarías usando Excel como generador de números aleatorios. )

Después de hacer las clasificaciones, las copiaba y pegaba en nuevas celdas como valores. Luego vincularía cada nueva fila reorganizada a las celdas con las clasificaciones pegadas y a la fila original, usando la función OFFSET() en mis fórmulas.

Como alternativa, puede vincular las filas reorganizadas a las clasificaciones sin copiarlas y pegarlas como valores. Hacer eso provocaría que sus filas se mezclaran cada vez que Excel recalcule el documento, porque los números generados a partir de la función RAND() no son fijos en Excel.

Las imágenes probablemente sean esenciales para comprender lo que he descrito. Haga clic en el siguiente para ver imágenes grandes:

Vista de fórmula:

Vista normal: