Excelで次のような表を作りました。(例として書きます)
1. みかん
2. バナナ
3. もも
4. なし
5. 栗 ・・・(以下同様) と続いていきます。
今回、その表から、ランダムにデータを抽出し、別のセルに書き出したいのですがどうやっていいのかわかりません。(理想図を下に書きます。)
2. バナナ
5. 栗
1. みかん ・・・・・(以下同様)
このようにして、今ある表から、ランダムに並び替えた、 『新たな表』 を作りたいのです。
どのように関数(RAND関数?)を設定したらいいのかよくわかりません。 教えてください。お願いします。
ちなみにOffice2000です。
No.9ベストアンサー
- 回答日時:
こんにちは。
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関数の範囲をうまく調整したらできました!!(数式の意味がわかるようになれば話が早いんですけどね・・・w)
僕の解釈だと、要はランダムに並び替えるには、表を一列追加して、その数字をランダムに割り振り、その順に並べればいいのですね! 一つセオリー?みたいなのを習得することができたような気がします。
F9で更新すれば、何パターンも作ることができるのでとても使いやすくすることができました!
回答ありがとうございました。 とても役に立ちました。
No.13
- 回答日時:
もううんざりだという向きもあるかもしれませんが、
補足をしておきます。
>マクロと比べたら効率が悪いですし、私の方法はあまり良くなかった
実は、(言いたくはないですが)あのマクロは関数による方法に比べると相当効率が悪いです。
勘違いして欲しくないのですが、
私は、誰かを批判したかったのではなく
「ほとんどそういう症状がでない」と
「そういう症状がでない」は違うと言ってただけです。
No.12
- 回答日時:
#11様
> 100兆+1回目の乱数が「それ以前に出たものでない」
> なんてことが逆にあり得ると思いますか?
思いません。
200件を10万回の例は「なかなか重複しないデータが作れないぞ~」
ってほど、連続で重複が続く事は稀ではないか思ってあげました。
重複チェック用関数と併用すれば F9を1~数回押せば重複なしデータが
作成可能でしょう。(データ量によって重複確率は増しますが)
マクロと比べたら効率が悪いですし、私の方法はあまり良くなかった
ですが、関数だけで実現したいってニーズもあるでしょうし、最初に
書き込んだ時に出ていた回答は「マクロじゃないと」ってのが主でした
ので関数でもやり方を工夫すればって事を表したかっただけです。
No.11
- 回答日時:
papayuka 様
もしなにか気分を害されたのならすみません。
結局の処
100万回に一回の確率で弾がでる銃があるとして
その銃でロシアンルーレットをやったとすると
1回目に出る時もあるし、
100万回目に出る時もあるだろうと思います。
(まったくでないかもしれません)
例えば、一回目に出たとして
その事実の後に
同じ銃を使うとして、次に弾がでる確率は、
100万×100万分の1かと言うと
それは間違いで、
やはり、100万分の1です。
それで、その100万回に一回の確率で弾がでる銃を
渡されたとして、
この銃で頭を撃ったとしても、
死ぬ確率は交通事故で死ぬより低い。
ほとんど無いんだよと言われたとして
その銃を使ってみる気になりますか?
そういう意味では、
200件のデータで10回試行したということは
なんの意味もありません。
やはり、5行のデータで数回でもでるときはでるのです。
実際、桁を1つ減らして
100兆回の乱数を作成した時、
100兆+1回目の乱数が「それ以前に出たものでない」
なんてことが逆にあり得ると思いますか?
後になればなるほど、それ以前に出た数と一致するような気がするのですが。
No.10
- 回答日時:
#7様
> 数回実験してみた処たった5行のデータなのに重複が生じた
そうですか、それは驚きです。
素人なのでこんな方法で良いか解りませんが、自分なりに下記のように検証した
限りでは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
- 回答日時:
識者に批判を乞う。
>新たな表』を作りたいのです
表を別のシートか列にコピーします。
・コピーした列の隣列の第1行にでも=RAND()と入れて
最終行まで複写する。小数点以下9桁の乱数が表れる。
乱数列をコピーし、自分の列に「値」を複写し、各セルの式を消す。
データと乱数行を範囲指定し、乱数の入った列で「並び替え」する。
最初回答を見た時は「なんのこっちゃ?」と思いましたけど、今見るとなるほど!と思いました(笑)
要するに、表の先頭の数さえランダムにしてしまえば、あとはExcelの標準機能の、「並び替え」を使えばできてしまいますね。F9での更新機能は使えませんけど(使えないことも無いですが)、一番手軽で、しかも分かりやすいと思います。
回答ありがとうございました。
No.7
- 回答日時:
#6様
>おっしゃる通り、確かに絶対無いとは言えないでしょうけど、、、
>15桁だと 999兆9999億9999万9999 になるのかな?
>重複する確率って、、、
乱数のプログラム関係の本やサイトでも読まれたらわかると思いますが、それほどの(1000兆もの間)循環しないなんてことは、たかがエクセルの乱数では、ないと(いうかありえないとさえ)思います。
実際の処、 #4の回答を見て作らないでおこうかと思ったんですけど、
数回実験してみた処
たった5行のデータなのに重複が生じたのを実際に見たので作ることにしたのです。
もちろん、実用には充分だとは思いますが、
表が充分長くなってきた時には、結構重複がでてくる可能性があると思っています。
また、そのような長い表の時にそれと気づくのが実際の場面で難しいとも思います。
また、もともとの表に重複があった場合
#2で「A列のデータは単に行番号ではなく
入力された値である」
ということからも
#4の方法では、VLOOKUPでうまくいかない(一意でないキーの時にランダムに表を抽出するような)場合もあるかもしれません。
No.6
- 回答日時:
> だいたいいける(ランクが同列になる場合がある)
おっしゃる通り、確かに絶対無いとは言えないでしょうけど、、、
15桁だと 999兆9999億9999万9999 になるのかな?
重複する確率って、、、
No.5
- 回答日時:
#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
- 回答日時:
A1に =RAND()
B1に =RANK(A1,$A$1:$A$5)
C1に =VLOOKUP(B1,元リスト範囲,2,0)
として、A1:C1をA5:C5までコピー
F9キーで変わります。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【マクロ】functionプロシージ...
-
googleのスプレッドシート
-
エクセル IF計算式?でしょうか?
-
エクセル折れ線グラフについて...
-
Excelで数値を時間数に変換する...
-
Excelの警告について
-
EXCELで数値が異なった数字を足...
-
エクセル、LibreOffice Calcプ...
-
【マクロ】for nest について ...
-
エクセルを使用して、円周率を...
-
Excelについて
-
エクセルの数式バーのフォント...
-
Excelについて。Excelに縦1列に...
-
エクセルでファイルの最終更新...
-
【マクロ】関数をセルに入力す...
-
【マクロ】AブックからBブック...
-
エクセルマクロを教えてほしい...
-
【マクロ】マクロが割当てされ...
-
エクセルのセルの書式ユーザー...
-
エクセルをエクセレントに究める
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルの数式バーのフォント...
-
エクセルでファイルの最終更新...
-
2列に入っているデータを1列...
-
データチェックを行うエクセル...
-
再質問です。マクロの修正箇所...
-
エクセルで 自動的に◯や数字を...
-
数字入力後他の文字等が表示さ...
-
Excel 小さくなったスクロール...
-
F9キーについて。
-
【ExcelVBA】ダブルクォーテー...
-
Excelに詳しい方! B列が「日...
-
セルの数を求めたい
-
Excelを無料で使うには? パソ...
-
IFとIFS関数
-
【Excel】別シートから条件に合...
-
EXCELの散布図で日付が1900年に...
-
ある表にフィルターをかけて出...
-
【ExcelVBA】名前を付けて保存→...
-
Excelセルを跨いで合計を出す方法
-
エクセルのツールバーから数値...
おすすめ情報