エクセルでランダムに抽出後、一つのセルにまとめる方法

質問

エクセル初心者です。

エクセルのシート１に
Ａ列の１行目～１０行目にＡ～Ｊまでの文字が入力されています。

これをシート２の
Ｂ列の１～１００行目に
シート１からランダムで抽出した３つ又は４つの文字を
ＡＣＥ
ＤＦＧ
・・・・

のように埋めるには、どのような式を入れればできるのでしょうか？
文字はだぶらないように抽出したいと思います。
文字はＣＡＥのように、順番が逆になっても構いません。
むしろ時々逆になったりするほうが都合がよいです。
抽出される文字数も、３つだけでなく、ランダムで４つ抽出するときも
あるようにしたいです。

オフィス２００３を使用しています。

ご回答よろしくお願いします。

回答No.5

merlionXX · ベストアンサー

No1 merlionXXです。
質問文を読み直しましたが、ひょっとして

> 文字はだぶらないように抽出したいと思います。

とは、リストアップされる文字列100個が重複しないだけでなく、個々の文字列内でも同じ文字は使用しないという意味だったのでしょうか？
もしそうなら先ほどのではダメです。

以下のようにしてみてください。

Sub test02()
Dim myRng As Range '変数宣言
Dim myDic As Object
Dim x As Integer, myStr As String, v As String

Randomize '乱数初期化

Set myRng = Sheets("Sheet1").Range("A1:A10") 'データ範囲
Set myDic = CreateObject("Scripting.Dictionary") 'オブジェクト準備

Do Until myDic.Count = 100 'ユニークで100そろうまで
x = Int(2 * Rnd) + 3 '乱数で桁数設定（3～4）
myStr = ""

Do Until Len(myStr) = x
v = myRng(Int(10 * Rnd) + 1) '乱数で文字設定
If InStr(myStr, v) = 0 Then '文字列内で重複しなければ
myStr = myStr & v '文字列につなげる
End If
Loop

If Not myDic.exists(myStr) Then '単語が重複しなければ
myDic.Add myStr, x '収録
End If
Loop '繰り返し

Sheets("Sheet2").Range("B1").Resize(myDic.Count, 1).Value = Application.Transpose(myDic.Keys) '転記
End Sub

お礼

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

No.1で補足させていただいた通り、
先の式で同一セル内で重複さえされなければ完璧でした。

実際にはＡ列の行数も抽出する数も、Ｂ列に吐き出す行数も質問とは違うのですが、
ご指定いただいた式？だと私でも改変できそうです。

ただ、今回ご指定の式？で
No.1の手順で同様の作業をしてみたのですが、
test02()の選択をしても、何も挿入されないようです。

何か問題があったのでしょうか？

回答No.7

imogasi

#3です。
参考までに、お聞きしたい。
お礼で、＞ご指定の方法ではうまくできませんでした。
とありますが、結果について、どういう点で、質問の主旨に合わないですか。
（RAND関数で乱数利用ですから、私が回答に上げた例（結果）とは表面的（値・文字では）には、全く違う結果になるのはご存知ですよね。それは別にして、他に質問の主旨に合わない点は）

補足

せっかくご回答いただいたのですが、
私がすると
#NAME?
という表示がでてしまうのです。

私は関数に関してはほぼ知識ゼロなので、
私の理解力が足らないのが原因と思います。

ご面倒おかけしました。

回答No.6

merlionXX

No5 merlionXXです。

> 何も挿入されないようです。

挿入されないとはSheet2のB1以降に何も表示されないということですか？何かエラーメッセージはでませんでしたか？
今、エクセルのあたらしいBOOKを立ち上げ、No5で回答したコードをそのままコピーしてやってみましたが正常に作動しました。
原因がわかりません。
実際にはＡ列の行数も抽出する数も、Ｂ列に吐き出す行数も質問とは違うとのことなので、もし、nori213さんのBOOKの実情にあわせてコードを改造したのなら、実際の状態を教えてください。
こちらでも修正してみますので。

お礼

何度もご回答ありがとうございます。

私にミスがあったようで、
もう一度やったら
ちゃんと表示されました。

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

回答No.4

FEX2053

その、やたら面倒そうな関数で挑戦してみました。確かにかなり面倒です。

１．C1:L100（合計1000個のセル）に =RAND() を入力する。

２．M1に =RANK(C1,$C1:$L1) と入力、M1:V100にコピーする。

３．W1に =IF(M1>7,M1,"") と入力、W1:AF100にコピーする。

４．B1に =CONCATENATE(W1,X1,Y1,Z1,AA1,AB1,AC1,AD1,AE1,AF1) と入力、B1:B100にコピーする。

４文字作るなら、3.のところで =IF(M1>6,M1,"") とすればオッケーです。

要は、「重ならない３つを取り出す」という行為に「全部の対象文字に
乱数を発生させ、上から順に取り出す」という面倒な手順を踏んでいる
わけで（多分処理も目一杯遅そう）、素直にVBAを使ったほうが、私も
「速くて簡単」だと思います。

・・・ま、やれば出来る、という話です。

お礼

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

質問に「式」と書いてしまったのは
私にマクロを使用するという概念が全くなかったからで、
マクロでも式でも、
できるだけ簡単なやり方が良いです。

私の質問がまずかったようです。

回答No.3

imogasi

