Я хочу создать серию перестановок строки в Excel.
Строка генезиса может быть, например, такой:
cat | dog | rat | mouse | rhino | ape | fish
Я хотел бы сгенерировать произвольное количество других строк с тем же содержимым, но перемешанным, например
dog | mouse | rhino | ape | cat | fish | rat
rhino | rat | cat | mouse | fish | ape | dog
...
Это возможно?
решение1
решение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.
Изображения, вероятно, необходимы для понимания того, что я описал. Нажмите на изображение ниже, чтобы увидеть большие изображения: