Excelで重複のない乱数を生成する方法 - TechRepublic

画像: PixieMe/Shutterstock

ここ数ヶ月、Excelの新しい動的配列関数についていくつか書いてきました。これらの関数は強力で実装も簡単で、これまで必要だった面倒な式構築から解放されます。この記事では、これらの新しい動的配列関数をいくつか組み合わせて、一意のランダム値の配列を生成します。ランダム値を生成するのは簡単ですが、ランダム値セットの下限と上限を制限するのも同様に簡単です。しかし、後ほど説明しますが、一意のランダム値の配列を生成するのはそれほど簡単ではありません。しかし、動的配列関数を使えば、少し手間がかかるだけで済みます。

参照: すべてのユーザーが習得すべき Excel のヒント 83 選 (TechRepublic)

Windows 10 64ビットシステムでMicrosoft 365を使用しています。動的配列関数は、365、Excel for the Web、Excel 2021、Excel for iPad and iPhone、Excel for Androidタブレットおよびスマートフォンでサポートされています。デモファイルはありませんので、必要ありません。

ランダム関数について

Excelには、RAND()、RANDBETWEEN()、RANDARRAY()という3つの乱数生成関数があります。RAND()は0から1の間の乱数、つまりランダムな小数値を生成します。RANDBETWEEN()は下限値と上限値を指定できます。例えば、
RANDBETWEEN(10,1000)
10から1,000までのランダムな整数を生成するのに使用できます。

最後に、RANDARRAY() は次の構文を使用してランダムな値の配列を返します。
RANDARRAY([rows], [columns], [min], [max], [integer])
すべての引数はオプションであることに注意してください。これらの引数では、埋める行数と列数、返す最小値と最大値、そして整数か小数のどちらを返すかを指定できます。整数引数はデフォルトで false に設定されており、小数を返します。整数を返すには、TRUE を指定する必要があります。また、min と max は両端を含む値です。例えば、これらの 2 つの引数がそれぞれ 1 と 5 の場合、この関数は 2、3、4 だけでなく、1、2、3、4、5 を返します。

議論を検討するときは、次のルールに留意してください。

RANDARRAY() は RAND() と同様に動作し、すべての引数を省略すると 0 から 1 までの値を返します。
RANDARRAY() は RAND() と同様に動作し、行と列を省略すると 0 から 1 までの値を返します。
min と max を省略すると、RANDARRAY() はそれぞれ 0 または 1 を返します。
min 引数は max より小さくなければなりません。
整数を明示的に TRUE に設定しない場合、RANDARRAY() には小数値が含まれます。
RANDARRAY() は、計算を完了するために適切なサイズの範囲を作成します。この範囲はスピル範囲と呼ばれます。この範囲に適切なセルがない場合、RANDARRAY() はスピルエラーを返します。Excel のスピル範囲の詳細については、「Excel でスピル範囲を使用する方法」をご覧ください。
RANDARRAY() は、ソースデータが Table オブジェクト内にある場合、ソースデータが変更されると結果を更新してサイズを変更します。
現在、データソースが別のワークブック内にある場合、RANDARRAY() が正しく機能するには、両方のワークブックが開いている必要があります。
重複しない値のランダム配列を生成するには、RANDARRAY()、UNIQUE()、および SEQUENCE() の 3 つの動的配列関数を使用します。

重複のないランダムな値を生成する方法

RANDARRAY() 単体では、他のランダム関数と同様に、重複した値を返す可能性があります。時間に余裕があれば、重複のないランダム値を簡単に取得する方法があります。スピル範囲の値を静的な値に変換し（関数も置き換えます）、データタブの重複削除ツールを使用して重複を削除します。これは簡単ですが、一度きりの処理を行う場合を除いて、合理的な方法とは言えません。

RANDARRAY() 関数に一意の値のみを返すように強制することはできませんが、UNIQUE() 関数と組み合わせることで目的の結果を得ることができます。例えば、図 Aに示す次の関数は、1 から 20 までの 5 つの（一見）一意の値を含む単一の列を返します。

=UNIQUE(RANDARRAY(5, 1, 1, 20,TRUE))

ただし、次の 2 つのことが起こる可能性があります: 1) 指定した値よりも少ないランダムな一意の値のリストが表示されます。2)スピルエラーが表示されます (図 Aの右側)。

RANDARRAY() が、RANDARRAY() のランダム値のプールから5つの一意の値を返せないほど多くの値を繰り返す場合、返せる値を返します。ただし、1つか2つ値が不足する可能性があります。20個のプール（最小値は1、最大値は20）に重複する値が多すぎて、5つのリストを返せない可能性は低いですが、ゼロではありません。F9キーを押して式が更新されるのを確認してください。最終的に、5桁未満の配列が表示されます。

右側の画像（図A）は、数式バーに表示されている式です。RANDARRAY()関数のみを選択し、F9キーを押して手動で計算しました。ご覧のとおり、配列には1という値が繰り返して表示されます。そのため、この式は4つの一意の値しか返せません。

図A

図Bはスピルエラー「スピル範囲不明」を示しています。行数引数を5から19に変更して、このエラーがより頻繁に発生するようにしましたが、F9キーを何度も押すと、行数引数が5であってもスピルエラーが返されるようになります。これは、これらの関数の揮発性に関係しています。つまり、ある関数の計算速度が他の関数よりも速い場合があるのです。揮発性関数は、変更が行われるたびに再計算を行います。

図B

式が指定した値よりも少ない値を返さないようにする一つの方法は、ランダムな値の巨大なプールを作成することです。おそらく最も簡単な方法は、rows引数を特定の値から短い式（n ^2）に変更することです。例えば、

=UNIQUE(RANDARRAY(5^2, 1, 1, 20,TRUE))

行式 5^2 は25個のランダム値を返しますが、式が返す値（行）は最大25個になり、スピル範囲エラーが発生する可能性は依然として残ります。ただし、5行未満が返されることはほとんどありません。

これらすべての動作に問題がなければ、ここで止めてもいいでしょう。しかし、おそらくもっと安定したものを求めるようになるでしょう。

重複のないランダムな値を生成する方法

前のセクションでは、RANDARRAY() と UNIQUE() について、そしてそれらがどのように連携するかについて多くのことを学びました。ただし、結果は安定していません。この問題を次のレベルに進める必要があるかどうかは、ニーズによって決まります。

現在、正しい個数の値を返すとは限らず、スピルエラーが発生する可能性のある式があります。これらの2つの問題を回避するには、より複雑な式を次のように記述する必要があります。

=INDEX(UNIQUE(RANDARRAY(rows^2, columns, min, max, TRUE)), SEQUENCE(rows))

見た目はひどいかもしれませんが、心配しないでください。仕組みを理解すれば、すぐに理解でき、自分の仕事に応用するのも難しくないでしょう。

RANDARRAY() は、最小値と最大値に基づいてランダムな値の配列を生成します。rows 引数の2乗は、rows 引数の値そのものよりもはるかに大きなランダム値のプールを生成します。UNIQUE() は重複する値をすべて削除し、一意の値の配列を返します。ここまでは前のセクションで説明しました。

今回の違いは、UNIQUE() がシートに配列を返さないことです。UNIQUE() が重複を削除した後、INDEX() は SEQUENCE() で指定された行数に基づいて、シートに配列として返す一意のランダム値の数を決定します。

図 C は、以前の引数値を使用したこの式を示しています。

=INDEX(UNIQUE(RANDARRAY(5^2,1,1,10,TRUE)), SEQUENCE(5))

まず、RANDARRAY() は 1 から 10 までの 25 個のランダムな整数を含む単一列配列を返します。UNIQUE() は、これらの結果から重複をすべて削除します。次に、INDEX() は、SEQUENCE(5) によって決定された上位 5 つの一意のランダムな値を返します。

図C

minとmaxは依然として重要であることを覚えておいてください。例えば、minとmaxがそれぞれ1と5の場合、10個の値の配列を返すことはできません。1から5の間には、それほど多くの一意の値は存在しないからです。この式は5つの一意の値を返し、残りのセル範囲にはエラーが表示されます。これは過度に単純化された説明ですが、要点は伝わるでしょう。

さらに、大きな配列は時間がかかります。rows の値が 2000^2 だとすると、Excel は内部的に 4,000,000 個の乱数を計算しています。おそらく、これほど大きなプールは必要ないはずです。代わりに、rows 引数に別の値、例えば 10 を掛け合わせてみてください。すると 20,000 個の乱数が返されます。これは、最小値と最大値にもよりますが、十分な数かもしれません。

プールが十分な大きさかどうかを判断するには、実際に試してみる必要があります。UNIQUE() に渡される乱数の数が減るほど、不完全なスピル範囲が返される可能性が高くなるため、真の解決策はありません。

告白します。これはExcelの新しい動的配列関数を活用した、独創的で強力な式です。この式を考案した功績はすべて私にあると言いたいところですが、もうインターネット上では既にかなり前から出回っています。もし最初にこの式を考案した天才的な創造力に敬意を表せるなら、そうしたいところです。

動的配列関数の詳細については、次の記事をお読みください。