エクセル2013基本講座：データをランダムに並べ替える(シャッフルする)

2021/11/13

データをシャッフルする　　　　　 Topへ

下図のような都道府県名を列記した表があります。
- 元の順序に戻せるように、A列にはナンバーリングしてあります。
C2セルに　=RAND()　と入力して、フィルハンドルをダブルクリックして、数式を下方向へコピーしました。
C列のセルを選択した状態で、[データ]タブの[昇順]を実行します
- [降順]を実行してもかまいません。(ランダムに並べるのですから、どちらでもよい)
データがランダムに並べ変わりました(シャッフルされました)。
- 並べ替えの[昇順]または[降順]を実行するたびに再計算が実行されますので、シャッフルが実行できます。

次項のランダムに取り出すのと重複しますが、数式で他のセルへランダムに並べ替えることもできます。
結果だけを示します。
- E2セルには　=INDEX($B$2:$B$48,MATCH(LARGE($C$2:$C$48,ROW(A1)),$C$2:$C$48,0))　と入力しています。
  この数式をE48セルまでコピーします。
  [F9]キーで再計算するたびにデータが変化するのを確認できます。

Microsoft 365ではSORTBY関数とRANDARRAY関数が使えるようになりました。
これらの関数を使うことで、連数を発生する列(作業列)が不要になります。
- SORTBY関数で指定した範囲または配列でデータを並べ替える
- RANDARRAY関数でランダムな数値の配列を返す
D2セルに　=SORTBY(B2:B48,RANDARRAY(47))　と入力するだけです。
再計算のたびに並べ替えが実行されます。再計算のショートカットキーは[F9]です。
データをテーブルに変換すると、(テーブル名は「テーブル1」としています。)
=SORTBY(テーブル1[都道府県],RANDARRAY(ROWS(テーブル1[都道府県])))
のように数式を書くことができます。

Sheet1には上記のようにRAND関数を使って並べ替えるようにしてあります。
Ｄ2セルにＲＡＮＤ関数で発生させた値の大きさの順位を求めてみます。
Ｄ2セルに　=RANK(C2,$C$2:$C$48)　と入力します。下方向へ数式をコピーします。
Ｆ2セルに　=INDEX($B$2:$B$48,MATCH(ROW(A1),$D$2:$D$48,0))　と入力して、Ｆ6セルまでコピーします。
ＲＡＮＫ関数で求めた1～5に一致する行の都道府県名を取り出します。
- 数式中の　ROW(A1)　はA1セルを参照しているわけではありません。数値の1を得るためのものです。
[F9]キーを押して、再計算を実行するたびにRAND関数で返される値（C列の値）が変わり、それに応じてRANK関数で返される値（D列の値）も変わります。
よって、F2:F6の値が変わるのが確認できます。

上記の例では、RANK関数を使ってランダムに5つの県名を取り出しましたが、ちょっと複雑になりますが、RANK関数を使わなくても取り出すことができます。
LAREGE関数を使って取り出してみます。
- E2セルには　=INDEX($B$2:$B$48,MATCH(LARGE($C$2:$C$48,1),$C$2:$C$48,0))　と入力しています。
  なお、E3セル以降は　LARGE関数の引数は　2,3,4,5　とします。
  - 引数部分は　ROW(A1)として、=INDEX($B$2:$B$48,MATCH(LARGE($C$2:$C$48,ROW(A1)),$C$2:$C$48,0))　とすることもできます。
- [F9]キーを押して再計算を実行すると、取り出す都道府県名がランダムに変化するのを確認することができます。
SMALL関数を使うと下図のようになります。
- E2セルには　=INDEX($B$2:$B$48,MATCH(SMALL($C$2:$C$48,1),$C$2:$C$48,0))　と入力しています。
  なお、E3セル以降は　SMALL関数の引数は　2,3,4,5　とします。

Microsoft 365ではSORTBY関数とRANDARRAY関数とFILTER関数を使うことができます。
SORTBY関数とRANDARRAY関数でランダムに並べ替えて、FILTER関数で5個のデータを取り出します。
数式は一寸見た目が悪いのですが、作業列を一切使わずにデータを取り出すことができます。
=FILTER(SORTBY(B2:B48,RANDARRAY(47)),
{1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0})
なお、データがテーブルに変換されている場合は↓のような数式になります。
=FILTER(SORTBY(テーブル1[都道府県],RANDARRAY(ROWS(テーブル1[都道府県]))),
{1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0})
で、5個の都道府県をランダムに取り出すことができます。
1と0で表示する行を指定しています。上の5行を1;1;1;1;1;で表現しています。
- Filter関数の使い方は　FILTER関数でデータを抽出するをご覧ください。