Excelでリストからレコードを無作為に抽出・ランダムする方法
Excelでは、データ分析や統計的な処理を行う際に、リストから無作為にレコードを抽出することがよくあります。ここでは、Excelの関数を使って無作為抽出の操作を行う方法を解説します。具体的には、FILTER関数とSORTBY関数、SEQUENCE関数、RANDARRAY関数を組み合わせた計算式を使用します。
無作為抽出テストのためのサンプルデータ
以下のサンプルデータを使用して説明します。このデータはA1:C28に配置されています。
| 書籍番号 | 書名 | 金額 |
|---|---|---|
| 1001 | ケーススタディ民法 | 1400 |
| 1002 | 交通六法 | 2700 |
| 1003 | 法人税法の基礎 | 2700 |
| 1004 | 会社法 | 2800 |
| 1005 | 民法総則 | 2700 |
| 1006 | 判例六法 | 2400 |
| 1007 | 基礎法入門 | 2500 |
| 1008 | やさしい経済法 | 2500 |
| 1009 | 刑法各論 | 2100 |
| 1010 | 現近代民法 | 1400 |
| 1011 | 会社法概論 | 2000 |
| 1012 | 商法総則 | 1900 |
| 1013 | 憲法 | 2500 |
| 1014 | 商行為法 | 1600 |
| 1015 | 倒産法入門 | 2300 |
| 1016 | 破産法 | 1200 |
| 1017 | 法社会学 | 1500 |
| 1018 | 民事訴訟法学 | 2800 |
| 1019 | 法学入門 | 2500 |
| 1020 | 労働法の要点 | 2400 |
| 1021 | はやわかり刑法 | 3000 |
| 1022 | 講義式憲法 | 3000 |
| 1023 | 刑法講義 | 2600 |
| 1024 | 会社法 | 1300 |
| 1025 | 民法事例集 | 1200 |
| 1026 | 六法全書 | 2300 |
| 1027 | 労働法実務辞典 | 2700 |
数式の構造
無作為抽出する計算式の基本形状
配列からレコードを無作為抽出するのExcelの計算式は以下の通りです。
=FILTER(配列,SORTBY(SEQUENCE(ROWS(配列)), RANDARRAY(ROWS(配列)))<=抽出数)例えば配列の範囲がサンプルのようにA2:C28、抽出数が3ならば以下の式となります。{F9}キーを使うと再計算され、新しい3件が表示されます。
=FILTER(A2:C28, SORTBY(SEQUENCE(ROWS(A2:C28)), RANDARRAY(ROWS(A2:C28))) <= 3)
Excelで無作為抽出を実現する計算式の例
LET関数を用いた式
上記の式の「配列」「抽出数」を前部で指定できるようにLET関数を用いて置き換えると
=LET(
_data, 配列,
_n, 抽出数,
FILTER(_data, SORTBY(SEQUENCE(ROWS(_data)), RANDARRAY(ROWS(_data))) <= _n)
)となります。1行で表すと
=LET(_data,抽出範囲,_n,抽出数,FILTER(_data,SORTBY(SEQUENCE(ROWS(_data)),RANDARRAY(ROWS(_data)))<=_n))
です。
数式の解説
以下の再掲サンプルを使ってこの数式の構造を順に解説していきます。
=FILTER(A2:C28, SORTBY(SEQUENCE(ROWS(A2:C28)), RANDARRAY(ROWS(A2:C28))) <= 3)
データ範囲の指定
A2:C28は、無作為抽出の対象となるデータ範囲です。この範囲内のレコードから抽出を行います。
抽出数の指定
数式内の3は抽出するレコードの数を指定しています。この例では3を設定していますが、必要に応じて変更できます。
ランダムサンプルの生成
SORTBY(SEQUENCE(ROWS(A2:C28)), RANDARRAY(ROWS(A2:C28)))
この部分では、ランダム順に並べ替えたインデックスを生成しています。
SEQUENCE(ROWS(A2:C28))：データ範囲の行数に基づいて連続した数値の配列を生成します。RANDARRAY(ROWS(A2:C28))：データ範囲の行数に基づいてランダムな数値の配列を生成します。SORTBY：生成された連続した数値の配列をランダムな数値で並べ替えます。
この計算式の詳しい解説は以下のページを参照してください。
重複しない整数乱数の生成方法(Excel)
重複しない整数乱数の生成方法Excelでは、特定の範囲内で重複しない整数乱数を簡単に生成することができます。この方法は、データ分析やテストデータの作成に非常に役立ちます。本記事では、SEQUENCE関数と RANDARRAY関数を組み合わせ...
www.helpaso.net
2024.06.16
FILTER関数の適用
FILTER(A2:C28, SORTBY(SEQUENCE(ROWS(A2:C28)), RANDARRAY(ROWS(A2:C28))) <= 3)
最後に、FILTER関数を使用して、ランダム順のインデックスが指定した抽出数（3）以下のレコードを抽出します。
この方法により、指定された範囲から任意の数のレコードを無作為に抽出できます。簡潔で読みやすい数式を使用することで、データのランダム抽出を効率的に行うことができます。データから無作為標本を抽出・作成する計算式を開設しました。
コメント