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

Question

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

zap35 · Accepted Answer

#06です。大サービスでヒントをさし上げますからがんばって下さい。マクロの勉強をするつもりがないなら、この先も「既製服に体を合わせる」しかないでしょうｗ。

数値をカンマ編集するには　
　対象.NumberFormatLocal = "#,##0"
を追加すればよいです。なお、それだけだと以前集計が入ったセルの書式が残りますから、それがいやなら
　.ClearContents
は
　.Clear
にしたほうがよいでしょう

集計セルの位置を変える方法は小計挿入、合計挿入でそれぞれ１回ずつ使用しているFOR分の意味を考えるとすぐに分かると思います。

このマクロはあるセルを基準として別のセルを指定するOffsetを多用していますが、その意味が分かれば難しいアルゴリズムではありませんよ。がんばって読んでくださいね。

zap35 · Answer

＃０１です。補足ありがとうございました。たぶんこのマクロでいけるでしょう。

次回ご質問されるときは、曖昧さがないように最初から具体的な例を提示してくださいね。お互いに二度手間、三度手間になってしまいますから…

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

NCU · Answer

質問です。

なぜ前回の質問は閉じてしまったのでしょうか？
提供されたコードに配列は使われていませんが、なぜ唐突に「配列変数の使い方がよくわかりません。」という発言が出てくるのでしょうか？

提供されたコードの使用に際しては、以下ご注意を。
・変数は型を宣言してから使う事
・Excel2007以降誰も使わなくなる事がわかっているようなコーディングをしない事

なお、「複数列（M列まで）ある表にそれぞれの列でマクロで空白行にコード毎の計と最後に合計を入れる」事がなぜ難しいのか？（大した違いはない筈なのに）
もしかすると、提供されたコードを全く理解しないまま使おうとされているのではないか？
という疑問にも、よろしければお答え下さい。

Wendy02 · Answer

前回書いた、Wendy02です。

前回の件で、そのまま締めてしまったようですが、前回のマクロで、SpecialCellsで、空白とデータを分けて、その空白セルに Subtotal関数を入れるように設計しました。しかし、実際には、SpecialCellsで、空白が取れないデータは、もともと、本来の空白がなかったとしか思えません。

私も他の方も書いたように、「集計」を使うほうが楽だと思います。それを記録マクロにしてもよいのではないでしょうか？

なぜ、マクロでなければならないのか理由が分かりませんし、また、データの空白値やデータの並び自体に信頼がおけないデータの場合は、最初、空白セルを追い出して、ベタの生データから、データ分類をしたほうが安全のようです。

私は、変数に加算方式で足し算するような方法は、極力さけるのは、大量になると、それはものすごく遅いことになるからです。

＞配列変数の使い方がよくわかりません。
なぜ、配列変数が出てくるのでしょうか？

zap35 · Answer

＃０１です。補足回答が待てなかったのでＢ列～任意の列全てに金額があると見なして小計と合計を入れるサンプルマクロを作ってみました。列毎に空白セルはバラバラの行にあっても構いません。また空白行が２行以上連続してもよいです。なお上記仕様だと「小計」「合計」は入れるところがなくなりますので小計：青文字、合計：赤文字にしました。もし「１行目に金額」とある行だけが集計対象」のような条件がある場合は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

onlyrom · Answer

No.1さんに加えて、
前の質問のコードで思ったとおりの結果が得られたということは、

●一行目には「見出し行」はない

●一回実行後、データの削除修正追加があった場合は、いちいち、”計”、"合計"の欄をクリアーしてから再計算

これ、非常に使いにくいのでは？
　
普通は一行目ではなくても「見出し行」があり、データはその次の行から入力され、
また、一回実行後、データの削除修正追加があっても、いちいち、"計","合計"の欄をクリアーしないでそのまま再計算するのが普通だと思うがどうか。

zap35 · Answer

補足要求です
前回の質問はＡ列がコード、Ｂ列が金額でＢ列の小計と合計を求めるものでした。

１）Ｍ列までデータがある場合、集計するべき列であることはどのようにすれば分かるのでしょうか。（Ｂ～Ｍは全て集計対象ですか？）

２）また各列のデータで空白行は全て一緒ですか？
（例えばＢ列はＢ４が空白なら、Ｃ４～Ｍ４も同じですか？　それとも列毎に空白の行は変化しますか？）

３）「計」という文字は今回も入れるのですか？
　その場合どの位置に入れれば良いですか？

上記の条件を教えていただかないと、適当にしかマクロを組めません

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

#06です。

＃０１です。

質問です。

前回書いた、Wendy02です。

＃０１です。

No.1さんに加えて、

補足要求です

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング