Excel VBA データを集計して集計シートに集計データ抽出する

スポンサーリンク
スポンサーリンク

データを集計して抽出シートに集計データを抽出する方法

ひと月の売上が担当者別に日毎に入力されたデータがあった場合、担当者毎のひと月の売上合計を集計し、元のデータは残したまま新たに抽出シートを作成して、そのシートに集計データを抽出するコードについて説明します。

実行結果


データを集計して抽出シートに集計データを抽出するコード

Sub 抽出()
Application.ScreenUpdating = False
 With Range("A1").CurrentRegion
   .Sort key1:=.Columns(2), order1:=xlAscending, Header:=xlYes
   .Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
 End With
   ActiveSheet.Outline.ShowLevels RowLevels:=2
   Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
   Sheets.Add(After:=Sheets(1)).Name = "抽出"
   ActiveSheet.Paste
   Columns(1).Delete
   Range("A1").CurrentRegion.Borders.LineStyle = True
   Cells.Replace What:="集計", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
   Sheets(1).Activate
 With Range("A1").CurrentRegion
   .RemoveSubtotal
   .Sort key1:=.Columns(1), order1:=xlAscending, Header:=xlYes
 End With
   Sheets("抽出").Activate
Application.ScreenUpdating = True
End Sub

コードの解説

2行目【Application.ScreenUpdating = False】
Application【アプリケーション】オブジェクトのScreenUpdating【スクリーンアップディーティング】プロパティに Falseを設定して画面更新を抑制して使用メモリ削減と処理の高速化を実行します。


3行目【With Range(“A1”).CurrentRegion】
A1セルからのデータ範囲をRange【レンジ】オブジェクトのCurrentRegion【カレントリージョン】プロパティで参照してWith【ウィズ】ステートメントで指定します。


4行目【.Sort key1:=.Columns(2), order1:=xlAscending, Header:=xlYes】
With【ウィズ】ステートメントで指定したRange【レンジ】オブジェクトのSort【ソート】メソッドで2列目の担当者を基準に並び替え実行します。担当者をグループの基準にして集計を行うため、日毎に散らばっている担当者を並び替えでひと集まりにします。


5行目~6行目
【.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True】

With【ウィズ】ステートメントで指定したRange【レンジ】オブジェクトのSubtotal【サブトータル】メソッドで集計グループの基準を設定する引数GroupBy【グループバイ】に2列目の担当者を指定し、集計方法を設定する引数Function【ファンクション】に合計を表す「xlSum」を指定して集計する列を配列で指定する引数TotalList【トータルリスト】には、Array【アレイ】関数で配列に売上列の3列目を指定します。


8行目【ActiveSheet.Outline.ShowLevels RowLevels:=2】
Worksheet【ワークシート】オブジェクトのOutline【アウトライン】プロパティを使用してアウトラインを表すOutline【アウトライン】オブジェクトを参照し、Outline【アウトライン】オブジェクトのShowLevels【ショウレベルズ】メソッドを使用してアウトラインの行の折りたたみレベルを2に設定して、集計行だけを表示しています。レベルは1~3まであり、レベル1が個々のデータの下に集計行が入った、すべて表示。レベル2は集計行だけのデータ表示。 レベル3は総計だけの表示になります。


9行目【 Range(“A1”).CurrentRegion.SpecialCells(xlCellTypeVisible).Copy】
Range【レンジ】オブジェクトのCurrentRegion【カレントリージョン】プロパティでA1セルから始まる表範囲を選択して、Range【レンジ】オブジェクトの特定のセルを参照するSpecialCells【スペシャルセルズ】メソッドの引数にxlCellTypeVisible【エックスエルタイプビジブル】を設定してレベル2で集計行だけ表示されているシートの表示されている部分だけを参照し、Range【レンジ】オブジェクトのCopy【コピー】メソッドで可視セルの値をコピーします。


10行目【Sheets.Add(After:=Sheets(1)).Name = “抽出”】
Sheets【シーツ】コレクションのAdd【アド】メソッドを使用して「抽出」という名前のシートを追加しています。


11行目【ActiveSheet.Paste】
10行目で追加した抽出シートがアクティブになっているので、Worksheet【ワークシート】オブジェクトのPaste【ペースト】メソッドを使用して9行目でコピーしてクリップボードに保管されている、集計データを追加した抽出シートに貼り付けます。


12行目【Columns(1).Delete】
アクティブになっている抽出シートのColumns【カラムス】プロパティで参照した1列目、つまり売上日の列を削除しています。


13行目【Range(“A1”).CurrentRegion.Borders.LineStyle = True】
アクティブになっている抽出シートのA1セルからRange【レンジ】オブジェクトのCurrentRegion【カレントリージョン】プロパティで表範囲を参照し、Range【レンジ】オブジェクトのBorders【ボーダーズ】プロパティで、セルの罫線を表すBorders【ボーダーズ】オブジェクトを取得して、Borders【ボーダーズ】オブジェクトの罫線の種類を設定するLineStyle【ラインスタイル】プロパティにTrueを設定して選択範囲のセルの4辺に罫線を引きます。


14行目~16行目
【Cells.Replace What:=”集計”, Replacement:=””, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False】

cells【セルズ】プロパティでアクティブな抽出シートセル全体を参照し、Range【レンジ】オブジェクトのReplace【リプレイス】メソッドを使用して「集計」の文字列を「””」空白に置き換えます。


17行目【Sheets(1).Activate】
Worksheet【ワークシート】オブジェクトのActivate【アクティベイト】メソッドでSheets【シーツ】プロパティの引数で指定したインデックス番号1のシート、つまり元のデータがあるシートをアクティブにします。


18行目【With Range(“A1”).CurrentRegion】
元のデータがあるシートのA1セルから始まる表範囲をRange【レンジ】オブジェクトのCurrentRegion【カレントリージョン】プロパティで参照して、With【ウィズ】ステートメントで指定します。


19行目【.RemoveSubtotal】
With【ウィズ】ステートメントで指定した表範囲をRange【レンジ】オブジェクトのRemoveSubtotal【リムーブサブトータル】メソッドを使用して5~6行目で実行したSubtotal【サブトータル】メソッドで追加された集計行とアウトラインを削除します。


20行目【.Sort key1:=.Columns(1), order1:=xlAscending, Header:=xlYes】
With【ウィズ】ステートメントで指定した表範囲をRange【レンジ】オブジェクトのSort【ソート】メソッドを使用して並べ替えます。4行目のSort【ソート】メソッドで担当者を基準に並べ替えを実行したので、最初の売上日順に並べ替えます。


22行目【Sheets(“抽出”).Activate】
「抽出シート」をアクティブにします。


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


以上で、データを集計して抽出シートに集計データ抽出するコードの解説を終了します。なお、データの形式により、列番号などを変更してお使いください。抽出した後に不要な列は13行目のColumns().Deleteの引数に列番号をいれて削除してください。今回は1列目に売上日を削除しています。
ありがとうございました。

スポンサーリンク
スポンサーリンク
スポンサーリンク
スポンサーリンク