【EXCEL：VBA】複数のEXCELデータを1つに集約したい

この記事では、複数のEXCELに入力されているデータを1つに集約する機能について説明しています。

同じ形式のデータが大量にある場合に、この機能は使えます。

1つ1つEXCELファイルを開いて、行をコピーし、貼り付けして、ファイルを閉じる。次のファイルをまた開いて・・・。

地味な作業ですが、大変ですし、ミスをしやすい作業です。

そんな大変な作業をこの機能を使えば、ボタン1つで正確に集約することができます。

それでは、EXCELで複数のデータを1つのデータに集約する機能を作成していきましょう。

困っている女性

大量にあるEXCELに入力されているデータを1つに集約作業があるんだけど、地味に大変なの・・・。

困っている女性

集約もれが発生したときなんか、もう大変なの。どのファイルがもれたか探さないといけないの。泣きそうになるわ。

それなら、VBAを使えばもれなく正確に1つに集約することができるよ。

解決する男性

困っている女性

それそれ、早く教えてよ。

以下の記事では、似たような作業でEXCELデータを比較したり、差分を抽出する機能について説明しています。

2020-06-28【EXCEL：VBA】2つのデータを比較したい

2020-07-01【EXCEL：VBA】2つのデータの差分を抽出したい

◆似たような作業で、単純に1つのCSVファイルを取り込みする方法については、以下の記事を参照してください。

2020-06-23【EXCEL：VBA】CSVファイルを取り込みたい

◆似たような作業で、指定されたフォルダに存在するすべてのCSVファイルを一気に取り込みする方法については、以下の記事を参照してください。

2020-12-26【EXCEL：VBA】フォルダに存在するCSVファイルを全て取り込みたい

今回の記事と合わせて、読んでみてください。

EXCELで複数のデータを1つのデータに集約する機能に必要なものを、1つ1つ順番に説明していきます。

１．複数のEXCELデータを1つに集約するために必要なシートを作成する

「メイン」シートを作成する

メインシートを追加する

「メイン」という名前のシートを作成します。
1行目に「対象フォルダ」、「シート名」、「データ開始行」、「集約開始行」のタイトル行を作成します。
2行目は「対象フォルダ」、「シート名」、「データ開始行」、「集約開始行」を入力する行となります。

「対象フォルダ」には、複数のEXCELファイルが保存されているフォルダパスを入力します。

「シート名」には、集約したいEXCELファイルのシート名を入力します。

「データ開始行」には、集約したEXCELファイルのデータ開始行を入力します。

1行目にタイトル行（ヘッダー行）があり、2行目以降にデータが入力されている場合は、2と入力します。

「集約開始行」には、集約したデータを書込みする開始行を入力します。

タイトル行（ヘッダー行）が必要ない場合、1と入力すれば1行目から集約したデータが書込みされます。

タイトル行（ヘッダー行）が必要な場合、2と入力すれば2行目から集約したデータが書込みされます。

「集約データ」シートを作成する

集約データシートを追加する

「集約データ」という名前のシートを作成します。
1行目には、集約するデータのタイトル行（ヘッダー行）を入力します。

タイトル行（ヘッダー行）が必要ない場合、なにも入力しないで良いです。

２．複数のEXCELデータを1つに集約する機能を記入する

「Microsoft Visual Basic for Applications」を起動します。

VBAプログラムを記入する（その１）

VBAプログラムを記入する（その２）

標準モジュールを追加して、以下のソースを書きます。

