エクセルで組み合せ一覧表を作成したいです

質問

エクセルでシート１のA1～O1の各セルには、アルファベットや数字(1～50)が入っています。
A1～O1までの１５個のセルから、６つを選択した時の組み合せ一覧を作成したいと思っています。

組み合わせは計算すると、15C6となるので全５００５通りになります。
組み合せの一覧は、シート２に表示させたいです。

(a,b,c,d,e,f)と(b,a,c,d,e,f)は同じものとします。
またA1～O1のセルに入るのは、アルファベットと数字の組み合せが複雑であれば、数字のみとしたいと思います。

関数だけで何とかしようと思いましたが、どうしても無理のような気がしてしまいましたので、こちらにご質問をさせて頂きました。

VBAの知識もないため、自分でプログラムを組む事もできません。
シートにVBAを組み込む方法は、これまでに経験があるので理解しております。

アドバイスを頂けると嬉しく思います。
何卒、よろしくお願い致します。

回答No.1

sige1701 · ベストアンサー

回答が付かないようですね。お遊びで

A1～A15にアルファベットや数字(1～50)を入れます

H列からM列を作業用列とします
H1=1 I1=2 J1=3 K1=4 L1=5 M1=6
H2=IF(H1=10,"",IF(I1=11,H1+1,H1))
I2=IF(H2="","",IF(I1=11,H2+1,IF(J1=12,I1+1,I1)))
J2=IF(H2="","",IF(J1=12,I2+1,IF(K1=13,J1+1,J1)))
K2=IF(H2="","",IF(K1=13,J2+1,IF(L1=14,K1+1,K1)))
L2=IF(I2="","",IF(L1=14,K2+1,IF(M1=15,L1+1,L1)))
M2=IF(J2="","",IF(M1=15,L2+1,M1+1))
と入力して5005行目までコピー

B1=INDEX($A:$A,H1)
B1:G5005にコピー

こんな感じかな

お礼

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

もしかして関数だけで、全通りの生成ができてしまうのでしょうか！
ネットでいろいろと調べてVBAでしか、組み合わせ全通りの生成が無理だと思っていたので、正直非常に驚いております。
全通りの組み合わせは「B1:G5005」で表示される組み合わせと考えてよいでしょうか？

あとまたA1～A20の中から６つを選択した場合の組み合わせの場合、教えて頂いた関数を、どのように変更したら良いでしょうか。
IF関数はこれまでに何度か使った事があるのですが、教えて頂いた記述につていは私自身が理論的な事（なぜこのような記述になるのか）が理解できず、どのように変更した良いのか検討も付きません。

20C6の組み合わせは、38760通りになるのでまだエクセルで算出できる範囲なので、20個の中から6個を選択した場合の組み合わせも、エクセルで表示させたいと思いました。

お手数をおかけしますが、ご教授頂けると助かります。

よろしくお願い致します。

補足

ずっと眺めていたら、何となく理解できてきました。
20C6にする場合、記述されている「１０～１５」の数字に+5して「１５～２０」に書き直せば良いだけですよね？

この記述方法で合っているのか分からないので、念のために確認をお願い致します。

回答No.5

sige1701

一応作成してみました、考え方は同じです
こんな感じで良いかな

Sub Macro1()
'
Dim A桁目 As Integer, B桁目 As Integer, C桁目 As Integer, D桁目 As Integer, E桁目 As Integer, F桁目 As Integer

行数 = 2
For A桁目 = 1 To 15
　For B桁目 = A桁目+1 To 16
　　For C桁目 = B桁目+1 To 17
　　　For D桁目 = C桁目+1 To 18
　　　　For E桁目 = D桁目+1 To 19
　　　　　For F桁目 = E桁目+1 To 20
　　　　　　Cells(行数, 1) = Cells(1, A桁目)
　　　　　　Cells(行数, 2) = Cells(1, B桁目)
　　　　　　Cells(行数, 3) = Cells(1, C桁目)
　　　　　　Cells(行数, 4) = Cells(1, D桁目)
　　　　　　Cells(行数, 5) = Cells(1, E桁目)
Cells(行数, 6) = Cells(1, F桁目)
　　　　　　行数 = 行数 + 1
Next F桁目
　　　　Next E桁目
　　　Next D桁目
　　Next C桁目
　Next B桁目
Next A桁目
'
End Sub

補足

VBAでも考えて頂きありがとうございます。

早速試してみましたが、VBAで全通りを生成しようとすると結構時間がかかるもんなんですね。
私の環境では４～５分かかかりました。
一方、最初に教えて頂いた関数の場合は、３０秒もかからなかったです。

