【ExcelVBA】集計表を使った売掛金管理①イベント処理活用【経理必見】

2019年5月31日
2019年9月13日
売掛金管理
Excel, VBA, イベント, 売掛金

売掛金管理

みなさん、こんにちはケンケンです。

今回は、売掛集計表の作り方と管理方法をご紹介します。

経理の方におすすめの記事となっています。

売掛金管理ってめんどうですよね。

それでいて最重要事項といえる業務のひとつです。

僕は経理と会計事務所で１５年の経験があるので、売掛金管理の苦労はよく分かります。

いうなれば、経理側の視点と経営側の視点両方を使って仕事をしてきたということです。

両方の視点で観察している内に、改善すべき点が明確になってきました。

そこで、今回は改善点をVBAを使ってご紹介していこうと思います。

集計表を作ったは良いけど管理にうまく使えていないという場面に出くわします。

入金があるかちゃんと確認していますか。

確認していても目視で確認していませんか。

月々の集計を簡潔に一覧でまとめていますか。

せっかくの集計表もただの計算用紙になってしまっては宝の持ち腐れです。

作った資料をそのままの状態で何も応用しないともったいないし効率も悪いです。

発想を豊かにしてどんな情報が必要でどう活かせるかを考えていきましょう。

1 VBAイベント処理で管理
2 CellsとColorIndex
3 ２重ループさせてみよう
- 3.1 ■条件を複数指定する方法
4 まとめ

VBAイベント処理で管理

では、VBAのイベント機能を使って集計表を管理してみましょう。

その前に、売掛金集計表の書式を用意します。

今回は下図のような集計表を使っていきます。

主要な項目は、

得意先
締日
前期繰越

毎月の項目は、

発生
入金
手数料
値引返品
残高

の各欄を設けました。説明の便宜上６月までの表になっています。

作った表に以下のようにデータを入力してみました。

ここまでは、何の変哲もない集計表ですが、ここからいろいろと仕掛けを打っていきます。

僕の経験上、ちゃんと集計表を作っている会社は多いですが、これを発展させて管理資料としてうまく活用しているところはほとんどありません。

せっかく作った集計表なので管理も同時にできるハイブリッドな資料に成長させてみましょう。

そのためにVBAを使っていきます。

VBAを使ってまず何をするかと言うと

未入金の得意先に色をつけていく、というものです。

得意先が多くなると入金管理が漏れてしまう危険性があります。

入金管理は経理で必須事項ですから、ストレスなく行いたいものです。

そこで、入金欄に金額が入力されるまで、つまり未入金の状態であることを色をつけることで視覚的に判断しやすくします。

これは、シートモジュールにActivateイベントとしてコードを記述していきます。

以下のコードを売掛金集計表シートのシートモジュールに書いてください。

Private Sub Worksheet_Activate()
    Dim LRow '最終行取得
    LRow = Range("D" & Rows.Count).End(xlUp).Row
    
    Dim cRow '得意先行ループ用
    Dim nyuukinRetu '各月の入金列取得
    
    For nyuukinRetu = 5 To 60 Step 5
        For cRow = 3 To LRow
            If Cells(cRow, nyuukinRetu).Value = "" And Cells(cRow, nyuukinRetu - 2) <> "" And Cells(cRow, 1) <> "" Then
                Cells(cRow, nyuukinRetu + 3).Interior.ColorIndex = 43
            Else
                Cells(cRow, nyuukinRetu + 3).Interior.ColorIndex = 0
            End If
        Next
    Next
End Sub

Private Sub Worksheet_Activate()

Dim LRow '最終行取得

LRow = Range("D" & Rows.Count).End(xlUp).Row

Dim cRow '得意先行ループ用

Dim nyuukinRetu '各月の入金列取得

For nyuukinRetu = 5 To 60 Step 5

For cRow = 3 To LRow

If Cells(cRow, nyuukinRetu).Value = "" And Cells(cRow, nyuukinRetu - 2) <> "" And Cells(cRow, 1) <> "" Then

Cells(cRow, nyuukinRetu + 3).Interior.ColorIndex = 43

Else

Cells(cRow, nyuukinRetu + 3).Interior.ColorIndex = 0

End If

End Sub

シートモジュールが分からない方は、こちらの記事をご覧ください。

コードを簡単に説明すると、

入金欄が未入力でかつ、前月残または繰越残高と得意先名（A列）に記載があったら、未入金の月の残高セルに色をつけ、条件が解除されたら色を消します。

この処理は、Activateイベントなので、シートがアクティブになる度に発生します。

