[教えて！VBA] 第12回各ワークシートのデータを集約して出力するにはどうすればいいの？？

Jan 15, 2022 · この記事は約13分で読めます · VBA Excel ·

Share on:

概要

この記事について

かんたんな概要と結論

データが記載された各ワークシートにアクセスし、データを集約する方法について、
サンプルを例示し、そのコードをベースにまとめました。

WorkSheetsコレクションの取り扱いと、集約後の加工についても少し触れています。

こんにちは、dedeです。

この記事では、
VBAマクロに関する質問のうち、
皆が疑問に思っているトピックについて解説いたします。

今回は、
ブックの各ワークシートのデータを集約して、他のワークシートやテキストファイルなどに出力する方法
を解説いたします。

※この記事は、Office VBAマクロのうち
Excel VBAマクロに関するトピックです。

レベル：初級者向け

環境

以下は、
Office 2016のExcel環境で検証済みです。

※2022/1時点の最新バージョンのExcelでも内容は変わりません。

ワークシートの情報にアクセス

ワークシートオブジェクトとは

各ワークシートの情報にアクセスするには、
ワークシートオブジェクトというオブジェクトを参照します。

VBAにおけるオブジェクト

VBAはVisual Basic 6.0の仕様に準拠しており、
オブジェクト指向のプログラミング言語です。

データは基本的にオブジェクト（データとそれに紐付く動作を一つにまとめたもの）として扱われます。
オブジェクトはクラスによって生成され、クラスはオブジェクトの雛形とみなすことができます。

詳しくは、こちらの記事で紹介しました。

アクセスするコードサンプル

ワークシートオブジェクトは、
ActiveSheet（表示中のワークシート）プロパティや、
WorkSheets コレクションを呼び出すことなどで参照できます。

WorkSheets コレクションについては下記で詳細に見ていきます。

1Sub ワークシートオブジェクトの情報にアクセスする()
2    Debug.Print ThisWorkbook.Worksheets(1).name '//ブックの一枚目のワークシートの名前を参照
3    Debug.Print ThisWorkbook.Worksheets(1).Range("B2").Value '//ブックの一枚目のワークシートの、B2セルに書き込まれた値を参照
4End Sub

ワークシートコレクションとは

ワークシートコレクションとは、
そのブックのワークシートがすべて格納されたコレクションを指します。

コレクションとは？

VBAにおけるコレクションとは、
同じ種類のオブジェクトやデータを一定の規則に従って格納し、引き出すことができるようにしたオブジェクトの集まりで、
それ自身もオブジェクトです。

「一定の規則」とは、
Collection.Item(インデックス番号)や Collection (インデックス番号)の形で〇〇番目のオブジェクトやデータを取得したり、
Collection.Count の形でコレクションに格納されたオブジェクトの数を算出したりする決まりを指します。
さらに For ループを用いて順番にオブジェクトのデータを処理できるなどの決まりも含まれます。

コレクションは、
New Collection の形で自分で定義できる方法もあれば、
WorkSheets のようにもともと定義されているものを即座に利用可能な場合も存在します。

 1
 2Sub コレクション利用サンプル()
 3    Dim myColl As Collection '//自作のコレクションを宣言
 4    
 5    Set myColl = New Collection '//自作のコレクションをインスタンス化（準備）
 6    
 7    myColl.Add "りんご" '//自作のコレクションに文字列を格納
 8    myColl.Add "みかん"
 9    myColl.Add "ぶどう"
10    
11    Debug.Print myColl.Item(1) '//自作のコレクションから1番目のデータを参照
12    Debug.Print myColl(2) '//自作のコレクションから2番目のデータを参照
13    Debug.Print myColl.Count '//自作のコレクションのデータ数を算出
14    
15End Sub
16

