Я хочу создать серию перестановок строки в Excel.
Строка генезиса может быть, например,
cat | dog | rat | mouse | rhino | ape | fish
Я хотел бы сгенерировать произвольное количество других строк с тем же содержимым, но, например, перемешанным
dog | mouse | rhino | ape | cat | fish | rat
rhino | rat | cat | mouse | fish | ape | dog
...
Это возможно?
Поместите значения в A1 - G1
В A2 - G2 введите:
=RAND()
В A3 через G3 введите:
=INDEX($A$1:$G$1,MATCH(LARGE($A$2:$G$2,COLUMN()),$A$2:$G$2,0))
Каждый раз, когда рабочий лист пересчитывается, создается новая перестановка.
Я использую метод, аналогичный тому, который опубликовал 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 Copyright 2018, Чарльз Х. Пирсон
Это абсолютно возможно, но методы его настройки далеко не очевидны.
Для каждой новой строки, чтобы генерировать числа, указывающие на позиции в исходной строке, я бы ранжировал ряд случайных чисел по размеру от наибольшего к наименьшему, используя функции RANK() и RAND().
Excel не запрещает RAND() генерировать одно и то же случайное число более одного раза. Чтобы обойти эту проблему, я бы использовал измененные случайные числа, которые вынуждены отличаться от всех остальных в списке. Мои выражения округляют каждое случайное число, а затем добавляют уникальное маленькое число (которое меньше шага округления), чтобы сделать это.
(Моя модификация заставляет некоторые перестановки быть тривиально более вероятными, чем другие, и я предполагаю, что вам не нужно, чтобы случайность документа была самого высокого калибра, иначе вы бы не использовали Excel в качестве генератора случайных чисел.)
После составления рейтинга я бы скопировал и вставил их в новые ячейки в качестве значений. Затем я привязывал каждую новую переставленную строку к ячейкам с вставленным ранжированием и к исходной строке, используя функцию OFFSET() в моих формулах.
В качестве альтернативы вы можете привязать перестроенные строки к ранжированию, не копируя и не вставляя их в качестве значений. Это может привести к тому, что строки будут перетасовываться каждый раз, когда Excel пересчитывает документ, потому что числа, сгенерированные функцией RAND(), не привязаны к Excel.
Изображения, вероятно, необходимы для понимания того, что я описал. Нажмите на ниже, чтобы увидеть большие изображения:
Formula View:
Обычный вид:
