エクセルで各行から一つずつセルを無作為抽出する方法

質問

表題の通りで、エクセルを使って、

A列（A1～A５）
B列（B1～B８）
C列（C１～C４）
D列（D１～D20）
E列（E１～E13）
…

などと、いくつかの行列データ？（各列は同数とは限らない）があった時に

A列→A３
B列→B8
C列→C２
D列→D16
E列→E9
…

などと、それぞれの列から任意のセルを抽出したいと思います。
このような時、どのようにすればよいでしょうか？？

具体的に、どんな関数を用いれば、実現できるか、
ご存知或いはお分かりの方、教えて下さい。。

またはマクロとやらを組まないとダメでしょうか？？
マクロは組んだことがないのですが、もしそれを使うべきとのことでしたら
その方法を教えて下さると、助かります。。

どうぞ、よろしくお願いいたします！

回答No.12

MackyNo1 · ベストアンサー

>そのような組み合わせを作るのに、一々手作業で、セルを埋めていく作業をすると時間がかかって仕方ないので。。

補足内容の意味がいま一つわからないのですが・・・

手作業でセルを埋めるとはどのような意味でしょうか？

例えば、私の回答でも提示した数式をそのままAA1セルにコピーし、「=RAND()」をAA2セルに、後から提示した数式をAA3セルにコピー貼り付けしてこの3つのセルを右方向にオートフィルコピーすれば1分もあれば操作できると思います。
そうしておけばAA3セルから右に自動的に各列からランダムに列内のセルを抽出した結果が得られる（例えばF9押せば、全ての操作が一度に終わる）と思うのですが・・・

もしかすると、単純に補助列を使いたくないということでしょうか？

十分に検討していませんが、非常に長い入れ子構造の複雑な数式を使えば補助列なしに表示できる可能性もありますが、そのような数式を作成するには大変な労力が必要となります（逆に入力に時間がかかる）。
そのような場合は、補助列を使うことで数式を簡略化できますが、これが不都合な理由は何なのでしょうか？

お礼

ご回答ありがとうございます！できました！！！
仰る通りでした、本当にすみません。。

列を並び替えた後、さらに手作業でコピペする必要があると
思い込んでおりました。。アホですね。。。
（でも、列の並び替え自体も、同様のコピペの計算を
エクセルにやらせてたのに、何でそんな思い込みしてたんだろ？？ｗｗ）

とにかく、仰るとおりのことで難なく解決できました。
本当に、ありがとうございました！！！心より感謝致します！

また、ご回答下さった方々にも、お詫びと感謝を致します。
すみませんでした。。ありがとうございます！

回答No.11

某HN クロメート(Chromate)（@CoalTar）

＃7です
>問題は、その後各行から抽出したセルをさらに、
>ランダムに並べ替えを行った組み合わせを作成したいと思っております。
と書かかれていたので最初(列)のランダム並べ替えはできているものと思っておりました。
といっても、今までの回答を理解できれば、
造作もないことなのですが、そこに何かしらの壁があるのでしょうかね。

F1セル =IF(A1="","",RAND())
右へ下へオートフィル
K1セル =IF(COUNT($A1:$E1)<>5,"",INDEX(A:A,RANK(F1,F:F)))
右へ下へオートフィル
P1セル =IF(K1="","",RAND())
右へ下へオートフィル
=IF(P1="","",INDEX($K1:$O1,RANK(P1,$P1:$T1)))
右へ下へオートフィル

お礼

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

下記のお礼にも、先ほど書きましたが、
仰ることは、既に実現できてるんですが、
それらの作業を、一度に！、行う方法はないのかと考えてます。。

つまり最初から、F9を押し続けるだけで
そのような組み合わせがどんどんできるような状態を
作りたいんです。

そのような組み合わせを作るのに、一々
手作業で、セルを埋めていく作業をすると
時間がかかって仕方ないので。。
（実際には、100以上の組み合わせをそれぞれのデータで
作成していくニーズがあるのです。）

やりたいことは、単純な無作為抽出の並び替えなので、
原理的には機械的な作業なんですが、
エクセルの普通の関数を使う方法があるかどうか、
分かりません。

ご存知、或いは、思い着かれましたらすみませんが、
ご教授下さい！！

補足

ほんとに、CoalTarさん始め、みなさんの仰る通りで、
普通にやればできることでした。。。

へんな思い込みで、できないと勘違いしておりました。
まことに、申し訳ありません。。
気付かせて下さり、お詫びと心よりの感謝を致します！＞＜

回答No.10

merlionXX

ANo5-9 merlionXXです。
昨夜は酔って帰ってあまり考えずに回答してしまいました。
列の並べ替えは、もちろん昨夜の回答で間違ってはいませんが、Scripting.Dictionaryを使えばもっと簡単でした。
後から列をランダムに並べ替えなくとも、最初からランダムかつ重複無しに列からデータを拾えますね、修正します。

Sub test03()
　　Dim x As Long, i As Long, z As Long
　　Dim myDic As Object
　　Set myDic = CreateObject("Scripting.Dictionary")
　　With Sheets("Sheet1")
　　　　x = .Range("A1").CurrentRegion.Columns.Count
　　　　Randomize
　　　　Do Until myDic.Count = x
　　　　　　z = Int(x * Rnd + 1)
　　　　　　If Not myDic.Exists(z) Then
　　　　　　　　myDic.Add z, .Cells(1, z).Offset(Int(.Cells(Rows.Count, z).End(xlUp).Row * Rnd)).Value
　　　　　　End If
　　　　Loop
　　End With
　　With Sheets("Sheet2")
　　　　.Rows(1).ClearContents
　　　　.Range("A1").Resize(1, myDic.Count).Value = myDic.items
　　End With
End Sub

お礼

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

下記のお礼にも書きましたが、
このプログラムの仕様と実際にどのように使うのか（動かし方？）
も、すみませんが、教えて頂けますでしょうか。。

お手数お掛けして、ほんとに申し訳ありません。。

回答No.9

merlionXX

> 問題は、その後各行から抽出したセルをさらに、
> ランダムに並べ替えを行った組み合わせを作成したいと思っております。

これでどうでしょう？

Sub test02()
　　Dim myV, myW
　　Dim x As Long, y As Long, i As Long
　　With Sheets("Sheet1")
　　　　x = .Range("A1").CurrentRegion.Columns.Count
　　　　y = .Range("A1").CurrentRegion.Rows.Count
　　　　ReDim myV(1 To x)
　　　　For i = 1 To x
　　　　　　Randomize
　　　　　　myV(i) = .Cells(1, i).Offset(Int(.Cells(Rows.Count, i).End(xlUp).Row * Rnd)).Value
　　　　Next i
　　End With
　　With Sheets("Sheet2")
　　　　.Rows(1).ClearContents
　　　　.Range("A1").Resize(1, UBound(myV)).Value = myV
　　　　For i = 1 To x
　　　　　　Randomize
　　　　　　.Cells(2, i).Value = Rnd
　　　　Next i
　　　　.Range("A1", .Cells(2, x)).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
　　　　.Rows(2).ClearContents
　　End With
End Sub

お礼

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

マクロ？というか、この言語？を良く分かってないので、
このプログラム自体がどのような仕様になっているか、
良く分かりません。。（すみません。。）

具体的に、質問にあるようにA～E列まで、各列それぞれ
A列（A1～A５）
B列（B1～B８）
C列（C１～C４）
D列（D１～D20）
E列（E１～E13）

のセルが埋まってた時に（それぞれのセルは文字列の場合もある）
どのようにプログラムを変更したらよろしいのでしょう？？

で、実際に、どのように動かせばよいのか、
大変恐縮なんですが、教えて頂けますでしょうか。。
初心者で、ほんとに、申し訳ありません。。

回答No.8

MackyNo1

＞エクセルのVerは2002です。
＞仰る関数はもしかしたら、ないかもしれません。。

私が回答したRANDBETWEEN関数を使用するには「ツール」「アドイン」で「分析ツール」にチェックを入れる必要があります（インストールが必要な場合もあります）。

>問題は、そのように抽出した各行のセル達を、さらにランダムに並べ替えを行いたい、ということです。

