Excelマクロでのデータ全通り組み合わせ出力方法

Excelマクロでのデータ全通り組み合わせ出力方法
VBA初心者です。以下の内容につき教えて頂けますと助かります。

添付ファイルの【因子/水準表】データの全組み合わせを【全通り組み合わせ表】に出力したいです。
以下にやりたいことを纏めます。

・【因子/水準表】データ(因子/水準)の全組み合わせを【全通り組み合わせ表】に出力したい。
・【因子/水準表】と【全通り組み合わせ表】は同一シート内で列を合わせたい。
・因子数、水準数は可変としたい。(添付ファイルでは因子数3、水準数の最大は4となっているが、この数が可変となるため、【全通り組み合わせ表】の開始セルも併せて可変となる)

以上、よろしくお願いいたします。

※添付画像が削除されました。

No.4 回答者： tom04 回答日時： 2010/06/21 21:49

こんばんは！

VBAでないので、参考にならなかったら無視してください。

↓の画像でA列～C列のデータをE列～G列に表示しています。

E2セルは
=IF(COUNTA($A$2:$A$100)*COUNTA($B$2:$B$100)*COUNTA($C$2:$C$100)<ROW(A1),"",INDEX($A$2:$A$100,INT((ROW(A1)-1+COUNTA($B$2:$B$100)*COUNTA($C$2:$C$100))/(COUNTA($B$2:$B$100)*COUNTA($C$2:$C$100)))))

F2セルは
=IF(E2="","",INDEX($B$2:$B$100,IF(MOD(INT((ROW(A1)-1)/COUNTA($C$2:$C$100))+1,COUNTA($B$2:$B$100))=0,COUNTA($B$2:$B$100),MOD(INT((ROW(A1)-1)/COUNTA($C$2:$C$100))+1,COUNTA($B$2:$B$100)))))

G2セルは
=IF(E2="","",INDEX($C$2:$C$100,IF(MOD(ROW(A1),COUNTA($C$2:$C$100))=0,COUNTA($C$2:$C$100),MOD(ROW(A1),COUNTA($C$2:$C$100)))))

という数式を入れ、E2～G2セルを範囲指定し、G2セルのフィルハンドルで下へずぃ～～～！っと
コピーすると画像のような感じになります。

以上、無理矢理って感じの方法ですが
最初に書いたように参考にならなかったら読み流してくださいね。m(__)m

この回答へのお礼

ご回答ありがとうございます！
なるほど関数でもいけるのですね。ご教授頂いた通りできました。
勉強になります。ありがとうございました。

お礼日時：2010/06/21 23:19

No.3 回答者： mimeu 回答日時： 2010/06/21 19:58

Ｎｏ１．です。

Ｆｏｒ ～ Ｎｅｘｔ 文は理解しやすくてよいのですが、因子の数が増減するとプログラムを書き換える必要があり、またその数がヤタラ多いと使えない欠点があります。
なので「理解しにくい」バージョンも作ってみましたので、紹介します。
これだと因子数や水準数が増減してもプログラムを書き換えずにこのままで使えます。
（どちらも最大限３２７６７件までですが、実用上問題ないでしょう）
添付図は実行結果です。

経験の浅い方には何をしてるのか一見して全然ワカランと思いますが、
実は　Ｆｏｒ ～ Ｎｅｘｔ 文と全く同じことをしています。
もしプログラムを書き込む方法をご存じなかったら、http://qa.jiji.com/qa5717655.html などをご覧ください。


' 仮に因子水準表のＡ列は水準１，水準２等の文字、Ｂ２セルは因子１の文字だと
' 仮置きします、違ってたら Const 行加算・列加算 を適宜訂正してください。
' また組合せ表は見出し行、見出し列なしにＡ１セルから
' いきなりデータを書き込んでいます。

Option Explicit
Option Base 1

