はじめに
Excelの一覧から一部の行だけを取り出したいとき、「無作為に選びたい場合」と「5行おき、10行おきのように一定間隔で選びたい場合」では方法が異なります。どちらも補助列を1つ作れば、関数と並べ替え・フィルターだけで実行できます。
この記事では、RAND関数を使ったランダム抽出と、ROW・MOD関数を使った一定間隔抽出を、実際に再現できる手順で解説します。あわせて、RAND関数の再計算で結果が変わる点や、一定間隔抽出が元データの並び順に影響される点も確認します。
ランダム抽出と一定間隔抽出の違い
ランダム抽出は、各行に乱数を付け、その乱数を使って順番を入れ替えてから必要な件数を取り出す方法です。一方、一定間隔抽出は、先頭から5行おきなど、決めた間隔に該当する行だけを取り出します。
| 方法 | 抽出の決め方 | 向いている場面 | 主な注意点 |
|---|---|---|---|
| ランダム抽出 | 各行へ乱数を付けて順番を決める | 行を無作為に選びたい | RAND関数は再計算で値が変わる |
| 一定間隔抽出 | 5行おきなど決まった間隔で選ぶ | 定期的な間引きや確認対象の抽出 | 元データの並び順や周期性の影響を受ける |
「ランダムなら必ず偏りがなくなる」「一定間隔なら母集団を正しく代表できる」という意味ではありません。標本の選び方そのものを理解したい場合は、標本抽出法とは?単純無作為抽出・層化抽出・系統抽出をわかりやすく解説も参考になります。
Excelでランダム抽出する方法
例として、A列からB列にデータがあり、1行目が見出し、2行目からデータが始まっているとします。C列を補助列として使います。
まずC2セルに次の式を入力します。
=RAND()
RAND関数は0以上1未満の乱数を返します。C2の式をデータの最終行までコピーすると、各行に乱数が入ります。
次に、C列の値を固定してから、A~C列を含む表全体をC列の乱数を基準に並べ替えます。C列だけを単独で並べ替えると元データとの対応が崩れるため、必ず行全体が一緒に移動する範囲で並べ替えます。乱数の小さい順または大きい順に並べ替えた後、上から必要な件数だけ取り出せばランダム抽出になります。並べ替え操作そのものは、Excelで並べ替える方法|昇順・降順・複数列の設定を解説で詳しく確認できます。
たとえば100行のデータから10行を取り出す場合は、乱数列で並べ替えた後の先頭10行を使います。必要件数だけを別シートへコピーすれば、抽出結果を元データから分けて管理できます。
RAND関数の結果を固定する方法
RAND関数は、ワークシートが再計算されるたびに新しい乱数へ変わります。そのままにしておくと、数式や別セルを編集しただけでも抽出順が変わることがあります。
抽出対象を一度決めた後は、補助列のRAND関数を「値」に置き換えて固定します。C列の乱数部分をコピーし、同じ場所へ「値として貼り付け」を行えば、数式ではなく数値だけが残ります。
作業の流れは次のとおりです。
- 補助列に
=RAND()を入力して最終行までコピーする - 乱数列をコピーし、値として貼り付ける
- 表全体を乱数列を基準に並べ替える
- 上から必要件数を取り出す
一度だけ抽出結果を決めたい場合は、値に固定してから並べ替える方が安全です。抽出のたびに別の行を選びたい場合は、RAND関数を残したまま再計算し、その都度値を固定して使います。
Excelで一定間隔に抽出する方法
一定間隔で抽出する場合は、ROW関数で現在行を取得し、MOD関数で「指定した間隔で割り切れる位置か」を判定します。
データがA2から始まり、5行おきに取り出す場合は、C2へ次の式を入力します。
=MOD(ROW()-ROW($A$2),5)=0
この式では、ROW()で現在の行番号を取得し、ROW($A$2)でデータ先頭の行番号を取得します。その差を5で割った余りが0ならTRUEになります。
C2から下へ式をコピーすると、A2、A7、A12、A17……に対応する行がTRUEになります。最後にC列をTRUEでフィルタリングすれば、5行おきのデータだけを表示できます。
| 抽出したい間隔 | 式の例 |
|---|---|
| 3行おき | =MOD(ROW()-ROW($A$2),3)=0 |
| 5行おき | =MOD(ROW()-ROW($A$2),5)=0 |
| 10行おき | =MOD(ROW()-ROW($A$2),10)=0 |
開始位置を変えたい場合は、基準にするセルを変えます。たとえばA4を最初の抽出行にしたい場合は、ROW($A$2)の部分をROW($A$4)へ変更します。
一定間隔抽出は並び順・周期性に注意する
一定間隔抽出は手順が単純ですが、元データの並び順に規則がある場合は注意が必要です。たとえば5行ごとに同じ種類のデータが並ぶ一覧から5行おきに抽出すると、特定の種類だけを繰り返し選ぶ可能性があります。
そのため、一定間隔抽出を「無作為抽出の代わり」として機械的に使うのではなく、元データの並びに周期的なパターンがないか確認します。単に大量データを一定間隔で間引いて確認する用途であれば使いやすい方法ですが、統計的な標本抽出として使う場合は選び方の妥当性も別に確認します。
ランダム抽出と一定間隔抽出の使い分け
行を無作為に選びたい場合はRAND関数を使った方法が分かりやすく、5行おき・10行おきのように規則的に取り出したい場合はROW・MOD関数が向いています。
ただし、ランダム抽出でも抽出した標本が必ず母集団を代表するとは限りません。また、一定間隔抽出では元データの並び順が結果に影響します。「どの方法なら必ず正しい」というより、抽出の目的と元データの並び方に合わせて選ぶことが重要です。
Excel上での操作だけを考えるなら、無作為に選ぶ場合は「RANDで乱数を付けて値固定し、並べ替える」、一定間隔なら「ROWとMODで対象行をTRUEにしてフィルターする」と覚えておくと使い分けやすくなります。
まとめ
Excelでランダム抽出する場合は、補助列にRAND関数を入力し、乱数を値として固定してから並べ替え、必要な件数を取り出します。RAND関数は再計算で値が変わるため、抽出対象を確定したいときは値への変換が重要です。
一定間隔抽出では、MOD(ROW()-ROW($A$2),5)=0のような式を使うことで、5行おきなどの対象行を判定できます。ただし、一定間隔抽出は元データの並び順や周期性の影響を受けるため、目的に合った方法かを確認して使い分けましょう。