☰

我可以在 Excel 中產生一行，該行是另一行的隨機排列嗎？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
我可以在 Excel 中產生一行，該行是另一行的隨機排列嗎？

我想在 Excel 中產生一行的一系列排列。

例如，創世銀可以是：

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

例如，我想產生任意數量的具有相同內容但已打亂的其他行

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

這可能嗎？

答案1

將數值放入A1透過G1

在A2透過G2進入：

=RAND()

在A3透過G3進入：

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

在此輸入影像描述

每次重新計算工作表時，都會產生一個新的排列。

答案2

我使用的方法與加里的學生發布的方法類似，但我RANK在公式中使用了它。我認為這簡化了公式並使其更容易理解。

對於範例資料A1:G1：

dog    mouse    rhino    ape    cat    fish    rat

將公式=RAND()填入A2:G2。

然後將下面的公式填入A3:G3。

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

在此輸入影像描述

這對於一次性或少量行很有用。

為了獲得更強大的解決方案，我將使用 VBA。下面的巨集將允許您選擇要打亂的值並指定要建立的排列數量。排列將列印到新的工作表中，您可以將它們複製並貼上到任何您喜歡的位置。

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

ShuffleArray 函數不是我的作品。
資料來源：www.cpearson.com/excel/ShuffleArray.aspx 版權所有 2018，Charles H. Pearson

答案3

這絕對是可能的，但設定它的技術卻遠非顯而易見。

對於每個新行，為了產生指向原始行中位置的數字，我將使用 RANK() 和 RAND() 函數按大小從最大到最小對一系列隨機數進行排序。

Excel 不會阻止 RAND() 多次產生相同的隨機數。為了避免這個問題，我將使用修改後的隨機數，這些隨機數被迫與清單中的所有其他隨機數不同。我的表達式對每個隨機數進行舍入，然後添加一個唯一的小數字（小於舍入增量）來執行此操作。

（我的修改導致某些排列比其他排列的可能性更大，並且我假設您不需要文檔的隨機性達到最高水平，否則您不會使用 Excel 作為隨機數生成器。 ）

進行排名後，我會將它們作為值複製並貼上到新單元格中。然後，我將使用公式中的 OFFSET() 函數將每個新的重新排列的行與貼上排名的儲存格以及原始行連接起來。

作為替代方案，您可以將重新排列的行與排名聯繫起來，而無需將它們複製並貼上為值。這樣做會導致每次 Excel 重新計算文件時都會打亂行，因為從 RAND() 函數產生的數字在 Excel 中不具有黏性。

圖片可能對於理解我所描述的內容至關重要。點擊下面看大圖：

公式視圖：
公式視圖

常規視圖：
常規視圖

相關內容