5. 月末にひと月分の締めを行い、お弁当屋さんへの支払い額の確認書と個々の社員への請求額の一覧表(給与天引リスト)を作成する
お弁当注文システムを作る その1、その2の続きです。その1では、グーグルフォームを活用して、お弁当注文フォームを作成しました。その2ではお弁当の注文内容の日々の取りまとめを行い発注書を自動出力します。そして、今回は月次処理として、お弁当屋さんへの1カ月分の支払額の確認書の作成と社員への請求額の一覧表(給与天引リスト)をマクロを使って自動作成していきたいと思います。
(1)支払金額確認書を作成する
①新しいシートを作成し、シート名を「支払金額確認書」に変更します。
![](https://kuroneconote.com/wp-content/uploads/2021/05/50b70fd9d6a4fef39489596d63c2710f-800x393.jpg)
②「支払金額確認書」のフォーマットを作成します。
![](https://kuroneconote.com/wp-content/uploads/2021/05/b6542d80d38ca863d7476c8edbaf87e4-800x431.jpg)
※ここでは行・列の内容を間違えないようにしてください。
③「支払金額確認書」に出力するマクロを書いていきます。「開発」タグの「visual Basic」を選択し、「標準モジュール」内に下記マクロコードを入力(コピペ)します。
Sub 支払金額確認書()
Worksheets("支払金額確認書").Activate
a = Worksheets("支払金額確認書").Cells(Rows.Count, 1).End(xlUp).Row
If a <> 15 Then
Worksheets("支払金額確認書").Range("a16:e" & a).Select
Selection.ClearContents
Selection.Borders.LineStyle = xlLineStyleNone
End If
Dim yy As Integer, mm As Integer
yy = Val(InputBox("「年」を入力してください。例:2021年の場合「2021」"))
mm = Val(InputBox("「月」を入力してください。例:5月の場合「5」"))
Dim n As Integer, o As Integer, x As Integer, e As Date, f As Date, p As Integer
o = Worksheets("DB").Cells(Rows.Count, 1).End(xlUp).Row
x = 0
For n = 2 To o
If Worksheets("DB").Cells(n, 1) = 1 Then
e = Worksheets("DB").Cells(n, 2)
If Year(e) = yy And Month(e) = mm Then
f = DateSerial(Year(e), Month(e), Day(e))
Set rcd = Worksheets("支払金額確認書").Range("a:a").Find(f)
If Not rcd Is Nothing Then
Set firstcell = rcd
If rcd.Offset(0, 1) = Worksheets("DB").Cells(n, 6) Then
rcd.Offset(0, 2) = rcd.Offset(0, 2) + Worksheets("DB").Cells(n, 7)
Set rcd2 = Worksheets("マスター").Range("a:a").Find(Worksheets("DB").Cells(n, 6))
rcd.Offset(0, 3) = rcd.Offset(0, 3) + (rcd2.Offset(0, 1) * Worksheets("DB").Cells(n, 7))
Else
Do
Set rcd = Worksheets("支払金額確認書").Range("a:a").FindNext(rcd)
If rcd.Address = firstcell.Address Then
p = Worksheets("支払金額確認書").Cells(Rows.Count, 1).End(xlUp).Row + 1
Worksheets("支払金額確認書").Cells(p, 1) = f
Worksheets("支払金額確認書").Cells(p, 2) = Worksheets("DB").Cells(n, 6)
Worksheets("支払金額確認書").Cells(p, 3) = Worksheets("DB").Cells(n, 7)
Set rcd2 = Worksheets("マスター").Range("a:a").Find(Worksheets("DB").Cells(n, 6))
Worksheets("支払金額確認書").Cells(p, 4) = rcd2.Offset(0, 1) * Worksheets("DB").Cells(n, 7)
Exit Do
End If
If rcd.Offset(0, 1) = Worksheets("DB").Cells(n, 6) Then
rcd.Offset(0, 2) = rcd.Offset(0, 2) + Worksheets("DB").Cells(n, 7)
Set rcd2 = Worksheets("マスター").Range("a:a").Find(Worksheets("DB").Cells(n, 6))
rcd.Offset(0, 3) = rcd.Offset(0, 3) + (rcd2.Offset(0, 1) * Worksheets("DB").Cells(n, 7))
Exit Do
End If
Loop
End If
Else
p = Worksheets("支払金額確認書").Cells(Rows.Count, 1).End(xlUp).Row + 1
Worksheets("支払金額確認書").Cells(p, 1) = f
Worksheets("支払金額確認書").Cells(p, 2) = Worksheets("DB").Cells(n, 6)
Worksheets("支払金額確認書").Cells(p, 3) = Worksheets("DB").Cells(n, 7)
Set rcd2 = Worksheets("マスター").Range("a:a").Find(Worksheets("DB").Cells(n, 6))
Worksheets("支払金額確認書").Cells(p, 4) = rcd2.Offset(0, 1) * Worksheets("DB").Cells(n, 7)
End If
End If
End If
Next n
q = Worksheets("支払金額確認書").Cells(Rows.Count, 1).End(xlUp).Row + 1
Worksheets("支払金額確認書").Cells(q, 1) = "合計"
For r = 16 To q
s = s + Worksheets("支払金額確認書").Cells(r, 3)
k = k + Worksheets("支払金額確認書").Cells(r, 4)
Next r
Worksheets("支払金額確認書").Cells(q, 3) = s
Worksheets("支払金額確認書").Cells(q, 4) = k
Worksheets("支払金額確認書").Range("a15:e" & q).Borders.LineStyle = xlContinuous
Worksheets("支払金額確認書").Cells(12, 3) = "金" & Worksheets("支払金額確認書").Cells(q, 4) & "円也"
Worksheets("支払金額確認書").Cells(8, 1) = yy & "年" & mm & "月分のお弁当代金として下記金額を振り込みますのでご確認ください。"
Worksheets("支払金額確認書").Cells(1, 5) = Date
End Sub
![](https://kuroneconote.com/wp-content/uploads/2021/05/558a0b90bea457ab54faf90196b8e527-800x713.jpg)
④「メニュー」シートの「開発」タグから「挿入」、フォームコントロールの「ボタン」を選択し、「メニュー」シート上にボタンを作成します。
⑤「マクロの登録」ダイヤログでは、マクロ名で「支払金額確認書」を選択します。
![](https://kuroneconote.com/wp-content/uploads/2021/05/3e5d3d98af639309fc2f8325fece12ef-800x527.jpg)
⑥ボタン上で右クリックして、ボタンの表示名を「支払金額確認書」に変更します。
![](https://kuroneconote.com/wp-content/uploads/2021/05/a8247b6b9d3e822813aca6faa99bafaa.jpg)
⑦「支払金額確認書」ボタンをクリックして、「年」と「月」を入力すると指定した年月の「支払金額確認書」が出力されます。
![](https://kuroneconote.com/wp-content/uploads/2021/05/cd160299c5fa6f5ce1ab8c931e3e6959-2.jpg)
![](https://kuroneconote.com/wp-content/uploads/2021/05/c7d25a8205a7d052a4ba3f59daccabfd.jpg)
![](https://kuroneconote.com/wp-content/uploads/2021/05/59a1440116289ae10946765bafff7c7b.jpg)
(2) 給与天引きリストを作成する
①新しいシートを作成し、シート名を「給与天引リスト」に変更します。
![](https://kuroneconote.com/wp-content/uploads/2021/05/a4ac64df60b4941ea3cdda0aa2ebb3c6.jpg)
②「給与天引リスト」のフォーマットを作成します。
※ここでは行・列の内容を間違えないようにしてください。
![](https://kuroneconote.com/wp-content/uploads/2021/05/e7a63e5c0e19c8865dd089154a13a170-800x361.jpg)
③「給与天引リスト」に出力するマクロを書いていきます。「開発」タグの「visual Basic」を選択し、「標準モジュール」内に下記マクロコードを入力(コピペ)します。
Sub 給与天引リスト()
Worksheets("給与天引リスト").Activate
a = Worksheets("給与天引リスト").Cells(Rows.Count, 1).End(xlUp).Row
If a <> 2 Then
Worksheets("給与天引リスト").Range("a3:e" & a).Select
Selection.ClearContents
Selection.Borders.LineStyle = xlLineStyleNone
End If
Dim yy As Integer, mm As Integer
yy = Val(InputBox("「年」を入力してください。例:2021年の場合「2021」"))
mm = Val(InputBox("「月」を入力してください。例:5月の場合「5」"))
Dim n As Integer, o As Integer, x As Integer, e As Date, f As Date, p As Integer
o = Worksheets("DB").Cells(Rows.Count, 1).End(xlUp).Row
For n = 2 To o
If Worksheets("DB").Cells(n, 1) = 1 Then
e = Worksheets("DB").Cells(n, 2)
If Year(e) = yy And Month(e) = mm Then
x = Worksheets("DB").Cells(n, 4)
Set rcd = Worksheets("給与天引リスト").Range("a:a").Find(x, lookat:=xlWhole)
If rcd Is Nothing Then
p = Worksheets("給与天引リスト").Cells(Rows.Count, 1).End(xlUp).Row + 1
Worksheets("給与天引リスト").Cells(p, 1) = x
Worksheets("給与天引リスト").Cells(p, 2) = Worksheets("DB").Cells(n, 5)
Worksheets("給与天引リスト").Cells(p, 3) = Worksheets("DB").Cells(n, 7)
Set rcd2 = Worksheets("マスター").Range("a:a").Find(Worksheets("DB").Cells(n, 6))
Worksheets("給与天引リスト").Cells(p, 4) = Worksheets("DB").Cells(n, 7) * rcd2.Offset(0, 1)
Worksheets("給与天引リスト").Cells(p, 5) = "'" & mm & "/" & Day(e)
Else
rcd.Offset(0, 2) = rcd.Offset(0, 2) + Worksheets("DB").Cells(n, 7)
Set rcd2 = Worksheets("マスター").Range("a:a").Find(Worksheets("DB").Cells(n, 6))
rcd.Offset(0, 3) = rcd.Offset(0, 3) + (Worksheets("DB").Cells(n, 7) * rcd2.Offset(0, 1))
rcd.Offset(0, 4) = rcd.Offset(0, 4) & "," & mm & "/" & Day(e)
End If
End If
End If
Next n
q = Worksheets("給与天引リスト").Cells(Rows.Count, 1).End(xlUp).Row + 1
Worksheets("給与天引リスト").Cells(q, 1) = "合計"
For r = 3 To q
s = s + Worksheets("給与天引リスト").Cells(r, 3)
k = k + Worksheets("給与天引リスト").Cells(r, 4)
Next r
Worksheets("給与天引リスト").Cells(q, 3) = s
Worksheets("給与天引リスト").Cells(q, 4) = k
Worksheets("給与天引リスト").Range("a2:e" & q).Borders.LineStyle = xlContinuous
Worksheets("給与天引リスト").Cells(1, 1) = yy & "年" & mm & "月度 お弁当注文者の集計"
End Sub
![](https://kuroneconote.com/wp-content/uploads/2021/05/d526d9fc84961c043331ca9c4c901482-800x686.jpg)
④「メニュー」シートの「開発」タグから「挿入」、フォームコントロールの「ボタン」を選択し、「メニュー」シート上にボタンを作成します。
⑤「マクロの登録」ダイヤログでは、マクロ名で「給与天引リスト」を選択します。
![](https://kuroneconote.com/wp-content/uploads/2021/05/178b08e12fe2a9d5f71401801d3f9016-800x454.jpg)
⑥ボタン上で右クリックして、ボタンの表示名を「給与天引リスト」に変更します。
![](https://kuroneconote.com/wp-content/uploads/2021/05/fc0dd422ab4b1e79cfda101764b230c6.jpg)
⑦「給与天引きリスト」ボタンをクリックして、「年」と「月」を入力すると指定した年月の「給与天引リスト」が出力されます。
![](https://kuroneconote.com/wp-content/uploads/2021/05/982ea61af361a007df1ab5eafe983876-800x221.jpg)
![](https://kuroneconote.com/wp-content/uploads/2021/05/bc341eae8b22a71b0df97655a001e7a9-800x221.jpg)
![](https://kuroneconote.com/wp-content/uploads/2021/05/d368430b8d987c77ac331d4d7c109fd5-800x238.jpg)
コメント