Excel VBA の構文を教えてください（2）

質問

仕様の説明が上手くできてないので、再度、新規に投稿します。
Excel VBAにて（添付映像）
① I列に区分け条件があります
② 区分けされた「行」にそれぞれの数値があります
③ 行毎の集計を　CP列からEC列の2行/5行/6行に合計したい
④ JA ～JD に条件別に合計をしてみました
⑤ ④の値をCP2/CP5/CP6に記載
⑥　①②③までを、ダイレクト集計可能なら④以降はいらない

I列の条件区分をJA2に集計するところまでは、何とかたどりつきました。

Dim i As Long
For i = 1 To 4

Range("IZ2").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("CP:CP"))
Next i

上記のRange（”IZ2”）と　Range("CP:CP")を1文ずつ作成すれば
出来そうですが、もう少しシンプルな内容の構成にしたい
For～Next の二重ループで可能でしょうか？
ヒントがあれば、ご教授願います。

回答No.5

kkkkkm · ベストアンサー

元のコードを尊重してOffsetを利用したもので、DS列以降で飛びの間隔が変わっているので2個の多重ループになったのですが、以下で試してみてください。3行目と4行目以外に飛んでいる行は無いと考えてます。

Sub Test()
Dim i As Long, j As Long, k As Long
Dim FRow As Long

FRow = 2
k = 0
For j = 0 To 21
If k Mod 3 = 0 Then
k = k + 1
End If
If j <> 3 - FRow And j <> 4 - FRow Then
For i = 1 To 4
Range("IZ2").Offset(j, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("CP:CP").Offset(, k - 1))
Next i
Range("CP2").Offset(, k - 1) = WorksheetFunction.Sum(Range("JA2:JD2").Offset(j, 0))
Range("CP5").Offset(, k - 1) = WorksheetFunction.Sum(Range("JA2:JB2").Offset(j, 0))
Range("CP6").Offset(, k - 1) = WorksheetFunction.Sum(Range("JA2:JC2").Offset(j, 0))
k = k + 1
End If
Next j

For k = 0 To 9
If k <> 2 And k <> 3 And _
k <> 6 And k <> 7 Then
For i = 1 To 4
Range("IZ2").Offset(j, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DS:DS").Offset(, k))
Next i
Range("CP2").Offset(, k + 29) = WorksheetFunction.Sum(Range("JA2:JD2").Offset(j, 0))
Range("CP5").Offset(, k + 29) = WorksheetFunction.Sum(Range("JA2:JB2").Offset(j, 0))
Range("CP6").Offset(, k + 29) = WorksheetFunction.Sum(Range("JA2:JC2").Offset(j, 0))
j = j + 1
End If
Next k
End Sub

お礼

kkkkkm さん
ありがとうございました。指定したセルに、求めてたい値が出力されました。（検算もして、問題ありませんでした。）私のプログラムは、約250行ぐらいありましたが、kkkkkmさんのプログラムは、僅か30行程度。For文、IF文でシンプルです。私には、まだ、For と If のところが、何故、このように考えることが出来るのか、理解ができませんので、勉強します。
私の作成した、プログラムを見れば、私の初心者レベルが判ると思います。ご迷惑でなければ、私のレベルの者が、参考にすべき参考書があれば、教えて下さい。よろしくお願いします。

回答No.4

HohoPapa

>ダイレクトに 集計できれば必要ありません。
その『ダイレクトに 集計』しているツモリです。

繰り返しますが
私のコードでは不足なところ、過剰なところ、間違っているところを指摘してほしいのです。

回答No.3

HohoPapa

I18:Ixxと、CP18:EQxxの値を使って集計し
CP2:EQ2、CP5:EQ6に集計結果を埋める。
（xxはデータの末尾の行番号）
これが求めている仕様と解しましたが、違いますでしょうか？

そもそも、JA2:JD19を埋める必要があるんですか？

ソースコードを示す前に、
私のコードでは不足なところ、過剰なところ、間違っているところを指摘してほしいのです。
ぶっちゃけ、
> Range("IZ2").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", >"D", "E*○", "E*△"), Range("CP:CP"))
> Range("IZ5").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", >"D", "E*○", "E*△"), Range("CQ:CQ"))
といった記述を異常なほど羅列されても参考にはなりません。

>IZ２,IZ5,IZ6,IZ7～IZ29に条件選別した数値を入れる
IZ2,IZ5,IZ6、IZ7～IZ29は、sample画面を見る限り、
計算結果を埋めているようには思えません。
いかがでしょうか？

>途中抜けている数値は、使用しない（CR列、CU列、CX列など）
これは、益率の列は、マクロの処理対象にはしないという意味と思います。
ならば、
以下の6行は、コメントアウトしてください。
　　Else
　　　If ((Cells(2, ColCnt - 1).Value <> 0) And (Cells(2, ColCnt - 2).Value <> 0)) Then
　　　　Cells(2, ColCnt).Value = Cells(2, ColCnt - 1).Value / Cells(2, ColCnt - 2).Value
　　　　Cells(5, ColCnt).Value = Cells(5, ColCnt - 1).Value / Cells(5, ColCnt - 2).Value
　　　　Cells(6, ColCnt).Value = Cells(6, ColCnt - 1).Value / Cells(6, ColCnt - 2).Value
　　　End If

補足

HohoPaPaさん、ありがとうございます。仕様は、I列が分類要件（決～E*△まで）、CP18:EQxxの値を使って集計します。これが仕様で問い合わせ内容に間違いありません。
JA2:JD19は、上記の集計（決= xxx, D=xxx ...)を一旦集計していてCP2: EB6 を埋めて（コピー）しています。ダイレクトに 集計できれば必要ありません。
よろしくお願いします。