今までになかった珍奇な質問で＞エクセル初心者には難しいように思う。
＞ランダムで４つ抽出するときもあるようにしたいです
３文字と４文字を（1つの結果シートに）混ぜるのは難しいともう
（１）３文字バージョン
（２）４文字バージョン
に分けてとりあえず３文字バージョンを考える。
その後３を規定しているところろを４にする、で我慢すること。
ーーー
＞文字はだぶらないように抽出したいと思います。
本件はランダム関連のＲＡＮＤ、ＲＡＮＤＢＥＴＷＥＥＮのようなものを使うことになると思うが、乱数はそもそも重複出現は排除されない。
同じものが現れれば捨てるとして、関数で表現すると、非常に式が長くなって、手に負えない。
ーーー
最終形はアルファベットの文字の３文字（か４文字）でしょうが、扱うのは、とりあえずは数にならざるを得ないと思う。たまたま本質問はＡ－Ｊの１０文字なので、０－９に置き換えて、０－９の乱数を３つ（３桁用）発生させて１２３、３１１、４６１・・のようにすると思うが
それをＢＣＤ・・などに変換する関数は長くなる（ＣＨｏｏｓｅ関数でも使う？）。
＝＝＝
結果として、私はＶＢＡで無いと面倒と思う。質問者はＶＢＡは警官無いだろうから、そちらは無理。
ーー
１セル内の３桁文字にするのは後にして
３列を考え、それぞれの列で０－９の数字を発生させる。
その段階で（各列ごとに）Ｃｈｏｏｓｅ関数でアルファベット化する。別の３列で文字化も出来る。
最後に３列を「＆」結合演算子で、１つの文字列にする。
順序も含め（＝ＡＢＤとＡＤＢは違うものとする）同じセルがあれば（ＣＯＵＮＴＩＦ関数など利用か？）、手作業で削除する。行の削除はimogasi方式などで出来るが複雑。
ーーー
Ａ１セルに
=CHOOSE(randbetween(0,9)+1,"A","B","C","D","E","F","G","H","I","J")
と入れてＣ１セルまで式複写・
Ａ１：Ｃ１の式を、Ａ５：Ｃ５まで式複写。
E D A
H I B
F I J
J J I
J I A
１つの文字列化は
Ｄ１に=A1&B1&C1
ーーー
しかしＲＡＮＤ関数の特徴として、再計算されるたびに、毎回セルの値＝結果が変わる。
だから早いうちに、セルの式を消してしまう（形式を選択して貼り付けー値）方が良い（考えやすい）と思う。
ーー
私は乱数理論に詳しく無いので、乱数に関連して、上記のやり方が、理論的な面で、欠陥が指摘されるかもしれないと思っている。
ーー
理論的な面も含め、総体的にみて、（質問者には、回答の式をコピーすれば結果が出て、ありがとう、と言うことだが、本来背後に持っているべき関連知識などがあり）、初心者には無理な質問であろうと思う。

お礼

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

ご指摘の通り、「そもそもＶＢＡとは何？」ということをググることから始めてます。

私のやり方が間違っていると思うのですが、
ご指定の方法ではうまくできませんでした。

回答No.2

Sinogi

>抽出される文字数も、３つだけでなく、ランダムで４つ抽出するときもあるようにしたいです
に対応します。→　Ａ11　はブランクにしておいてください。

※結果は３桁よりも４桁の方が多くなります。

作業列に　Ｃ～Ｑを使用します。
まず、Ｇ1～Ｑ1に　=RAND()　を入力し、11個の乱数を発生させます。
次にＣ1に
=RANK(G1,$G1:$Q1)を入力し,D1～Ｆ1にCopy&Paste
続いてＢ１に
=INDEX($A$1:$A$11,C1)&INDEX($A$1:$A$11,D1)&INDEX($A$1:$A$11,E1)&INDEX($A$1:$A$11,F1)

Ｂ１～Ｑ１を１００行目まで　Copy&Pasteして完成です。

お礼

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

ご指定の通りにしたら、良い形のものができました。

ただ、実際には
Ａ列に１００行くらい、Ｂ列に３０００行くらいのものを作りたかったので
少し大変そうです。

回答No.1

merlionXX

関数ではやたらめんどうそうなのでVBAでやってみました。
とても簡単ですので以下の手順をおためしください。

1.AltキーとF11キー同時に押し（以下Alt+F11キーと記述）て Visual Basic Editor を呼び出します。

2.Visual Basic Editor のメニューから「挿入」、「標準モジュール」で出てきたコードウィンド（右側の白い広い部分）に以下のコード（Sub～End Sub）をコピー＆ペーストします。

'********これより下**********

Sub test01()
Dim myRng As Range '変数宣言
Dim myDic As Object
Dim x As Integer
Dim myStr As String

Randomize '乱数初期化

Set myRng = Sheets("Sheet1").Range("A1:A10") 'データ範囲
Set myDic = CreateObject("Scripting.Dictionary") 'オブジェクト準備

Do Until myDic.Count = 100 'ユニークで100そろうまで
x = Int(2 * Rnd) + 3 '乱数で桁数設定（3～4）
myStr = ""
For i = 1 To x
myStr = myStr & myRng(Int(10 * Rnd) + 1) '乱数で文字順設定
Next i
If Not myDic.exists(myStr) Then '重複しなければ
myDic.Add myStr, x '収録
End If
Loop '繰り返し

Sheets("Sheet2").Range("B1").Resize(myDic.Count, 1).Value = Application.Transpose(myDic.Keys) '転記
End Sub

'********これより上**********

3.Alt+F11キーでワークシートへもどります.

4.Alt+F8キーで出てきたマクロ名（test01）を選択して実行します。

補足

何がどうなっているのか、私にはさっぱりわからないのですが(;^_^A
ご指定の通りにしたら、それらしきものができました。

これで同一セル内に
同じ文字が重複しなければ問題なかったんですが・・・