プロが教えるわが家の防犯対策術!

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

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

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

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

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

A 回答 (4件)

こんばんは!


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
「Excelマクロでのデータ全通り組み合わ」の回答画像4
    • good
    • 0
この回答へのお礼

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

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

No1.です。



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

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


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

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
「Excelマクロでのデータ全通り組み合わ」の回答画像3
    • good
    • 0
この回答へのお礼

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

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

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


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

'//
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
    • good
    • 0
この回答へのお礼

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

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

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


また Excel のバージョンにもよると思いますが
少数なら (汗)  For ~ Next 文で簡単にできます。
こんな感じです.

  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 文を入れ子にするには限度があります。
残念ながら私はその限界数をしりませんので正確なことを言えませんが、
もし因子数が何十何百とあるなら、この方法はつかえません。
その場合は、少しだけ理解しにくい別の手を使います。
    • good
    • 0
この回答へのお礼

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

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

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています