複数セルに入っている数字を、別セルにランダムに並び替えしたい。

質問

複数セルに入っている数字を、別セルにランダムに並び替えしたい。

大変、お世話になります。

Ａ　Ｂ　Ｃ　～　Ｖ　Ｗ　Ｘ　Ｚ
12　25　32　～　78　54　22
47　14　20　～　88　24　96
48　29　87　～　14　54　58
19　67　　　　　44　33　87
77　　　　　　　45　47　33
20　　　　　　　55　　　48
　　　　　　　　20　　　47
　　　　　　　　22

こんな感じで並んでます。

Ａセル縦に、いくつかのランダム数字
他のセル縦に、同じようにランダム数字が入っています。

ですが、これらのセル縦の数字量は、まちまちです。

（ここの例では、Ｖセルが一番、多いですよね）

また、ここでの例では、Ａ～Ｘセルまでとしましたが、
実際には、Ａ～ＢＢみたいに、横長でもあります。

以上の条件で、・・・

最後のセル　例：Ｚセル縦列に、
Ａ～Ｘセルの数字を全て使用して、ランダムに並び替えを
したいのです。

ただし、　Ａ～Ｘセルに書かれている数が、例えば　90個
あったとしたら、　Ｚセルにランダムに並び替えられる数も、
90個とします。

（減ったり、増えたりしたら、ダメ）

以上です。

宜しくお願いします。

回答No.13

Wendy02 · ベストアンサー

#6の回答者ですが、一応、返事をもう一度付けておきます。上手く行かなかったら無視しても構いませんが、以下のようにすれば出来るはずです。

>Ａ～ＡＦ　までセルを使用しています。

----
AH1;～
=SMALL($A$1:$AF$20,ROW(A1))　

#NUM! のエラーが出る所まで、オートフィルコピーします。
*20は、データの最終行（広げる必要はありません)

AI1:～
=RAND()
セルの右下をダブルクリック

AG1: ～（ここにランダム並べ替えの順序にダブリのない数字が取れます)
=INDEX(AH:AH,RANK(AI1,AI:AI))

エラーが出る所まで、オートフィルコピーします。
--
条件は、$A$1:$AF$20の範囲に「0」がないことです。

空白ではなく、「0」があって、それを省く場合には、
AG1:～の数式は、このようになります。
=SMALL($A$1:$AF$20,ROW(A1)+COUNTIF($A$1:$AF$20,0))

お礼

Wendy02さん、丁寧なお答え　ありがとうございました。
皆さん、それぞれ　すばらしいですね。
助かりました。

またの質問の際には、どうぞ　宜しく、　お願いいたします。

補足

Wendy02さん、

私に合わせて、式を書き直して下さり、ありがとうございます。

>Ａ～ＡＦ　までセルを使用しています。
これに合わせてあるので、このまま今夜、もう一度やってみます。

また、報告致します。

回答No.14

KURUMITO

空白のセルがあったら0表示になりますね。それを避けたいのでしたら次の式にしてください。
ＡＧ1セルへの入力の式は

=IF(INDEX(A:AF,ROUNDUP(ROW(A1)/COLUMN(AF1),0),MOD(ROW(A1)-1,COLUMN(AF1))+1)="","",INDEX(A:AF,ROUNDUP(ROW(A1)/COLUMN(AF1),0),MOD(ROW(A1)-1,COLUMN(AF1))+1))

ＡＨ1セルへの入力の式は

=IF(ROW(A1)>COUNT(AG:AG),"",LARGE(AG:AG,ROW(A1)))

ＡＩ1への入力の式は

=IF(AH1="","",RAND())

ＡＪ1への入力の式は

=IF(AI1="","",INDEX(AH:AH,RANK(AI1,AI:AI)))

お礼

KURUMITOさん、大変お世話になりました。

ありがとうございました。
また、質問の際には、よろしくお願いいたします。

回答No.12

KURUMITO

回答Ｎｏ10さんのコメントありがとうございます。
最後の式では同じ数値が出る可能性がありますね。次のようにすればＡからＸ列までに入力された数値がダブることもなく（ＡからＸ列ですでにダブっている数値は別として）ランダムに並べ変えることができます。
ＡＡ列への式はすでに示した式をやめてAA1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(Z1>0,RAND(),"")

次に並べ替えをした数値の答えですがAB列に表示させることにしてAB1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AA1="","",INDEX(Z:Z,RANK(AA1,AA:AA)))

これでＡからＸ列までにある数値をランダムに並べ変えることができますね。

補足

お世話になります。

Ａ～ＡＦ　までセルを使用しています。
ということは、下記の数式で、ＯＫですよね？

=INDEX(A:AF,ROUNDUP(ROW(A1)/COLUMN(AF1),0),MOD(ROW(A1)-1,COLUMN(AF1))+1)

=LARGE(AG:AG,ROW(A1))

=IF(AH1>0,RAND(),"")

=IF(AI1="","",INDEX(AH:AH,RANK(AI1,AI:AI)))

ところが、使用しているセルに　例えばＣセルには、Ｃ１ひとつだけしか数値が
入っていない場合がある時、　ＡＧセルに、　０（ゼロ）が　出てくるのではないかと
思われます。
どうでしょうか？

回答No.11

Wendy02

#6の回答者です。
>全くわからず、手に負えませんでした。

VBAは使えれば便利ですが、あえて、お薦めはしません。

私の回答は、ひとつの定石ですが、今、調べてみましたが、こちらの作ったマクロは、#2,#3 の作ったものとほぼ同じ考え方です。だから、関数でするなら、#2,3 でお試しください、というしかありません。ただ、#6で書いたけれども、ワークシート関数のRAND()の、一様乱数の発生は 32,768 種しかありません。数が少なければ、それだけ同じパターンが出てくる可能性があります。また、乱数から一意の数値をとるなら、1より以下で使わないと、一意の数としては使えませんが、それでは上手くいく保証がありませんから、その乱数に付随した数列から、並べ替えで数列をランダムにしてやるわけです。

回答No.10

noname#204879

[回答番号：No.1KURUMITOさん]へのコメント、

なるほどなぁ、とテクニックに感動しました。私も参考にさせていただきます。
ただ、試してみて分かったことですが、

》 ＡＡ列にはＡからＸ列までに入力されている数値がランダムに表示されます

はそのとおりですが、提案された式のままでは、元表（のデータ数が多いほど）に１個しか存在しないデータでもそれが複数個表示されるとか、２個存在するのに１個しか表示されないとかの問題が発生する可能性が大です。その原因は「INT(COUNTIF(Z:Z,">0")*RAND())」にあると思います。
私の見付けた解決策は作業列が５列も必要になるし、質問者は既に満足しておられるので、此処では注意を促すだけにしておきます。、

回答No.9

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

回答番号2,3です
KURUMITOさんの方法が完璧なら 当方の回答は無視してください。

ちょっとだけ言わせてもらうと、KURUMITOさんの方法はランダムな並び替えではありません。
「元データからランダムな抽出を 元データと同じ数だけ行う」ということです。
ここで重要なのは、A1セルの「12」が10回出る可能性もあるということです。

解決したようなので失礼します。

補足

CoalTarさん

写真付きでの説明ありがとうございました。
いろんなやり方があるのですね。
ご指摘の件もありますので、もう一度良く見てみます。
ありがとうございました。

回答No.8

KURUMITO

回答1です。
答えた式は列が多くなっても十分に対応することができます。

数式の途中ある

=INDEX(A:X,ROUNDUP(ROW(A1)/3,0),MOD(ROW(A1)-1,3)+1)

は次の式に変えてください。
次の式に変えることで最終列がＡＡになればＸをＡＡに変えるだけです。

=INDEX(A:X,ROUNDUP(ROW(A1)/COLUMN(X1),0),MOD(ROW(A1)-1,COLUMN(X1))+1)

=LARGE(Y:Y,ROW(A1))

上記の式は間違っていません。作業列が変わればもちろん変わります。

=IF(OR(Z1="",Z1=0),"",INDEX(Z:Z,1+INT(COUNTIF(Z:Z,">0")*RAND())))

作業列が変わればＺが変わるのは当然です。

補足

KURUMITOさん！

すばらしいです。完璧に出来ました。

少しの例で（Ａ、Ｂ、Ｃセル）、やったので次のように数式を変更しましたよ。

=INDEX(A:C,ROUNDUP(ROW(A1)/3,0),MOD(ROW(A1)-1,3)+1)

=LARGE(D:D,ROW(A1))

=IF(OR(E1="",E1=0),"",INDEX(E:E,1+INT(COUNTIF(E:E,">0")*RAND())))

それで、やってみたところ完璧です！

ちゃんと、Ａ～Ｃセルに書き込んである数字を、ランダムに、且つ同じ数だけＦセルに
表示できました！！！

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

回答No.7

layy

仕様の確認）
もともとあった数値とランダムに選んだ数値が同じときは
再度ランダムに選び直しますか？。

前と後で全く同じというのも計算上ありえます。

お礼

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

