表（リスト）からランダムに抽出するには？

Excelで次のような表を作りました。（例として書きます）

1.　みかん
2.　バナナ
3.　もも
4.　なし
5.　栗　　　・・・（以下同様）　と続いていきます。

今回、その表から、ランダムにデータを抽出し、別のセルに書き出したいのですがどうやっていいのかわかりません。（理想図を下に書きます。）


2.　バナナ
5.　栗
1.　みかん　　・・・・・（以下同様）

このようにして、今ある表から、ランダムに並び替えた、　『新たな表』　を作りたいのです。

どのように関数（RAND関数？）を設定したらいいのかよくわかりません。　教えてください。お願いします。

ちなみにOffice2000です。

No.9 ベストアンサー 回答者： maruru01 回答日時： 2004/11/12 11:03

こんにちは。

maruru01です。

作業列を1列だけ使用した、絶対に重複しない数式での方法です。
元データがA1:B5で、C列を作業列に使用して、D:E列に表示するとします。
まず、C1に、

=INT(RAND()*5+1)

と入力します。次に、C2に、

=LARGE(INDEX(NOT(COUNTIF($C$1:C1,ROW($A$1:$A$5)))*ROW($A$1:$A$5),),INT(RAND()*(5-ROW(C1))+1))

と入力して、C5までコピーします。
そうしたら、D1に、

=INDEX(A:A,$C1)

と入力して、E1にコピー。
さらに、D1:E1を5行目までコピー。

やはり、[F9]を押すたびごとに並べ替えられます。

この回答へのお礼

回答ありがとうございました！！
数式の意味は実はほとんど、というか全く理解できません(笑)　けれど、自分の表に合うように、セルアドレスを修正したり、RAND関数の範囲をうまく調整したらできました！！（数式の意味がわかるようになれば話が早いんですけどね・・・ｗ）

僕の解釈だと、要はランダムに並び替えるには、表を一列追加して、その数字をランダムに割り振り、その順に並べればいいのですね！　一つセオリー？みたいなのを習得することができたような気がします。

F9で更新すれば、何パターンも作ることができるのでとても使いやすくすることができました！

回答ありがとうございました。　とても役に立ちました。

お礼日時：2004/11/13 17:16

No.13 回答者： BLUEPIXY 回答日時： 2004/11/13 02:14

もううんざりだという向きもあるかもしれませんが、

補足をしておきます。

＞マクロと比べたら効率が悪いですし、私の方法はあまり良くなかった

実は、（言いたくはないですが）あのマクロは関数による方法に比べると相当効率が悪いです。

勘違いして欲しくないのですが、
私は、誰かを批判したかったのではなく
「ほとんどそういう症状がでない」と
「そういう症状がでない」は違うと言ってただけです。

No.12 回答者： papayuka 回答日時： 2004/11/13 01:34

#11様

> １００兆＋１回目の乱数が「それ以前に出たものでない」
> なんてことが逆にあり得ると思いますか？

思いません。
200件を10万回の例は「なかなか重複しないデータが作れないぞ～」
ってほど、連続で重複が続く事は稀ではないか思ってあげました。
重複チェック用関数と併用すれば F9を1～数回押せば重複なしデータが
作成可能でしょう。（データ量によって重複確率は増しますが）

マクロと比べたら効率が悪いですし、私の方法はあまり良くなかった
ですが、関数だけで実現したいってニーズもあるでしょうし、最初に
書き込んだ時に出ていた回答は「マクロじゃないと」ってのが主でした
ので関数でもやり方を工夫すればって事を表したかっただけです。

No.11 回答者： BLUEPIXY 回答日時： 2004/11/12 23:25

papayuka 様

もしなにか気分を害されたのならすみません。

結局の処
１００万回に一回の確率で弾がでる銃があるとして
その銃でロシアンルーレットをやったとすると
１回目に出る時もあるし、
１００万回目に出る時もあるだろうと思います。
（まったくでないかもしれません）
例えば、一回目に出たとして
その事実の後に
同じ銃を使うとして、次に弾がでる確率は、
１００万×１００万分の１かと言うと
それは間違いで、
やはり、１００万分の１です。
それで、その１００万回に一回の確率で弾がでる銃を
渡されたとして、
この銃で頭を撃ったとしても、
死ぬ確率は交通事故で死ぬより低い。
ほとんど無いんだよと言われたとして
その銃を使ってみる気になりますか？
そういう意味では、
２００件のデータで１０回試行したということは
なんの意味もありません。

やはり、５行のデータで数回でもでるときはでるのです。

実際、桁を１つ減らして
１００兆回の乱数を作成した時、
１００兆＋１回目の乱数が「それ以前に出たものでない」
なんてことが逆にあり得ると思いますか？
後になればなるほど、それ以前に出た数と一致するような気がするのですが。

No.10 回答者： papayuka 回答日時： 2004/11/12 21:22

#7様

