VBA｜同じフォルダ内にあるすべてのcsvファイルに同じ処理を行う

数値データを毎日ダウンロードして集計を行う、そんな定型業務をこれまで行っていました。単純作業のわりに時間を奪われるので自動化できないかな？と思いコーディングしてみました。

やりたいこと

採用サイトのデータを集計しようと思います。データは日別のcsvファイルに格納されています。

「データ取得用マクロ.xlsm」では各csvファイルを元に、日付（A列）×職種（B列）×勤務地（C列）のペアが重複しないように各数値を集計したいと思います。

csvファイルの中身はこちら。職種（B列）×勤務地（C列）のペアごとに数値を出したいのですが、同じペアが複数出てきているため、重複の排除が必要です。

コード

最終形はこちらです。

Sub データ整形()
Dim purofession_list(), address_list(), mix_list() As String
Dim input_sheet, output_sheet
Dim FirstRow_output, LastRow_input, LastRow_output, LastRow_output_n, Title, Name
Dim mix_range As Range
Dim i, j, x, y As Long
Dim Wb As Workbook
Dim FolderPass, Output_Workbook
Dim 年, 月, 日
Dim 職種名, 勤務地, 表示回数, クリック数, クリック率, 平均CPC, 合計費用 As Long

FolderPass = ActiveWorkbook.Path
Set output_sheet = Worksheets("数値取得")

