Excel で別の行をランダムに並べ替えた行を作成できますか?

Question 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))

ワークシートが再計算されるたびに、新しい順列が生成されます。

Answer

値をA1を通してG1

でA2を通してG2入力：

=RAND()

でA3を通してG3入力：

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

ワークシートが再計算されるたびに、新しい順列が生成されます。

Question 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))

これは、1 回限りまたは少数の行に適しています。

より堅牢なソリューションには、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, Charles H. Pearson

Answer

私は 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))

これは、1 回限りまたは少数の行に適しています。

より堅牢なソリューションには、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, Charles H. Pearson

Question 3

それは絶対に可能ですが、これを設定するためのテクニックは決して明らかではありません。

新しい行ごとに、元の行の位置を指す数字を生成するために、RANK() 関数と RAND() 関数を使用して、一連の乱数を最大から最小までのサイズでランク付けします。

Excel では、RAND() が同じ乱数を複数回生成することを防止できません。この問題を回避するには、リスト内の他のすべての乱数とは異なるように強制される修正乱数を使用します。私の式では、各乱数を丸めてから、一意の小さな数値 (丸め増分よりも小さい数値) を追加してこれを実行します。

(私の変更により、いくつかの順列が他の順列よりも明らかに確率が高くなっています。また、ドキュメントのランダム性が最高水準である必要はないと想定しています。そうでなければ、Excel を乱数ジェネレーターとして使用しないでしょう。)

ランキングを作成したら、それをコピーして新しいセルに値として貼り付けます。次に、数式で OFFSET() 関数を使用して、並べ替えた新しい行を、貼り付けたランキングのセルと元の行に結び付けます。

代わりに、並べ替えた行を値としてコピーして貼り付けずに、ランキングに結び付けることもできます。これを行うと、RAND() 関数から生成された数値は Excel では固定されないため、Excel がドキュメントを再計算するたびに行がシャッフルされます。

私が説明した内容を理解するには、おそらく画像が不可欠です。以下をクリックすると、大きな画像が表示されます。

数式ビュー:

通常表示:

Answer