ワークシートコレクションはExcelで既に定義されたオブジェクトで、コレクションの一種のため、
上述したようにForループを用いて
順番にブックのワークシートの情報を調べることができます。

 1
 2Sub ワークシートコレクションをループするサンプル()
 3    Dim counter As Long
 4    Dim currentSheet As Worksheet '//ForEachループで使用するワークシートオブジェクト変数
 5    
 6    '//ループ処理によって各シートの名前を取得したい
 7    '////インデックス番号のカウンターを用いたForループの場合
 8    For counter = 1 To ThisWorkbook.Worksheets.Count
 9        Debug.Print ThisWorkbook.Worksheets(counter).name
10    Next counter
11    
12    '////For Each機構を利用したループの場合
13    For Each currentSheet In ThisWorkbook.Worksheets
14        Debug.Print currentSheet.name
15    Next currentSheet
16    
17End Sub
18

ワークシートコレクションが属するブック

ワークシートコレクションを使用する際に注意すべきことは、
調べたいワークシートコレクションがどのブックに属しているかということを明確に理解しておくべきということです。

下のコードでも示すように、
Worksheetsでどのブックのワークシートを取り扱うかということは、
Worksheetsのドットの前のブックオブジェクトがどのようなブックを指しているかということで決まります。

 1
 2Sub ワークシートコレクションが属するブック()
 3    Dim targetWSColl As Sheets
 4    Dim newBook As Workbook
 5    
 6    Set targetWSColl = ThisWorkbook.Worksheets '// 1. マクロを記述しているブックのワークシートコレクションを変数に入れる
 7    
 8    Set targetWSColl = ActiveWorkbook.Worksheets '// 2. 現在開いているブックのワークシートコレクションを変数に入れる
 9    
10    Set newBook = Workbooks.Add '// 新しくブックを作成
11    Set targetWSColl = newBook.Worksheets '// 3. 新しく作成したブックのワークシートコレクションを変数に入れる
12    newBook.Close False
13End Sub
14

現在開いているブックのシートを取り扱いたいのに、
ThisWorkbook.Worksheetsを参照したりすることがないように気をつける必要があります。

各シートから取得したい範囲のデータを集約するサンプル

各シートのA1~A3セルの内容をメッセージ本文として集約するサンプルです。

 1
 2Sub 各シートから取得したい範囲のデータを集約するサンプル()
 3    Dim currentSheet As Worksheet
 4    Dim aggregatedDataText As String
 5     '// 全シートについてセルの内容を調査
 6    For Each currentSheet In ThisWorkbook.Worksheets
 7        '// A1セルの内容取得
 8        aggregatedDataText = aggregatedDataText & currentSheet.Range("A1").Value & ";"
 9        '// A2セルの内容取得
10        aggregatedDataText = aggregatedDataText & currentSheet.Range("A2").Value & ";"
11        '// A3セルの内容取得
12        aggregatedDataText = aggregatedDataText & currentSheet.Range("A3").Value
13        aggregatedDataText = aggregatedDataText & vbLf
14    Next currentSheet
15    
16    aggregatedDataText = "各シートから集めたデータはこちら" & vbLf & vbLf & aggregatedDataText
17    
18    '// 取得した内容をメッセージとして表示
19    MsgBox aggregatedDataText, vbOKOnly, "各シートから取得したい範囲のデータを集約するサンプル"
20End Sub
21

データを出力する

次は、取得対象のデータを集約し、出力する処理について記します。

前提：シートに存在するデータの様式がそろっていること

まず前提として、
シート状の、取得対象のデータの様式が揃っていなければなりません。

具体的には、
取得したいセル範囲、セル結合しているか否か、各列の内容とその順番が統一されている必要があります。
セル範囲がワークシートごとにまちまちだったり、項目の順番があるシートだけ逆順になっていたりするとコードが複雑化してしまいます。

今回は、
２列のセル範囲を持つ売上金データをまとめてみます。

売上金データの様式

シートは、都道府県ごとのシートが複数と、
例外となるシートも存在することとします。
対象となるシートの名前は「〇〇データ」であるすべてのシートとします。

シート名の様式

