驚異の効率化！ ‐ChatGPT4‐ でExcelVBA給与自動仕訳ツールを作成して手間と時間を50％カット！

‐ChatGPT‐

給与の仕訳は面倒くさい

給与の仕訳って、なんでこんなにめんどくさいんだろうって思いませんか？特に、大手企業だと給与計算システムと会計システムが連動していないところが多いのが難点。だから、経理担当者は従業員名を隠した給与データを渡されて、一人ずつ仕訳を切らなければならないことがあるんですよね。

もちろん、小さい会社だと社労士に給与計算を任せたり、生データだけを渡して後は経理が仕訳を切ったりする場合もあります。でも、それでも給与データが大体Excelの1行に対して従業員の基本給や社会保険、源泉税、会社負担分などがあるので、一個ずつ仕訳を切っていたら日がくれてしまうかもしれません。

そんなとき、給与自動仕訳ツールがあれば、従業員名ごとに仕訳を作成し、会計システムに一気にバッチで取り込めるように仕訳を指定した最終行に一つずつ追加することができます。このツールを作成するために、私はChatGPTに質問しながら作ってみました。

給与の仕訳は、確かに厄介なものですが、適切なツールを使えば、時間と手間を大幅に削減することができます。

給与計算システムと会計システムの問題点

給与計算システムと会計システムの連動に問題があることは、多くの企業でよく知られています。これは、従業員の給与が支払われるたびに、会計部門が手動で仕訳を作成しなければならないことを意味します。

この問題は、企業が増えるにつれて深刻化しています。大企業では、人事部門が給与計算を外部委託している場合があり、経理部門が使用しているシステムと異なる場合があります。小企業では、社労士に給与計算を任せている場合があり、生データだけを提供し、仕訳は経理部門が手動で作成することがあります。

給与計算システムと会計システムが連動していないため、経理部門は従業員名を隠した給与データを渡されるだけで、自分たちで仕訳を作成しなければならないという状況に陥ることがあります。また、上場企業の場合、管理会計を意識して部門別に給与の仕訳を入力することが求められる場合があります。これにより、従業員毎に仕訳を入力する必要が生じるため、経理部門はかなりの手間を費やすことになります。

給与データは通常、Excelの1行に従業員の基本給、社会保険、源泉税、会社負担分などが含まれます。このデータを手動で仕訳に変換するのは非常に困難であり、時間がかかるため、経理部門は常にストレスを感じています。

結局のところ、給与計算システムと会計システムの連動に問題があることは、企業の経理部門にとって大きな問題です。この問題に対処するには、給与自動仕訳ツールのようなソリューションを使用することが推奨されます。これにより、従業員の給与が支払われるたびに、手動で仕訳を作成する必要がなくなり、経理部門はより重要な業務に時間を割くことができます。

chatGPTによる給与自動仕訳ツールの開発

給与自動仕訳ツールは、給与計算データから自動的に仕訳帳に入力される給与仕訳を作成するためのツールです。ExcelのVBAを使用して、以下の仕様を実現しています。

給与テストデータの作成

給与データをExcelの表に入力します。1行に1人分の給与データが入力されており、従業員ID、氏名、基本給、通勤手当、その他手当、割増賃金、雇用保険（従業員負担）、雇用保険（会社負担）、健康保険（従業員負担）、健康保険（会社負担）、年金保険料（従業員負担）、年金保険料（会社負担）、所得税、住民税、総支給額が含まれます。

シート名は、給与データ

【ChatGPTへの質問事例】

従業員ID、氏名、基本給、通勤手当、その他手当、割増賃金、雇用保険（従業員負担）、雇用保険（会社負担）、健康保険（従業員負担）、健康保険（会社負担）、年金保険料（従業員負担）、年金保険料（会社負担）、所得税、住民税、総支給額　の項目で　表形式でテストデータを作ってみて

【回答】

表形式で作ってくれるので、給与データシートに以下のように貼り付けてください。

給与仕訳テンプレートシートを作成

