Kann ich in Excel eine Zeile erstellen, die eine zufällige Permutation einer anderen Zeile ist?

Question

Ich möchte eine Reihe von Permutationen einer Zeile in Excel generieren.

Die Genesis-Zeile könnte zum Beispiel lauten:

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

Ich möchte eine beliebige Anzahl weiterer Zeilen mit dem gleichen Inhalt, aber gemischt, erzeugen, zum Beispiel

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

Ist das möglich?

Answer 1

Platzieren Sie die Werte inA1durchG1

InA2durchG2eingeben:

=RAND()

InA3durchG3eingeben:

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

Bei jeder Neuberechnung des Arbeitsblattes wird eine neue Permutation generiert.

Answer 2

Ich verwende eine Methode, die der von Garys Student geposteten ähnelt, aber ich verwende RANKstattdessen in meiner Formel. Ich denke, das vereinfacht die Formel und macht sie etwas leichter verständlich.

Für Beispieldaten in A1:G1:

dog    mouse    rhino    ape    cat    fish    rat

Füllen Sie die Formel =RAND()quer aus A2:G2.

Füllen Sie dann die folgende Formel quer aus A3:G3.

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

Dies ist für ein Einzelstück oder eine kleine Anzahl von Zeilen gut.

---

Für eine robustere Lösung würde ich VBA verwenden. Mit dem folgenden Makro können Sie die Werte auswählen, die Sie mischen möchten, und die Anzahl der Permutationen angeben, die Sie erstellen möchten. Die Permutationen werden auf ein neues Blatt gedruckt, wo Sie sie kopieren und an beliebiger Stelle einfügen können.

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

Die ShuffleArray-Funktion ist nicht meine Arbeit.
Quelle: www.cpearson.com/excel/ShuffleArray.aspx Copyright 2018, Charles H. Pearson

Answer 3

Dies ist durchaus möglich, die dafür erforderlichen Techniken sind jedoch alles andere als offensichtlich.

Um für jede neue Zeile Zahlen zu generieren, die auf Positionen in der ursprünglichen Zeile verweisen, würde ich mithilfe der Funktionen RANK() und RAND() eine Reihe von Zufallszahlen nach Größe vom größten zum kleinsten ordnen.

Excel verhindert nicht, dass RAND() dieselbe Zufallszahl mehr als einmal generiert. Um dieses Problem zu umgehen, würde ich modifizierte Zufallszahlen verwenden, die sich zwangsläufig von allen anderen in der Liste unterscheiden müssen. Meine Ausdrücke runden jede Zufallszahl und fügen dann eine eindeutige kleine Zahl hinzu (die kleiner als das Rundungsinkrement ist), um dies zu erreichen.

(Meine Änderung führt dazu, dass einige Permutationen unwesentlich wahrscheinlicher sind als andere, und ich gehe davon aus, dass die Zufälligkeit des Dokuments nicht von allerhöchster Qualität sein muss, sonst würden Sie nicht Excel als Zufallszahlengenerator verwenden.)

Nachdem ich die Ranglisten erstellt hatte, kopierte ich sie und fügte sie als Werte in neue Zellen ein. Dann verknüpfte ich jede neu angeordnete Zeile mit den Zellen mit den eingefügten Ranglisten und mit der ursprünglichen Zeile, indem ich in meinen Formeln die Funktion OFFSET() verwendete.

Alternativ können Sie die neu angeordneten Zeilen an die Rangfolge binden, ohne sie als Werte zu kopieren und einzufügen. Dies würde dazu führen, dass Ihre Zeilen jedes Mal neu gemischt werden, wenn Excel das Dokument neu berechnet, da die von der Funktion RAND() generierten Zahlen in Excel nicht beständig sind.

Bilder sind wahrscheinlich wichtig, um zu verstehen, was ich beschrieben habe. Klicken Sie auf die folgenden Bilder, um sie in großer Größe anzuzeigen:

Formelansicht:

Normale Ansicht: