エクセルで重複なくランダムにデータを選択する方法

＜目次＞

エクセルで重複なくランダムにデータを選択する方法
　実現したいこと
　実現方法
　関数の仕組み
　例の紹介
　参考記事
　筆者のぼやき

エクセルで重複なくランダムにデータを選択する方法

実現したいこと

一意なデータの中から、データをランダムに重複なく取得したい。

（例）
・テストデータをランダムにシャッフルする場合など
※ディープラーニングの計算をExcel上で行う際に、データを重複なくシャッフルするケースなど。

（図100）

サンプル
⇒【サンプルダウンロードURL】

＞目次にもどる

実現方法

（構文）

=INDEX(SORTBY([データ範囲], RANDARRAY(ROWS([データ範囲]))), SEQUENCE([データ数]), {列数だけカンマ区切りで数字を1から記載})

（例）

=INDEX(SORTBY(B2:F31, RANDARRAY(ROWS(B2:F31))), SEQUENCE(30), {1,2,3,4,5})

（図111）

＞目次にもどる

関数の仕組み

内部で使われる1つ1つの関数について簡単に解説します。

・ROWS(B2:F31)
→行数を取得します。今回の場合は「30」が返ります。
↓
・RANDARRAY(ROWS(B2:F31))
→行数（例：30）だけランダムな数値を縦に生成します。これをソートに利用します。
↓
・SORTBY(B2:F31, RANDARRAY(ROWS(B2:F31)))
→指定した範囲（B2:F31）のデータを、生成したランダム数値をベースに並べ替えます。
↓
・INDEX(SORTBY(B2:F31, RANDARRAY(ROWS(B2:F31))), SEQUENCE(30), {1,2,3,4,5})
→行（SEQUENCE(30)）と列（{1,2,3,4,5}）を指定し、その交差部分を取得します。

※今回の場合、30行×5列（B～F列）のマトリクスが取得範囲になります。

＞目次にもどる

例の紹介

左側の表データを、ランダムに重複なく並べ替える処理を試してみます。
（ディープラーニングのテストデータを想定）

・Beforeの状態
（図121）

↓
右表の、左上のセルに数式を挿入します。
（そこを起点に残りの部分は自動的に埋まります）

・Afterの状態
（図122）

（備考）
・この方法はExcelの「動的配列数式」と呼ばれ、範囲全体が自動で埋まる挙動は「スピル」と呼ばれます。
・この機能は「Excel for Microsoft 365」や「Excel 2021」以降で利用可能です。
・ランダムデータは、セルに変更を加える度にリフレッシュされます。

＞目次にもどる

参考記事

（図131）

動的配列数式とスピル配列の動作（Microsoft公式）

＞目次にもどる

筆者のぼやき

最近はOffice製品がどんどん進化しており、本機能もさることながら、Power Queryなど強力な機能も多数登場しています。
従来はVBAで実装していたような処理も、標準機能で実現できるようになってきました。
ローコード・ノーコードの流れが加速しており、エンジニア以外の方々でも便利機能が簡単に利用できるようになっています。
VBAは維持コストもかかるため、代替可能なものはどんどん置き換えていきたいところですね。

＞目次にもどる