給与仕訳テンプレートは、仕訳作成のためのフォーマットであり、従業員名称、 勘定科目、 消費税、 金額、 勘定科目、 消費税、 金額が含まれます。

シート名は、給与仕訳テンプレート

とりあえず、以下のように手動で作成してみてください。あくまでもテスト用です。仕訳の勘定科目などは、会社によって異なるのでご自身の会社の勘定科目で作成してみてください。

給与仕訳シートを作成

ここには、仕訳バッチを作成するために最終行に一個ずつ仕訳を追加していきます。後で、データをクリアできるようにクリアボタンを実装します。

シート名は、給与仕訳

　

ExcelVBAによる給与仕訳の自動作成 　

給与データから給与仕訳テンプレートに適切な箇所に数値を入力して、給与仕訳テンプレートを新しいシートにコピーしてシート名を従業員名称にします。給与仕訳シートには最終行に一つずつ給与仕訳を追加するようにします。

【ChatGPTへの質問事例】

①VBAで給与仕訳を自動にて一気に作るようにしましょう。給与データというシートに１行に一人分の給与データがあります。１行毎に仕訳を作りたいです。A列　従業員ID B列　氏名 C列　基本給 D列　通勤手当 E列　その他手当 F列　割増賃金 G列　雇用保険（従業員負担） H列　雇用保険（会社負担） I列　健康保険（従業員負担） J列　健康保険（会社負担） K列　年金保険料（従業員負担） L列　年金保険料（会社負担） M列　所得税 N列　住民税 O列　総支給額　となっています。これを給与仕訳テンプレートに入力していきたいと思います。給与仕訳テンプレートシートのセルＡ2に　給与データシートのＢ列　給与仕訳テンプレートシートのセルD2に給与データシートのＣ列　給与仕訳テンプレートシートのセルD4に給与データシートのＤ列　給与仕訳テンプレートシートのセルD5に給与データシートのE列　給与仕訳テンプレートシートのセルD3に給与データシートのF列　給与仕訳テンプレートシートのセルG6に給与データシートのG列　給与仕訳テンプレートシートのセルG8に給与データシートのH列　給与仕訳テンプレートシートのセルG5に給与データシートのI列＋K列　給与仕訳テンプレートシートのセルG7に給与データシートのJ列＋L列　給与仕訳テンプレートシートのセルG3にM列　給与仕訳テンプレートシートのセルG4にN列　給与仕訳テンプレートシートのセルG2に給与データシートのO列　

②給与仕訳を一つのシートに最終行に一個ずつ付け足すようにもして

③給与仕訳を新しいシートにコピーして、それを指定したシートの最終行に一つずつ給与仕訳を追加するというのが良いです

④給与仕訳シートのデータをクリアするVBAを書いて

などなど、要件をどんどん質問してください。そうするとVBAのコードをどんどん改善してくれます。

ChatGPTも完璧ではありませんが、0から作れないようなVBAコードを作成してくれます。抽出するセルや表示するセルの箇所は、ズレていたりしますが、VBAを実行しながら自分で補正していきます。

以下、給与自動仕訳のコード

