エクセルVBAで、全ての組み合わせを表現する方法を教えてください
既にA列に色、B列に数字、C列にサイズが入力済みだとします
A B C
1 色 数字 サイズ ←タイトルの行
2 赤 1 S
3 青 2 M
4 緑 3 L
(完成例)
D E F
1 色 数字 サイズ ←タイトルの行
2 赤 1 S
3 赤 1 M
4 赤 1 L
5 赤 2 S
6 赤 2 M
7 赤 2 L
8 赤 3 S
9 赤 3 M
10 赤 3 L
11 青 1 S
12 青 1 M
13 青 1 L
14 青 2 S
15 青 2 M
16 青 2 L
17 青 3 S
18 青 3 M
19 青 3 L
20 緑 1 S
21 緑 1 M
22 緑 1 L
23 緑 2 S
24 緑 2 M
25 緑 2 L
26 緑 3 S
27 緑 3 M
28 緑 3 L
・全ての組み合わせが表現できていれば、2~28行目は上の完成例の順番でなくてもいいです
・完成はD~F列の上から(2行目から)結果を反映させ、空白行を作らないようにする
・今回はA~C列の4行まで入力されている例をあげましたが、実際はA~C列の何行まで入力されているか随時変更します
・A~C列のデータ入力は、必ず上から(2行目から)されています
・A~C列のデータ入力は、5行目以降に続くこともあります
・A~C列のデータ入力は、データがない場合もあります
(データ入力がない場合)
A B C
1 色 数字 サイズ ←タイトルの行
2 1 S
3 2 M
4 L
(この場合の完成例)
D E F
1 色 数字 サイズ ←タイトルの行
2 1 S
3 1 M
4 1 L
5 2 S
6 2 M
7 2 L
空白行が出来てしまってはいけないので、『まずデータ入力されている列を認識し、認識した列の情報で全ての組み合わせを作成する』という考え方なのかな?と思ったのですが、そのようなことをエクセルVBAでできるのでしょうか
もし分かる方がいたら教えてください
よろしくお願いします
A 回答 (7件)
- 最新から表示
- 回答順に表示
No.7
- 回答日時:
関数だと速いのかなあ なんて思いから ちょっとやってみました
Sub 並べるXL2003()
Dim n As Long
Dim n2 As Long
Dim n3 As Long
Dim n4 As Long
Range("D1").Formula = "=IF(A2="""","""",COUNTA(A:A)-1)"
Range("E1").Formula = "=IF(B2="""","""",COUNTA(B:B)-1)"
Range("F1").Formula = "=IF(C2="""","""",COUNTA(C:C)-1)"
Range("G1").Formula = "=PRODUCT(E1,F1)"
Range("H1").Formula = "=PRODUCT(D1:F1)"
If Range("H1") > 65535 * 4 Then Exit Sub
n = WorksheetFunction.Min(Range("H1") + 1, 65536)
Range("D2:D" & n).Formula = "=INDEX(A:A,2+(ROW()-2)/G$1)"
Range("D2:D" & n).Value = Range("D2:D" & n).Value
Range("E2:E" & n).Formula = "=INDEX(B:B,2+MOD((ROW()-2)/F$1,E$1))"
Range("E2:E" & n).Value = Range("E2:E" & n).Value
Range("F2:F" & n).Formula = "=INDEX(C:C,2+MOD(ROW()-2,F$1))"
Range("F2:F" & n).Value = Range("F2:F" & n).Value
If Range("H1") <= 65535 Then Exit Sub
n2 = WorksheetFunction.Min(Range("H1") - 65535 + 1, 65536)
Range("G2:G" & n2).Formula = "=INDEX(A:A,2+(ROW()-2+65535)/G$1)"
Range("G2:G" & n2).Value = Range("G2:G" & n2).Value
Range("H2:H" & n2).Formula = "=INDEX(B:B,2+MOD((ROW()-2+65535)/F$1,E$1))"
Range("H2:H" & n2).Value = Range("H2:H" & n2).Value
Range("I2:I" & n2).Formula = "=INDEX(C:C,2+MOD(ROW()-2+65535,F$1))"
Range("I2:I" & n2).Value = Range("I2:I" & n2).Value
If Range("H1") <= 65535 * 2 Then Exit Sub
n3 = WorksheetFunction.Min(Range("H1") - 65535 * 2 + 1, 65536)
Range("J2:J" & n3).Formula = "=INDEX(A:A,2+(ROW()-2+65535*2)/G$1)"
Range("J2:J" & n3).Value = Range("J2:J" & n2).Value
Range("K2:K" & n3).Formula = "=INDEX(B:B,2+MOD((ROW()-2+65535*2)/F$1,E$1))"
Range("K2:K" & n3).Value = Range("K2:K" & n2).Value
Range("L2:L" & n3).Formula = "=INDEX(C:C,2+MOD(ROW()-2+65535*2,F$1))"
Range("L2:L" & n3).Value = Range("L2:L" & n2).Value
n4 = WorksheetFunction.Min(Range("H1") - 65535 * 3 + 1, 65536)
Range("M2:M" & n4).Formula = "=INDEX(A:A,2+(ROW()-2+65535*3)/G$1)"
Range("M2:M" & n4).Value = Range("M2:M" & n2).Value
Range("N2:N" & n4).Formula = "=INDEX(B:B,2+MOD((ROW()-2+65535*3)/F$1,E$1))"
Range("N2:N" & n4).Value = Range("N2:N" & n2).Value
Range("O2:O" & n4).Formula = "=INDEX(C:C,2+MOD(ROW()-2+65535*3,F$1))"
Range("O2:O" & n4).Value = Range("O2:O" & n2).Value
End Sub
約25万件になるデータが11秒ほどでできました
遊びですがf(^^;
No.6
- 回答日時:
No.5です。
たびたびごめんなさい。
前回の
>No.1さんの補足に
は
>No.2さんの
の間違いです。ごめんなさい。
それと、前回のコードはA~C列の1行目からデータがある!という前提のコードでしたが
1行目は項目行になっていてデータは2行目からあるのですよね?
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
For j = 1 To Cells(Rows.Count, "B").End(xlUp).Row
For k = 1 To Cells(Rows.Count, "C").End(xlUp).Row
の3行の「1」をすべて「2」に変更してください。
どうも失礼しました。m(_ _)m
No.5
- 回答日時:
こんにちは!
横からお邪魔します。
No.1さんの補足に
>今のところ、最大で238,238の組み合わせが生じそうです
とありますので・・・
Sub Sample1()
Dim i As Long, j As Long, k As Long, cnt As Long, myRow As Long, myCol As Long, endCol As Long
endCol = ActiveSheet.UsedRange.Columns.Count
If endCol > 3 Then
Range(Columns(4), Columns(endCol)).ClearContents
End If
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
For j = 1 To Cells(Rows.Count, "B").End(xlUp).Row
For k = 1 To Cells(Rows.Count, "C").End(xlUp).Row
cnt = cnt + 1
If cnt Mod 65536 = 0 Then
myRow = 65536
myCol = Int(cnt / 65536) * 3 + 1
Else
myRow = cnt Mod 65536
myCol = (Int(cnt / 65536) + 1) * 3 + 1
End If
With Cells(myRow, myCol)
.Value = Cells(i, "A")
.Offset(, 1) = Cells(j, "B")
.Offset(, 2) = Cells(k, "C")
End With
Next k
Next j
Next i
MsgBox "処理完了"
End Sub
※ じっくり考えれば1セルずつ舐めるように表示させるより、一定範囲をコピー&ペーストすれば
もっと早くなると思いますが、65536行目がどこで終わるか?の判断が難しくなりますので、
単純にずらぁ~~~!っと並べています。
腕組みをしてじっくり画面とにらめっこしてください。m(_ _)m
No.4
- 回答日時:
んじゃまぁついでに。
sub macro1for2003()
dim c1 as long, c2 as long, c3 as long
dim r as long
dim w as worksheet
set w = activesheet
range("D:F").clearcontents
range("A1:C1").copy range("D1")
r = 2
for c1 = 2 to application.max(2, w.cells(rows.count, "A").end(xlup).row)
for c2 = 2 to application.max(2, w.cells(rows.count, "B").end(xlup).row)
for c3 = 2 to application.max(2, w.cells(rows.count, "C").end(xlup).row)
if r > 65536 then
worksheets.add after:=activesheet
w.range("A1:C1").copy range("D1")
r = 2
end if
cells(r, "D").value = w.cells(c1, "A").value
cells(r, "E").value = w.cells(c2, "B").value
cells(r, "F").value = w.cells(c3, "C").value
r = r + 1
next c3
next c2
next c1
end sub
言わずもがなですが、わざわざ65536までひっぱる必要は勿論ありません。
もっとも10万行を超える書き出しを逐一行ってると、随分とろくさいですけどね。
2003用までありがとうございます
このようなコードを作成したおことがないのですが、最終行までいくと、新しいシートが作成させるのでしょうか?
できれば一つのシートで完成させたいので、(1)エクセル2003では使用しない、(2)入力できるデータ数を制限する、で何とか1つのシートで完成しようと思います
本当に助かりました
No.3
- 回答日時:
>●=2は何を意味するのでしょうか?
ABC列に記入が無い場合の対処です。
>■で65536を使わずにできないかな?と思い
現実問題としてABC列に6万を超える「リストの元ネタ」を、本当に並べるつもりがあるのですか?(DEF列の事じゃ勿論ありませんよ)
もしマジメにそうする必要があってそうしたいと言うのでしたら、勿論そのように対処してください。
ふつーに考えると「A9999」とかでも全然十分だと思ってましたけどね。
sub macro1r1()
dim c1 as long, c2 as long, c3 as long
dim r as long
range("D:F").clearcontents
range("A1:C1").copy range("D1")
r = 2
for c1 = 2 to application.max(2, cells(rows.count, "A").end(xlup).row)
for c2 = 2 to application.max(2, cells(rows.count, "B").end(xlup).row)
for c3 = 2 to application.max(2, cells(rows.count, "C").end(xlup).row)
cells(r, "D").value = cells(c1, "A").value
cells(r, "E").value = cells(c2, "B").value
cells(r, "F").value = cells(c3, "C").value
r = r + 1
next c3
next c2
next c1
end sub
分かりやすい説明ありがとうございます
後になり、Cells(Rows.Count, 1).End(xlUp).Row
で解決すると気が付きました
ありがとうございます
No.2
- 回答日時:
基本的には No.1 さんの方法でいいと思いますが、シートの最大の行数に注意してください。
Excel 2003 以前で 65,536、Excel 2007 以後で 1,048,576 です。組み合わせの数は、爆発的に増加します。質問文の例だと色・数字・サイズという 3 属性が 3 種類ずつなので、組み合わせの数は 3^3 = 27 通りとなりますね。
しかし 41 種類ずつだと 41^3 = 68,921、102 種類ずつだと 102^3 = 1,061,208 となって、すぐに制限を超えてしまいます。他にも例えば、色・数字・サイズが 20・350・10 であれば 20 x 350 x 10 = 70,000、120・1,000・10 であれば 120 x 1,000 x 10 = 1,200,000 などとなり、やはりオーバーします。
ですから制限を超えそうな場合は、途中で列を変えて続きを記入していくなどの工夫が必要になります。コード中、For の行に記述している行番号の変数(No.1 さんのコードで言えば c1 あたり)の最大値を調節することなどによって、途中で無事に終われます。
そうしておかないと、最大の行数までの記入は実行されますが、その次の記入でマクロがエラーになります。
なおマクロの実行にかかる時間は、環境にもよりますが、100 万行を記入するなら何分かかかると思ってください。
>……、『まずデータ入力されている列を認識し、……と思ったのですが、そのようなことをエクセルVBAでできるのでしょうか
条件に関する必要な情報が十分に与えられれば、その手のことはいくらでも可能だと思います。しかし難しいことをしようとするほど、VBA に関する様々な知識が必要になっていきます。まずは基本の処理を習得し、それ以上のことは追々、学んでいってください。
回答ありがとうございます
今のところ、最大で238,238の組み合わせが生じそうです
既にエクセル2003では超えてしまいますね
また、データが今後増えていくことが予想できるので
エクセル2003で使用できるように、入力できるデータ数を制限するのも一つの手かなと考えています
ありがとうございました
No.1
- 回答日時:
奇をてらわずに、単純にぐるぐる廻してくだけで十分です。
sub macro1()
dim c1 as long, c2 as long, c3 as long
dim r as long
range("D:F").clearcontents
range("A1:C1").copy range("D1")
r = 2
for c1 = 2 to application.max(2, range("A65536").end(xlup).row)
for c2 = 2 to application.max(2, range("B65536").end(xlup).row)
for c3 = 2 to application.max(2, range("C65536").end(xlup).row)
cells(r, "D").value = cells(c1, "A").value
cells(r, "E").value = cells(c2, "B").value
cells(r, "F").value = cells(c3, "C").value
r = r + 1
next c3
next c2
next c1
end sub
早い回答ありがとうございます
意外と短く表現できるんですね!
助かりました
Application.Max(●, ■)で、最大行を取得しているんだと思うのですが、
●=2は何を意味するのでしょうか?
また、■で65536を使わずにできないかな?と思い
Cells(Rows.Count, 1).End(xlUp) としましたが、うまくいきませんね・・
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Windows 7 エクセルで重複データから抽出したい 2 2022/05/18 23:31
- 高校 数学A組み合わせの考え方 3 2022/04/19 09:05
- Access(アクセス) Accessのクエリの結果を、既存のエクセルに追加したい 2 2022/07/31 22:44
- Visual Basic(VBA) エクセルVBAについて 2 2023/01/31 16:21
- Visual Basic(VBA) 列の最終行までのセルと1つ隣のセルの合計を別の列に表示 2 2022/07/12 19:50
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 2 2022/06/25 22:42
- Visual Basic(VBA) vbaについて 主に以下のような設定をしたいです。 Aブックの表の行数が20未満だったら Bブックの 1 2023/06/08 23:40
- アニメ 幽遊白書・暗黒武術会「3位決定戦」が実施された場合? 1 2022/10/30 00:00
- Excel(エクセル) VBAで重複データを合算したい(時間) 1 2022/12/08 23:06
- Excel(エクセル) Excelにの以下の設定方法について教えてください! C列にデータ入力の設定をしています。(出、入を 3 2022/06/22 01:33
このQ&Aを見た人はこんなQ&Aも見ています
-
新NISA制度は今までと何が変わる?非課税枠の拡大や投資対象の変更などを解説!
少額から投資を行う人のための非課税制度であるNISAが、2024年に改正される。おすすめの銘柄や投資額の目安について教えてもらった。
-
Excelでの全通りの組み合わせ出力方法(文字列)
Excel(エクセル)
-
エクセルでnCr (組み合わせ)の作成方法
Excel(エクセル)
-
エクセルで重複しない組み合わせ出力方法
Excel(エクセル)
-
-
4
Excelですべての組合せ(重複組合せ)を出力するには?
Visual Basic(VBA)
-
5
Excelでデータ全通り組み合わせ出力方法
Excel(エクセル)
-
6
エクセル:マクロ「Application.CutCopyMode = False」って?
Excel(エクセル)
-
7
【Excel VBA】行×列1,列2,列3,列4の組み合わせで列挙する方法
Visual Basic(VBA)
-
8
エクセルVBAで、特定の数字になる組み合わせを知りたいのですが・・・
Excel(エクセル)
-
9
Excelマクロでのデータ全通り組み合わせ出力方法
Excel(エクセル)
-
10
Application.ScreenUpdating = Falseが効きません
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
別シートからの文字を変更
-
エクセルの行の抽出について質...
-
Excel 2019 のピボットテーブル...
-
Excelのセルを飛ばして入力する
-
【マクロ】エクセルにかいてあ...
-
Excelのオートフィル
-
Excel初心者です。 詳しい方、...
-
スプレッドシート クエリ関数 1...
-
MOS365 Excel Expert / Excel R...
-
西暦や和暦の表示をyyyymmdd表...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセルの数式で教えてください。
-
スプレッドシートの関数VLOOKUP...
-
エクセルでセルに「氏名を入力...
-
エクセルで指定した日付、店舗...
-
【Excel】セル内の時間帯が特定...
-
Excelのグラフ軸について
-
Excel 2019 は、SPILL機能があ...
-
関数を教えて下さい。
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報