EXCELマクロを使い、空白行ではさまれた複数列の範囲の数字を合計

質問

NO.2280663で質問した者です。
http://oshiete1.goo.ne.jp/kotaeru.php3?q=2280663
２．のsassakunさんの回答で思っていた通りの結果が得られました。
この例では、データ列は、B列だけですが、
これを発展させ、複数列（M列まで）ある表にそれぞれの列で
マクロで空白行にコード毎の計と最後に合計を入れる方法を教えてください。
配列変数の使い方がよくわかりません。

回答No.7

zap35 · ベストアンサー

#06です。大サービスでヒントをさし上げますからがんばって下さい。マクロの勉強をするつもりがないなら、この先も「既製服に体を合わせる」しかないでしょうｗ。

数値をカンマ編集するには　
　対象.NumberFormatLocal = "#,##0"
を追加すればよいです。なお、それだけだと以前集計が入ったセルの書式が残りますから、それがいやなら
　.ClearContents
は
　.Clear
にしたほうがよいでしょう

集計セルの位置を変える方法は小計挿入、合計挿入でそれぞれ１回ずつ使用しているFOR分の意味を考えるとすぐに分かると思います。

このマクロはあるセルを基準として別のセルを指定するOffsetを多用していますが、その意味が分かれば難しいアルゴリズムではありませんよ。がんばって読んでくださいね。

お礼

よく読んでみて、わかりました。
結果はうまくいきました。ありがとうございました。

回答No.6

zap35

＃０１です。補足ありがとうございました。たぶんこのマクロでいけるでしょう。

次回ご質問されるときは、曖昧さがないように最初から具体的な例を提示してくださいね。お互いに二度手間、三度手間になってしまいますから…

Option Explicit
Sub Macro2()
Dim Target, Rng, OfRng, StRng As Range, Adr As String
Dim LastC, LastR, idxC, idxR As Long
On Error Resume Next
Application.ScreenUpdating = False
'初期化、最終セル位置確認
LastR = Range("a65536").End(xlUp).Row + 1
LastC = Range("IV1").End(xlToLeft).Column
Set Target = Nothing
Set Target = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 23)
If Target.Cells.Count > 0 Then
Target.ClearContents
End If
For idxR = 2 To LastR
If Cells(idxR, 1) = "計" Or Cells(idxR, 1) = "合計" Then
Cells(idxR, 1).ClearContents
End If
Next idxR
LastR = Range("a65536").End(xlUp).Row + 1
'小計挿入
For idxR = LastR To 2 Step -1
Set Rng = Cells(idxR, 1)
With Rng
.Value = "計"
Set OfRng = .Offset(-1, 0)
Set StRng = OfRng.End(xlUp)
For idxC = 2 To LastC
If OfRng.Offset(-1, 0) <> "" Then
Adr = Range(StRng.Offset(0, idxC - 1), _
OfRng.Offset(0, idxC - 1)).Address
Rng.Offset(0, idxC - 1).Formula = "=subtotal(9," & Adr & ")"
Else
Rng.Offset(0, idxC - 1).Formula = "=subtotal(9," & _
OfRng.Offset(0, idxC - 1).Address & ")"
End If
Next idxC
idxR = .End(xlUp).Row
End With
Next idxR
'合計挿入
LastR = Range("A65536").End(xlUp).Row + 1
Cells(LastR, 1) = "合計"
For idxC = 2 To LastC
Cells(LastR, idxC).Formula = "=subtotal(9," & Cells(2, idxC).Address & ":" _
& Cells(LastR - 1, idxC).Address & ")"
Next idxC
Application.ScreenUpdating = True
End Sub

お礼

