この記事でできること
交通費データの一覧シートから、精算書テンプレートに自動転記＋合計計算するVBAマクロを作れる。コピペ → 実行まで5分。
対象読者：交通費精算を毎月Excelで手作業している人。VBAは初心者でOK。
—
どんな場面で使う？
- 月末の交通費精算ルーティン — 毎月、一覧表から精算書テンプレートに1行ずつコピペしている作業をボタン1つで終わらせたい場合
- 複数の外出先がある営業職の精算 — 1か月で20〜30件の交通費が発生する人は、手作業だと転記ミスや集計ミスが起きやすい。自動化すれば合計金額も正確
- 部署全体の精算書を取りまとめるとき — 各メンバーの交通費データを1つの一覧シートにまとめておけば、月を指定するだけで精算書が自動生成される
- 経理への提出をPDF+メールで行うとき — 実務版コードを使えば、精算書のPDF変換とOutlookでのメール下書き作成まで一気に完了する
- 精算フォーマットが決まっている会社 — 自社の精算書テンプレートに合わせて転記先のセル位置を調整するだけで、そのまま使える
—
導入
毎月の交通費精算、自分も手作業でやっていた時期がある。一覧表からテンプレートに1行ずつコピペして、合計を電卓で確認して、PDFにして提出。正直、月末のこの作業が地味にストレスだった。
VBAで自動化してからは、ボタン1つで精算書が完成するようになった。転記ミスもゼロになって、月末の作業時間が30分から2分に縮まった。
この記事で、同じ作業に時間を取られている人がサクッと自動化できるようになればうれしい。
前提条件
– Excel 2016以降 / Microsoft 365
– Windows 10/11
– ファイル保存形式：.xlsm（マクロ有効ブック）
– コードの貼り付け場所：標準モジュール
– 実行方法：マクロ実行（Alt + F8）またはボタン割り当て
—
完成イメージ（Before / After）
Before（手作業）
「交通費一覧」シートに日付・区間・金額を入力 → 「精算書」テンプレートに1行ずつ手でコピペ → 合計を手で計算 → PDFに変換 → メールに添付
After（VBA実行後）
「交通費一覧」シートのデータが「精算書」テンプレートに自動転記され、合計金額も自動計算。PDF出力・メール送信準備まで一気に完了。
【交通費一覧シート（データ入力用）】
A列 B列 C列 D列 E列
1 日付 出発駅 到着駅 片道/往復 金額
2 2026/3/1 東京 新宿 往復 400
3 2026/3/3 東京 渋谷 片道 200
4 2026/3/5 東京 横浜 往復 1,140
...
↓ VBA実行 ↓
【精算書シート（テンプレート）】
A列 B列 C列 D列 E列
5 日付 出発駅 到着駅 区分 金額
6 2026/3/1 東京 新宿 往復 400
7 2026/3/3 東京 渋谷 片道 200
8 2026/3/5 東京 横浜 往復 1,140
...
15 合計 ¥12,340
—
事前準備
シート構成
以下の2つのシートを用意する。
1. 「交通費一覧」シート（データ入力用）
| セル | 内容 |
|---|---|
| A1 | 日付 |
| B1 | 出発駅 |
| C1 | 到着駅 |
| D1 | 片道/往復 |
| E1 | 金額 |
| A2以降 | 実データを入力 |
2. 「精算書」シート（テンプレート）
| セル | 内容 |
|---|---|
| B2 | 精算書タイトル（例：交通費精算書） |
| B3 | 申請者名 |
| B4 | 申請日 |
| A5:E5 | ヘッダー行（日付・出発駅・到着駅・区分・金額） |
| A6以降 | データ転記エリア |
—
手順
- 交通費一覧シートとテンプレートシートを作成する（上記のシート構成を参照）
- Alt + F11 でVBE（コードを書く画面）を開く
- 左側のプロジェクトエクスプローラで 「挿入」→「標準モジュール」 を選択
- 下のコードをコピペして貼り付ける
- Alt + F8 で「CreateExpenseReport」を選んで実行
- 「精算書」シートにデータが転記されていることを確認
- Ctrl + S で .xlsm形式 で保存
—
基本コード（一覧 → テンプレート転記）
まずはこれで動く、最小限のコード。一覧シートのデータを精算書テンプレートに転記し、合計金額を計算する。
Sub CreateExpenseReport()
Dim wsData As Worksheet
Dim wsReport As Worksheet
Dim lastRow As Long
Dim i As Long
Dim writeRow As Long
Dim totalAmount As Double
' シートを設定
Set wsData = ThisWorkbook.Sheets("交通費一覧")
Set wsReport = ThisWorkbook.Sheets("精算書")
' 一覧シートの最終行を取得
lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
' データがなければ終了
If lastRow < 2 Then
MsgBox "交通費一覧にデータがありません。", vbExclamation
Exit Sub
End If
' 精算書の転記エリアをクリア（6行目以降）
wsReport.Range("A6:E" & wsReport.Rows.Count).ClearContents
' 転記開始行
writeRow = 6
totalAmount = 0
' 一覧データを1行ずつ転記
For i = 2 To lastRow
wsReport.Cells(writeRow, "A").Value = wsData.Cells(i, "A").Value ' 日付
wsReport.Cells(writeRow, "B").Value = wsData.Cells(i, "B").Value ' 出発駅
wsReport.Cells(writeRow, "C").Value = wsData.Cells(i, "C").Value ' 到着駅
wsReport.Cells(writeRow, "D").Value = wsData.Cells(i, "D").Value ' 片道/往復
wsReport.Cells(writeRow, "E").Value = wsData.Cells(i, "E").Value ' 金額
' 日付の書式設定
wsReport.Cells(writeRow, "A").NumberFormat = "yyyy/mm/dd"
' 金額の書式設定
wsReport.Cells(writeRow, "E").NumberFormat = "#,##0"
totalAmount = totalAmount + wsData.Cells(i, "E").Value
writeRow = writeRow + 1
Next i
' 合計行を書き込み
wsReport.Cells(writeRow, "D").Value = "合計"
wsReport.Cells(writeRow, "D").Font.Bold = True
wsReport.Cells(writeRow, "E").Value = totalAmount
wsReport.Cells(writeRow, "E").NumberFormat = "¥#,##0"
wsReport.Cells(writeRow, "E").Font.Bold = True
' 申請日を自動入力
wsReport.Range("B4").Value = "申請日：" & Format(Date, "yyyy年mm月dd日")
MsgBox "精算書を作成しました。" & vbCrLf & _
"件数：" & (lastRow - 1) & "件" & vbCrLf & _
"合計：" & Format(totalAmount, "¥#,##0"), vbInformation
End Sub
コードの処理フロー解説
基本コードは4つのブロックで構成されている。
- シート参照とデータ存在チェック —
ThisWorkbook.Sheets("交通費一覧")でデータ元シートを、Sheets("精算書")で転記先シートを取得する。ThisWorkbookを付けているのは、他のブックが開いていても確実にこのブックのシートを参照するため。データが0行ならメッセージを出して早期終了する。
- 転記エリアのクリアと初期化 —
ClearContentsで6行目以降を一括クリアしてから転記を始める。これを省略すると前回のデータが残ったまま新しいデータが追記されてしまい、精算金額が倍になるという致命的なミスにつながる。
- 1行ずつループで転記 — Forループで一覧データを1行ずつ精算書に写す。
NumberFormatを設定しているのは、日付がシリアル値（5桁の数字）で表示されたり、金額にカンマが付かなかったりする問題を防ぐため。セル転記の基本テクニックは セルの転記を自動化する方法 で詳しく解説している。
- 合計行の書き込みと申請日の設定 — ループ終了後の
writeRowが合計行の位置になる。合計金額はVBA側で計算したtotalAmountを直接書き込んでいるので、SUM関数を入れるより確実。
転記の基本パターンはセルの転記を自動化する方法と同じ考え方。Value = Value で値を移すだけなので、構文エラーが起きにくい。
—
実務版コード（月別自動集計＋承認欄＋PDF出力＋メール送信準備）
自分はこの実務版を使ってから、月末の精算作業を完全にルーティン化できた。月を指定するだけで、その月のデータだけ抽出して精算書を作り、PDFにしてメール下書きまで一気に準備してくれる。
Sub CreateExpenseReportFull()
'=== 実務版：月別集計＋承認欄＋PDF出力＋メール送信準備 ===
Dim wsData As Worksheet
Dim wsReport As Worksheet
Dim lastRow As Long
Dim i As Long
Dim writeRow As Long
Dim totalAmount As Double
Dim targetMonth As String
Dim dataDate As Date
Dim pdfPath As String
Dim recordCount As Long
' シートを設定
Set wsData = ThisWorkbook.Sheets("交通費一覧")
Set wsReport = ThisWorkbook.Sheets("精算書")
' ---- ここが追加：対象月の入力 ----
targetMonth = InputBox("精算対象の年月を入力してください（例：2026/03）", _
"対象月の指定", Format(Date, "yyyy/mm"))
If targetMonth = "" Then Exit Sub
' 入力値の検証
If Not targetMonth Like "####/##" Then
MsgBox "形式が正しくありません。「yyyy/mm」形式で入力してください。", vbExclamation
Exit Sub
End If
' 一覧シートの最終行を取得
lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then
MsgBox "交通費一覧にデータがありません。", vbExclamation
Exit Sub
End If
' 精算書の転記エリアをクリア
wsReport.Range("A6:E" & wsReport.Rows.Count).ClearContents
' ---- ここが追加：画面更新を停止して高速化 ----
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
writeRow = 6
totalAmount = 0
recordCount = 0
' ---- ここが追加：月別フィルタリング ----
For i = 2 To lastRow
' 日付データの検証
If IsDate(wsData.Cells(i, "A").Value) Then
dataDate = CDate(wsData.Cells(i, "A").Value)
' 対象月のデータだけ転記
If Format(dataDate, "yyyy/mm") = targetMonth Then
wsReport.Cells(writeRow, "A").Value = dataDate
wsReport.Cells(writeRow, "B").Value = wsData.Cells(i, "B").Value
wsReport.Cells(writeRow, "C").Value = wsData.Cells(i, "C").Value
wsReport.Cells(writeRow, "D").Value = wsData.Cells(i, "D").Value
wsReport.Cells(writeRow, "E").Value = wsData.Cells(i, "E").Value
wsReport.Cells(writeRow, "A").NumberFormat = "yyyy/mm/dd"
wsReport.Cells(writeRow, "E").NumberFormat = "#,##0"
' 金額の型チェック
If IsNumeric(wsData.Cells(i, "E").Value) Then
totalAmount = totalAmount + CDbl(wsData.Cells(i, "E").Value)
End If
writeRow = writeRow + 1
recordCount = recordCount + 1
End If
End If
Next i
' 対象月のデータがなかった場合
If recordCount = 0 Then
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox targetMonth & " のデータが見つかりませんでした。", vbExclamation
Exit Sub
End If
' 合計行
wsReport.Cells(writeRow, "D").Value = "合計"
wsReport.Cells(writeRow, "D").Font.Bold = True
wsReport.Cells(writeRow, "E").Value = totalAmount
wsReport.Cells(writeRow, "E").NumberFormat = "¥#,##0"
wsReport.Cells(writeRow, "E").Font.Bold = True
' 罫線を引く（データ範囲）
Dim dataRange As Range
Set dataRange = wsReport.Range("A5:E" & writeRow)
With dataRange.Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
' ヘッダー情報を設定
wsReport.Range("B2").Value = "交通費精算書"
wsReport.Range("B2").Font.Size = 14
wsReport.Range("B2").Font.Bold = True
wsReport.Range("B3").Value = "申請者名：________________"
wsReport.Range("B4").Value = "申請日：" & Format(Date, "yyyy年mm月dd日")
wsReport.Range("D3").Value = "対象期間：" & targetMonth
' ---- ここが追加：承認欄 ----
Dim approvalRow As Long
approvalRow = writeRow + 2
wsReport.Cells(approvalRow, "A").Value = "承認欄"
wsReport.Cells(approvalRow, "A").Font.Bold = True
wsReport.Cells(approvalRow + 1, "A").Value = "上長確認"
wsReport.Cells(approvalRow + 1, "B").Value = "署名：__________"
wsReport.Cells(approvalRow + 1, "C").Value = "日付：____/____/____"
wsReport.Cells(approvalRow + 2, "A").Value = "経理確認"
wsReport.Cells(approvalRow + 2, "B").Value = "署名：__________"
wsReport.Cells(approvalRow + 2, "C").Value = "日付：____/____/____"
' ---- ここが追加：PDF出力 ----
pdfPath = ThisWorkbook.Path & "\交通費精算書_" & _
Replace(targetMonth, "/", "") & ".pdf"
' 印刷範囲を設定
wsReport.PageSetup.PrintArea = wsReport.Range("A1:E" & approvalRow + 2).Address
wsReport.PageSetup.Orientation = xlPortrait
wsReport.PageSetup.FitToPagesWide = 1
wsReport.PageSetup.FitToPagesTall = 1
wsReport.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=pdfPath, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
OpenAfterPublish:=False
' 画面更新を復帰
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
' ---- ここが追加：メール送信準備（Outlook下書き作成） ----
Dim outlookApp As Object
Dim mailItem As Object
On Error Resume Next
Set outlookApp = CreateObject("Outlook.Application")
On Error GoTo 0
If Not outlookApp Is Nothing Then
Set mailItem = outlookApp.CreateItem(0) ' olMailItem = 0
With mailItem
.Subject = "【交通費精算】" & targetMonth & " 精算書提出"
.Body = "お疲れさまです。" & vbCrLf & vbCrLf & _
targetMonth & " の交通費精算書を提出いたします。" & vbCrLf & _
"件数：" & recordCount & "件" & vbCrLf & _
"合計金額：" & Format(totalAmount, "¥#,##0") & vbCrLf & vbCrLf & _
"ご確認のほどよろしくお願いいたします。"
.Attachments.Add pdfPath
.Display ' 下書きとして表示（送信はしない）
End With
Set mailItem = Nothing
Set outlookApp = Nothing
End If
MsgBox "精算書を作成しました。" & vbCrLf & _
"対象月：" & targetMonth & vbCrLf & _
"件数：" & recordCount & "件" & vbCrLf & _
"合計：" & Format(totalAmount, "¥#,##0") & vbCrLf & _
"PDF保存先：" & pdfPath, vbInformation
End Sub
コードの処理フロー解説（実務版）
実務版は基本コードに4つの機能を追加している。それぞれ「なぜ必要か」を理解しておくと、自社の運用に合わせた調整がしやすくなる。
- 月別フィルタリング —
InputBoxで対象年月を入力させ、Format(dataDate, "yyyy/mm")で日付を比較して該当月のデータだけを転記する。なぜ月指定にするかというと、交通費精算は通常月単位で提出するため。全期間のデータが混在した精算書を提出してしまうミスを防げる。入力値のフォーマットチェック（Like "####/##"）も入れて、不正な入力を弾いている。
- 承認欄の自動生成 — データ転記エリアの2行下に「上長確認」「経理確認」の署名欄を自動で配置する。承認欄の位置はデータ件数に応じて動的に変わるので、毎月データ量が異なっても精算書のレイアウトが崩れない。
- PDF出力 —
PrintAreaで印刷範囲をデータ+承認欄に合わせ、FitToPagesWide = 1でA4の幅に収まるように設定している。ファイル名に対象年月を含めるので、過去の精算書PDFを日付で検索しやすい。
- Outlookメール下書き作成 —
CreateObject("Outlook.Application")でOutlookを操作し、精算書PDFを添付したメールの下書きを自動作成する。.Displayで表示するだけなので自動送信はされない。Outlookがインストールされていない環境ではOn Error Resume Nextでスキップされるので安全。
画面更新の停止（ScreenUpdating = False）を入れているのは、大量データでも処理が一瞬で終わるようにするため。この高速化テクニックは画面更新・再計算を止めてマクロを高速化する方法で詳しく解説している。
精算書のテンプレート設計は請求書を自動作成する方法と同じ発想。一覧データ → テンプレート転記のパターンを覚えると、見積書でも納品書でも横展開できる。
注意：実行前に元データのバックアップを取ってください。
ClearContentsで精算書シートの既存データが消えます。
—
落とし穴
| # | 症状 | 原因 | 対策 |
|---|---|---|---|
| 1 | 「インデックスが有効範囲にありません」エラー | シート名が「交通費一覧」「精算書」と完全一致していない（全角/半角の違い含む） | シート名を正確に確認する。タブをダブルクリックして名前を再設定するのが確実 |
| 2 | 合計金額が0になる | 金額列に文字列として数値が入っている（CSVから貼り付けた場合に多い） | 金額列を選択 →「データ」→「区切り位置」→ 完了で数値に変換。またはコード内で CDbl() を使う（実務版は対応済み） |
| 3 | 日付が数字5桁で転記される | 日付のシリアル値がそのまま貼り付いている | 転記後に NumberFormat = "yyyy/mm/dd" を設定する（コードに組み込み済み） |
| 4 | PDFが白紙で出力される | 印刷範囲の設定が間違っている、またはデータが別のシートにある | PrintArea をデータ範囲に合わせて設定する。自分もこれで一度やらかして、上司に白紙のPDFを送ってしまったことがある。PrintArea の確認は必須 |
| 5 | Outlookが起動していないとメール作成でエラーになる | CreateObject("Outlook.Application") はOutlookがインストールされていないと失敗する |
実務版では On Error Resume Next でエラーを回避済み。Outlookがない環境ではメール作成部分をコメントアウトする |
| 6 | 月別フィルタで1件もヒットしない | 日付の入力形式が「2026年3月1日」のように文字列になっている | 一覧シートのA列を日付形式（yyyy/mm/dd）に統一する。IsDate() で事前チェックするのが安全 |
| 7 | Application.Calculation を戻し忘れて他のシートの計算が手動のままになる |
エラーで処理が中断し、xlCalculationAutomatic に戻す処理が実行されなかった |
On Error GoTo のエラーハンドラ内でも必ず Calculation = xlCalculationAutomatic を実行する。エラー処理応用のパターンを参考に |
VBAで交通費の合計計算がおかしいときの対処法
「精算書の合計金額が実際の合計と合わない」という場合、原因は金額列に文字列として数値が入っていることが多い。CSVやWebから貼り付けたデータは見た目は数字でも内部的に文字列型になっていることがあり、+ 演算子で加算すると文字列結合になってしまう。対策として、コード内で CDbl() を使って明示的に数値変換するか、一覧シートの金額列を選択して「データ」→「区切り位置」→「完了」で数値に変換する。自分もこれで合計が10倍近い金額になって焦ったことがある。
VBAで精算書の日付が自動入力でずれるときの対処法
「申請日や交通費の日付が1日ずれて表示される」という場合、原因はセルの表示形式と実際の日付データの不一致だ。Format(Date, "yyyy年mm月dd日") で文字列として書き込むと日付型ではなくなるため、後から日付として計算できなくなる点にも注意が必要。日付を正確に転記するには、転記元の値を CDate() で日付型に変換してから書き込み、転記先セルの NumberFormat を "yyyy/mm/dd" に設定するのが確実。シリアル値のまま表示される場合も NumberFormat を設定すれば解決する。
VBAで月別フィルタが1件もヒットしないときの対処法
「対象月を入力したのにデータが見つかりませんと表示される」という場合、原因は一覧シートの日付列が文字列形式（例：「2026年3月1日」）で入力されていて、IsDate() チェックや Format(CDate(...), "yyyy/mm") の比較が想定通りに動いていないことだ。一覧シートのA列を日付形式（yyyy/mm/dd）に統一し、入力規則で日付以外の値が入らないようにしておくと安全。InputBoxに入力する年月の形式も "yyyy/mm" であることを確認してほしい。
—
FAQ
Q1. 交通費の一覧に「備考」列を追加したい
A. 一覧シートにF列（備考）を追加し、コードの転記部分に以下を1行追加すればOK。
wsReport.Cells(writeRow, "F").Value = wsData.Cells(i, "F").Value ' 備考
精算書テンプレートのF列にもヘッダー「備考」を忘れずに追加する。
Q2. 複数人分の精算書をまとめて作りたい
A. 一覧シートに「申請者」列を追加し、Dictionaryで重複チェック・集計を高速化する方法のテクニックで申請者ごとにデータを分けて、ループで人数分の精算書を作成する方法がある。シートを人数分コピーするか、PDF個別出力で対応できる。
Q3. 定期区間（通勤費）を除外して精算したい
A. 一覧シートに「種別」列（交通費/通勤費）を追加し、転記のループ内で If wsData.Cells(i, "F").Value <> "通勤費" Then のように条件を入れれば、交通費だけを精算書に転記できる。
Q4. 金額に端数（小数点以下）が出る場合はどう処理する？
A. 数値の端数処理を正しく行う方法で解説している WorksheetFunction.Round を使うのが安全。VBA標準の Round は銀行丸め（偶数丸め）なので注意。精算書では WorksheetFunction.Round(totalAmount, 0) で整数に丸めるのが一般的。
Q5. マクロ実行をボタン化したい
A. マクロをボタン1つで実行する方法を参照。精算書シートにボタンを設置して CreateExpenseReportFull を割り当てると、ボタンクリックだけで精算書が完成する。自分はこの方法で経理担当にもマクロを使ってもらえるようになった。
—
まとめ
この記事では、交通費データの一覧からExcelの精算書テンプレートに自動転記＋合計計算するVBAマクロを作成した。
- 基本コード：一覧 → テンプレート転記＋合計計算
- 実務版：月別フィルタ＋承認欄＋PDF出力＋メール送信準備
帳票テンプレートへの転記パターンを覚えると、請求書・見積書・勤怠表など、他の業務帳票にもそのまま横展開できる。
自分はこの仕組みを導入してから、月末の精算作業が完全にルーティン化された。「一覧にデータを入力 → ボタンを押す → PDFを確認 → メールで送信」の流れが毎月同じ手順で完了するので、月末のストレスが大幅に減った。まずは基本コードで転記の動きを確認し、問題なければ実務版に切り替えてPDF出力+メール準備まで一気に自動化するのがおすすめ。交通費以外にも、セルの転記を自動化する方法で基本パターンを押さえておけば、出張精算書や立替精算書など、似たフォーマットの帳票にもすぐ応用できる。
—
次にやりたくなること
- 請求書も自動で作りたい → Excelで請求書を自動作成する方法で、顧客データ一覧から請求書テンプレートに自動転記する方法を解説している
- 見積書・納品書も同じ仕組みで自動化したい → 見積書・納品書をテンプレートから自動作成する方法で、連番管理＋PDF出力まで一気に自動化できる
- 勤怠管理表も月次で自動集計したい → Excelで勤怠管理表を自動化する方法で、出退勤データから月次集計表を自動作成する方法を解説している
—
コメント