Excel VBA 複数シートのデータを集計シートにまとめる

スポンサーリンク

複数のシートのデータを集計シートにまとめる方法

月ごとの売り上げデータを、月ごとにシートにわけて管理している場合や、店舗ごとにシートにわけて管理しているケースは多いと思います。

全体を集計して、全体を分析したい場合は、わかれているデータを一つのまとめる必要があります。

例として、各店舗のカテゴリ別月売上表が各シートにわかれています。これを集計シートに、まとめるコードをご紹介します。

各シートのフォーマットは統一されていることが前提です。

売上管理表


東京店、神奈川店、埼玉店、千葉店、茨城店

5店の売上データが店ごとにシートにわかれている場合を想定し、各店の売上データを追加作成した集計シートにまとめるコードを作成します。

実行結果


複数シートのデータを集計シートにまとめるコードと解説

Sub 集計シート作成()
Application.ScreenUpdating = False
 Dim i As Long
 Dim maxcol As Long
 Dim maxrow As Long
 For i = Sheets.Count To 1 Step -1
  If Application.WorksheetFunction.CountA(Worksheets(i).Cells) = 0 Then
Application.DisplayAlerts = False
    Worksheets(i).Delete
Application.DisplayAlerts = True
  End If
 Next i
 Sheets.Add (after:=Sheets(Sheets.Count)).Name = "集計シート"
 Sheets(1).Activate
 Sheets(1).Range("A1").CurrentRegion.Select
 Selection.Resize(Selection.Rows.Count - 1).Copy
 Sheets("集計シート").Activate
  Sheets("集計シート").Range("A1").PasteSpecial
 For i = 2 To Sheets.Count
 Sheets(i).Activate
 Sheets(i).Range("A1").CurrentRegion.Select
  Selection.Offset(1).Resize(Selection.Rows.Count - 2).Copy
  Sheets("集計シート").Activate
 Sheets("集計シート").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial
 Next i
  maxcol = Cells(1, Columns.Count).End(xlToLeft).Column
  maxrow = Cells(Rows.Count, 1).End(xlUp).Row
 With Cells(maxrow, 2).Offset(1)
  .Resize(, maxcol - 1).Formula = "=SUM(" & Range("B2", Range("B" & maxrow)).Address(False, False) & ")"
 End With
 Cells(maxrow, 1).Offset(1) = "総合計"
 Columns.AutoFit
  Range("A1").CurrentRegion.Select
 Selection.Borders.LineStyle = True
 Range(Cells(maxrow + 1, 1), Cells(maxrow + 1, maxcol)).Interior.Color = RGB(192, 192, 192)
Application.ScreenUpdating = True
End Sub
2行目【Application.ScreenUpdating = False】
Application【アプリケーション】オブジェクトのScreenUpdating【スクリーンアップディーティング】プロパティにFalseを設定して、メモリ使用の抑制を処理の高速化のために画面の更新を抑止します。


3行目【Dim i As Long】
繰り返し処理の中で使用する、シートのインデックス番号を格納するカウンター変数「i」を長整数型(Long)で宣言します。


4行目【Dim maxcol As Long】
追加してデータを集めた「集計シート」のデータ範囲の最終列番号を格納する変数「maxcol」を長整数型(Long)で宣言します。


5行目【Dim maxrow As Long】
追加してデータを集めた「集計シート」のデータ範囲の最終行番号を格納する変数「maxrow」を長整数型(Long)で宣言します。


6行目【For i = Sheets.Count To 1 Step -1】
データがないシートがあると後の処理でエラーになるため、空のシートを削除するための繰り返し処理の始まりです。
カウンター変数の「i」にシートのインデックス番号を代入しますが、ここでのポイントは、シート1から始まりではなく
Sheets.Countでシートの数を数えて、シートの最終つまり右からStep-1で左、つまりシート1方向に空セルが無いか確認して空セルがあった場合は、そのシートを削除します。右のシートから確認する理由は、左セルから見て空シートがあって削除した場合シートが左にずれるためその左にずれたシートがこの繰り返し処理から漏れてしまうためです。後ろからシートを削除した場合はシートがずれることがないためシート右端から確認します。


7行目【If Application.WorksheetFunction.CountA(Worksheets(i).Cells) = 0 Then】
ワークシート関数のCuntA(カウントエー)関数を使って空のシートを条件分岐します。CuntA関数は、範囲に含まれる空白でないセルの個数を返す関数です。引数に範囲をしてします。ここでは、CountA(Worksheets(i).Cells)でワークシートのセルを確認し
データの個数が0だった時8行目からの処理をすることを定義しています。