一長一短ですね。
VBAを利用すれば容量を抑える事ができるけど、時間がかかる。
関数の場合は、処理速度は早いけど、容量が多くなってしまう。

今のところ、教えて頂いた関数とVBAの２通りの方法しかないのですが、その他にも良い方法はありそうですか？
処理速度を考えると、多少容量が大きくなってしまっても、関数の方が良いのかなーと思ってます。

この関数をもっとスマート（容量軽減のため）にできないか、私も考えてみようと思います。

また何か良いアイディアがありましたら、アドバイスをお願い致します。

回答No.4

noname#192382

NO2ですが、ためしに実行したところ下記のプログラムで56通りのデータが得られました。下記のプログラムのなかの
to 1をto 10
to 2をto 11
to 3をto 12
to 4をto　13
にそれぞれ修正して実行すれば計算どおりのデータが得られると思います。
'
' Macro1 Macro
' マクロ記録日 : 2008/2/26 ユーザー名 :
'
Dim top As Integer, sec As Integer, thd As Integer, four As Integer, fif As Integer, six As Integer, myl As Integer, t1 As Integer, s1 As Integer, th1 As Integer, fo1 As Integer, fif1 As Integer

myl = 3
For top = 1 To 1
Cells(2, 1) = Cells(1, top)
t1 = top + 1

For sec = t1 To 2
Cells(2, 2) = Cells(1, sec)
s1 = sec + 1

For thd = s1 To 3
Cells(2, 3) = Cells(1, thd)
th1 = thd + 1

For four = th1 To 4
Cells(2, 4) = Cells(1, four)
fo1 = four + 1

For fif = fo1 To 14
Cells(2, 5) = Cells(1, fif)
fif1 = fif + 1

For six = fif1 To 15
Cells(2, 6) = Cells(1, six)

Cells(myl, 1) = Cells(2, 1)
Cells(myl, 2) = Cells(2, 2)
Cells(myl, 3) = Cells(2, 3)
Cells(myl, 4) = Cells(2, 4)
Cells(myl, 5) = Cells(2, 5)
Cells(myl, 6) = Cells(2, 6)
myl = myl + 1

Next six
Next fif
Next four
Next thd
Next sec
Next top
'
End Sub

お礼

ご回答ありがとうございます。
早速試してみました。完璧ですね(^^)

数値をいじって、20C6の場合も試してみましたが、エクセルが途中でオーバーフロー(?)で止まってしまいました(^_^;
処理時間も数分(PCの能力によりますが)かかってしまうのですが、VBAで自動に処理させようとすると、やはりこのようにある程度の時間がかかってしまうのでしょうか。

関数でやるか、VBAでやるか迷っていますが、その他にも何かよいアイディアがありましたら、アドバイスをお願い致します。

よろしくお願いします。

回答No.3

sige1701

>この記述方法で合っているのか分からないので、
>念のために確認をお願い致します。
それで良いですよ
実際に試してみれば分かりますよ

お礼

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

実際に試してみましたが、膨大な組み合わせのため、本当にこれで合っているのか不安でした(^_^;

関数で実現させてしまうとは、天才的だと思ってしまいました。
法則性はあると何となく分かっていましたが、私には見出せませんでした。

的確なアドバイスに心より感謝致します。
ありがとうございました。

回答No.2

noname#192382

5C2の場合、a1からe1の間にデータが入っているとし、組み合わせの結果を3行目以降に入れることにすると、下記のマクロで答えが得られました。これを発展させればご希望のものができると思います。
Sub Macro1()
'
' Macro1 Macro
' マクロ記録日 : 2008/2/26 ユーザー名 :
'
Dim top As Integer, sec As Integer, thd As Integer, myl As Integer, t1 As Integer, s1 As Integer

myl = 3
For top = 1 To 3
Cells(2, 1) = Cells(1, top)
t1 = top + 1
For sec = t1 To 4
Cells(2, 2) = Cells(1, sec)
s1 = sec + 1
For thd = s1 To 5
Cells(2, 3) = Cells(1, thd)
Cells(myl, 1) = Cells(2, 1)
Cells(myl, 2) = Cells(2, 2)
Cells(myl, 3) = Cells(2, 3)
myl = myl + 1
Next thd
Next sec
Next top

'
End Sub

お礼

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

私には、ここから15C6まで発展させれる事ができませんでした。
と言うより、5C2の記述でさえ、意味不明な箇所が数カ所あるので、どのようにいじってよいのか検討も付きませんでした。

もう少しご教授頂けると助かります。

お手数をおかけしますが、よろしくお願いします。