Excel VBAとピボットテーブルで-消込業務-を効率化！経理処理を5秒で実現しよう

-消込業務-

経理-消込業務-の課題
ピボットテーブルを使った-消込業務-
VBAで自動化する

経理-消込業務-の課題

経理消込業務は、企業の財務管理において重要なプロセスであり、正確かつ効率的に行うことが求められます。しかし、現実には多くの課題が存在し、経理担当者にとって時間と労力がかかる作業となっています。

まず、消込業務は繰り返し行われる作業であり、手動で行う場合は非常に時間がかかります。特に、多くの取引先があり、請求書や入出金データが大量にある場合、データの整理や消込の確認に多くの時間を割かなければならず、他の業務に支障をきたすことがあります。

また、消込業務の中で発生するエラーや不整合が、将来的な財務上の問題を引き起こす可能性があります。請求書や入出金データの入力ミスや、消込の際のミスマッチなど、人為的なミスが原因で未消込のまま放置されることがあるため、定期的なチェックが必要です。

さらに、消込業務は複数のシステムやフォーマットを扱うことが一般的で、データの互換性が低い場合、システム間でのデータのやり取りが困難になります。このため、データのクレンジングや整形が必要となり、作業の効率が低下することがあります。

これらの課題を解決するために、経理消込業務の効率化が求められています。効率化を図るためには、Excel VBAやピボットテーブルなどのツールを活用して、データの整理や消込の確認、レポーティングを自動化することが有効です。このような効率化の取り組みにより、経理担当者は他の重要な業務に注力し、企業の財務管理の質を向上させることができます。

ピボットテーブルを使った-消込業務-

ピボットテーブルを使って消込業務を行う場合、以下の手順を参考にしてください。

未払金データと入出金明細データをまとめた新しいシートを作成します。
未払金データの各行に「未払金」というフラグを追加し、入出金明細データの各行に「預金」というフラグを追加します。

未払金データ

銀行の入出金明細

以下のように未払金データの下に入出金明細データの一部を貼り付ける
新しいシートにピボットテーブルを挿入します。データ範囲として、まとめた未払金データと入出金明細データを選択します。
ピボットテーブルの行フィールドに会社名を追加し、列フィールドにフラグ（未払金・預金）を追加します。値フィールドに金額を設定し、それぞれの金額を合計します。
従来のピボットテーブルレイアウトに設定し、会社別に未払金および預金の金額が表示されるようにします。

以下のような感じのものが作成されます。
このピボットテーブルをもとに、未払金と預金の差額を計算し、それを別の列に表示します。差額が0の場合、消込が完了したと判断できます。
未払金・預金の差額が分かるように、ピボットテーブルを適宜更新し、経理担当者が容易に確認できるようにします。

この手順を使って、Excelのピボットテーブル機能を利用して消込業務を行うことができます。手作業で業務を効率化することが可能です。

VBAで自動化する

Excelファイルを開きます。
「シートの追加」をクリックして、未払金データシートを追加して以下のようなデータを作成します。
「シートの追加」をクリックして、入出金明細シートを追加して以下のようなデータを作成します。
「開発」タブを表示します。開発タブが表示されていない場合は、Excelのオプションで「開発」タブを有効にする必要があります。
「Visual Basic」をクリックして、Visual Basicエディタを開きます。
プロジェクトエクスプローラーウィンドウで、VBAProject（ブック名）を右クリックし、「挿入」→「モジュール」をクリックします。
新しいモジュールが追加され、コードウィンドウが表示されます。
下のコードをコピーして、新しいモジュールのコードウィンドウに貼り付けます。
VBAを実行するとピボットテーブルとピボットテーブルの値貼り付けしたシートを自動で生成します。５秒足らずで消込業務が実施されます。

Sub UpdateUnpaidAmountsAndCreatePivotTable()

    ' シートの定義
    Dim unpaidDataSheet As Worksheet
    Dim paymentDetailsSheet As Worksheet
    Set unpaidDataSheet = ThisWorkbook.Worksheets("未払金データ")
    Set paymentDetailsSheet = ThisWorkbook.Worksheets("入出金明細")

    ' ピボットテーブル用のシートを追加
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("PivotTable").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Dim pivotTableSheet As Worksheet
    Set pivotTableSheet = ThisWorkbook.Worksheets.Add
    pivotTableSheet.Name = "PivotTable"

    ' 入出金明細シートの最終行を取得
    Dim lastRowPaymentDetails As Long
    lastRowPaymentDetails = paymentDetailsSheet.Cells(Rows.Count, 1).End(xlUp).Row

    ' 未払金データシートの最終行を取得
    Dim lastRowUnpaidData As Long
    lastRowUnpaidData = unpaidDataSheet.Cells(Rows.Count, 1).End(xlUp).Row

    ' 未払金データシートのD列に「フラグ」というヘッダを表示
    unpaidDataSheet.Cells(1, 4).Value = "フラグ"

    ' 未払金データの各行のD列に「未払金」と表示
    Dim i As Long
    For i = 2 To lastRowUnpaidData
        unpaidDataSheet.Cells(i, 4).Value = "未払金"
    Next i

    ' 入出金明細データを繰り返し処理
    For i = 2 To lastRowPaymentDetails ' 1行目はヘッダーのため、2行目から処理を開始

        ' 入出金明細の会社名と金額を取得
        Dim companyName As String
        Dim paymentAmount As Double
        companyName = paymentDetailsSheet.Cells(i, 3).Value
        paymentAmount = paymentDetailsSheet.Cells(i, 5).Value

        ' 未払金データに入出金明細の会社名と金額を追加
        unpaidDataSheet.Cells(lastRowUnpaidData + 1, 2).Value = companyName
        unpaidDataSheet.Cells(lastRowUnpaidData + 1, 3).Value = paymentAmount

        ' 入出金明細データの行に「預金」と表示
        unpaidDataSheet.Cells(lastRowUnpaidData + 1, 4).Value = "預金"

        ' 最終行を更新
        lastRowUnpaidData = lastRowUnpaidData + 1

    Next i

     ' ピボットテーブルの作成
    Dim pivotCache As pivotCache
    Dim pivotTable As pivotTable
    Dim pivotRange As Range

    Set pivotRange = unpaidDataSheet.Range("B1:D" & lastRowUnpaidData)
    Set pivotCache = ThisWorkbook.PivotCaches.Create(xlDatabase, pivotRange)
    Set pivotTable = pivotCache.CreatePivotTable(pivotTableSheet.Range("A3"), "PivotTable1")

    ' ピボットテーブルの設定
    With pivotTable
        .ColumnGrand = False
        .RowGrand = False
        .HasAutoFormat = True
        .DisplayErrorString = True
        .ErrorString = "-"
        .NullString = "0"

        ' 行フィールドに会社名を追加
        .PivotFields("会社名").Orientation = xlRowField
        .PivotFields("会社名").Position = 1

        ' 列フィールドにフラグを追加
        .PivotFields("フラグ").Orientation = xlColumnField
        .PivotFields("フラグ").Position = 1

        ' 値フィールドに金額を追加
        .AddDataField .PivotFields("未払金の金額"), "Sum of 金額", xlSum
    End With

    ' 従来のピボットテーブルレイアウトに設定
    pivotTable.LayoutRowDefault = xlTabularRow
    pivotTable.MergeLabels = False
    
      ' 値のみを別のシートに貼り付ける
    Dim pasteSheet As Worksheet
    Set pasteSheet = ThisWorkbook.Worksheets.Add
    pasteSheet.Name = "PivotTableValues"

    ' ピボットテーブルの範囲をコピー
    Dim pivotTableRange As Range
    Set pivotTableRange = pivotTable.TableRange1
    pivotTableRange.Copy

    ' 値のみを貼り付け
    pasteSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False


End Sub