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

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

データを集計するSubtotal(サブトータル)メソッド

例えば
ひと月の売上が担当者別に日毎に入力されたデータがあったとして
担当者毎のひと月の売上合計をもとめたいケースで
コードを考えます。

もとのデータは残したまま新たに抽出シートを作成して
そのシートに集計データを抽出することも考慮します。



VBAのRangeオブジェクトのSubtotalメソッドは
ワークシート関数のSUBTOTAL関数とは違った働きをします。

Excelでは、データタブのリボンにある「小計」ボタンが
ありますが、VBAのSubtotalメソッドはこれと同じ働きをします。


【Subtotalメソッドの構文】
Subtotal(GroupBy,Function,TotalList,Replace,PageBreaks,SummaryBelowData)


Subtotalメソッド引数一覧表

名前説明
GroupBy(グループバイ)
【必須】
グループ化の基準となるフィールドの番号を、1 から始まる整数で指定
Function(ファンクション)
【必須】
集計方法を関数で指定
TotalList(トータルリスト)
【必須】
集計するフィールド表す、1 から始まるオフセット番号の配列指定
Replace(リプレイス)
【省略可】
既存の集計表と置き換えるには、True 既定値は True
PageBreaks(ページブレークス)
【省略可】
グループごとに改ページが挿入されるようにするには、True 既定値は False
SummaryBelowData(サマリ-ビーローデータ)
【省略可】
集計結果に表示位置 (上)0(下)1既定値は(下)1

Function(ファンクション)の値 集計方法 一覧表

名前説明
xlAverage(エックスエルアベレージ)平均
xlCount(エックスエルカウント)カウント
xlCountNums(エックスエルカウントナムズ)カウント数値のみ。
xlDistinctCount(エックスエルディステインクトカウント)Distinct Count 分析を使ったカウント
xlMax(エックスエルマックス)最大
xlMin(エックスエルミニッツ)最小
xlProduct(エックスエルプロダクト)
xlStDev(エックスエルスタンダードディービエーション)標本に基づく標準偏差
xlStDevP(エックスエルスタンダードディービエーションピー)母集団全体に基づく標準偏差
xlSum(エックスエルサム)合計
xlUnknown(エックスエルアンノン)小計に使用する関数は指定されません。
xlVar(エックスエルバー)標本に基づく変動
xlVarP(エックスエルバーピー)母集団全体に基づく変動

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

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
With Range("A1").CurrentRegion
 .SpecialCells(xlCellTypeVisible).Copy
End With
  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
 .Cells.ClearOutline
End With
 Sheets("抽出").Activate
Application.ScreenUpdating = True
End Sub

コードの解説

2行目
Application.ScreenUpdating = Falseで画面の更新を止めて
処理の高速化をしています。


3行目
【With Range(“A1”).CurrentRegion】
A1セルからのデータ範囲を
With【ウィズ】ステートメントで指定します。


4行目
【.Sort key1:=.Columns(2), order1:=xlAscending, Header:=xlYes】
Sort(ソート)メソッドで2列目に担当者を基準に並び替えをしています。
担当者をグループの基準にして集計を行うため、日毎に散らばっている担当者
を並び替えでひと集まりにします。Sortメソッドの詳細はこちらをご覧ください。


5行目~6行目
【.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True】
Subtotal(サブトータル)メソッドで2列目の担当者をグループの基準にして
Array(アレイ)関数で配列に売上行の3列目を入れました。これが合計される列です。


8行目
【ActiveSheet.Outline.ShowLevels RowLevels:=2】
集計されたシートの表示レベルを2に設定して集計行だけを表示しています。
レベルは1~3まであり、1が個々のデータの下に集計行が入った、すべて表示
2は集計行だけのデータ表示 3は総計だけの表示になります。


9行目10行目
【With Range(“A1”).CurrentRegion
.SpecialCells(xlCellTypeVisible).Copy】
レベル2で集計行だけ表示されているシートの表示されている部分だけ
SpecialCells(xlCellTypeVisible).Copyでコピーをしています。


12行目
【Sheets.Add(After:=Sheets(1)).Name = “抽出”】
Add(アド)メソッドで「抽出」という名前のシートを追加しています。


13行目
【ActiveSheet.Paste】
追加した「抽出シート」に10行目でコピーしたデータを貼り付けています。


14行目
【Columns(1).Delete】
抽出シートに抽出したデータの1列目、つまり売上日の列を削除しています。


15行目
【Range(“A1”).CurrentRegion.Borders.LineStyle = True】
抽出シートのデータ範囲に罫線をひいています。
表範囲に罫線を引く方法ついてはこちらをご覧ください。


16行目~18行目
【Cells.Replace What:=”集計”, Replacement:=””, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False】
Replace(リプレース)メソッドで置き換えをしています。
集計行を抽出したので担当者名の後に「集計」と文字例が表記されているので
「集計」を「” “」(空白)に置き換えしています。
Replaceメソッドに関してはこちらをご覧ください。


19行目
【Sheets(1).Activate】
最初のデータがあるシートをインデクス番号で指定して
アクティブにしています。


20行目~22行目
【With Range(“A1”).CurrentRegion
.RemoveSubtotal
.Cells.ClearOutline】
Subtotalがかかっている、つまり集計用の表示になっているシートを
.RemoveSubtotalで集計行と総計を消して
.Cells.ClearOutline画面左横に表示されているアウトライン(表示レベルを変更するボタン)
を削除して通常の状態にもどしています。


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


25行目
Application.ScreenUpdating = Trueで高速化のために
止めていた画面の更新を再開しています。


実行結果


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

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

フォローする

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

コメント

  1. 名無し より:

    英語の読み方が所々間違っているようですが…誤った読み方を記載するのはよろしくないと思います…。

    • kosapi より:

      ご指摘ありがとうございました。
      修正させていただきました。