例えば、元のデータがA列からN列までにあるなら、提示した式でAA１セルから右にAN1セルまでに各列のランダムな値を表示しておき、AA2セルに「=RAND()」と入力して右方向にオートフィルます。
最後にAA3セルに以下の式を入力して右方向にオートフィルしてください。

=INDEX($AA$1:$AN$1,RANK(AA2,$AA$2:$AN$2))

これで3行目に各列からランダムに抽出したデータが表示されます。

お礼

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

仰ることは分かります。
各列をランダムに並べ替えできるので、
トップのセルが決まっていれば、そのセルたちを
並び替えするのは、同様にできるのです。

問題は、”一度に”各列から無作為にセルを抽出して
さらにそのセルたちを並び替える、という作業ができるか、ということなんです。。
ポイントは、”一度に”、です。

理想は、F9キーを押せば、そのような組み合わせが
どんどん作れる、という状態です。

やはりマクロを組まなければ無理でしょうかね？？
私にマクロを理解する能力があればよいのですけれど…。

普通の関数で実現できないかと、思った次第です。
すみません、面倒な質問をして。。

ご存知でしたら、ぜひ教えて下さい。m(_ _)m

回答No.7

某HN クロメート(Chromate)（@CoalTar）

＞問題は、そのように抽出した各行のセル達を、さらに
＞ランダムに並べ替えを行いたい、ということです。
F1セル =IF(COUNT($A1:$E1)<>5,"",RAND())
右へ下へオートフィル
K1セル =IF(F1="","",RANK(F1,$F1:$J1))
右へ下へオートフィル
P1セル =IF(K1="","",INDEX($A1:$E1,K1))
右へ下へオートフィル
ただし、A:E列は無作為抽出した後とする

お礼

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

A:E列が無作為抽出した後（並べ替えした後）だったなら、
A1～E1までを今度は同じように、並べ替えすればできるんですよね～。

ただその場合は、例えばF9押せば、全ての操作が一度に終わる…と
いうわけにはいかないですよね。

そういう一度にたくさんのパターンを欲しいんですよ。
やはりマクロしかないですかね？？

回答No.6

mu2011

NO4です。
RANDBETWEEN関数ですが、ツール→アドイン→「分析ツール」を選択→ＯＫが必要であることを失念していましたので追記します。

お礼

わざわざ、ありがとうございます！

ぜひ、下記の質問にもう一度、お答え下さいましたら、
とても助かります。。m(_ _)m

回答No.5

merlionXX

ワークシート関数での方法はもうでているので、マクロでの一例です。
「任意」とお書きですが、無作為にという意味でよいですね？
列はどこまであるんでしょう？
とりあえず、「Sheet1のA1セルから連続する範囲」　ということにします。
転記先は？
これはSheet2の1行目にしましょうか。

手順は以下のとおりです。初めてでも超簡単です。

1.エクセルの画面で、AltキーとF11キー同時に押し（以下Alt+F11キーと記述）て Visual Basic Editor を呼び出します。
2.Visual Basic Editor のメニューから「挿入」、「標準モジュール」で出てきたコードウィンド（右側の白い広い部分）に以下のコード（Sub～End Sub）をコピペします。

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

Sub test01()
　　Dim myV
　　Dim x As Long, y As Long, i As Long
　　With Sheets("Sheet1")
　　　　x = .Range("A1").CurrentRegion.Columns.Count
　　　　y = .Range("A1").CurrentRegion.Rows.Count
　　　　ReDim myV(1 To x)
　　　　For i = 1 To x
　　　　　　Randomize
　　　　　　myV(i) = .Cells(1, i).Offset(Int(.Cells(Rows.Count, i).End(xlUp).Row * Rnd)).Value
　　　　Next i
　　End With
　　With Sheets("Sheet2")
　　　　.Rows(1).ClearContents
　　　　.Range("A1").Resize(1, UBound(myV)).Value = myV
　　End With
End Sub

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

3.Alt+F11キーでワークシートへもどります。
4.Alt+F8キーで出てきたマクロ名（test01）を選択して実行します。

４を繰り返せば、何度でも各列のランダムなセルの値がSheet2に転記されます。

お礼

ご回答くださり、本当にありがとうございます！！
マクロのやり方を今回、初めて知りました！！
こんなすごいことができるとは、驚きです！
このやり方をすれば、本当に色々なことができそうですね。。