イベントが実行されると以下のようにシートの各セルに色が付きます。

どうですか。入金欄に金額が入っていない得意先の残高セルに色が付きましたね。

このコードにはいろいろな要素が含まれています。

ひとつは、ForNext構文をstepを使って処理しさらに、２重ループさせています。

そして、セルの指定をRangeではなくCellsで指定しています。

さらに、セルを塗りつぶすためにInterior.ColorIndexを使っています。

それでは、ひとつずつ解説していきます。

CellsとColorIndex

２重ループの説明の前に一回目の処理を一緒に見ていきましょう。繰り返し処理を行う際には一回目の処理が大事です。

一回目の処理のコードが以下です。

Private Sub Worksheet_Activate()
    If Cells(3, 5).Value = "" Then
        Cells(3, 8).Interior.ColorIndex = 43
    Else
        Cells(3, 8).Interior.ColorIndex = 0
    End If
End Sub

Private Sub Worksheet_Activate()

If Cells(3, 5).Value = "" Then

Cells(3, 8).Interior.ColorIndex = 43

Else

Cells(3, 8).Interior.ColorIndex = 0

End If

End Sub

コードは、セルE3が空欄(未入金)だったらセルH3の色を変える、そうでなかったら白（０）に変える、というものです。

CellsはRangeと同様、セル番地を指定することができます。

Cells（行,列）で表現され、行と列にはそれぞれ数値が入ります。

つまり、Cells(3,5)は３行目で５列目になるので、セルE３となります。

続いて、Interior.ColorIndexですが、Interiorは背景色を表し、ColorIndexはVBAが持つ色の番号を指定することで該当する色を使うことができるプロパティです。

とはいっても、どの番号が何色なのかよく分からないと思います。

ネットで検索すれば分かりますが、ここではColorIndexが持つ色と番号をシート上に記載するコードをご紹介します。

Sub colorindex_hyouji()
    Dim cnt
    
    For cnt = 0 To 56
        Cells(cnt + 1, 1).Interior.ColorIndex = cnt
        Cells(cnt + 1, 1).Value = cnt
    Next
End Sub

Sub colorindex_hyouji()

Dim cnt

For cnt = 0 To 56

Cells(cnt + 1, 1).Interior.ColorIndex = cnt

Cells(cnt + 1, 1).Value = cnt

End Sub

標準モジュールに上記コードを書いて実行してみましょう。

すると、

こんな感じで色とその番号が表記された一覧を作ることができます。

一度一覧を作っておくと調べる手間も省けるので、練習がてらコードを書いてみることをおススメします。

さて、ColorIndexについては理解できたと思いますので、Activateイベントのコードをループさせてみましょう。

Private Sub Worksheet_Activate()
    Dim LRow '最終行取得
    LRow = Range("D" & Rows.Count).End(xlUp).Row
    
    Dim cRow '得意先行ループ用
    
    For cRow = 3 To LRow
        If Cells(cRow, 5).Value = "" Then
            Cells(cRow, 8).Interior.ColorIndex = 43
        Else
            Cells(cRow, 8).Interior.ColorIndex = 0
        End If
    Next
End Sub

Private Sub Worksheet_Activate()

Dim LRow '最終行取得

LRow = Range("D" & Rows.Count).End(xlUp).Row

Dim cRow '得意先行ループ用

For cRow = 3 To LRow

If Cells(cRow, 5).Value = "" Then

Cells(cRow, 8).Interior.ColorIndex = 43

Else

Cells(cRow, 8).Interior.ColorIndex = 0

End If

End Sub

変数LRowを宣言しD列の最終行を取得しています。

一番最初にデータが入るのは４月分（D列）からなのでD列を検索しています。

続いて、変数cRowを使って５列目、つまり４月の入金欄すべての行を調べていきます。

上記コードを記述し、シートをアクティブにすると以下のようになります。

４月分は成功しました。

２重ループさせてみよう

続いて５月分以降の処理も出来るようにコードを追加します。

Private Sub Worksheet_Activate()
    Dim LRow '最終行取得
    LRow = Range("D" & Rows.Count).End(xlUp).Row
    
    Dim cRow '得意先行ループ用
    Dim nyuukinRetu '各月の入金列取得
    
    For nyuukinRetu = 5 To 60 Step 5
        For cRow = 3 To LRow
            If Cells(cRow, nyuukinRetu).Value = "" Then
                Cells(cRow, nyuukinRetu + 3).Interior.ColorIndex = 43
            Else
                Cells(cRow, nyuukinRetu + 3).Interior.ColorIndex = 0
            End If
        Next
    Next