Sub Try()
　　Const 行加算 = 2
　　Const 列加算 = 1
　　Dim 因子数行 As Integer, 因子数 As Integer, 水準値配列() As Integer, 因子数配列() As Integer
　　Dim 因子水準表 As Worksheet, 組合せ表 As Worksheet
　　Dim 行 As Long, 列 As Long
　　' 作業準備
　　Set 因子水準表 = Worksheets("水準表") ' 適当な名前に直してね
　　Set 組合せ表 = Worksheets("組合せ")　　 ' 適当な名前に直してね
　　因子数行 = 因子水準表.Range("A" & 因子水準表.Rows.Count).End(xlUp).Row
　　因子数 = 因子水準表.Range("B2").End(xlToRight).Column - 列加算
　　ReDim 水準値配列(因子数)
　　ReDim 因子数配列(因子数)
　　For 列 = 1 To 因子数
　　　　因子数配列(列) = 因子水準表.Cells(因子数行, 列 + 列加算)
　　Next 列
　　組合せ表.UsedRange.Clear
　　行 = 1
　　列 = 1
　　Application.ScreenUpdating = False
　　
組合せ表出力:
　　水準値配列(列) = 水準値配列(列) + 1
　　If 水準値配列(列) > 因子数配列(列) Then
　　　　If 列 = 1 Then
　　　　　　Application.ScreenUpdating = True
　　　　　　End
　　　　End If
　　　　水準値配列(列) = 0
　　　　列 = 列 - 1
　　　　GoTo 組合せ表出力
　　End If
　　If 列 < 因子数 Then
　　　　列 = 列 + 1
　　　　GoTo 組合せ表出力
　　End If
　　For 列 = 1 To 因子数
　　　　組合せ表.Cells(行, 列) = 因子水準表.Cells(水準値配列(列) + 行加算, 列 + 列加算)
　　Next 列
　　列 = 因子数
　　行 = 行 + 1
　　GoTo 組合せ表出力
End Sub

この回答へのお礼

ご回答ありがとうございます！
現在ご教授頂いた内容を勉強中です。後日再コメントさせて頂きます！

お礼日時：2010/06/21 23:26

No.2 回答者： Wendy02 回答日時： 2010/06/21 11:53

既に回答が出ていますが、私の書き方で書いてみました。

解釈というよりも、味付けだけの違いで、考え方は同じです。

'//
Sub CombinationMaking()
　Dim x As Long, y As Long, z As Long
　Dim a As Range, b As Range, c As Range
　
　j = 10 '書き出し行
　'因子に空白行を入れないでください。
　Set a = Range("B2", Range("B2").End(xlDown))
　Set b = Range("C2", Range("C2").End(xlDown))
　Set c = Range("D2", Range("D2").End(xlDown))
　Range(Cells(j, 2), Cells(j, 4).End(xlDown)).Resize(, 3).ClearContents
　Application.ScreenUpdating = False
　For x = 1 To a.Count
　　For y = 1 To b.Count
　　　For z = 1 To c.Count
　　　　Cells(j, 2).Value = a.Cells(x, 1).Value
　　　　Cells(j, 3).Value = b.Cells(y, 1).Value
　　　　Cells(j, 4).Value = c.Cells(z, 1).Value
　　　　j = j + 1
　　　Next
　　Next
　Next
　Application.ScreenUpdating = True
End Sub

この回答へのお礼

ご回答ありがとうございます！
No1の方のご回答と合わせて理解することができました。
次は因子数を自動的に判定し、全通りの組み合わせができるよう、
自分なりに検討してみます。ありがとうございました。

お礼日時：2010/06/21 23:17

No.1 回答者： mimeu 回答日時： 2010/06/21 11:25

因子の数がいくらくらいかによりますが、

また Excel のバージョンにもよると思いますが
少数なら （汗）　　Ｆｏｒ ～ Ｎｅｘｔ 文で簡単にできます。
こんな感じです.

　　For 因子1の水準値 = 1 To 因子1の水準数
　　　　For 因子2の水準値 = 1 To 因子2の水準数
　　　　　　For 因子3の水準値 = 1 To 因子3の水準数
　　　　　　　　～～～～ 以下同形式の繰り返し ～～～～
　　　　　　　　　　行 = 行 + 1
　　　　　　　　　　出力シート.Cells(行,1) = 因子水準表.Cells(因子1の水準値+頭位置,1)
　　　　　　　　　　出力シート.Cells(行,2) = 因子水準表.Cells(因子2の水準値+頭位置,2)
　　　　　　　　　　出力シート.Cells(行,3) = 因子水準表.Cells(因子2の水準値+頭位置,3)
　　　　　　　　　　～～～～ 以下同形式の繰り返し ～～～～
　　　　　　　　Next
　　　　　　～～～～ 以下同形式の繰り返し ～～～～
　　　　Next
　　Next


ただし For ～ Next 文を入れ子にするには限度があります。
残念ながら私はその限界数をしりませんので正確なことを言えませんが、
もし因子数が何十何百とあるなら、この方法はつかえません。
その場合は、少しだけ理解しにくい別の手を使います。

この回答へのお礼

ご回答ありがとうございます！
VBA初心者ですがご丁寧な説明で内容を理解することができました。
2点目のご回答も自分なりに勉強してみます！

お礼日時：2010/06/21 23:14