☰

我可以在 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

我使用的方法与 Gary's Student 发布的方法类似，但我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 中不是固定的。

图片对于理解我所描述的内容可能至关重要。点击下方可查看大图：

公式视图：
公式视图

常规视图：
常规视图

相关内容