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

この記事では、単票形式のEXCELデータを1つのEXCELシートに集約する方法について説明しています。

指定されたフォルダに存在するEXCELファイルすべてから、指定したセル位置の情報を取得して、1つのEXCELシートに集めることができます。

VBAを利用すれば、以下の作業をボタン1つ押すだけで簡単に終わらせることができます。

EXCELファイルを開く
集めたい情報が入力されているセルをコピーする
集約先のEXCELファイルを開いて、所定のセルに貼り付ける

しかも、集めたい情報が入力されているセルが複数ある場合でも、EXCELファイルが複数存在していても、ボタン1つ押すだけです。

単票形式なので複数セルを範囲指定してコピーできず、セル1つ1つをコピーする必要がありますが、大丈夫です、ボタン1つ押すだけです。

それでは、指定したフォルダに存在する複数の単票形式データを1つのEXCELシートに集約する機能を作成していきましょう。

困っている女性

従業員やお客様に記入してもらったアンケートの情報を、1つのEXCELシートに集約する作業がとっても大変なの・・・、わかる！？

困っている女性

そのアンケートが数十ファイル、数百ファイルあるから1つ1つ手作業でコピーして、貼り付けていく必要があるの。

困っている女性

しかも、一覧形式ではなく単票形式だから一気にコピーできなくて、1セルずつコピーして貼り付ける作業を繰り返す必要があるの・・・。

それなら、VBAを使えばボタン1つ押すだけで、大量のEXCELファイルから必要な情報を集めることができるよ。

解決する男性

EXCELファイルが存在するフォルダパスを指定すれば、そのフォルダに存在するEXCELファイルすべてを対象に1つのEXCELシートに集約してくれるよ。

解決する男性

困っている女性

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

◆似たような作業で、一覧形式の複数EXCELファイルの１つのシートを、1つのEXELファイルに集約する方法については、以下の記事を参照してください。

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

◆似たような作業で、一覧形式の複数EXCELファイルの複数シートを、1つのEXCELファイルに集約する方法については、以下の記事を参照してください。

2020-07-11【EXCEL：VBA】複数EXCELのシートを1つのEXCELファイルに集約したい（簡単2ステップ）

◆他には、複数のCSVファイルを1つのEXCELファイルに集約する方法については、以下の記事を参照してください。

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

単票形式のデータとは

単票形式とは、以下のように各項目が横並びの一覧になっておらず、各項目が点在している形式のことです。

単票形式データサンプル

源泉徴収票や年末調整申告書などをイメージしてもらうと、良いです。

単票形式のデータは、各項目が横並びになっていないので各項目が点在しており、一気にコピーすることができません。

手作業で集約する場合、上記サンプルで説明すると、B2セルをコピーして集約先シートに貼り付ける。

次にB3セルをコピーして集約先シートに貼り付ける。

というように必要な項目数分、コピーと貼り付けるを繰り返す必要があります。

以下は、集約後のイメージです。

1行分が1つのEXCELシートから取得したデータになっています。

集約後のサンプル

それでは、複数の単票データを1つのEXCELをシートに集約する機能の作成方法を、1つ1つ順番に説明していきます。

フォルダ指定欄、集約データ情報欄を準備する

まず、フォルダを指定する欄と集約するデータの各項目名とセル位置を記入する欄を作成します。

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

メインシートを作成する

「メイン」という名前のシートを作成します。
1行目に「フォルダパス」のタイトル行を作成します。
2行目に「フォルダパス」を入力する欄を作成します。
3行目に「集約データ情報」のタイトル行を作成します。
4行目に「項目名」、「行番号」、「列番号」のタイトル行を作成します。
5行目以降は、具体的に「項目名」、「行番号」、「列番号」を書き込む欄になります。

「項目名」は、集約先の項目タイトルとして利用します。

「行番号」、「列番号」は単票形式のシートのその項目データのセル位置を指定します。

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

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

各EXCELから集めたデータを集約するシートを用意しておきます。

このシートは、特になにも入力する必要はありません。

VBAでシート名を取得する機能を記入する

開発タブの「Visual Basic」をクリックして、「Microsoft Visual Basic for Applications」を起動します。

Microsoft Visual Basic for Applicationsを起動する

「Microsoft Visual Basic for Applications」にVBAプログラムを入力します。

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

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

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

Public Sub Main_Proc()
    Dim shtMain As Worksheet
    Dim shtData As Worksheet
    Dim MaxRow As Long
    Dim varSyuyaku As Variant
    Dim i As Long
    Dim fso As Object
    Dim f As Object
    Dim nowRow As Long
    Dim wb As Workbook
    
    '①参照するシートを変数に格納する
    Set shtMain = ThisWorkbook.Sheets("メイン")
    Set shtData = ThisWorkbook.Sheets("集約データ")
    
    '②「集約データ」シートをクリアする
    shtData.Cells.Clear
    
    '③集約データ情報の最終行を取得する
    MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row
    
    '④集約データ情報を取得する
    varSyuyaku = shtMain.Range(shtMain.Cells(5, 1), shtMain.Cells(MaxRow, 3))
    
    '⑤集約データ情報の項目名を「集約データ」シートにセットする
    For i = 1 To UBound(varSyuyaku)
        shtData.Cells(1, i) = varSyuyaku(i, 1)
    Next
    
    '⑥FileSystemObjectを変数に格納する
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    nowRow = 2
    
    '⑦指定されているフォルダに存在するファイル数分処理を繰り返す
    For Each f In fso.GetFolder(shtMain.Range("A2")).Files
    
        '⑧拡張子がxlsxのファイルのみ対象とする
        If LCase(fso.getextensionName(f.Name)) = "xlsx" Then
        
            '⑨EXCELファイルを開く
            Set wb = Workbooks.Open(shtMain.Range("A2") & "\" & f.Name)
            
            '⑩EXCELの1番左のシートから各データを取得して、「集約データ」シートにセットする
            For i = 1 To UBound(varSyuyaku)
                shtData.Cells(nowRow, i) = wb.Sheets(1).Cells(varSyuyaku(i, 2), varSyuyaku(i, 3))
            Next
 
            nowRow = nowRow + 1
            
            '⑪EXCELファイルを閉じる
            wb.Close
            
        End If
        
    Next
    
    '⑫ワークブックをクリアする
    Set wb = Nothing
    
    '⑬FileSystemObjectをクリアする
    Set fso = Nothing
    
    MsgBox "完了"
    
End Sub

Public Sub Main_Proc()

Dim shtMain As Worksheet

Dim shtData As Worksheet

Dim MaxRow As Long

Dim varSyuyaku As Variant

Dim i As Long

Dim fso As Object

Dim f As Object

Dim nowRow As Long

Dim wb As Workbook

'①参照するシートを変数に格納する

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

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

'②「集約データ」シートをクリアする

shtData.Cells.Clear

'③集約データ情報の最終行を取得する

MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row

'④集約データ情報を取得する

varSyuyaku = shtMain.Range(shtMain.Cells(5, 1), shtMain.Cells(MaxRow, 3))

'⑤集約データ情報の項目名を「集約データ」シートにセットする

For i = 1 To UBound(varSyuyaku)

shtData.Cells(1, i) = varSyuyaku(i, 1)

'⑥FileSystemObjectを変数に格納する

Set fso = CreateObject("Scripting.FileSystemObject")

nowRow = 2

'⑦指定されているフォルダに存在するファイル数分処理を繰り返す

For Each f In fso.GetFolder(shtMain.Range("A2")).Files

'⑧拡張子がxlsxのファイルのみ対象とする

If LCase(fso.getextensionName(f.Name)) = "xlsx" Then

'⑨EXCELファイルを開く

Set wb = Workbooks.Open(shtMain.Range("A2") & "\" & f.Name)

'⑩EXCELの1番左のシートから各データを取得して、「集約データ」シートにセットする

For i = 1 To UBound(varSyuyaku)

shtData.Cells(nowRow, i) = wb.Sheets(1).Cells(varSyuyaku(i, 2), varSyuyaku(i, 3))

nowRow = nowRow + 1

'⑪EXCELファイルを閉じる

wb.Close

End If

'⑫ワークブックをクリアする

Set wb = Nothing

'⑬FileSystemObjectをクリアする

Set fso = Nothing

MsgBox "完了"

End Sub

最後にEXCELを「◯◯◯.xlsm」というような感じでマクロ有効ブック形式で保存します。

単票データを1つのEXCELシートに集約する機能の使い方

それでは、単票データを1つのEXCELシートに集約する方法を説明します。

単票データを1つのEXCELシートに集約する方法

まず、A2セルにEXCELファイルが存在するフォルダパスを入力します。

次に、5行目以降に集めたい項目の情報として、「項目名」、「行番号」、「列番号」を入力します。

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

VBAプログラムを実行する

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

次に、「実行」ボタンをクリックして実行します。

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

しばらく待つと、指定したフォルダに存在するEXCELファイルから単票データを取得して、「集約データ」シートに一覧化されます。

単票データを集約した結果

1行目には、「メイン」シートに入力されている「項目名」が出力されます。

2行目以降には、各EXCELファイルから取得した情報が出力されます。

という感じで、複数のEXCELファイルに記入されている単票データを一覧化することが可能になりました。

集めた単票データを一覧化して、簡単に利用できます。

ちなみに、実際のフォルダは以下のようになっています。

実際のフォルダの様子

EXCELファイルが3つ存在しています。

ですから、先程の「集約データ」シートには3行のデータが出力されています。

単票データを集約するVBAプログラム（Main_Proc）の説明

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

①参照するシートを変数に格納する

'①参照するシートを変数に格納する
Set shtMain = ThisWorkbook.Sheets("メイン")
Set shtData = ThisWorkbook.Sheets("集約データ")

'①参照するシートを変数に格納する

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

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

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

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

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

②「集約データ」シートをクリアする

'②「集約データ」シートをクリアする
shtData.Cells.Clear

1 2	'②「集約データ」シートをクリアする shtData.Cells.Clear

ここでは、「集約データ」に入力されているデータをクリアしています。

何度も実行すると、前回実行時の集約データが残っています。

だから、次の集約処理を行う前にクリアする必要があります。

③集約データ情報の最終行を取得する

'③集約データ情報の最終行を取得する
MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row

1 2	'③集約データ情報の最終行を取得する MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row

ここでは、「メイン」シートの5行目以降に入力されている「集約データ情報」の最終行を取得しています。

具体的には、A列「項目名」が入力されている最終行を取得します。

このあとの処理で、集約データ情報を配列に格納するために行っています。

④集約データ情報を取得する

'④集約データ情報を取得する
varSyuyaku = shtMain.Range(shtMain.Cells(5, 1), shtMain.Cells(MaxRow, 3))

1 2	'④集約データ情報を取得する varSyuyaku = shtMain.Range(shtMain.Cells(5, 1), shtMain.Cells(MaxRow, 3))

ここでは、「メイン」シートに入力されている「集約データ情報」を配列に格納しています。

先程取得した「集約データ情報」の最終行を利用しています。

具体的には、5行目の1列目から最終行の3列目までを取得しています。

設定されている「項目名」、「行番号」、「列番号」を配列に格納します。

⑤集約データ情報の項目名を「集約データ」シートにセットする

'⑤集約データ情報の項目名を「集約データ」シートにセットする
For i = 1 To UBound(varSyuyaku)
  shtData.Cells(1, i) = varSyuyaku(i, 1)
Next

'⑤集約データ情報の項目名を「集約データ」シートにセットする

For i = 1 To UBound(varSyuyaku)

shtData.Cells(1, i) = varSyuyaku(i, 1)

ここでは、「集約データ」シートの1行目に「項目名」をセットしています。

先程取得した「集約データ情報」の配列に格納されている「項目名」を全て出力します。

「集約データ」シートのタイトル行を1行目に準備し、このあとの処理で取得する単票データは2行目以降にセットされます。

⑥FileSystemObjectを変数に格納する

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

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

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

「FileSystemObject」は、フォルダに存在するファイルを調べることに利用します。

⑦指定されているフォルダに存在するファイル数分処理を繰り返す

'⑦指定されているフォルダに存在するファイル数分処理を繰り返す
For Each f In fso.GetFolder(shtMain.Range("A2")).Files

1 2	'⑦指定されているフォルダに存在するファイル数分処理を繰り返す For Each f In fso.GetFolder(shtMain.Range("A2")).Files

ここでは、A2セルに入力されたフォルダに存在するファイル数分、以降の処理を繰り返します。

⑧拡張子がxlsxのファイルのみ対象とする

'⑧拡張子がxlsxのファイルのみ対象とする
If LCase(fso.getextensionName(f.Name)) = "xlsx" Then

1 2	'⑧拡張子がxlsxのファイルのみ対象とする If LCase(fso.getextensionName(f.Name)) = "xlsx" Then

ここでは、フォルダに存在するファイルの拡張子が「xlsx」か確認しています。

拡張子が「xlsx」のファイルのみを対象に、以降の処理を行います。

例えば、拡張子が「txt」なら、対象外となります。

⑨EXCELファイルを開く

'⑨EXCELファイルを開く
Set wb = Workbooks.Open(shtMain.Range("A2") & "\" & f.Name)

1 2	'⑨EXCELファイルを開く Set wb = Workbooks.Open(shtMain.Range("A2") & "\" & f.Name)

ここでは、フォルダに存在するEXCELファイルを開いています。

⑩EXCELの1番左のシートから各データを取得して、「集約データ」シートにセットする

'⑩EXCELの1番左のシートから各データを取得して、「集約データ」シートにセットする
For i = 1 To UBound(varSyuyaku)
  shtData.Cells(nowRow, i) = wb.Sheets(1).Cells(varSyuyaku(i, 2), varSyuyaku(i, 3))
Next

'⑩EXCELの1番左のシートから各データを取得して、「集約データ」シートにセットする

For i = 1 To UBound(varSyuyaku)

shtData.Cells(nowRow, i) = wb.Sheets(1).Cells(varSyuyaku(i, 2), varSyuyaku(i, 3))

ここでは、開いたEXCELファイルの1番左のシートから、各項目の値を取得して、「集約データ」シートに出力しています。

④で取得した「集約データ情報」が格納された配列を利用して、「行番号」、「列番号」を利用して各セルの値を取得します。

仮にEXCELファイルの複数シート存在していても、1番左のシートからしかデータは取得しませんので、ご注意ください。

⑪EXCELファイルを閉じる

'⑪EXCELファイルを閉じる
wb.Close

1 2	'⑪EXCELファイルを閉じる wb.Close

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

全ての集約データを「集約データ」シートに記入し終わると、EXCELファイルは閉じます。

⑫ワークブックをクリアする

'⑫ワークブックをクリアする
Set wb = Nothing

1 2	'⑫ワークブックをクリアする Set wb = Nothing

ここでは、使い終わったワークブックオブジェクトをクリアしています。

⑬FileSystemObjectをクリアする

'⑬FileSystemObjectをクリアする
Set fso = Nothing

1 2	'⑬FileSystemObjectをクリアする Set fso = Nothing

ここでは、使い終わったFileSystemObjectをクリアしています。

結果として、指定されたフォルダに存在するEXCELファイル全てから、単票データを取得し、「集約データ」シートに出力することができるようになりました。

それでは、このツールを作って、作業を効率化しましょう。

以上です。

単票形式のデータとは

フォルダ指定欄、集約データ情報欄を準備する

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

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

VBAでシート名を取得する機能を記入する

単票データを1つのEXCELシートに集約する機能の使い方

単票データを集約するVBAプログラム（Main_Proc）の説明

①参照するシートを変数に格納する

②「集約データ」シートをクリアする

③集約データ情報の最終行を取得する

④集約データ情報を取得する

⑤集約データ情報の項目名を「集約データ」シートにセットする

⑥FileSystemObjectを変数に格納する

⑦指定されているフォルダに存在するファイル数分処理を繰り返す

⑧拡張子がxlsxのファイルのみ対象とする

⑨EXCELファイルを開く

⑩EXCELの1番左のシートから各データを取得して、「集約データ」シートにセットする

⑪EXCELファイルを閉じる

⑫ワークブックをクリアする

⑬FileSystemObjectをクリアする

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

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