Sub CreatePayrollJournal()
    Dim payrollSheet As Worksheet
    Dim templateSheet As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim employeeID As String
    Dim employeeName As String
    Dim basicPay As Long
    Dim commutingAllowance As Long
    Dim otherAllowance As Long
    Dim overtimePay As Long
    Dim employeeEI As Long
    Dim companyEI As Long
    Dim employeeHI As Long
    Dim companyHI As Long
    Dim employeePI As Long
    Dim companyPI As Long
    Dim incomeTax As Long
    Dim residentTax As Long
    Dim totalPay As Long
    Dim journalLastRow As Long
     
     
    ' 給与データシートと給与仕訳テンプレートシートと給与仕訳のオブジェクトを設定
    Set payrollSheet = ThisWorkbook.Sheets("給与データ")
    Set templateSheet = ThisWorkbook.Sheets("給与仕訳テンプレート")
    Set journalSheet = ThisWorkbook.Sheets("給与仕訳")
   
    
    ' 給与データシートの最終行を取得
    lastRow = payrollSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    ' 各行について給与仕訳を作成
    For i = 2 To lastRow
        ' 給与データシートからデータを取得
        employeeID = payrollSheet.Cells(i, 1).Value
        employeeName = payrollSheet.Cells(i, 2).Value
        basicPay = payrollSheet.Cells(i, 3).Value
        commutingAllowance = payrollSheet.Cells(i, 4).Value
        otherAllowance = payrollSheet.Cells(i, 5).Value
        overtimePay = payrollSheet.Cells(i, 6).Value
        employeeEI = payrollSheet.Cells(i, 7).Value
        companyEI = payrollSheet.Cells(i, 8).Value
        employeeHI = payrollSheet.Cells(i, 9).Value
        companyHI = payrollSheet.Cells(i, 10).Value
        employeePI = payrollSheet.Cells(i, 11).Value
        companyPI = payrollSheet.Cells(i, 12).Value
        incomeTax = payrollSheet.Cells(i, 13).Value
        residentTax = payrollSheet.Cells(i, 14).Value
        totalPay = payrollSheet.Cells(i, 15).Value
        
        ' 給与仕訳テンプレートシートにデータを転記
        templateSheet.Cells(2, 1).Value = employeeName
        templateSheet.Cells(2, 4).Value = basicPay
        templateSheet.Cells(4, 4).Value = commutingAllowance
        templateSheet.Cells(5, 4).Value = otherAllowance
        templateSheet.Cells(3, 4).Value = overtimePay
        templateSheet.Cells(6, 7).Value = employeeEI
        templateSheet.Cells(8, 7).Value = companyEI
        templateSheet.Cells(7, 7).Value = companyHI + companyPI
        templateSheet.Cells(5, 7).Value = employeeHI + employeePI
        templateSheet.Cells(3, 7).Value = incomeTax
        templateSheet.Cells(4, 7).Value = residentTax
        templateSheet.Cells(2, 7).Value = totalPay
        
        
        ' 給与仕訳テンプレートシートをコピーして新しいシートにペースト
        templateSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
        'ペーストしたシートの名前を従業員名に変更
        ActiveSheet.Name = employeeName
        
         ' Journalシートの最終行に給与仕訳を追加
        lastRow = journalSheet.Cells(Rows.Count, 2).End(xlUp).Row
        templateSheet.Range("A1:G8").Copy Destination:=journalSheet.Range("A" & lastRow + 1)
        
        
        
    Next i

    ' 給与仕訳テンプレートシートのA列の従業員名称 D 列と G 列の数値をクリア
    templateSheet.Range("A2:A6").ClearContents
    templateSheet.Range("D2:D6").ClearContents
    templateSheet.Range("G2:G8").ClearContents

    ' 終了メッセージを表示
    MsgBox "給与仕訳の作成が完了しました。"

End Sub

以下、給与仕訳シートのデータをクリアにするコード

Sub ClearPayrollJournal()
    Dim journalSheet As Worksheet
    Set journalSheet = ThisWorkbook.Sheets("給与仕訳")
    
    journalSheet.Cells.ClearContents
    journalSheet.Cells.ClearFormats
    journalSheet.Cells.Clear
End Sub

給与自動仕訳ツールの出力結果

給与自動仕訳ツールによる出力結果は、以下のようになります。

給与仕訳テンプレートシートを基礎として、シートを追加して以下のように従業員ごとの給与仕訳を自動的に作成します。

給与仕訳シートには、会計ソフトにインポートしやすいように従業員毎の仕訳結果を一人ずつ下へ追加していきます。

このデータをうまく活用して、会計ソフトに取り込むバッチツールを作成すれば、一気に仕訳を従業員毎に取り込むことが可能です。データの持ち方によっては、部門別にも可能となります。

また、VBAコードをうまく編集することで一気に取り込みバッチまでも作ることは可能でしょう。