8行目【Application.DisplayAlerts = False】
9行目で空のワークシートを削除するときに「本当に削除していいですか?」とアラートがでるのアラートをださない設定をします。詳しくはこちらをご覧ください。


9行目【Worksheets(i).Delete】
データのないシートを削除しています。


10行目【Application.DisplayAlerts = True】
確認アラートの停止を解除しています。


13行目【Sheets.Add (after:=Sheets(Sheets.Count)).Name = “集計シート”】
Addメソッドで「集計シート」という名前のシートを追加しています。シートの追加についてこちらをご覧ください。


14行目~18行目
【Sheets(1).Activate
Sheets(1).Range(“A1”).CurrentRegion.Select
Selection.Resize(Selection.Rows.Count – 1).Copy
Sheets(“集計シート”).Activate
Sheets(“集計シート”).Range(“A1”).PasteSpecial】

Sheets(1)でシートをインデックス番号で指定しています。(1)は先頭のシート すなわちここでは、東京店のシートです。
東京店のシートのデータ範囲をCurrentRegion(カレントリジョン) プロパティで選択し、最終の合計行はいらないのでResize(リザイズ)プロパティでResize(Selection.Rows.Count – 1)とし、最終行を選択状態からはずして、copy(コピー)メソッドでコピーをして、追加した集計シートのA1セルにPasteSpecial(ペーストスペシャル)メソッドで、貼付けています。


19行目~25行目
【For i = 2 To Sheets.Count
Sheets(i).Activate
Sheets(i).Range(“A1”).CurrentRegion.Select
Selection.Offset(1).Resize(Selection.Rows.Count – 2).Copy
Sheets(“集計シート”).Activate
Sheets(“集計シート”).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial
Next i】

For文で各シートのデータをコピーして
集計シートのデータ最終行に貼り付けています。sheet(2)以降は1行目の項目行もいらないので、A1セルからCurrentRegionで選択した範囲をSelection.Offset(1)で1行下げて(選択範囲を1行下にずらしている状態).Resize(Selection.Rows.Count – 2)で選択範囲の最終行から-2行することで合計行をコピーの選択範囲から除外しています。


26行目27行目
【maxcol = Cells(1, Columns.Count).End(xlToLeft).Column
maxrow = Cells(Rows.Count, 1).End(xlUp).Row】

集計シートのデータ範囲の最終行数と列数を変数に代入しています。


28行目【With Cells(maxrow, 2).Offset(1)】
集計シートのデータの最終行の下行に合計行を作成するため、Cells(maxrow, 2).Offset(1)で2列目のmaxrow最終データ行のOffset(1)で1つ下の行を選択してWithステートメントで指定します。


29行目【 .Resize(, maxcol – 1).Formula = “=SUM(” & Range(“B2”, Range(“B” & maxrow)).Address(False, False) & “)”】
28行目で選択した2列目のセルに対してResize(, maxcol – 1)で、データ範囲の最終列から、2列目から指定しているので-1することで2列目からデータの最終列を選択状態にしています。そこに、Formula = “=SUM(” & Range(“B2”, Range(“B” & maxrow)).Address(False, False) & “)”でSUM関数の式をFormulaプロパティに代入して上記で選択状態のセルに数式を代入しています。


31行目【Cells(maxrow, 1).Offset(1) = “総合計”】
1列目のデータ最終行の下のセルに、総合計の文字列を代入しています。


32行目【Columns.AutoFit】
列幅をデータ幅に合わせています。


33行目34行目
【Range(“A1”).CurrentRegion.Select
Selection.Borders.LineStyle = True】

データ範囲に罫線をひいてます。


35行目【Range(Cells(maxrow + 1, 1), Cells(maxrow + 1, maxcol)).Interior.Color = RGB(192, 192, 192)】
最後の総合計行を薄いグレーで塗りつぶしてします。


36行目【Application.ScreenUpdating = True】
止めていた画面の更新を再開しています。

このコードは、シート数やシート名 データの範囲に関わらす、集計シートにデータをまとめることができるので、応用範囲が広いと思います。ただ、各シートのデーター形式のフォーマットは統一してください。

以上で、複数のシートのデータを集計シートにまとめるコードの解説を終了します。

スポンサーリンク

関連記事・広告