なお、デモに用いたファイルは
こちらからダウンロードできます。

シートに出力する場合

まず、データを集めるシートと同じブックに、
まとめ用シートを作成することを考えます。

まとめ用シートの作成の流れ

データの集約と出力は、
コピーペーストではなく、値の取得・出力で行います

理由は、コピーペーストの場合、コメントや条件付き書式など、
不必要なデータも伴ってしまう可能性があるからです。

 1
 2Sub シートに出力する場合01_月単位でまとめないバージョン()
 3    Dim currentSheet As Worksheet '// ループ用
 4    Dim matomeSheet As Worksheet '// 作成したまとめシート
 5    Dim sheetDataDic As Object '//シートのデータ格納用のDictionary
 6    Dim currentSheetName As Variant '// ループ用
 7    Dim rowNumCursor As Long '// 操作する行番号を示すカーソル
 8    
 9    '// シートのデータ格納用のDictionaryを設定
10    Set sheetDataDic = CreateObject("Scripting.Dictionary")
11    
12    '// 全シートごとにセルの内容を調査
13    For Each currentSheet In ThisWorkbook.Worksheets
14        '// 「〇〇データ」の名前のシートのみ調査
15        If InStr(currentSheet.Name, "データ") > 0 Then
16            sheetDataDic.Add currentSheet.Name, currentSheet.Range("B8:C19").Value
17        End If
18        '// まとめシートがすでにある場合は削除
19        If currentSheet.Name = "まとめ" Then
20            Application.DisplayAlerts = False
21            currentSheet.Delete
22            Application.DisplayAlerts = True
23        End If
24    Next currentSheet
25    
26    '// まとめシートを作成
27    Set matomeSheet = ThisWorkbook.Worksheets.Add
28    matomeSheet.Name = "まとめ"
29    
30    '// まとめシートに各シートの売上金などのデータを貼り付け
31    rowNumCursor = 3
32    '//// ヘッダー文字列を設定
33    matomeSheet.Cells(2, 2).Value = "都道府県"
34    matomeSheet.Cells(2, 3).Value = "計上月"
35    matomeSheet.Cells(2, 4).Value = "売上金"
36    '//// 各シートごとにデータを処理
37    For Each currentSheetName In sheetDataDic.Keys
38        '// 都道府県名を書き込み
39        matomeSheet.Range( _
40        matomeSheet.Cells(rowNumCursor, 2), _
41        matomeSheet.Cells(rowNumCursor + 11, 2) _
42        ).Value = currentSheetName
43        '// 売上金などデータを書き込み
44        matomeSheet.Range( _
45        matomeSheet.Cells(rowNumCursor, 3), _
46        matomeSheet.Cells(rowNumCursor + 11, 4) _
47        ).Value = sheetDataDic(currentSheetName)
48        
49        '// 次の都道府県へ
50        rowNumCursor = rowNumCursor + 12
51    Next currentSheetName
52    
53End Sub
54

実行すると、
「まとめ」シートが作成されます。

「まとめ」シート

すべての都道府県シートにおいてB8:C19の範囲でデータが有るため、
コードが簡潔に書けています。

シートに出力する場合（並べ替え処理を追加）

次に、上の関数に、
計上月順に並べ替えを行う処理を追加します。

 1
 2Sub シートに出力する場合02_月単位でまとめるバージョン()
 3    Dim currentSheet As Worksheet                '// ループ用
 4    Dim matomeSheet As Worksheet                 '// 作成したまとめシート
 5    Dim sheetDataDic As Object                   '//シートのデータ格納用のDictionary
 6    Dim currentSheetName As Variant              '// ループ用
 7    Dim rowNumCursor As Long                     '// 操作する行番号を示すカーソル
 8    
 9    '// シートのデータ格納用のDictionaryを設定
