Можно ли создать строку в Excel, которая будет случайной перестановкой другой строки?

Question

Я хочу создать серию перестановок строки в Excel.

Строка генезиса может быть, например, такой:

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

Я хотел бы сгенерировать произвольное количество других строк с тем же содержимым, но перемешанным, например

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

Это возможно?

Answer 1

Поместите значения вА1черезГ1

ВА2черезГ2входить:

=RAND()

ВА3черезГ3входить:

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

Каждый раз при повторном расчете рабочего листа будет генерироваться новая перестановка.

Answer 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

Answer 3

Это вполне возможно, но методы реализации этого далеко не очевидны.

Для каждой новой строки, чтобы сгенерировать числа, указывающие на позиции в исходной строке, я бы ранжировал ряд случайных чисел по размеру от наибольшего к наименьшему, используя функции RANK() и RAND().

Excel не мешает RAND() генерировать одно и то же случайное число более одного раза. Чтобы обойти эту проблему, я бы использовал измененные случайные числа, которые принудительно отличаются от всех остальных в списке. Мои выражения округляют каждое случайное число, а затем добавляют уникальное небольшое число (которое меньше шага округления), чтобы сделать это.

(Моя модификация делает некоторые перестановки тривиально более вероятными, чем другие, и я предполагаю, что вам не нужно, чтобы случайность документа была самого высокого уровня, иначе вы бы не использовали Excel в качестве генератора случайных чисел.)

После создания рейтингов я копировал и вставлял их в новые ячейки как значения. Затем я привязывал каждую новую переставленную строку к ячейкам со вставленными рейтингами и к исходной строке, используя функцию OFFSET() в моих формулах.

В качестве альтернативы вы можете привязать переставленные строки к рейтингам, не копируя и не вставляя их как значения. Это приведет к тому, что ваши строки будут перемешиваться каждый раз, когда Excel пересчитывает документ, поскольку числа, сгенерированные функцией RAND(), не являются липкими в Excel.

Изображения, вероятно, необходимы для понимания того, что я описал. Нажмите на изображение ниже, чтобы увидеть большие изображения:

Вид формулы:

Обычный вид: