エクセル ランダム・ペア

Excel Windows

[EXCEL] エクセルで楽にランダムペアを割り当てるには?

今回はEXCELの小ネタです。

本ページはこんな方におすすめ

  • VBA 無しでランダムペアを組みたい
  • 口頭で説明できるレベルの簡単な関数を使いたい
  • 優しさ優先で、関数式は簡潔にしたい

かつてVBA を使ったランダム抽出のサンプルを紹介しましたが、いちいちVBAなんてメンドウ、ダサいなどいろいろマイナスの意見もあるかと思います。
くじ引きや抽選の場合は、やはりプログラム的に少しでも説明のつくコードを書くほうがいいかと思いますが、テニスの練習のペアを決めるなど、どうでもいいランダム抽出をしたいことがあります。

使用する関数は

  • RANDBETWEEN
  • INDEX
  • COUNTA

Excel INDEX RANDBETWEEN

使える関数 RANDBETWEEN

ここでは、使える関数「RANDBETWEEN」を紹介します。

 
=RANDBETWEEN(下限値, 上限値)

で返り値は整数になります。

Excel習いたての初心者でも十分使える関数です。

例えば、=RANDBETWEEN(1,50) とやると、1から50までのどれかの整数を気まぐれに返します。
気まぐれというのは、厳密にはワークブック内のワークシートが再計算されると、ランダムな値を再生成します。
ちなみに、RANDBETWEEN が絡んでいない無関係なセルに対しての変更であっても、ランダム値に変更されます。

Excel INDEX RANDBETWEEN

はっと気づいたかと思うのですが、この方法でランダムな値を取得しても、セルに対して何らかのアクションを行うと(再計算が行われれば)、ランダムな値が再生成されてしまいます。
一旦、セルに何かを入力して確定させてしまうと、急いでCrtl+Z(やり直し)しても、値は元には戻りません
この方法で出した値を実務で実際に使いたければ、セルを選択して「コピー」、そして別のシートやセルに「形式を指定して貼り付け」して「値」としてペーストする必要があります。

その前に、RANDBETWEEN関数の復習をしておきましょう。

INDEX関数は、範囲内(縦横何番目)のセルの値を調べる関数です。タテヨコのクロスしたセルの位置の値を求める関数ですね。

書式は、

 
=INDEX(範囲,縦位置,横位置)

横位置を省略すると、1列目がデフォルトでセットされます。

MATCH関数と組み合わせる応用が有名ですが、ここではINDEXの使い方だけ抑えておきます。

Excel INDEX 関数

上の例では、セル範囲(C4:F9)には名前が入っていますので、その範囲の上から4番目の(=INDEX(C4:F9,4,1))名前を求めています。

この関数を利用して、INDEX(C4:F9,4,1) のの部分を関数で置き換えてランダム抽出を試みるわけです、

次に、[code]RANDBETWEEN(1,COUNTA($B3:$B12)) [/code]の式の部分をチェックします。

COUNTA($B$3:$B$12) は範囲($B$3:$B$12)(ここでは、ペアを組む人の名前の中)から空白以外のマス目(セル)の数を数えます。
本例では空白のセルはないので、そのままマス目の数(つまり10名)が返ります。オートフィルでセルのアドレスがズレないように、絶対アドレスを参照しています。
間違いがちなのは、COUNTA の代わりに、COUNT($B$3:$B$12) とやってはいけません。COUNT関数は、(計算に使える)数値が入っているマス目だけを数える関数ですので、本例のような名前(文字列)が入っているマス目は数えられず、COUNT関数ではゼロを返してしまいます。

これで、式の中身の説明が終わりましたので、まとめていくと、
[code]RANDBETWEEN(1,COUNTA($B3:$B12)) [/code]は最小値1から最大値10(リストの全人数)の間で、ランダムに一つ(の整数)を選べという意味です。
本例では、1かせ10までの10通りの値がランダムに返ります。

つまり、[code]INDEX($B3:B12,RANDBETWEEN(1,COUNTA($B$3:$B$12)),1)[/code]は
ペアを組む人の名前の中(範囲$B$3:$B$12)から、上から「1から10までの範囲からランダム抽出した数字」番目のマス目の値(そのマス目に書かれている名前)を返しなさいという意味です。

ここまで読んでいただいて、ツッコミがあるかと思います。
「同じ人を何回もピックアップしてんじゃないよ!」
上の例を見ても「アリャ」となるのが、テニスのペアを組むといいながら、自分同士でペアになってどうするんだ(図のセルD12)って抽出になっています。
という声が聞こえてくるわけですが、今回の例は即席のランダムペア作成ですので、良しとしてください。

重複なしのランダム抽出方法もすぐに公開します。


関連記事
excel-mod-sumproduct
[Excel]1つ飛ばしでセルを合計する小ネタ

Excel を使っていると、セルを飛ばし飛ばしで合計を出したいときがよくあります。その方法は様々ですが、今回は私がよく使 ...

続きを見る


関連記事
エクセル ランダム
[VBA]Excel でランダム抽出するマクロ

かつて予想外に速攻作成に悩んだのが、Excel VBA で一覧からの無作為(ランダム)抽選です。ランダムは追求すると難し ...

続きを見る

RECOMMEND

-Excel, Windows
-, , ,