返事がおそくなりました。丁寧なご回答ありがとうございました。うまくできました！！
ここで要望が３点あります。
１．例では、２桁の数字を入れていました。これを千円単位の数字に置き換えて実行しますと、空白セルの下の計と合計欄がカンマ区切りになりません。計、合計すべてカンマ区切りにする。
２．例では、コード番号がA列でした。A,B列に他の項目が入り、コード番号がC列、データがD列からJ列までのデータにする。
３．注釈を「小計挿入」のように付けていただいていますが、他の行でも、もう少し詳しい注釈をつけて下さい。

回答No.5

NCU

質問です。

なぜ前回の質問は閉じてしまったのでしょうか？
提供されたコードに配列は使われていませんが、なぜ唐突に「配列変数の使い方がよくわかりません。」という発言が出てくるのでしょうか？

提供されたコードの使用に際しては、以下ご注意を。
・変数は型を宣言してから使う事
・Excel2007以降誰も使わなくなる事がわかっているようなコーディングをしない事

なお、「複数列（M列まで）ある表にそれぞれの列でマクロで空白行にコード毎の計と最後に合計を入れる」事がなぜ難しいのか？（大した違いはない筈なのに）
もしかすると、提供されたコードを全く理解しないまま使おうとされているのではないか？
という疑問にも、よろしければお答え下さい。

お礼

ご回答ありがとうございます。
具体例は、NO.1の補足をご参照下さい。

前回の質問では、１列の計ができたので、解決したと思い、質問を閉じました。
その後、複数列の疑問が生じ新たに質問し直した訳です。
複数列の場合は、配列変数を使うのかなと思ったからです。

回答No.4

Wendy02

前回書いた、Wendy02です。

前回の件で、そのまま締めてしまったようですが、前回のマクロで、SpecialCellsで、空白とデータを分けて、その空白セルに Subtotal関数を入れるように設計しました。しかし、実際には、SpecialCellsで、空白が取れないデータは、もともと、本来の空白がなかったとしか思えません。

私も他の方も書いたように、「集計」を使うほうが楽だと思います。それを記録マクロにしてもよいのではないでしょうか？

なぜ、マクロでなければならないのか理由が分かりませんし、また、データの空白値やデータの並び自体に信頼がおけないデータの場合は、最初、空白セルを追い出して、ベタの生データから、データ分類をしたほうが安全のようです。

私は、変数に加算方式で足し算するような方法は、極力さけるのは、大量になると、それはものすごく遅いことになるからです。

＞配列変数の使い方がよくわかりません。
なぜ、配列変数が出てくるのでしょうか？

お礼

ご回答ありがとうございます。
具体例は、NO.1の補足をご参照下さい。
前回の質問では、１列のみだったので、
今回のように複数列の場合は、配列変数を使うのかなと
思ったからです。

回答No.3

zap35

＃０１です。補足回答が待てなかったのでＢ列～任意の列全てに金額があると見なして小計と合計を入れるサンプルマクロを作ってみました。列毎に空白セルはバラバラの行にあっても構いません。また空白行が２行以上連続してもよいです。

なお上記仕様だと「小計」「合計」は入れるところがなくなりますので小計：青文字、合計：赤文字にしました。

もし「１行目に金額」とある行だけが集計対象」のような条件がある場合はif文を追加すればいけると思います。修正してみてください。