10    Set sheetDataDic = CreateObject("Scripting.Dictionary")
11    
12    '// 全シートごとにセルの内容を調査
13    For Each currentSheet In ThisWorkbook.Worksheets
14        '// 「〇〇データ」の名前のシートのみ調査
15        If InStr(currentSheet.Name, "データ") > 0 Then
16            sheetDataDic.Add currentSheet.Name, currentSheet.Range("B8:C19").Value
17        End If
18        '// まとめシートがすでにある場合は削除
19        If currentSheet.Name = "まとめ" Then
20            Application.DisplayAlerts = False
21            currentSheet.Delete
22            Application.DisplayAlerts = True
23        End If
24    Next currentSheet
25    
26    '// まとめシートを作成
27    Set matomeSheet = ThisWorkbook.Worksheets.Add
28    matomeSheet.Name = "まとめ"
29    
30    '// まとめシートに各シートの売上金などのデータを貼り付け
31    rowNumCursor = 3
32    '//// ヘッダー文字列を設定
33    matomeSheet.Cells(2, 2).Value = "都道府県"
34    matomeSheet.Cells(2, 3).Value = "計上月"
35    matomeSheet.Cells(2, 4).Value = "売上金"
36    '//// 各シートごとにデータを処理
37    For Each currentSheetName In sheetDataDic.Keys
38        '// 都道府県名を書き込み
39        matomeSheet.Range( _
40        matomeSheet.Cells(rowNumCursor, 2), _
41        matomeSheet.Cells(rowNumCursor + 11, 2) _
42        ).Value = currentSheetName
43        '// 売上金などデータを書き込み
44        matomeSheet.Range( _
45        matomeSheet.Cells(rowNumCursor, 3), _
46        matomeSheet.Cells(rowNumCursor + 11, 4) _
47        ).Value = sheetDataDic(currentSheetName)
48        
49        '// 次の都道府県へ
50        rowNumCursor = rowNumCursor + 12
51    Next currentSheetName
52    
53    '// 計上月と都道府県の列を入れ替える
54    matomeSheet.Columns(3).Cut
55    matomeSheet.Columns(2).Insert Shift:=xlToRight
56    
57    '// 計上月順に並び替えを行う
58    With matomeSheet.Sort
59        .SortFields.Clear
60        .SortFields.Add2 Key:=matomeSheet.Range( _
61        matomeSheet.Cells(3, 2), _
62        matomeSheet.Cells(rowNumCursor - 1, 2) _
63        ) _
64        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
65        .SetRange matomeSheet.Range( _
66        matomeSheet.Cells(3, 2), _
67        matomeSheet.Cells(rowNumCursor - 1, 4) _
68        )
69        .Header = xlYes
70        .MatchCase = False
71        .Orientation = xlTopToBottom
72        .SortMethod = xlPinYin
73        .Apply
74    End With
75    
76End Sub
77

実行すると、
「まとめ」シートが作成され、
なおかつデータが月の若い順からソートされて表示されます。

ソートされたまとめシート

このように、
集約して別シートとしてデータをまとめれば、
並び替えや分析も容易になります。

シートに出力する場合（書式も合わせる）

今度は、
データのテーブルの書式も都道府県シートの元データテーブルに
寄せてみます。

 1
 2Sub シートに出力する場合03_書式も合わせるバージョン()
 3    Dim currentSheet As Worksheet                '// ループ用
 4    Dim matomeSheet As Worksheet                 '// 作成したまとめシート
 5    Dim sheetDataDic As Object                   '//シートのデータ格納用のDictionary
 6    Dim currentSheetName As Variant              '// ループ用
 7    Dim rowNumCursor As Long                     '// 操作する行番号を示すカーソル
 8    
 9    '// シートのデータ格納用のDictionaryを設定