今はせっかく組んで下さってるプログラム？をほとんど解析できませんが、
一つずつ、解読してみたいと思います。。

それと、下記の方々と同じく、大変重要なことを書き忘れておりました。。

実は、No1の方のお礼に書いたとおり、私も列をランダムに並べ替えて、
常にトップのセルを選択すれば、各行から任意に抽出することが可能であるところまでは
できておりました。

問題は、その後各行から抽出したセルをさらに、
ランダムに並べ替えを行った組み合わせを作成したいと思っております。

ワークシート関数？で、無理でしたら、また、
マクロを使っても構いませんので、ご教授頂けると嬉しいです。

質問が不足いていて大変申し訳ありませんでした。。
ご存知でしたら、どうぞ、お願いいたします。

回答No.4

mu2011

一例です。
仮にＡ列からの任意抽出します。
=INDEX(A:A,RANDBETWEEN(1,COUNTA(A:A)))、=INDEX(B:B,RANDBETWEEN(1,COUNTA(B:B)))、以降は列番号を変更して下さい。

但し、RANDBETWEEN関数はシート内で更新があると値が変位しますので計算方法を手動とし、Ｆ９キー押下で他列と同期して抽出できると思います。

お礼

ご回答くださり、本当にありがとうございました。
下記の方々と同じく、大変重要なことを書き忘れておりました。。

実は、No1の方のお礼に書いたとおり、私も列をランダムに並べ替えて、
常にトップのセルを選択すれば、各行から任意に抽出することが可能であるところまでは
できておりました。

問題は、その後各行から抽出したセルをさらに、
ランダムに並べ替えを行った組み合わせを作成したいと思っております。

できれば、F9のようなキーを押すだけで、その組み合わせが
どんどんと変わっていくような、そんなことを実現させる方法は
ありますでしょうか？？

質問が不足いていて大変申し訳ありませんでした。。
ご存知でしたら、どうぞ、ご教授下さい。

回答No.3

nattocurry

=INDIRECT("A"&INT(RAND()*COUNTA(A:A))+1)

抽出対象列とは異なる列に入力してください。

お礼

ご回答くださり、本当にありがとうございました。
下記の方々と同じく、大変重要なことを書き忘れておりました。。

実は、No1の方のお礼に書いたとおり、私も列をランダムに並べ替えて、
常にトップのセルを選択すれば、各行から任意に抽出することが可能であるところまでは
できておりました。

問題は、その後各行から抽出したセルをさらに、
ランダムに並べ替えを行った組み合わせを作成したいと思っております。

できれば、F9のようなキーを押すだけで、その組み合わせが
どんどんと変わっていくような、そんなことを実現させる方法は
ありますでしょうか？？

質問が不足いていて大変申し訳ありませんでした。。
ご存知でしたら、どうぞ、ご教授下さい。

回答No.2

MackyNo1

たとえば以下のような関数を空白列に入力し右方向にオートフィルすれば、各列の中からランダムに値を抽出することができます。

=INDEX(A:A,RANDBETWEEN(1,COUNTA(A:A)))

＃Officeソフトはバージョンによって使用できる機能や操作法が異なりますので、質問の際には必ずバージョンを明記するようにしましょう（たとえば上記のRANDBETWEEN関数は2007以降なら問題なく使用できます）。

お礼

ご回答くださり、本当にありがとうございました。
大変重要なことを書き忘れておりました。。

すみません、エクセルのVerは2002です。
仰る関数はもしかしたら、ないかもしれません。。

ただ、仰る意味は分かります。
実は、No1の方のお礼に書いたとおり、私も列をランダムに並べ替えて、
常にトップのセルを選択すれば、各行から任意に抽出することが可能であるところまでは
できておりました。

問題は、その後各行から抽出したセルをさらに、
ランダムに並べ替えを行った組み合わせを作成したいと思っております。

できれば、F9のようなキーを押すだけで、その組み合わせが
どんどんと変わっていくような、そんなことを実現させる方法は
ありますでしょうか？？

質問が不足いていて大変申し訳ありませんでした。。
ご存知でしたら、どうぞ、ご教授下さい。