End Sub

Private Sub Worksheet_Activate()

Dim LRow '最終行取得

LRow = Range("D" & Rows.Count).End(xlUp).Row

Dim cRow '得意先行ループ用

Dim nyuukinRetu '各月の入金列取得

For nyuukinRetu = 5 To 60 Step 5

For cRow = 3 To LRow

If Cells(cRow, nyuukinRetu).Value = "" Then

Cells(cRow, nyuukinRetu + 3).Interior.ColorIndex = 43

Else

Cells(cRow, nyuukinRetu + 3).Interior.ColorIndex = 0

End If

End Sub

各月の入金列を取得するための変数nyuukinRetuを新たに宣言し、ForNextで回します。

回す範囲は、５列目から６０列目までです。初月の入金列が５列目で最後の月の入金列が６０列目（５×１２ヶ月）だからです。

そして、入金列は５列間隔であらわれるので、「step5」を使っています。

Cells(cRow, nyuukinRetu + 3).Interior.ColorIndex = 43の「nyuukinRetu + 3」の部分は、入金列から３列ずらした列を指定しています。つまり、各月の残高列を表しています。

上記コードを転記し、シートをアクティブにすると５月分以降の処理も可能になりました。

しかし、このままだと得意先が入っていない空欄の行まで色が変わってしまいます。

■条件を複数指定する方法

そこで、If文の条件を付け加えます。

具体的には、If文を以下のように変更します。

Private Sub Worksheet_Activate()
    Dim LRow '最終行取得
    LRow = Range("D" & Rows.Count).End(xlUp).Row
    
    Dim cRow '得意先行ループ用
    Dim nyuukinRetu '各月の入金列取得
    
    For nyuukinRetu = 5 To 60 Step 5
        For cRow = 3 To LRow
            If Cells(cRow, nyuukinRetu).Value = "" And Cells(cRow, nyuukinRetu - 2) <> "" And Cells(cRow, 1) <> "" Then
                Cells(cRow, nyuukinRetu + 3).Interior.ColorIndex = 43
            Else
                Cells(cRow, nyuukinRetu + 3).Interior.ColorIndex = 0
            End If
        Next
    Next
End Sub

Private Sub Worksheet_Activate()

Dim LRow '最終行取得

LRow = Range("D" & Rows.Count).End(xlUp).Row

Dim cRow '得意先行ループ用

Dim nyuukinRetu '各月の入金列取得

For nyuukinRetu = 5 To 60 Step 5

For cRow = 3 To LRow

If Cells(cRow, nyuukinRetu).Value = "" And Cells(cRow, nyuukinRetu - 2) <> "" And Cells(cRow, 1) <> "" Then

Cells(cRow, nyuukinRetu + 3).Interior.ColorIndex = 43

Else

Cells(cRow, nyuukinRetu + 3).Interior.ColorIndex = 0

End If

End Sub

条件が長くなってしまいましたが、今回は３つ上げています。

入金列が未入力「Cells(cRow, nyuukinRetu).Value = “”」
前月残高欄に記載がある「Cells(cRow, nyuukinRetu – 2) <> “”」
得意先欄に記載がある「Cells(cRow, 1) <> “”」

上記の場合に該当した時のみ、残高欄に色を付けていくということです。

それでは、結果を見てみましょう。

余計な箇所に色が付かなくなりすっきりしましたね。

また、得意先によっては入金なしでOKという月もあるでしょう。その時は入金欄に「０」と記載しておくと残高欄に色が付かなくなります。

まとめ

いかがでしたか。

ひと手間加えると、単に集計表を作った場合よりもバリエーションが増え、管理面においてもミスや確認漏れが少なくなります。

今回はイベント処理を使いましたが、標準モジュールに記述しボタンを作成して都度実行するという方法もありですね。

いろいろ工夫してみてください。

次回は、さらに集計表をカスタマイズしてもっと使いやすくしていきます。

ご期待ください。

※イベントはブレークポイントをいれておかないとステップインモードが使えないのでご注意を！

売掛金管理ツール作成記事一覧

【ExcelVBA】集計表を使った売掛金管理①イベント処理活用【経理必見】

【ExcelVBA】集計表を使った売掛金管理②月別合計を転記する方法【経理必見】

【ExcelVBA】集計表を使った売掛金管理③得意先ごとの集計を一発処理【経理必見】

ここまで読んでいただいた方におすすめの記事

【ExcelVBA】シートの一覧を作成してリンクも貼り付けしよう【便利技】

【ExcelVBA】指定した範囲だけスクロールさせる【イベントの基本】