10    Set sheetDataDic = CreateObject("Scripting.Dictionary")
11    
12    '// 全シートごとにセルの内容を調査
13    For Each currentSheet In ThisWorkbook.Worksheets
14        '// 「〇〇データ」の名前のシートのみ調査
15        If InStr(currentSheet.Name, "データ") > 0 Then
16            sheetDataDic.Add currentSheet.Name, currentSheet.Range("B8:C19").Value
17        End If
18        '// まとめシートがすでにある場合は削除
19        If currentSheet.Name = "まとめ" Then
20            Application.DisplayAlerts = False
21            currentSheet.Delete
22            Application.DisplayAlerts = True
23        End If
24    Next currentSheet
25    
26    '// まとめシートを作成
27    Set matomeSheet = ThisWorkbook.Worksheets.Add
28    matomeSheet.Name = "まとめ"
29    
30    '// まとめシートに各シートの売上金などのデータを貼り付け
31    rowNumCursor = 3
32    '//// ヘッダー文字列を設定
33    matomeSheet.Cells(2, 2).Value = "都道府県"
34    matomeSheet.Cells(2, 3).Value = "計上月"
35    matomeSheet.Cells(2, 4).Value = "売上金"
36    '//// 各シートごとにデータを処理
37    For Each currentSheetName In sheetDataDic.Keys
38        '// 都道府県名を書き込み
39        matomeSheet.Range( _
40        matomeSheet.Cells(rowNumCursor, 2), _
41        matomeSheet.Cells(rowNumCursor + 11, 2) _
42        ).Value = currentSheetName
43        '// 売上金などデータを書き込み
44        matomeSheet.Range( _
45        matomeSheet.Cells(rowNumCursor, 3), _
46        matomeSheet.Cells(rowNumCursor + 11, 4) _
47        ).Value = sheetDataDic(currentSheetName)
48        
49        '// 次の都道府県へ
50        rowNumCursor = rowNumCursor + 12
51    Next currentSheetName
52    
53    '// 計上月と都道府県の列を入れ替える
54    matomeSheet.Columns(3).Cut
55    matomeSheet.Columns(2).Insert Shift:=xlToRight
56    
57    '// 計上月順に並び替えを行う
58    With matomeSheet.Sort
59        .SortFields.Clear
60        .SortFields.Add2 Key:=matomeSheet.Range( _
61                               matomeSheet.Cells(3, 2), _
62                         matomeSheet.Cells(rowNumCursor - 1, 2) _
63                         ) _
64        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
65        .SetRange matomeSheet.Range( _
66                  matomeSheet.Cells(3, 2), _
67                  matomeSheet.Cells(rowNumCursor - 1, 4) _
68                  )
69        .Header = xlYes
70        .MatchCase = False
71        .Orientation = xlTopToBottom
72        .SortMethod = xlPinYin
73        .Apply
74    End With
75    
76    '// 書式を元のデータテーブルに合わせる
77    '//// 罫線
78    matomeSheet.Range( _
79        matomeSheet.Cells(2, 2), _
80        matomeSheet.Cells(rowNumCursor - 1, 4) _
81        ).Borders.LineStyle = xlContinuous
82    '//// ヘッダー背景色、中央揃え
83    With matomeSheet.Range( _
84         matomeSheet.Cells(2, 2), _
85         matomeSheet.Cells(2, 4) _
86         )
87        .Interior.Color = 15917529
88        .HorizontalAlignment = xlCenter
89    End With
90End Sub
91

実行すると、
「まとめ」シートが作成され、ソートされ、
なおかつヘッダーの背景色や罫線が元テーブル仕様に変更されます。

書式が合わせられたまとめシート

テキストファイルに出力する場合

シートに出力せずにテキストファイルに出力する場合でも、
いったん作業用シートとして一つのシートにまとめ、
並び替えなどを行った後にテキストに出力すると便利な場合があるでしょう。

テキストに出力する流れ

コードは以下のようになります。

 1
 2Sub テキストファイル_CSV_に出力する場合()
 3    Dim currentSheet As Worksheet                '// ループ用
 4    Dim matomeSheet As Worksheet                 '// 作成したまとめシート
 5    Dim sheetDataDic As Object                   '//シートのデータ格納用のDictionary
 6    Dim currentSheetName As Variant              '// ループ用
 7    Dim rowNumCursor As Long                     '// 操作する行番号を示すカーソル
 8    
 9    '// シートのデータ格納用のDictionaryを設定
