EXCEL VBA データ集計・選択集計・消費税自動計算（指定箇所での小計・中計・合計）（テクニック）

EXCEL VBA データ集計・選択集計（指定箇所での小計・中計・合計）（テクニック）

●はじめに

EXCELで一覧表や集計表を作成して、登録されている数値データを集計する事があると思いますが、通常の場合はある項目毎や月毎など、決まったグループ毎に集計すると思います。今回紹介する方法は、自分で指定する箇所に小計・中計・合計を指定して合計値を集計する方法です。自分で集計場所を指定する事で使い方が幅広くなると思います。なお、これから紹介するプログラムは、データの変更や行追加によるデータを追加しても、再集計出来る仕様になっていますので、様々な集計処理に利用する事が出来ると思います。

EXCEL VBA データ集計・選択範囲での集計（説明）

●プログラム説明　（サンプルプログラム：データの集計・選択範囲での集計）

下記のサンプルプログラムは、EXCELのデータに対してユーザーが指定した選択範囲で集計を行うサンプルプログラムです。ある条件による集計では無く、自由に集計箇所を選択して集計する事ができます。
なお、今回のプログラムは、集計区分として「小計」・「中計」・「合計」３つの集計区分を用意してユーザーがどの範囲で集計するのかを自在に指定する事ができます。
詳しくは、下図を参照して下さい。

※注意：下記のプログラムは、シート名を指定しているので、必ず「月次データ”」を入力、かつデータの最終行判断として、集計区分の「合計」列の最終列に「●」を入力して下さい。

Sub 指定合計計算()

    Dim ws01 As Worksheet
    Dim I, S_Sum, M_Sum, L_Sum, mRow As Long
    
    Set ws01 = Worksheets("月次データ")  'シート名「月次データ」を指定


    mRow = ws01.Cells(Rows.Count, "E").End(xlUp).Row 'E列の最終行取得（合計）
    
    ws01.Range("A5:J" & mRow).Borders.Weight = xlHairline '中細線

    ws01.Range("A5:J" & mRow).BorderAround Weight:=xlThin  '外側細線

    
    S_Sum = 5  '5行名からカウント開始（小計）
    M_Sum = 5  '5行名からカウント開始（中計）
    L_Sum = 5  '5行名からカウント開始（合計）
    
    For I = 5 To mRow
    

        '---------------------小計（設定）
        If ws01.Cells(I, "C") = "●" Then
                ws01.Cells(I, "G") = "=SUBTOTAL(9,G" & S_Sum & ":G" & I - 1 & ")"
                ws01.Cells(I, "H") = "=SUBTOTAL(9,H" & S_Sum & ":H" & I - 1 & ")"
                ws01.Cells(I, "I") = "=SUBTOTAL(9,I" & S_Sum & ":I" & I - 1 & ")"
                             
                ws01.Range("A" & I & ":J" & I).BorderAround Weight:=xlThin  '外側細線
                ws01.Cells(I, "F") = "小計"
            S_Sum = I + 1  '小計カウント＋１
        End If
        '--------------------中計（設定）
        If ws01.Cells(I, "D") = "●" Then
                ws01.Cells(I, "G") = "=SUBTOTAL(9,G" & M_Sum & ":G" & I - 1 & ")"
                ws01.Cells(I, "H") = "=SUBTOTAL(9,H" & M_Sum & ":H" & I - 1 & ")"
                ws01.Cells(I, "I") = "=SUBTOTAL(9,I" & M_Sum & ":I" & I - 1 & ")"
            
                ws01.Range("A" & I & ":J" & I).BorderAround Weight:=xlThin  '外側細線
                ws01.Cells(I, "F") = "中計"
            M_Sum = I + 1  '小計カウント＋１
        End If
            
        '-------------------合計（設定）
        If ws01.Cells(I, "E") = "●" Then
                ws01.Cells(I, "G") = "=SUBTOTAL(9,G" & L_Sum & ":G" & I - 1 & ")"
                ws01.Cells(I, "H") = "=SUBTOTAL(9,H" & L_Sum & ":H" & I - 1 & ")"
                ws01.Cells(I, "I") = "=SUBTOTAL(9,I" & L_Sum & ":I" & I - 1 & ")"
                
                ws01.Range("A" & I & ":J" & I).BorderAround Weight:=xlThin  '外側細線
                ws01.Range("A" & mRow & ":J" & mRow).Borders(xlEdgeTop).LineStyle = xlDouble   '二重合計線
                ws01.Cells(I, "F") = "合計"
            L_Sum = I + 1  '合計カウント＋１
                
        End If
        
    
    Next I
    '--------------------全ての行間（セル幅）の設定
    ws01.Rows("6:" & mRow).RowHeight = 16 '行16サイズに設定
    

End Sub

●実行前～実行後　　※プログラム実行後　事前に指定した集計区分により、小計・中計・合計の計算が実行されました。

EXCEL VBA データ集計・税区分を指定して消費税の自動計算

●プログラム説明　（サンプルプログラム：税区分を指定して消費税の自動計算）

下記のサンプルは、上記プログラムの続きとなるプログラムになりますが、今回のプログラムについては、「税区分」を指定して税区分により、消費税の計算を行うサンプルプログラムです。税区分に「０」・・非課税　「８」・・消費税8%　「10」・・消費税10％　を指定する事で入力した「税区分」ごとに消費税の計算を行います。
詳しくは、下図を参照して下さい。
※注意：下記のプログラムは、シート名を指定しているので、必ず「月次データ”」を入力、かつデータの最終行判断として、集計区分の「合計」列の最終列に「●」を入力します。

 Sub 消費税計算()

    Dim ws01 As Worksheet
    Dim I, mRow As Long
    
    Set ws01 = Worksheets("月次データ")

    mRow = ws01.Cells(Rows.Count, "E").End(xlUp).Row 'E列最終行

    For I = 5 To mRow  '最終行まで繰り返す。
         
        '-------------------------------　請求額
        If ws01.Cells(I, "J") = "非" Then
        
                ws01.Cells(I, "M") = 0 '非課税
                ws01.Cells(I, "N") = ws01.Cells(I, "L") '合計（非課税）
         
         ElseIf ws01.Cells(I, "J") = "8" Then
                
                On Error Resume Next  'エラーを回避
                ws01.Cells(I, "M") = Int(ws01.Cells(I, "L") * 0.08)  '消費税(8%)
                ws01.Cells(I, "N") = ws01.Cells(I, "L") + ws01.Cells(I, "M") '合計
                
         Else
                On Error Resume Next  'エラーを回避
                ws01.Cells(I, "M") = Int(ws01.Cells(I, "L") * 0.1)  '消費税（10%）
                ws01.Cells(I, "N") = ws01.Cells(I, "L") + ws01.Cells(I, "M") '合計
        
        End If
    
    Next I
    
End Sub

●実行前～実行後　　※プログラム実行後　事前に指定した税区分により、消費税・合計の計算が実行されました。

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。

AKIRA