Output_Workbook = Dir(FolderPass & "" & "*.csv"

Do While Output_Workbook <> ""
  Workbooks.Open (FolderPass & "\" & Output_Workbook)
  Set Wb = Workbooks(Output_Workbook)
  Set input_sheet = Wb.Worksheets(Left(Output_Workbook, 31))
  
  年 = Mid(Output_Workbook, 18, 4)
  月 = Mid(Output_Workbook, 23, 2)
  日 = Mid(Output_Workbook, 26, 2)
  
  FirstRow_output = output_sheet.Cells(1, 1).End(xlDown).Row + 1
  LastRow_input = input_sheet.Cells(1, 1).End(xlDown).Row
  
  j = 0
  For y = 4 To LastRow_input
    
    Key = Str(DateSerial(年, 月, 日)) + "|" + input_sheet.Cells(y, 2) + input_sheet.Cells(y, 3)
    input_sheet.Range("AF" & y).Value = Key
    
    LastRow_output = output_sheet.Cells(1, 1).End(xlDown).Row
    Set mix_range = output_sheet.Range("I1:I" & LastRow_output).Find(What:=input_sheet.Range("AF" & y), LookAt:=xlWhole)
    
    If mix_range Is Nothing Then
        ReDim Preserve purofession_list(j)
        ReDim Preserve address_list(j)
        ReDim Preserve mix_list(j)

        purofession_list(j) = input_sheet.Cells(y, 2)
        address_list(j) = input_sheet.Cells(y, 3)
        mix_list(j) = Key

        output_sheet.Cells(LastRow_output + 1, 1).Value = DateSerial(年, 月, 日)
        output_sheet.Cells(LastRow_output + 1, 2).Value = purofession_list(j)
        output_sheet.Cells(LastRow_output + 1, 3).Value = address_list(j)
        output_sheet.Cells(LastRow_output + 1, 9).Value = mix_list(j)
        
        j = j + 1
    End If
    
    Next y
  
    LastRow_output = output_sheet.Cells(1, 1).End(xlDown).Row
    For i = FirstRow_output To LastRow_output
        LastRow_output_n = output_sheet.Cells(1, 4).End(xlDown).Row
        
        表示回数 = WorksheetFunction.SumIf(input_sheet.Range("AF4:AF" & LastRow_input), output_sheet.Cells(i, 9), input_sheet.Range("J4:J" & LastRow_input))
        クリック数 = WorksheetFunction.SumIf(input_sheet.Range("AF4:AF" & LastRow_input), output_sheet.Cells(i, 9), input_sheet.Range("M4:M" & LastRow_input))
        合計費用 = WorksheetFunction.SumIf(input_sheet.Range("AF4:AF" & LastRow_input), output_sheet.Cells(i, 9), input_sheet.Range("AE4:AE" & LastRow_input))
        
        If 表示回数 = 0 Then
            クリック率 = 0
        Else
            クリック率 = Round(クリック数 / 表示回数, 3)
        End If
        
        If クリック数 = 0 Then
            クリック数 = 0
        Else
            平均CPC = Round(合計費用 / クリック数, 1)
        End If
        
        output_sheet.Cells(LastRow_output_n + 1, 4).Value = 表示回数
        output_sheet.Cells(LastRow_output_n + 1, 5).Value = クリック数
        output_sheet.Cells(LastRow_output_n + 1, 6).Value = クリック率
        output_sheet.Cells(LastRow_output_n + 1, 7).Value = 平均CPC
        output_sheet.Cells(LastRow_output_n + 1, 8).Value = 合計費用
        
    Next i
    
    Output_Workbook = Dir()
    Wb.Close SaveChanges:=False
Loop

End Sub

解説

FolderPass = ActiveWorkbook.Path

「データ取得用マクロ.xlsm」が格納されているフォルダパスを取得します。

Set output_sheet = Worksheets("数値取得")

集計結果を書き込むシートを定義します。

Output_Workbook = Dir(FolderPass & "" & "*.csv")

同じフォルダに格納されているcsvファイルをDirで探しています。複数ファイルが格納されていたり、ファイル名が不明な場合はワイルドカード「＊」を使います。

変数＝Dir（"ファイルパス"）
Do While　変数　＜＞　””
　　　処理
　　Output_Workbook = Dir()
    Wb.Close 
Loop

複数ファイルに対して処理を繰り返す場合は上記の通り記載します。

  年 = Mid(Output_Workbook, 18, 4)
  月 = Mid(Output_Workbook, 23, 2)
  日 = Mid(Output_Workbook, 26, 2)

ここではファイル名から日付を取得しています。
Mid()は対象文字列から特定の文字を抜き出すときに使用します。
Mid(対象文字列, 開始位置, 文字数)
（例）
Mid（”あいうえお", 4, 2)　⇒　"えお"を返す
Mid（”あいうえお", 2, 3)　⇒　"いうえ"を返す

  FirstRow_output = output_sheet.Cells(1, 1).End(xlDown).Row + 1
  LastRow_input = input_sheet.Cells(1, 1).End(xlDown).Row

「FirstRow_output」は「データ取得用マクロ.xlsm」最下行から一行下の行数を示しています。
「LastRow_input」は各csvファイルの最下行を取得しています。

    Key = Str(DateSerial(年, 月, 日)) + "|" + input_sheet.Cells(y, 2) + input_sheet.Cells(y, 3)
    input_sheet.Range("AF" & y).Value = Key

「Key」には各行の日付・職種・勤務地の文字列を格納して、重複値を見つけるために用います。

    LastRow_output = output_sheet.Cells(1, 1).End(xlDown).Row
    Set mix_range = output_sheet.Range("I1:I" & LastRow_output).Find(What:=input_sheet.Range("AF" & y), LookAt:=xlWhole)
　　
　　If mix_range Is Nothing Then
        ReDim Preserve purofession_list(j)
        ReDim Preserve address_list(j)
        ReDim Preserve mix_list(j)

        purofession_list(j) = input_sheet.Cells(y, 2)
        address_list(j) = input_sheet.Cells(y, 3)
        mix_list(j) = Key

        output_sheet.Cells(LastRow_output + 1, 1).Value = DateSerial(年, 月, 日)
        output_sheet.Cells(LastRow_output + 1, 2).Value = purofession_list(j)
        output_sheet.Cells(LastRow_output + 1, 3).Value = address_list(j)
        output_sheet.Cells(LastRow_output + 1, 9).Value = mix_list(j)
        
        j = j + 1
    End If

「mix_range」には重複値があった場合に値が入るため、If文の中では「mix_range」がNothingであった場合のみIf文の中の処理を実行するように指定しています。
これで日付・職種・勤務地の前ペアがダブりなく抽出できました。

    For i = FirstRow_output To LastRow_output
        LastRow_output_n = output_sheet.Cells(1, 4).End(xlDown).Row
        
        表示回数 = WorksheetFunction.SumIf(input_sheet.Range("AF4:AF" & LastRow_input), output_sheet.Cells(i, 9), input_sheet.Range("J4:J" & LastRow_input))
        クリック数 = WorksheetFunction.SumIf(input_sheet.Range("AF4:AF" & LastRow_input), output_sheet.Cells(i, 9), input_sheet.Range("M4:M" & LastRow_input))
        合計費用 = WorksheetFunction.SumIf(input_sheet.Range("AF4:AF" & LastRow_input), output_sheet.Cells(i, 9), input_sheet.Range("AE4:AE" & LastRow_input))
        
        If 表示回数 = 0 Then
            クリック率 = 0
        Else
            クリック率 = Round(クリック数 / 表示回数, 3)
        End If
        
        If クリック数 = 0 Then
            クリック数 = 0
        Else
            平均CPC = Round(合計費用 / クリック数, 1)
        End If
        
        output_sheet.Cells(LastRow_output_n + 1, 4).Value = 表示回数
        output_sheet.Cells(LastRow_output_n + 1, 5).Value = クリック数
        output_sheet.Cells(LastRow_output_n + 1, 6).Value = クリック率
        output_sheet.Cells(LastRow_output_n + 1, 7).Value = 平均CPC
        output_sheet.Cells(LastRow_output_n + 1, 8).Value = 合計費用
        
    Next i

続いて、各数値を集計しています。桁数の大きい数値を扱う場合はInteger型だとエラーが起こる場合がある為、Long型を指定するのが良いです。
表示回数やクリック数をIf文で記載しているのは分母に0が入ると、エラーが出るためです。

    Output_Workbook = Dir()
    Wb.Close SaveChanges:=False

csvファイルも編集しているため、「SaveChanges:=False」で編集内容を保存せずファイルを閉じるように指定します。

毎週1～2時間かけていた作業がワンクリックで処理できるようになりました！

noteをご覧いただきありがとうございました。