10    Set sheetDataDic = CreateObject("Scripting.Dictionary")
11    
12    '// 全シートごとにセルの内容を調査
13    For Each currentSheet In ThisWorkbook.Worksheets
14        '// 「〇〇データ」の名前のシートのみ調査
15        If InStr(currentSheet.Name, "データ") > 0 Then
16            sheetDataDic.Add currentSheet.Name, currentSheet.Range("B8:C19").Value
17        End If
18        '// まとめシートがすでにある場合は削除
19        If currentSheet.Name = "まとめ" Then
20            Application.DisplayAlerts = False
21            currentSheet.Delete
22            Application.DisplayAlerts = True
23        End If
24    Next currentSheet
25    
26    '// まとめシートを作成
27    Set matomeSheet = ThisWorkbook.Worksheets.Add
28    matomeSheet.Name = "まとめ"
29    
30    '// まとめシートに各シートの売上金などのデータを貼り付け
31    rowNumCursor = 2
32    '//// ヘッダー文字列を設定
33    matomeSheet.Cells(1, 1).Value = "都道府県"
34    matomeSheet.Cells(1, 2).Value = "計上月"
35    matomeSheet.Cells(1, 3).Value = "売上金"
36    '//// 各シートごとにデータを処理
37    For Each currentSheetName In sheetDataDic.Keys
38        '// 都道府県名を書き込み
39        matomeSheet.Range( _
40        matomeSheet.Cells(rowNumCursor, 1), _
41        matomeSheet.Cells(rowNumCursor + 11, 1) _
42        ).Value = currentSheetName
43        '// 売上金などデータを書き込み
44        matomeSheet.Range( _
45        matomeSheet.Cells(rowNumCursor, 2), _
46        matomeSheet.Cells(rowNumCursor + 11, 3) _
47        ).Value = sheetDataDic(currentSheetName)
48        
49        '// 次の都道府県へ
50        rowNumCursor = rowNumCursor + 12
51    Next currentSheetName
52    
53    '// 計上月と都道府県の列を入れ替える
54    matomeSheet.Columns(2).Cut
55    matomeSheet.Columns(1).Insert Shift:=xlToRight
56    
57    '// 計上月順に並び替えを行う
58    With matomeSheet.Sort
59        .SortFields.Clear
60        .SortFields.Add2 Key:=matomeSheet.Range( _
61                               matomeSheet.Cells(3, 1), _
62                         matomeSheet.Cells(rowNumCursor - 1, 1) _
63                         ) _
64        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
65        .SetRange matomeSheet.Range( _
66                  matomeSheet.Cells(3, 1), _
67                  matomeSheet.Cells(rowNumCursor - 1, 3) _
68                  )
69        .Header = xlYes
70        .MatchCase = False
71        .Orientation = xlTopToBottom
72        .SortMethod = xlPinYin
73        .Apply
74    End With
75    
76    '// まとめシートを別ブックとして取り出す
77    matomeSheet.Move
78    
79    '// まとめシートをCSV形式のテキストファイルとしてブックと同じフォルダに保存
80    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "まとめ.csv" _
81        , FileFormat:=xlCSVUTF8, CreateBackup:=False
82    ActiveWorkbook.Close
83    
84End Sub
85

Moveは必須です

matomeSheet.Move は、
CSVとして保存する前にかならず行うべき処理です。

これをせずにCSV形式でブックを保存すると、
マクロが記載されたブックをCSVに変換することになり、不本意な結果に繋がる可能性があります。

終わりに

ワークシートのデータを集約し、
まとめる方法についていくつかのバリエーションを見てきました。

より複雑なケースにも対応できるように、
ワークシートに記載するデータの様式を整えておくと便利でしょう。