回答No.2

Ultra-Hetare

申し訳ないですが、
「① I列に区分け条件があります
② 区分けされた「行」にそれぞれの数値があります」

この時点で、区分けされた行のI列はどう評価さるるのか？
循環しないのか？という疑問を持ってしまいます。

失礼ですが、説明が拙すぎます。
前回の質問と併せてみても何がしたいのか見えてきません。
「エクセルファイルや入力ソースを全部もって、俺のところに
直接来い」と、言いたくなってしまいます。

回答No.1

HohoPapa

以下はいかがでしょうか。

Option Explicit

Sub sample()

　Dim GrName As Variant
　Dim GrToTal(4)　As Long
　Dim LastRow As Long
　Dim LastCol As Long
　Dim ColCnt As Long
　Dim i As Long

　GrName = Array("", "決", "D", "E*○", "E*△")

　'最終列番号取得
　LastCol = Cells(16, Columns.Count).End(xlToLeft).Column

　For ColCnt = 94 To LastCol

　　'最終行番号取得
　　LastRow = Cells(Rows.Count, ColCnt).End(xlUp).Row

　　If ((ColCnt Mod 3 = 1) Or (ColCnt Mod 3 = 2)) Then
　　　'要素別合計算出
　　　For i = 1 To 4
　　　　GrToTal(i) = WorksheetFunction.SumIf(Range(Cells(18, 9), _
　　　　　Cells(LastRow, 9)), GrName(i), _
　　　　　Range(Cells(18, ColCnt), Cells(LastRow, ColCnt)))
　　　Next i

　　　'合計値を出力
　　　Cells(2, ColCnt).Value = GrToTal(1) + GrToTal(2) + GrToTal(3) + GrToTal(4)
　　　Cells(5, ColCnt).Value = GrToTal(1) + GrToTal(2)
　　　Cells(6, ColCnt).Value = GrToTal(1) + GrToTal(2) + GrToTal(4)
　　Else
　　　If ((Cells(2, ColCnt - 1).Value <> 0) And (Cells(2, ColCnt - 2).Value <> 0)) Then
　　　　Cells(2, ColCnt).Value = Cells(2, ColCnt - 1).Value / Cells(2, ColCnt - 2).Value
　　　　Cells(5, ColCnt).Value = Cells(5, ColCnt - 1).Value / Cells(5, ColCnt - 2).Value
　　　　Cells(6, ColCnt).Value = Cells(6, ColCnt - 1).Value / Cells(6, ColCnt - 2).Value
　　　End If
　　End If

　Next ColCnt

End Sub

補足

Sub 集計条件に一致した数値の合計()

’IZ２,IZ5,IZ6,IZ7～IZ29に条件選別した数値を入れる、途中抜けている数値は、使用しない（CR列、CU列、CX列など）

Dim i As Long

For i = 1 To 4

Range("IZ2").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("CP:CP"))
Range("IZ5").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("CQ:CQ"))

Range("IZ6").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("CS:CS"))
Range("IZ7").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("CT:CT"))

Range("IZ8").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("CV:CV"))
Range("IZ9").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("CW:CW"))

Range("IZ10").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("CY:CY"))
Range("IZ11").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("CZ:CZ"))

Range("IZ12").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DB:DB"))
Range("IZ13").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DC:DC"))

Range("IZ14").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DE:DE"))
Range("IZ15").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DF:DF"))

Range("IZ16").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DH:DH"))
Range("IZ17").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DI:DI"))

Range("IZ18").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DK:DK"))
Range("IZ19").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DL:DL"))

Range("IZ20").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DN:DN"))
Range("IZ21").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DO:DO"))

Range("IZ22").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DQ:DQ"))
Range("IZ23").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DR:DR"))

Range("IZ24").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DS:DS"))
Range("IZ25").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DT:DT"))

Range("IZ26").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DW:DW"))
Range("IZ27").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("DX:DX"))

Range("IZ28").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("EA:EA"))
Range("IZ29").Offset(, i) = WorksheetFunction.SumIf(Range("I:I"), Choose(i, "決", "D", "E*○", "E*△"), Range("EB:EB"))

Next i
End Sub

’これらで得た数値を　
'CP列～EB列に書き込む

Range("CP2") = WorksheetFunction.Sum(Range("JA2:JD2"))
Range("CP5") = WorksheetFunction.Sum(Range("JA2:JB2"))
Range("CP6") = WorksheetFunction.Sum(Range("JA2:JC2"))