【Excel】指定した列から無作為にセルの中身を抜き出す

下図のように、A 列にデータが並んでいるとします。
ここから無作為にデータを抜き出して任意のセルに出力することを考えてみます。
 

【Excel】特定の列からランダムにセルを選びます

Excel 関数の組合せによる表式は次のようになります：

=INDIRECT("A"&RANDBETWEEN(1,COUNTA(A:A)))

このように入れ子になっている（ネストされている）関数の意味を理解するには、一番内側にある関数から見ていくのがコツです。
 
・COUNTA は列にある空白以外のセルの個数を得る関数です。
・A:A は「A 列全体を指定」という意味ですから、

=COUNTA(A:A)

と入力すれば A 列にあるデータの個数を返してくれるわけです。
上図の例では「5」という数値を返します。

RANDBETWEEN は指定範囲内の整数値の乱数を返します。上の例では

=RANDBETWEEN(1,5)

というように入力していることになりますから、1 から 5 を無作為に返して来ます。INDIRECT は少しわかりにくい関数ですが大雑把にいうと、「文字列で指定したセルを参照する関数」です。正確に言えば指定した名前のセル範囲を全てもってくるのですが、セルに特定の名前を定義していない限りは上のように理解していれば十分です。普通は他のセルを参照する場合は

=A3

のようにセル番地を入力すれば当該セルの中身を素直に返してくれますが、行番号をランダムに選択したいからといって、

=B&RANDBETWEEN(1,10)

のように強引なことをしてもエラーとなるだけです（当たり前です）。
 
そんなときに使うのが INDIRECT という関数なのです。

=INDIRECT("A"&RANDBETWEEN(1,5))

このように入力すれば引数をちゃんとセル番地として読み取ってくれます。たとえば RANDBETWEEN(1,5) が「3」を返したときに、セル A3 を参照して次のようにセルの中身をもってきてくれるのです。
 

Excel 関数で構成された “くじ引き関数” の説明はこれでおしまいですが、やはり記述が分かりづらいですし、しばらくすると意味を忘れてしまいそうです。同じ働きをするユーザー定義関数を作っておけば短い関数名で好きな時に呼び出すことができます。

'[VBA] 列からランダムにセルを抜き出すプロシージャ

Function RSELECT(xx As Range, y As Integer) As Variant
  Dim rd As Long, cnta As Long, clm As Integer
  Randomize
　cnta = WorksheetFunction.CountA(xx)
　rd = Int((cnta - y + 1) * RND) + y
　clm = xx.Column
　RSELECT = Cells(rd, clm)
End Function

RSELECT は Random Select の略です。
引数に列全体と上端セルの行番号を指定します。たとえば、

=RSELECT(A:A,1)

と入力するだけで、A 列の 1 行目から（データの入力された）１番下までの範囲を認識して無作為にデータをもってきます。もし 1 行目を見出しにしているなどの理由で外したいと思えば、

=RSELECT(A:A,2)

というように入力します。上のコードの意味がわからなくても標準モジュールに貼りつけておくだけで動くので、必要な人はコピーしてお持ち帰りください。

RSELECT 関数の構造を説明しておきます。ユーザーが入力するセルデータの種類が不定なので、 RSELECT 関数自体はバリアント型（Variant）にしておきます：

Function RSELECT(xx As Range, y As Integer) As Variant

引数 xx にはユーザーが列を指定するのでセル範囲型 (Range)です。このサイトではセル範囲型変数に xx という記号を使うことが多いので頭の片隅に置いておいてください。引数 y は上端セルの行数なので整数型 (Integer) 、セル数 cnta は非常に大きな値をとる可能性があるので長整数型 (Long) で宣言しておきます:

Dim rd As Long, cnta As Long, clm As Integer

列番号 clm は一般に小さな値（といっても 10,000 程度なら全く問題ありません）をとると考えられるので、普通の整数型 (Integer) で宣言してあります。次にワークシート関数 Conutaを呼び出して、ユーザーが指定した範囲 xx におけるセルの行数を得ます：

cnta = WorksheetFunction.CountA(xx)

このように、WorksheetFunction.[Excel 関数の名前] という記述でほとんどの Excel 関数 を呼び出すことができます。次は表の上端（y）から末端（cnta）までの間の乱数を得る記述です：

rd = Int((cnta - y + 1) * RND) + y

ユーザーが指定した範囲の列番号は Colum プロパティによって参照します：

clm = xx.Column

最後に RSELECT 関数にセルの中身を入れてユーザーにデータを返します：

RSELECT = Cells(cnta, rd)

以上です。短く簡単なプロシージャですが、初級の方はセル範囲型変数や、そこから列番号を得るやり方に戸惑うかもしれません。普段の操作で複数セルを選択したときのイメージで「そこ（Range 型オブジェクト）には行数や列数の情報が含まれているはず」と考えてください。VBA におけるプロパティはそうした情報を引き出すという感覚で用います。