Last Updated on 2024年2月26日
はじめに
前回は前回の記事では、シート操作や文字設定の基本を学びました。今回は、VBAを使って実務に直結するより高度な技術に挑戦します。具体的には、「明細」シートのデータを基に合計数量と合計金額を計算し、「報告書」シートに自動で転記する方法と、受注日から報告書のタイトルを生成する方法を学びます。
完成イメージ
学べること
- 日付データの操作方法
- VBAでの最終行の自動判定方法
- シート間でのデータ転記方法
- 変数の使い方
- セル書式の設定方法
- With句の使い方
実践プログラム：データ集計と報告書作成
ステップ1: 明細データの準備
①シートを追加して、名前を「明細」にします。
②「明細」シートに以下のサンプルデータをコピーして貼り付けてください。
| 受注日 | 商品名 | 数量 | 単価 | 売上 |
|---|---|---|---|---|
| 2024/2/1 | 商品A | 10 | 500 | 5,000 |
| 2024/2/2 | 商品B | 20 | 250 | 5,000 |
| 2024/2/3 | 商品C | 15 | 400 | 6,000 |
| 2024/2/4 | 商品D | 5 | 800 | 4,000 |
| 2024/2/5 | 商品E | 12 | 600 | 7,200 |
ステップ2: 報告書シートの準備
①シートを追加して、名前を「報告書」にします。
②上の完成イメージ図を参考に、（処理前）のフォーマットを作成してください。
※面倒であれば、フォーマットは作成しなくても「報告書」シートさえあればプログラムは実行できます。
ステップ3: コードを作成
VBEに新規モジュールを作成し、以下のコードをコピペしてください。F8（Fn + F8）でステップ実行しながら、各行が何をしているかを確認してみましょう。
このコードでは「変数」を使用しています。 変数が初めての方はこちらの記事を参考にしてください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
'*----------------------------------------------------------------------* '* 処理名：CreateReportSheet_入門版 '* 概 要 ：「明細」シートの数量計・金額計を算出し、「報告書」シートへ転記する '*----------------------------------------------------------------------* Sub CreateReportSheet_入門版() Dim currentMonth As Long '明細の受注月を代入 Dim totalQuantity As Long '合計売上数を代入 Dim totalAmount As Double '合計売上金額を代入 Dim endRow As Long '明細シートの最終行を代入 '--- 「明細」シートのデータ処理 ----------------------------------------* ' 受注データの2行目から受注月を取得して変数に代入する currentMonth = Month(ThisWorkbook.Worksheets("明細").Cells(2, 1)) ' 明細シートの最終行を取得して変数に代入する endRow = ThisWorkbook.Worksheets("明細").Cells(Rows.Count, 1).End(xlUp).Row ' 数量合計を変数に代入する totalQuantity = Application.WorksheetFunction.Sum(ThisWorkbook.Worksheets("明細").Range("C2:C" & endRow)) ' 数量合計を明細行の下にカンマ付き書式で入力 ThisWorkbook.Worksheets("明細").Cells(endRow + 1, 3).Value = Format(totalQuantity, "#,##0 ;[赤]-#,##0 ") ' 売上合計を変数に代入する totalAmount = Application.WorksheetFunction.Sum(ThisWorkbook.Worksheets("明細").Range("E2:E" & endRow)) ' 売上合計を明細行の下にカンマ付き書式で入力 ThisWorkbook.Worksheets("明細").Cells(endRow + 1, 5).Value = Format(Val(totalAmount), "#,##0 ;[赤]-#,##0 ") '合計行に「合計」の文字を入力 ThisWorkbook.Worksheets("明細").Cells(endRow + 1, 1) = "合計" ' 合計行の文字を太字にする ThisWorkbook.Worksheets("明細").Rows(endRow + 1).Font.Bold = True ' 明細表全体を「CurrentRegion」で指定して罫線（格子線）を引く ThisWorkbook.Worksheets("明細").Cells(1, 1).CurrentRegion.Borders.LineStyle = True '--- 「報告書」シート作成処理 ----------------------------------------* '作成日を入力 ThisWorkbook.Worksheets("報告書").Range("G3").Value = Date 'タイトルを入力 ThisWorkbook.Worksheets("報告書").Range("D5").Value = currentMonth & "月 売上報告書" '売上合計数を入力 ThisWorkbook.Worksheets("報告書").Range("D9").Value = totalQuantity '売上合計金額を入力 ThisWorkbook.Worksheets("報告書").Range("D11").Value = totalAmount '処理終了メッセージを表示 MsgBox ("処理が終わりました。"), vbInformation, "報告書作成" End Sub |
前述のコードは、特に入門者にとっては理解しやすい構造をしていますが、実際のプログラミングでは冗長性を避け、より効率的なコードを書くことが求められます。以下に示すコードは、より実務に即した形で、同じ処理をより簡潔に実行する方法を示しています。初心者の方は、まずは基本的なコードの流れを理解し、徐々にこのような簡潔なコードが書けるように挑戦してみましょう。音声付き動画での解説も予定していますが、まずはテキストの説明とコードを通じて基本を把握してください。音声付き動画は完成次第、この記事を更新し、新たな学習リソースとしてご紹介します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
*----------------------------------------------------------------------* '* 処理名：CreateReportSheet_実用版 '* 概 要 ：「明細」シートの数量計・金額計を算出し、「報告書」シートへ転記する '*----------------------------------------------------------------------* Sub CreateReportSheet_実用版() Dim currentMonth As Long '明細の受注月を代入 Dim totalQuantity As Long '合計売上数を代入 Dim totalAmount As Double '合計売上金額を代入 Dim endRow As Long '明細シートの最終行を代入 '--- 「明細」シートのデータ処理 ----------------------------------------* With ThisWorkbook.Worksheets("明細") ' 受注データの１行目から受注月を取得して変数に代入する currentMonth = Month(.Cells(2, 1)) ' 明細シートの最終行を取得する endRow = .Cells(Rows.Count, 1).End(xlUp).Row ' 数量合計を変数に代入する totalQuantity = Application.WorksheetFunction.Sum(.Range("C2:C" & endRow)) ' 数量合計を明細行の下にカンマ付き書式で入力 .Cells(endRow + 1, 3).Value = Format(totalQuantity, "#,##0 ;[赤]-#,##0 ") ' 売上合計を変数に代入する totalAmount = Application.WorksheetFunction.Sum(.Range("E2:E" & endRow)) ' 売上合計を明細行の下にカンマ付き書式で入力 .Cells(endRow + 1, 5).Value = Format(Val(totalAmount), "#,##0 ;[赤]-#,##0 ") '合計行に「合計」の文字を入力 .Cells(endRow + 1, 1) = "合計" ' 合計行の文字を太字にする .Rows(endRow + 1).Font.Bold = True '明細表全体を「CurrentRegion」で指定して罫線（格子線）を引く .Cells(1, 1).CurrentRegion.Borders.LineStyle = True End With '--- 「報告書」シート作成処理 ----------------------------------------* With ThisWorkbook.Worksheets("報告書") '作成日を入力 .Range("G3").Value = Date 'タイトルを入力 .Range("D5").Value = currentMonth & "月 売上報告書" '売上合計数を入力 .Range("D9").Value = ThisWorkbook.Worksheets("明細").Cells(endRow + 1, 3) '売上合計金額を入力 .Range("D11").Value = ThisWorkbook.Worksheets("明細").Cells(endRow + 1, 5) End With '処理終了メッセージを表示 MsgBox ("処理が終わりました。"), vbInformation, "報告書作成" End Sub |
4. DXの観点
Excel関数で同様の操作を行う場合、データ範囲が変動すると手動で範囲を調整する必要があります。また、複数のシート間でデータをリンクさせる際には、関数をコピー＆ペーストする等の手間が発生します。VBAを使用することで、これらの操作を自動化し、エラーのリスクを減らしながら効率を大幅に向上させることができます。
まとめ
この記事では、VBAを使って「明細」シートのデータから合計数量と合計金額を計算し、「報告書」シートに自動で転記する方法を学びました。また、受注日から動的に報告書のタイトルを生成する方法も紹介しました。これらの技術は、実務での報告書作成など、多くの場面で役立つでしょう。次回は、さらに複雑なデータ処理に挑戦していきます。
