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

Question

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

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
…

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

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

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

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

MackyNo1 · Accepted Answer

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

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

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

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

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

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

CoalTar · Answer

＃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)))
右へ下へオートフィル

merlionXX · Answer

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

merlionXX · Answer

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

これでどうでしょう？

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

MackyNo1 · Answer

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

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

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

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

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

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

CoalTar · Answer

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

mu2011 · Answer

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

merlionXX · Answer

ワークシート関数での方法はもうでているので、マクロでの一例です。
「任意」とお書きですが、無作為にという意味でよいですね？
列はどこまであるんでしょう？
とりあえず、「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に転記されます。

mu2011 · Answer

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

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

nattocurry · Answer

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

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

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

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

＃7です

この回答への補足

ANo5-9 merlionXXです。

> 問題は、その後各行から抽出したセルをさらに、

＞エクセルのVerは2002です。

＞問題は、そのように抽出した各行のセル達を、さらに

NO4です。

ワークシート関数での方法はもうでているので、マクロでの一例です。

一例です。

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

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング