☰

Excel에서 다른 행의 무작위 순열인 행을 생성할 수 있나요?

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
Excel에서 다른 행의 무작위 순열인 행을 생성할 수 있나요?

Excel에서 일련의 행 순열을 생성하고 싶습니다.

예를 들어, Genesis 행은 다음과 같을 수 있습니다.

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() 함수를 사용하여 새로 재배열된 각 행을 붙여넣은 순위가 있는 셀과 원래 행에 연결합니다.

또는 재정렬된 행을 값으로 복사하여 붙여넣지 않고도 순위에 연결할 수 있습니다. 이렇게 하면 RAND() 함수에서 생성된 숫자가 Excel에서 고정되지 않기 때문에 Excel에서 문서를 다시 계산할 때마다 행이 섞이게 됩니다.

내가 설명한 내용을 이해하려면 이미지가 필수적일 것입니다. 아래를 클릭하시면 큰 이미지를 보실 수 있습니다.

수식 보기:
수식 보기

일반 보기:
일반보기

관련 정보