【転記をなくす③】マクロ編|複数ファイルの集計をボタン1つで自動化

エクセル(excel)

「集約」ボタンを押すだけで、全支店のデータが1つになる

②パワークエリ編では、フォルダに入れた支店ごとのファイルを「すべて更新」で1つの表にまとめました。この記事では、まったく同じ結果をマクロで作ります。

コードはコピーして貼り付けるだけです。書き換えるのはフォルダの場所など、先頭の3行だけです。

使う前(支店ごとにバラバラ)

売上データ(フォルダ)
├ 東京支店_202610.xlsx
├ 大阪支店_202610.xlsx
└ 福岡支店_202610.xlsx

どのファイルにも「売上」シートがあり、次の形でデータが入っています。

使った後(集計ブックの「集約」シート)

ボタンを押すと、全支店のデータが縦につながります。A列には、ファイル名から取り出した支店名が入ります。

ボタンを押すたびに、前回の結果を消してから作り直します。何度押してもデータが二重になることはありません。

用意するもの

始める前に、次の4つがそろっているか確認しましょう。

  • Windows版のExcel(デスクトップ版)。Web版のExcelではマクロを実行できません
  • 支店ごとのファイルを入れたフォルダ(例:C:\売上データ)
  • どのファイルも「同じシート名」「同じ列の並び」「見出しは1行目」になっている
  • ファイル名が「支店名_年月.xlsx」の形になっている(支店名を取り出すため)

フォルダやファイルのルールは、②パワークエリ編と同じです。すでに②で準備したフォルダがあれば、そのまま使えます。

手順

STEP1:集計用のブックを作る

  1. Excelで新しいブックを開きます。(すでにデータがあるファイルではありません)
  2. シート名(画面下の「Sheet1」)をダブルクリックして、「集約」に変えます。
  3. A1~E1セルに、「支店」「日付」「商品コード」「数量」「金額」と見出しを入力します。
  4. B列を選んでCtrl + 1を押し、表示形式を「日付」にしておきます(日付が数字で表示されるのを防ぐため)。

注意:この集計ブックは、「売上データ」フォルダの外に保存してください。

STEP2:「開発」タブを表示する

初めてマクロを使うときだけ必要な設定です。すでに「開発」タブがある場合はSTEP2は不要です。

  1. 「ファイル」→「オプション」を開きます。
  2. 左の一覧から「リボンのユーザー設定」を選びます。
  3. 右側の一覧で「開発」にチェックを入れ、OKを押します。

STEP3:コードを貼り付ける

  1. 「開発」→「Visual Basic」(Alt + F11)を押して、VBAの画面(VBE)を開きます。
  2. メニューの「挿入」→「標準モジュール」を選びます。
  3. 右側に出た白い画面に、次のコードをすべてコピーして貼り付けます。
Option Explicit

Sub 売上データを集約()
    ' ===== 設定(ここだけ自分の環境に合わせて書き換える)=====
    Const SRC_FOLDER As String = "C:\売上データ\"  ' 支店ファイルを入れたフォルダ(最後の\を忘れずに)
    Const SRC_SHEET As String = "売上"              ' 各ファイルの読み込むシート名
    Const OUT_SHEET As String = "集約"              ' 結果を出すシート名
    ' ==========================================================

    Dim wsOut As Worksheet, wb As Workbook, wsSrc As Worksheet
    Dim fileName As String, branch As String
    Dim lastRow As Long, outRow As Long, n As Long, fileCount As Long

    Set wsOut = ThisWorkbook.Worksheets(OUT_SHEET)

    ' (1) 前回の結果を消す(見出しの1行目は残す)
    wsOut.Range("A2:E" & wsOut.Rows.Count).ClearContents
    outRow = 2

    Application.ScreenUpdating = False

    ' (2) フォルダ内の .xlsx を順に開く
    fileName = Dir(SRC_FOLDER & "*.xlsx")
    Do While fileName <> ""
        If Left(fileName, 2) <> "~$" Then   ' 開いている途中の一時ファイルは飛ばす
            Set wb = Workbooks.Open(SRC_FOLDER & fileName, ReadOnly:=True)
            Set wsSrc = wb.Worksheets(SRC_SHEET)

            ' (3) データの最終行を調べる
            lastRow = wsSrc.Cells(wsSrc.Rows.Count, "A").End(xlUp).Row
            n = lastRow - 1   ' 見出しを除いたデータの行数

            If n > 0 Then
                ' (4) ファイル名から支店名を取り出す(東京支店_202610.xlsx → 東京支店)
                branch = Split(fileName, "_")(0)

                ' (5) A列に支店名、B~E列にデータを「値」で書き込む
                wsOut.Cells(outRow, "A").Resize(n, 1).Value = branch
                wsOut.Cells(outRow, "B").Resize(n, 4).Value = wsSrc.Range("A2:D" & lastRow).Value
                outRow = outRow + n
            End If

            wb.Close SaveChanges:=False   ' 元のファイルは保存せずに閉じる
            fileCount = fileCount + 1
        End If
        fileName = Dir()
    Loop

    Application.ScreenUpdating = True
    MsgBox fileCount & "件のファイルから、" & (outRow - 2) & "行を集約しました。", vbInformation
End Sub

STEP4:設定の3行を書き換える

コードの先頭にある「設定」の3行を、自分の環境に合わせて書き換えます。それ以外の行は触らなくて大丈夫です。

書き換える場所入れるもの例
SRC_FOLDER支店ファイルを入れたフォルダの場所。最後に「\」を付けるC:\共有\売上データ\
SRC_SHEET各ファイルの、読み込むシート名売上
OUT_SHEET結果を出すシート名(STEP1で作ったもの)集約

フォルダの場所の調べ方:エクスプローラーでフォルダを開き、上部のアドレスバーをクリックすると「C:\…」の形で表示されます。それをコピーして、最後に「\」を足します。

STEP5:マクロ有効ブックで保存し、ボタンを置く

  1. VBEを閉じてExcelに戻り、F12(名前を付けて保存)を押します。
  2. 「ファイルの種類」を「Excelマクロ有効ブック(*.xlsm)」にして保存します。(.xlsxで保存するとコードが消えます)
  3. 「集約」シートで、「開発」タブ→「挿入」→「フォームコントロール」の「ボタン」を選びます。
  4. 見出しの右側(G1セルあたり)でドラッグして、ボタンを描きます。
  5. 「マクロの登録」画面で「売上データを集約」を選び、OKを押します。
  6. ボタンを右クリック→「テキストの編集」で、文字を「集約する」に変えます。

STEP6:実行して結果を確かめる

  1. 「集約する」ボタンを押します。
  2. 「3件のファイルから、○行を集約しました。」と表示されれば成功です。
  3. 各支店ファイルの行数の合計と、集約された行数が合っているか確認しましょう。

翌月からの使い方

毎月の作業は、3つだけです。

  1. 「売上データ」フォルダの先月分のファイルを、別のフォルダ(例:「過去分」)に移します。
  2. 今月届いたファイルを、「売上データ」フォルダに入れます。
  3. 集計ブックを開いて、「集約する」ボタンを押します。

フォルダにあるファイルをすべて読み込む仕組みなので、先月分も含めて累計したい場合は、手順1を飛ばしてください。支店が増えても、ファイルをフォルダに入れるだけで集約されます。

集約したデータは、ピボットテーブルや①関数編のSUMIFSで、支店別・商品別に集計できます。

ぜひ一度お試しください。

コメント

コメント

-->
タイトルとURLをコピーしました