Public Sub MainProc()
    Dim shtMain As Worksheet
    Dim folderPath As String
    Dim shtName As String
    Dim dataStartRow As Long
    Dim nowRow As Long
    Dim shtTaisyo As Worksheet
    Dim shtSyuyaku As Worksheet
    Dim ext As String
    Dim wb As Workbook
    Dim lastRow As Long
    Dim lastCol As Long
    Dim fso As Object
    Dim f As Object
    
    '①「メイン」シートを変数に格納する
    Set shtMain = ThisWorkbook.Sheets("メイン")
    
    '②対象フォルダを変数に格納する
    folderPath = shtMain.Range("A2")
    
    '③シート名を変数に格納する
    shtName = shtMain.Range("B2")
    
    '④データ開始行を変数に格納する
    dataStartRow = shtMain.Range("C2")
    
    '⑤集約データ開始行を変数に格納する
    nowRow = shtMain.Range("D2")
            
    '⑥「集約データ」シートを変数に格納する
    Set shtSyuyaku = ThisWorkbook.Sheets("集約データ")
            
    '⑦「集約データ」シートの開始行以下をクリアする
    shtSyuyaku.Rows(nowRow & ":" & shtSyuyaku.Rows.Count).Clear
    
    '⑧FileSystemObjectを変数に格納する
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    '⑨対象フォルダに存在するファイル数分処理する
    For Each f In fso.GetFolder(folderPath).Files
        
        '⑩ファイルの拡張子を変数に格納する
        ext = LCase(fso.getextensionName(f.Name))
        
        '⑪拡張子が「xlsx」のみ処理を行う
        If ext = "xlsx" Then
        
            '⑫対象ブックを開く
            Set wb = Workbooks.Open(folderPath & "\" & f.Name)
            
            '⑬対象シートを変数に格納する
            Set shtTaisyo = wb.Sheets(shtName)
            
            '⑭対象シートの最終行を取得する
            lastRow = shtTaisyo.Cells(shtTaisyo.Rows.Count, 1).End(xlUp).Row
            
            '⑮対象シートの最終列を取得する
            lastCol = shtTaisyo.Cells(1, shtTaisyo.Columns.Count).End(xlToLeft).Column
            
            '⑯対象シートの対象データを集約シートにコピーする
            shtTaisyo.Range(shtTaisyo.Cells(dataStartRow, 1), shtTaisyo.Cells(lastRow, lastCol)).Copy _
                            (shtSyuyaku.Cells(nowRow, 1))
            
            '⑰次の貼り付け行を計算する
            nowRow = nowRow + lastRow - (dataStartRow - 1)
            
            '⑱対象ブックを閉じる
            wb.Close
            
            '⑲メモリを解放する
            Set wb = Nothing
            
        End If
    Next
    
    MsgBox "完了"
End Sub

Public Sub MainProc()

Dim shtMain As Worksheet

Dim folderPath As String

Dim shtName As String

Dim dataStartRow As Long

Dim nowRow As Long

Dim shtTaisyo As Worksheet

Dim shtSyuyaku As Worksheet

Dim ext As String

Dim wb As Workbook

Dim lastRow As Long

Dim lastCol As Long

Dim fso As Object

Dim f As Object

'①「メイン」シートを変数に格納する

Set shtMain = ThisWorkbook.Sheets("メイン")

'②対象フォルダを変数に格納する

folderPath = shtMain.Range("A2")

'③シート名を変数に格納する

shtName = shtMain.Range("B2")

'④データ開始行を変数に格納する

dataStartRow = shtMain.Range("C2")

'⑤集約データ開始行を変数に格納する

nowRow = shtMain.Range("D2")

'⑥「集約データ」シートを変数に格納する

Set shtSyuyaku = ThisWorkbook.Sheets("集約データ")

'⑦「集約データ」シートの開始行以下をクリアする

shtSyuyaku.Rows(nowRow & ":" & shtSyuyaku.Rows.Count).Clear

'⑧FileSystemObjectを変数に格納する

Set fso = CreateObject("Scripting.FileSystemObject")

'⑨対象フォルダに存在するファイル数分処理する

For Each f In fso.GetFolder(folderPath).Files

'⑩ファイルの拡張子を変数に格納する

ext = LCase(fso.getextensionName(f.Name))

'⑪拡張子が「xlsx」のみ処理を行う

If ext = "xlsx" Then

'⑫対象ブックを開く

Set wb = Workbooks.Open(folderPath & "\" & f.Name)

'⑬対象シートを変数に格納する

Set shtTaisyo = wb.Sheets(shtName)

'⑭対象シートの最終行を取得する

lastRow = shtTaisyo.Cells(shtTaisyo.Rows.Count, 1).End(xlUp).Row

'⑮対象シートの最終列を取得する

lastCol = shtTaisyo.Cells(1, shtTaisyo.Columns.Count).End(xlToLeft).Column

'⑯対象シートの対象データを集約シートにコピーする

shtTaisyo.Range(shtTaisyo.Cells(dataStartRow, 1), shtTaisyo.Cells(lastRow, lastCol)).Copy _

(shtSyuyaku.Cells(nowRow, 1))

'⑰次の貼り付け行を計算する

nowRow = nowRow + lastRow - (dataStartRow - 1)

'⑱対象ブックを閉じる

wb.Close

'⑲メモリを解放する

Set wb = Nothing

End If

MsgBox "完了"

End Sub

３．複数のEXCELデータを1つに集約する機能の使い方

それでは、集約したいEXCELファイルを用意します。

今回のサンプルでは、「従業員番号」、「氏名」の2列のデータにしていますが、

お好きな形式のデータで構いません。

集約したいEXCELファイルを1つのフォルダに保存します。

今回のサンプルではファイル3つ用意していますが、必要なだけファイルを保存してください。

集約したいEXCELファイルを1つのフォルダに保存する

集約したいEXCELファイルの1つ目は以下です。

集約したいEXCELファイルその１

集約したいEXCELファイルの2つ目は以下です。

集約したいEXCELファイルその2

集約したいEXCELファイルの3つ目は以下です。

集約したいEXCELファイルその3

それでは、今回用意したサンプルのEXCELファイル3つを1つに集約していきます。

「メイン」シートの「対象フォルダ」、「シート名」、「データ開始行」、「集約開始行」を入力します。

次に、「開発」タブの「マクロ」ボタンをクリックします。

マクロボタンをクリックする

先程作成した自作関数「MainProc」が一覧に表示されていますので、選択します。

次に、「実行」ボタンをクリックしてデータ集約を開始します。

実行ボタンをクリックする

少し待つと、複数のEXCELデータが1つに集約されて、「集約データ」シートに書込みされます。

間違いなく、3つのEXCELファイルのデータが1つに集約されています。

3つのEXCELデータが1つに集約された

どうですか、簡単でしょ。

もっとたくさんのEXCELファイルでも、同じく簡単ですのでやってみてください。

４．複数のEXCELデータを1つに集約するVBAプログラムの説明

それでは、VBAプログラムを説明します。

①「メイン」シートを変数に格納する

'①「メイン」シートを変数に格納する
Set shtMain = ThisWorkbook.Sheets("メイン")

1 2	'①「メイン」シートを変数に格納する Set shtMain = ThisWorkbook.Sheets("メイン")

ここでは、このあとの処理で使う「メイン」シートを変数に格納しています。

そうすることにより、入力するプログラムを短くすることができるからです。

何度も「ThisWorkbook.Sheets(“メイン”)」と入力するより、「shtMain」と入力するほうが少なくてすむからです。

②対象フォルダを変数に格納する

'②対象フォルダを変数に格納する
folderPath = shtMain.Range("A2")

1 2	'②対象フォルダを変数に格納する folderPath = shtMain.Range("A2")

ここでは、「メイン」シートのA2セルに入力されている「対象フォルダ」を変数に格納しています。

③シート名を変数に格納する

'③シート名を変数に格納する
shtName = shtMain.Range("B2")

1 2	'③シート名を変数に格納する shtName = shtMain.Range("B2")

ここでは、「メイン」シートのB2セルに入力されている「シート名」を変数に格納しています。

④データ開始行を変数に格納する

'④データ開始行を変数に格納する
dataStartRow = shtMain.Range("C2")

1 2	'④データ開始行を変数に格納する dataStartRow = shtMain.Range("C2")

ここでは、「メイン」シートのC2セルに入力されている「データ開始行」を変数に格納しています。

⑤集約データ開始行を変数に格納する

'⑤集約データ開始行を変数に格納する
nowRow = shtMain.Range("D2")

1 2	'⑤集約データ開始行を変数に格納する nowRow = shtMain.Range("D2")

ここでは、「メイン」シートのD2セルに入力されている「集約開始行」を変数に格納しています。

⑥「集約データ」シートを変数に格納する

'⑥「集約データ」シートを変数に格納する
Set shtSyuyaku = ThisWorkbook.Sheets("集約データ")

1 2	'⑥「集約データ」シートを変数に格納する Set shtSyuyaku = ThisWorkbook.Sheets("集約データ")

ここでは、「集約データ」シートを変数に格納しています。

⑦「集約データ」シートの開始行以下をクリアする

'⑦「集約データ」シートの開始行以下をクリアする
shtSyuyaku.Rows(nowRow & ":" & shtSyuyaku.Rows.Count).Clear

1 2	'⑦「集約データ」シートの開始行以下をクリアする shtSyuyaku.Rows(nowRow & ":" & shtSyuyaku.Rows.Count).Clear

ここでは、「集約データ」シートの集約データの開始行以下をクリアしています。

これは、何度も実行しても良いように、データ集約前に前に集約されたデータをクリアしています。

⑧FileSystemObjectを変数に格納する

'⑧FileSystemObjectを変数に格納する
Set fso = CreateObject("Scripting.FileSystemObject")

1 2	'⑧FileSystemObjectを変数に格納する Set fso = CreateObject("Scripting.FileSystemObject")

ここでは、FileSystemObjectを変数に格納しています。

FileSystemObjectは、フォルダやファイル操作するときに利用するライブラリです。

⑨対象フォルダに存在するファイル数分処理する

'⑨対象フォルダに存在するファイル数分処理する
For Each f In fso.GetFolder(folderPath).Files

1 2	'⑨対象フォルダに存在するファイル数分処理する For Each f In fso.GetFolder(folderPath).Files

ここでは、対象フォルダに存在するファイル数分を対象に処理を繰り返します。

⑩ファイルの拡張子を変数に格納する

'⑩ファイルの拡張子を変数に格納する
ext = LCase(fso.getextensionName(f.Name))

1 2	'⑩ファイルの拡張子を変数に格納する ext = LCase(fso.getextensionName(f.Name))

ここでは、取り出したファイルの拡張子を変数に格納しています。

対象とするファイルをEXCELに絞るために利用します。

⑪拡張子が「xlsx」のみ処理を行う

'⑪拡張子が「xlsx」のみ処理を行う
If ext = "xlsx" Then

1 2	'⑪拡張子が「xlsx」のみ処理を行う If ext = "xlsx" Then

ここでは、先程取り出したファイルの拡張子が「xlsx」が確認しています。

「xlsx」なら、データ集約を実行します。

⑫対象ブックを開く

'⑫対象ブックを開く
Set wb = Workbooks.Open(folderPath & "\" & f.Name)

1 2	'⑫対象ブックを開く Set wb = Workbooks.Open(folderPath & "\" & f.Name)

ここでは、取り出したEXCELを開いて変数に格納しています。

⑬対象シートを変数に格納する

'⑬対象シートを変数に格納する
Set shtTaisyo = wb.Sheets(shtName)

1 2	'⑬対象シートを変数に格納する Set shtTaisyo = wb.Sheets(shtName)

ここでは、先程開いたEXCELの集約対象のシートを変数に格納しています。

⑭対象シートの最終行を取得する

'⑭対象シートの最終行を取得する
lastRow = shtTaisyo.Cells(shtTaisyo.Rows.Count, 1).End(xlUp).Row

1 2	'⑭対象シートの最終行を取得する lastRow = shtTaisyo.Cells(shtTaisyo.Rows.Count, 1).End(xlUp).Row

ここでは、先程変数に格納したシートの最終行を変数に格納しています。

最終行を求めておくと、データ集約対象範囲を判別できるからです。

⑮対象シートの最終列を取得する

'⑮対象シートの最終列を取得する
lastCol = shtTaisyo.Cells(1, shtTaisyo.Columns.Count).End(xlToLeft).Column

1 2	'⑮対象シートの最終列を取得する lastCol = shtTaisyo.Cells(1, shtTaisyo.Columns.Count).End(xlToLeft).Column

ここでは、先程変数に格納したシートの最終列を変数に格納しています。

最終列を求めておくと、データ集約対象範囲を判別できるからです。

⑯対象シートの対象データを集約シートにコピーする

'⑯対象シートの対象データを集約シートにコピーする
shtTaisyo.Range(shtTaisyo.Cells(dataStartRow, 1), shtTaisyo.Cells(lastRow, lastCol)).Copy _
                (shtSyuyaku.Cells(nowRow, 1))

'⑯対象シートの対象データを集約シートにコピーする

shtTaisyo.Range(shtTaisyo.Cells(dataStartRow, 1), shtTaisyo.Cells(lastRow, lastCol)).Copy _

(shtSyuyaku.Cells(nowRow, 1))

ここでは、集約対象データを「集約データ」シートにコピーしています。

⑰次の貼り付け行を計算する

'⑰次の貼り付け行を計算する
nowRow = nowRow + lastRow - (dataStartRow - 1)

1 2	'⑰次の貼り付け行を計算する nowRow = nowRow + lastRow - (dataStartRow - 1)

ここでは、「集約データ」シートの次に貼り付けする行を計算しています。

次のコピーの準備をしています。

⑱対象ブックを閉じる

'⑱対象ブックを閉じる
wb.Close

1 2	'⑱対象ブックを閉じる wb.Close

ここでは、先程開いたEXCELファイルを閉じています。

⑲メモリを解放する

'⑲メモリを解放する
Set wb = Nothing

1 2	'⑲メモリを解放する Set wb = Nothing

ここでは、メモリを解放しています。

それでは、集約したいデータを用意して、1つに集約してみましょう。

以上です。

１．複数のEXCELデータを1つに集約するために必要なシートを作成する

「メイン」シートを作成する

「集約データ」シートを作成する

２．複数のEXCELデータを1つに集約する機能を記入する

３．複数のEXCELデータを1つに集約する機能の使い方

４．複数のEXCELデータを1つに集約するVBAプログラムの説明

①「メイン」シートを変数に格納する

②対象フォルダを変数に格納する

③シート名を変数に格納する

④データ開始行を変数に格納する

⑤集約データ開始行を変数に格納する

⑥「集約データ」シートを変数に格納する

⑦「集約データ」シートの開始行以下をクリアする

⑧FileSystemObjectを変数に格納する

⑨対象フォルダに存在するファイル数分処理する

⑩ファイルの拡張子を変数に格納する

⑪拡張子が「xlsx」のみ処理を行う

⑫対象ブックを開く

⑬対象シートを変数に格納する

⑭対象シートの最終行を取得する

⑮対象シートの最終列を取得する

⑯対象シートの対象データを集約シートにコピーする

⑰次の貼り付け行を計算する

⑱対象ブックを閉じる

⑲メモリを解放する

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル