縦横に並んだリストから無作為に1つのセルを抜き出します

 ワークシートの 2 次元配列から1つの項目を無作為に選び出します。

 ExcelVBA2次元表から無作為抽出

 別記事で作った ALPHABET 関数に Excel 関数を組合わせる方法と、VBA のみでやる方法の 2 種類を紹介します。

ALPHABET 関数を応用します

 以前に作った ALPHABET 関数というのは、まさにこういうことをやるために作っておいたファンクションマクロなのです。前の記事を読んでいない人もわざわざ ALPHABET 関数の記事へ飛んで参照するのも面倒でしょうから、ここに載せちゃいますよ。コピーして使ってくださいな。

 'ランダムアルファベット
 Function ALPHABET() As String
  Randomize
  Dim n As Variant
  Dim k As Integer, m As Integer
  n = Array("A", "B", "C")
  m = UBound(n) - LBound(n) + 1
  k = Int(m * RND)
  ALPHABET = n(k)
 End Function

 これをモジュールに追加したうえで 3 列の表を作り、ワークシートのセルに

=INDIRECT(ALPHABET()&RANDBETWEEN(2,COUNTA(A:A)))

と入力すると表の中から項目の1つが無作為に選ばれます。ただし A 列のセルでは使用しないでくださいね。 COUNTA(A:A) に引っ掛かってしまって正常に機能しませんから。何だか妙に長い記述ですけど、上の関数入力について簡単に説明すると ......

 ① COUNTA 関数で A 列の空白でないセル数を数えます
 ② RANDBETWEEN 関数で 2 から A 列のセル数までの値をランダムに得ます。
 ③ ALPHABET 関数で A, B, C の中から無作為で 1 つの文字を選択します。
 ④ ②と③で取得した値を & で結んでセル番地の形に整えます。
 ⑤ INDIRECT 関数でセル番地に対応するセルを参照します。

ということになります。もし表の列を増やしたい場合は、 ALPHABET 関数のマクロ記述の中にある配列要素を "D", "E", ...... と増やしておきます。
 

XYSELECT 関数

 表から無作為抽出する関数を 1 から作ります。

=XYSELECT()

というように引数なしで使用するユーザー定義関数です。

 '2次元表から無作為にセルを1つ抜き出します

 Function XYSELECT() As Variant

  Dim r1 As Long, r2 As Long
  Dim rdx As Long, rdy As Long

  Randomize

  r1 = ActiveSheet.Rows.Count
  r2 = Cells(r1, 1).End(xlUp).Row
  rdx = Int(3 * RND) + 1
  rdy = Int((r2 - 1) * RND) + 2
  XYSELECT = Cells(rdy, rdx).Value

 End Function

 

XYSELECT 関数の概要

 まず以下の記述で使用中のシート(どのシートでも同じですが)の全行数を取得して変数 r1 に入れます:

r1=ActiveSheet.Rows.Count

 End プロパティと Row プロパティで A 列の最終行を得て r2 に入れます:

r2 = Cells(r1, 1).End(xlUp).Row

 あとは rdx に「 1 ~ 3 」、 rdy に「 2 ~ A 列の行数 -1 」の乱数を入れ、(rdy, rdx)にあるセルを参照して関数として出力します。列の数を増やしたい場合は rdx の RND にかかる係数を好きな列数に書き換えてください。

スポンサードリンク
末尾大型広告
末尾大型広告

コメントをどうぞ

メールアドレスが公開されることはありません。

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)