お弁当注文システムを作る　～その３～

Excelマクロによる業務効率化

5. 月末にひと月分の締めを行い、お弁当屋さんへの支払い額の確認書と個々の社員への請求額の一覧表(給与天引リスト)を作成する

お弁当注文システムを作る　その1、その2の続きです。その1では、グーグルフォームを活用して、お弁当注文フォームを作成しました。その2ではお弁当の注文内容の日々の取りまとめを行い発注書を自動出力します。そして、今回は月次処理として、お弁当屋さんへの1カ月分の支払額の確認書の作成と社員への請求額の一覧表(給与天引リスト)をマクロを使って自動作成していきたいと思います。

(1)支払金額確認書を作成する

　①新しいシートを作成し、シート名を「支払金額確認書」に変更します。

　②「支払金額確認書」のフォーマットを作成します。

　　※ここでは行・列の内容を間違えないようにしてください。

③「支払金額確認書」に出力するマクロを書いていきます。「開発」タグの「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

④「メニュー」シートの「開発」タグから「挿入」、フォームコントロールの「ボタン」を選択し、「メニュー」シート上にボタンを作成します。

⑤「マクロの登録」ダイヤログでは、マクロ名で「支払金額確認書」を選択します。

⑥ボタン上で右クリックして、ボタンの表示名を「支払金額確認書」に変更します。

⑦「支払金額確認書」ボタンをクリックして、「年」と「月」を入力すると指定した年月の「支払金額確認書」が出力されます。

支払い金額堪忍書が自動作成されました

(2) 給与天引きリストを作成する

①新しいシートを作成し、シート名を「給与天引リスト」に変更します。

②「給与天引リスト」のフォーマットを作成します。

　　※ここでは行・列の内容を間違えないようにしてください。

③「給与天引リスト」に出力するマクロを書いていきます。「開発」タグの「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

④「メニュー」シートの「開発」タグから「挿入」、フォームコントロールの「ボタン」を選択し、「メニュー」シート上にボタンを作成します。

⑤「マクロの登録」ダイヤログでは、マクロ名で「給与天引リスト」を選択します。

⑥ボタン上で右クリックして、ボタンの表示名を「給与天引リスト」に変更します。

⑦「給与天引きリスト」ボタンをクリックして、「年」と「月」を入力すると指定した年月の「給与天引リスト」が出力されます。

給与天引リストが出力されました

コメント

タイトルとURLをコピーしました