回答No.6

Wendy02

どういう目的の質問なのでしょう。その意味がわらないと、何か、回答する意味が、盲目的になっているような気がします。（演習問題？たぶん違うでしょうけれど、Randomというのは、意外に曲者です。)
それはともかく、以下のマクロをフォームボタンに取り付けたらよいかと思います。
標準で、2行目からの書き出しにしたほうが誤動作が少なくてすみます。
Z1のように、1行目を使う場合は、
Set rng = Range("A1").CurrentRegion.Resize(, k)
ここを、
Set rng = Range("A1:Y11")
などとします。空白セルを含めて良いもよいです。空白セルは確保されません。

そして、
2行目書き出し
Cells(2, k + 1)
を
Cells(1, k + 1)
にします。

'//標準モジュール
Sub RandomPickUP()
　　Dim rng As Range
　　Dim Ar() As Variant
　　Dim Ar2() As Variant
　　Dim i As Long, j As Long
　　Dim k As Long
　　'1行目に右から左にデータを探す
　　k = Cells(1, Columns.Count).End(xlToLeft).Column
　　Set rng = Range("A1").CurrentRegion.Resize(, k)
　　i = WorksheetFunction.Count(rng)
　　ReDim Ar(i - 1)
　　For Each c In rng.Cells
　　　　If IsNumeric(c.Value) And c.Value <> "" Then
　　　　　　Ar(j) = c.Value
　　　　　　j = j + 1
　　　　End If
　　Next
　　ReDim Ar2(1, j - 1)
　　Randomize
　　For i = 0 To j - 1
　　　　Ar2(0, i) = Ar(i)
　　　　Ar2(1, i) = Rnd()
　　Next
　　B_Sort Ar2()

　　'2行目からの書き出し
　　Cells(2, k + 1).Resize(UBound(Ar2, 2) + 1).Value = Application.Transpose(Ar2)
End Sub

Sub B_Sort(ByRef Ar() As Variant)
　Dim u As Long
　Dim i As Long
　Dim j As Long
　Dim t1 As Variant
　Dim t2 As Variant
　u = UBound(Ar(), 2)
　i = LBound(Ar(), 2)
　Do While i < u
　　j = u
　　Do While j > i
　　　If Ar(1, j) < Ar(1, i) Then '昇順
　　　　t1 = Ar(0, j)
　　　　t2 = Ar(1, j)
　　　　Ar(0, j) = Ar(0, i)
　　　　Ar(1, j) = Ar(1, i)
　　　　Ar(0, i) = t1
　　　　Ar(1, i) = t2
　　　End If
　　　j = j - 1
　　Loop
　　i = i + 1
　Loop
End Sub

補足

すみません、マクロっていうのが、まずどこをどうやればいいのか
全くわからず、手に負えませんでした。
せっかくの、長い文書でお答えいただき、ありがとうございました。

これは、ある物流の集計データを、実際に流れているものに作り直すのに
その過程として使いたいと考えたものでした。

回答No.5

web2525

ランダムに作成された数値群をランダムに1列に配置したい？

初めからZ列にランダムな数値の並びを作成したらいいのでは?

それとももとになる数値データ群はランダムに作成されたものでなく何かのデータなのでしょうかね?

補足

はい、それでもいいのですが
出来上がってきた、ランダム数郡が、いくつかあるので、
Ａ～？セルと分かれていたというわけです。

回答No.4

mt2008

何かの演習問題に付き合っている気分になってきました……。
以下のマクロで一列にまとめた後は、QNo.5908365と同様に=Rand()を使って並び替えて完成。

Sub sample()
　nMaxCol = Range("A1").End(xlToRight).Column
　nRow = 1
　For nCol = 1 To nMaxCol
　　Range(Cells(1, nCol), Cells(1, nCol).End(xlDown)).Copy
　　Cells(nRow, nMaxCol + 1).PasteSpecial Paste:=xlPasteValues
　　nRow = Cells(Rows.Count, nMaxCol + 1).End(xlUp).Row + 1
　Next nCol
End Sub

QNo.5908365
http://okwave.jp/qa/q5908365.html

補足

mt2008さん、すみません。
お付き合い頂き、本当にありがとうございます。

私ですが、セルに計算式を入れるくらいなら、出来るのですが、
マクロというのが、まずどのようにして入力？すればいいのかも
わかっていませんでした。

長い文章で、説明頂いたのですが、理解できず、申し訳ありません。
別の方の方法で、解決しましたが、もしよろしければ、マクロの使い方を
教えて下さい。

エクセル2000を使用しております。