みなさん、こんにちはケンケンです。
今回は、前回に引き続き売掛金管理についてお話ししていきます。
前回は集計表の入金欄が埋まっていない箇所に色を付けて警告を出す、という技をご紹介しました。
みなさん、こんにちはケンケンです。 今回は、売掛集計表の作り方と管理方法をご紹介します。 経理の方におすすめの記事となっています。 売掛金管理ってめんどうですよね。 それでいて[…]
次にあったら便利なものとして、各月の合計を集計するシートを作ることをおススメします。
集計表を見た時に毎月の合計はパッと見で把握できますが、年間を通じて把握することはシートを横スクロールしていかなくてはいけないので利便性が低いですね。
そこで、毎月の合計のみ別シートで管理すると見やすく管理面で有用です。
今回は、その集計方法をVBAを使ってご紹介します。
集計シートにVBAを使って転記
入金管理と同様、毎月の取引総額を視覚的に管理することは重要です。
関数やピボットテーブルを使って集計することも可能ですが、
今回はVBAを使って集計していきます。
まず、集計用のシートを作り、シートモジュールを追加します。
上記のように「集計」シートを選択してActivateイベント用のプロシージャを生成しましょう。
ちなみに集計シートは以下のように作りました。
各月の発生から値引返品までを集計し、最後に通期の合計を表示させます。
それでは、売掛金集計表から転記するコードを書いていきましょう。
まず、４月分だけ転記するためのコードです。
セルD25からG25までを集計シートに転記すれば良いですね。
|
1 2 3 4 5 6 7 8 9 10 |
Private Sub Worksheet_Activate() Dim uriS As Worksheet Set uriS = Worksheets("売掛金集計表") Range("B2").Value = uriS.Cells(25, 4).Value Range("C2").Value = uriS.Cells(25, 5).Value Range("D2").Value = uriS.Cells(25, 6).Value Range("E2").Value = uriS.Cells(25, 7).Value End Sub |
コードは上記のようになります。
コード転記後、集計シートをアクティブにすると以下のように集計されます。
４月分の転記に成功しました。
そこまで出来たら最後の月まで転記できるように変数を使ってループさせます。
変数を作るコツとして、今、何をループさせたいかを考えると良いです。
ループさせたい箇所は
- 「集計」シートの４月～３月、行にして２行目から１３行目
- 売掛金集計表の４月～３月の各要素（発生、入金、手数料、値引返品）
ですね。
つまり、２つ変数を用意すると良さそうです。
そこで、１の変数を「tenkiSaki」とし２の変数を「tenkiMoto」とします。
ではまず、１の変数「tenkiSaki」を使ってループさせてみましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub Worksheet_Activate() Dim uriS As Worksheet Set uriS = Worksheets("売掛金集計表") Dim tenkiSaki For tenkiSaki = 2 To 13 Range("B" & tenkiSaki).Value = uriS.Cells(25, 4).Value Range("C" & tenkiSaki).Value = uriS.Cells(25, 5).Value Range("D" & tenkiSaki).Value = uriS.Cells(25, 6).Value Range("E" & tenkiSaki).Value = uriS.Cells(25, 7).Value Next End Sub |
集計シートの４～３月まで転記することができました。
しかし、引っ張ってきているデータが集計表の４月分なので、今度は集計表の４～３月分をループさせる必要があります。
そこで、第２の変数「tenkiMoto」を使い、コードを以下のように変更します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Private Sub Worksheet_Activate() Dim uriS As Worksheet Set uriS = Worksheets("売掛金集計表") Dim tenkiSaki Dim tenkiMoto tenkiMoto = 4 For tenkiSaki = 2 To 13 Range("B" & tenkiSaki).Value = uriS.Cells(25, tenkiMoto).Value Range("C" & tenkiSaki).Value = uriS.Cells(25, tenkiMoto + 1).Value Range("D" & tenkiSaki).Value = uriS.Cells(25, tenkiMoto + 2).Value Range("E" & tenkiSaki).Value = uriS.Cells(25, tenkiMoto + 3).Value tenkiMoto = tenkiMoto + 5 Next End Sub |
解説用に下記の画像を用意しました。
コードと集計表を見比べてください。
変数tenkiMotoを宣言した理由と値の変化が分かるかと思います。
分かりづらい方は、ブレークポイントを入れてステップインモードで確認すると良いでしょう。
それでは、この状態で集計シートをアクティブにしてみましょう。
集計表と同じく６月の発生まで数値が転記されました。
ここまで出来ると、集計シートをアクティブにする度に転記されるので便利です。
最後に売掛金集計表の最終行を取得するコードも追加しておきましょう。
２５行目を指定するコードを書いていましたが、得意先が増えると当然２５行以上必要になってきます。
変数LRowを宣言し、売掛金集計表のD列の最終行を格納し該当箇所を変更します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Private Sub Worksheet_Activate() Dim uriS As Worksheet Set uriS = Worksheets("売掛金集計表") Dim Lrow Lrow = uriS.Range("D" & Rows.Count).End(xlUp).Row Dim tenkiSaki Dim tenkiMoto tenkiMoto = 4 For tenkiSaki = 2 To 13 Range("B" & tenkiSaki).Value = uriS.Cells(Lrow, tenkiMoto).Value Range("C" & tenkiSaki).Value = uriS.Cells(Lrow, tenkiMoto + 1).Value Range("D" & tenkiSaki).Value = uriS.Cells(Lrow, tenkiMoto + 2).Value Range("E" & tenkiSaki).Value = uriS.Cells(Lrow, tenkiMoto + 3).Value tenkiMoto = tenkiMoto + 5 Next End Sub |
最終的なコードは上記のとおりとなります。
変更後もエラーが生じないことを確認できたら完成です。
まとめ
いかがでしたか。
今回は、集計表から月別の合計を転記する方法をご紹介しました。
ここまで出来ると応用次第で、作りたいものを作る基礎が備わっているといって良いでしょう。
様々な書式に対応できるので、経理の方に限らず使うことができるテクニックです。
復習をしっかりして、仕事や趣味に役立ててください。
次回は、さらにカスタマイズして、得意先ごとの集計をしてみたいと思います。
売掛金管理ツール作成記事一覧
【ExcelVBA】集計表を使った売掛金管理①イベント処理活用【経理必見】
【ExcelVBA】集計表を使った売掛金管理②月別合計を転記する方法【経理必見】
【ExcelVBA】集計表を使った売掛金管理③得意先ごとの集計を一発処理【経理必見】