> 数回実験してみた処たった５行のデータなのに重複が生じた

そうですか、それは驚きです。
素人なのでこんな方法で良いか解りませんが、自分なりに下記のように検証した
限りではMsgが出る事はありませんでした。

先の方法で200件のデータを作成
C1:C200に =COUNTIF($B$1:$B$200,B1)
D1 に =SUM(C1:C200)
E1 に 200
下記を10回試した。

Sub Test()
　Randomize
　For i = 1 To 10000
　　Application.Calculate
　　If Range("D1").Value <> Range("E1") Then
　　　 MsgBox "重複"
　　　 Exit Sub
　　End If
　Next i
End Sub

#6さんのマクロ以外にも、#8さん、#9さんと回答が出揃ってますのでこの辺にしておきます。
回答ありがとうございました。

No.8 回答者： imogasi 回答日時： 2004/11/12 06:55

識者に批判を乞う。

＞新たな表』を作りたいのです
表を別のシートか列にコピーします。
・コピーした列の隣列の第1行にでも=RAND()と入れて
最終行まで複写する。小数点以下９桁の乱数が表れる。
乱数列をコピーし、自分の列に「値」を複写し、各セルの式を消す。
データと乱数行を範囲指定し、乱数の入った列で「並び替え」する。

この回答へのお礼

最初回答を見た時は「なんのこっちゃ？」と思いましたけど、今見るとなるほど！と思いました(笑)

要するに、表の先頭の数さえランダムにしてしまえば、あとはExcelの標準機能の、「並び替え」を使えばできてしまいますね。F9での更新機能は使えませんけど（使えないことも無いですが）、一番手軽で、しかも分かりやすいと思います。

回答ありがとうございました。

お礼日時：2004/11/13 17:11

No.7 回答者： BLUEPIXY 回答日時： 2004/11/12 02:08

#6様

＞おっしゃる通り、確かに絶対無いとは言えないでしょうけど、、、
＞15桁だと 999兆9999億9999万9999 になるのかな？
＞重複する確率って、、、
乱数のプログラム関係の本やサイトでも読まれたらわかると思いますが、それほどの（１０００兆もの間）循環しないなんてことは、たかがエクセルの乱数では、ないと（いうかありえないとさえ）思います。
実際の処、 ＃４の回答を見て作らないでおこうかと思ったんですけど、
数回実験してみた処
たった５行のデータなのに重複が生じたのを実際に見たので作ることにしたのです。
もちろん、実用には充分だとは思いますが、
表が充分長くなってきた時には、結構重複がでてくる可能性があると思っています。
また、そのような長い表の時にそれと気づくのが実際の場面で難しいとも思います。
また、もともとの表に重複があった場合
＃２で「Ａ列のデータは単に行番号ではなく
入力された値である」
ということからも
＃４の方法では、VLOOKUPでうまくいかない（一意でないキーの時にランダムに表を抽出するような）場合もあるかもしれません。

No.6 回答者： papayuka 回答日時： 2004/11/12 00:21

> だいたいいける（ランクが同列になる場合がある）

おっしゃる通り、確かに絶対無いとは言えないでしょうけど、、、

15桁だと 999兆9999億9999万9999 になるのかな？
重複する確率って、、、

No.5 回答者： BLUEPIXY 回答日時： 2004/11/11 23:46

#4の方ので、だいたいいける（ランクが同列になる場合がある）と思いますが、

一応マクロでのバージョンを作ってみました。

まず、マクロエディタでクラスの挿入というのをやります。
そこに必要な分だけ（表の列の数だけ）変数を確保します。
'- Class1 -------
Public value1
Public value2
'- Class1 end ---
ThisWorkBookにでも以下のコードをコピーペーストします。

Public Sub shuffle()
Dim base As Range
Dim toBase As Range
Dim data As New Collection
Dim ins As Class1
Dim pos As Integer
Dim i As Integer

Set base = Range("A1") ' 元表のトップ
Set toBase = Range("G1") 'コピー先のトップ

pos = 0
Do While base.Offset(pos, 0).Value <> ""
Set ins = New Class1
ins.value1 = base.Offset(pos, 0).Value '列の数だけやります。
ins.value2 = base.Offset(pos, 1).Value
data.Add ins, CStr(base.Offset(pos, 0).Value)
pos = pos + 1
Loop

Randomize
pos = 0
Do While data.Count <> 0
i = Int(Rnd * data.Count + 1)
toBase.Offset(pos, 0).Value = data.Item(i).value1 '列の数だけやります
toBase.Offset(pos, 1).Value = data.Item(i).value2
data.Remove (i)
pos = pos + 1
Loop
End Sub

No.4 回答者： papayuka 回答日時： 2004/11/11 21:45

A1に =RAND()

B1に =RANK(A1,$A$1:$A$5)
C1に =VLOOKUP(B1,元リスト範囲,2,0)
として、A1:C1をA5:C5までコピー

F9キーで変わります。