Option Explicit
Sub Macro1()
Dim Target, Rng, Ofrng As Range, Adr As String
Dim LastC, LastR, idxC, idxR As Long
　On Error Resume Next
　Application.ScreenUpdating = False
'初期化、最終セル位置確認
　Set Target = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
　Range(Cells(1, 1), Target).Font.ColorIndex = 0
　LastR = Target.Row + 1
　LastC = Target.Column
　Set Target = Nothing
　Set Target = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 23)
　If Target.Cells.Count > 0 Then
　　Target.ClearContents
　End If
'小計挿入
　For idxC = 2 To LastC
　　For idxR = LastR To 2 Step -1
　　　Set Rng = Cells(idxR, idxC)
　　　With Rng
　　　　Set Ofrng = .Offset(-1, 0)
　　　　Adr = Ofrng.Address
　　　　If Ofrng <> "" Then
　　　　　If IsNumeric(Ofrng) Then
　　　　　　If Ofrng.Offset(-1, 0) <> "" Then
　　　　　　　Adr = Range(Ofrng, Ofrng.End(xlUp)).Address
　　　　　　　idxR = Range(Ofrng, Ofrng.End(xlUp)).Row
　　　　　　Else
　　　　　　　Adr = Range(Ofrng).Address
　　　　　　End If
　　　　　　Rng.Formula = "=subtotal(9," & Adr & ")"
　　　　　　Rng.Font.ColorIndex = 5
　　　　　End If
　　　　End If
　　　End With
　　Next idxR
　Next idxC
'合計挿入
　For idxC = 2 To LastC
　　Set Target = Cells(65536, idxC).End(xlUp)
　　Adr = Target.Address
　　Set Target = Target.Offset(1, 0)
　　Target.Formula = "=subtotal(9," & Cells(1, idxC).Address & ":" & Adr & ")"
　　Target.Font.ColorIndex = 3
　Next idxC
　Application.ScreenUpdating = True
End Sub

お礼

ご回答ありがとうございます。
具体例は、NO.1の補足をご参照下さい。
データに空白があった場合、そこで集計されてしまいます。空白データを0に置き換えると、うまく、計欄に合計数字が出ます。
空白データを自動的に0に置き換える方法はありますか？

回答No.2

onlyrom

No.1さんに加えて、
前の質問のコードで思ったとおりの結果が得られたということは、

●一行目には「見出し行」はない

●一回実行後、データの削除修正追加があった場合は、いちいち、”計”、"合計"の欄をクリアーしてから再計算

これ、非常に使いにくいのでは？

普通は一行目ではなくても「見出し行」があり、データはその次の行から入力され、
また、一回実行後、データの削除修正追加があっても、いちいち、"計","合計"の欄をクリアーしないでそのまま再計算するのが普通だと思うがどうか。

お礼

ご回答ありがとうございます。
具体例は、NO.1の補足をご参照下さい。

回答No.1

zap35

補足要求です
前回の質問はＡ列がコード、Ｂ列が金額でＢ列の小計と合計を求めるものでした。

１）Ｍ列までデータがある場合、集計するべき列であることはどのようにすれば分かるのでしょうか。（Ｂ～Ｍは全て集計対象ですか？）

２）また各列のデータで空白行は全て一緒ですか？
（例えばＢ列はＢ４が空白なら、Ｃ４～Ｍ４も同じですか？　それとも列毎に空白の行は変化しますか？）

３）「計」という文字は今回も入れるのですか？
　その場合どの位置に入れれば良いですか？

上記の条件を教えていただかないと、適当にしかマクロを組めません

補足

返事が遅くなり、申し訳ありません。
1)Ｂ～Ｍは全て集計対象です。
2)列毎に空白の行は変化します。
3)「計」という文字は今回も入れます。
　Ｂ列のcodeの変わり目です。
（下記で１行目は列、１列目は行数を表しています）

実行前
------A------B------C-----D
1--code--data1--data2--data3
2-----1-----10-----40-----20
3-----1-----20------------30
4-----1-----50-----20-----50
5
6-----2-----40-----10
7-----2-----60-----20-----90
8-----2-----30------------80
9
10----3-----50-----40-----70
11----3-----10------------50
12----3-----30-----60

実行後
------A------B------C-----D
1--code--data1--data2--data3
2-----1-----10-----40-----20
3-----1-----20------------30
4-----1-----50-----20-----50
5----計-----80-----60----100
6-----2-----40-----10
7-----2-----60-----20-----90
8-----2-----30------------80
9----計----130-----30----170
10----3-----50-----40-----70
11----3-----10------------50
12----3-----30-----60
13---計-----90----100----120
14-合計----300----190----390