普段の業務で銀行の入出金CSVデータを加工して会計データとして利用しているのですが、借入金の返済などがあった場合にはExcelのマクロを使って「元本返済部分」と「利息部分」に取引を区別してデータを追加することがあります。
先日、同じ銀行から複数の借入を行っていて、返済時の通帳の記載をみると摘要欄が同じ表示になっていて、返済金額だけが違う。というケースに遭遇することがありました。
そういった場合に、返済金額の違いに応じてそれぞれ異なった仕訳を追加するにはどういったExcelマクロを書けば良いのか考える（実際に考えたのはChatGPTですが）ことがあったので記事にしておこうと思います。
freeeやマネーフォワードなどのクラウド会計ソフトであれば自動仕訳ルールを設定したうえで、そのルールが適用される金額の「範囲」を設定することで今回のやりたいことは実現できますが、それをExcelマクロでやる場合にはどうなるかを考えてみようという感じです。（←考えるのはChatGPTですが）
今回やりたいことの全体像
今回やりたいことのイメージとしてはこんな感じで通帳のデータがあるとして↓
借り入れの返済という取引があったら
- 返済金額が10万円台なら残高欄の横に〇と記載する
- 返済金額が5万円代なら残高欄の横に△と記載する
といった感じで金額に応じて違う作業をしたい。ということになります。
マクロ実行後のイメージはこんな感じです↓
実際には〇とか△を記載するのは実務上役にたたないので、元本返済部分と利息部分の取引データを追加したりすることになります。
freeeやマネーフォワードのクラウド会計であれば「簡単」にやりたいことが実現できる。
freeeやマネーフォワードなどのクラウド会計であれば自動仕訳ルールというものがあり、この自動仕訳ルールについては「何円以上」ならこのルールを適用する。であったり、「何円以下」ならこのルールを適用する。
といったようにルールを細かく設定することができるので、今回のように同じ銀行に対する複数の借入金の返済であっても簡単に利息部分と元本部分を分けることが可能です。
クラウド会計で自動仕訳ルールを作成する場合、通常は取引が行われた「銀行名（A銀行）」と、銀行入出金の明細に記載されている「摘要」の文字などに対してルールを作成していくことになります。
上の通帳データであれば「A銀行」の摘要の「ヘンサイ」の２つの要素をキーとして自動仕訳ルールを設定していくことになります。
その上で、まずは返済金額が10万円台である場合のルールを作成し、次に返済金額が5万円台のときのルールを作成することでそれぞれの返済金額に応じて自動で取引を推測・作成することができます。
10万円代の返済の場合はこんなイメージでルールを作ったり↓
5万円代の返済の場合はこんなイメージで作ったりできます↓
同じことをExcelのマクロでやろうとするとどうすればいいのか。
Excelのマクロで同じことをやろうとするとどうすればいいのか、ChatGPTに聞く前に自分なりに考えてみました。
アプローチの方法としてはクラウド会計の自動仕訳ルールを作成するときの流れと同じ流れで考えてみます。
- まずは銀行のCSVデータの摘要欄から「ヘンサイ」というキーワードを探す
- 「ヘンサイ」というキーワードが見つかったら「支払金額」がいくらなのかを確認する
- 返済金額が10万円台なら〇、5万円台なら△と記載する
このアプローチでやってみることにしました。
では早速ChatGPTに投げてみます。
こういったExcelデータをChatGPTに読み込ませて
こんな感じで指示をだしました↓
で返ってきたコードがこちらです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | Sub MarkHensai() Dim ws As Worksheet Dim lastRow As Long Dim i As Long ' アクティブなシートを取得 Set ws = ThisWorkbook.Sheets(1) ' 必要に応じてシート名を変更 ' C列の最終行を取得 lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ' ループでデータを処理 For i = 1 To lastRow ' C列の値が「ヘンサイ」の場合 If ws.Cells(i, 3).Value = "ヘンサイ" Then ' E列の出金額が10万円以上の場合 If ws.Cells(i, 5).Value >= 100000 Then ws.Cells(i, 7).Value = "〇" ' G列に「〇」を記載 ' E列の出金額が10万円未満の場合 ElseIf ws.Cells(i, 5).Value < 100000 Then ws.Cells(i, 7).Value = "△" ' G列に「△」を記載 End If End If Next i MsgBox "処理が完了しました！", vbInformationEnd Sub |
上のコードを実行した結果がこちらです↓
こちらの意図したとおりにできています。
ちなみにデータを追加しても問題なく作業できるか試してみたいので返済のデータを増やしてみます。
で、同じようにマクロを実行してみると
こちらも問題なく作業できているようです。
あとはそれぞれ上のコードのハイライト部分を自分のやりたい作業にコードを書き換えるとOKということになります。
同じ銀行からの借入金が複数ある場合で返済金額に応じて違う仕訳を追加するExcelマクロ（弥生会計インポート用）
ということで実際に弥生会計にCSVデータをインポートする場面を想定してコードを書き換えてみます。
まずはさきほどのA銀行のデータを弥生会計にインポートする形式にしておきます。
このデータの太字部分が借入金の返済取引になっているので、この取引を元にして「元本返済部分」と「利息部分」に仕訳を追加することにします。
具体的な作業については弥生会計の過去のこちらの記事を参考にしていただければ。
で、過去記事を参考にして今回のコードを書き換えたのがこちら↓
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 | Option ExplicitSub MarkHensai3() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Dim yayoiadd(24) As Variant ' アクティブなシートを取得 Set ws = ThisWorkbook.Sheets(2) ' 必要に応じてシート名を変更 ' C列の最終行を取得 lastRow = ws.Cells(ws.Rows.Count, "a").End(xlUp).Row ' ループでデータを処理 For i = lastRow To 2 Step -1 ' C列の値が「ヘンサイ」の場合 If ws.Cells(i, 17).Value = "ヘンサイ" Then Cells(i, 17).Offset(1, 0).EntireRow.Insert ' E列の出金額が10万円以上の場合 If ws.Cells(i, 9).Value >= 100000 Then '■1行目の追加する仕訳の項目を設定する。使用しない項目はコメントアウトしておくこと。 yayoiadd(0) = 2000 '識別フラグ yayoiadd(3) = Cells(i, 4).Value '日付 yayoiadd(4) = "支払利息" '借方科目' yayoiadd(5) = "借方補助科目" '借方補助科目 yayoiadd(7) = "非課税仕入" '借方消費税区分 yayoiadd(8) = Cells(i, 9) - 95000 '借方金額 yayoiadd(9) = Int(yayoiadd(8) * 10 / 110) '借方消費税額 yayoiadd(10) = "長期借入金" '貸方科目' yayoiadd(11) = "貸方補助科目" '貸方補助科目 yayoiadd(13) = "対象外" '貸方消費税区分 yayoiadd(14) = yayoiadd(8) '貸方金額 yayoiadd(15) = Int(yayoiadd(8) * 10 / 110) '貸方消費税額 yayoiadd(16) = "借入金利息 10万円台" '摘要欄 yayoiadd(19) = 0 yayoiadd(24) = "no" '■仕訳の追加 Range("a" & i, "y" & i).Offset(1, 0).Value = yayoiadd Range("a" & i, "y" & i).Offset(1, 0).EntireRow.Interior.ColorIndex = 4 ' E列の出金額が10万円未満の場合 ElseIf ws.Cells(i, 9).Value < 100000 Then '■1行目の追加する仕訳の項目を設定する。使用しない項目はコメントアウトしておくこと。 yayoiadd(0) = 2000 '識別フラグ yayoiadd(3) = Cells(i, 4).Value '日付 yayoiadd(4) = "支払利息" '借方科目' yayoiadd(5) = "借方補助科目2" '借方補助科目 yayoiadd(7) = "非課税仕入" '借方消費税区分 yayoiadd(8) = Cells(i, 9) - 45000 '借方金額 yayoiadd(9) = Int(yayoiadd(8) * 10 / 110) '借方消費税額 yayoiadd(10) = "長期借入金" '貸方科目' yayoiadd(11) = "貸方補助科目2" '貸方補助科目 yayoiadd(13) = "対象外" '貸方消費税区分 yayoiadd(14) = yayoiadd(8) '貸方金額 yayoiadd(15) = Int(yayoiadd(8) * 10 / 110) '貸方消費税額 yayoiadd(16) = "借入金利息 5万円台" '摘要欄 yayoiadd(19) = 0 yayoiadd(24) = "no" '■仕訳の追加 Range("a" & i, "y" & i).Offset(1, 0).Value = yayoiadd Range("a" & i, "y" & i).Offset(1, 0).EntireRow.Interior.ColorIndex = 6 End If End If Next i MsgBox "処理が完了しました！", vbInformationEnd Sub |
コードが長くなってはいますが、全体の構造としては大きく変わっていません。
で実行するとこんな感じになります。
10万円台の返済と5万円台の返済の取引の下にデータが1行追加され、元本との差額（10万円台の返済の場合は返済元本が95,000円、5万円台の返済の場合は返済元本が45,000円）が利息として計上されています。
ちなみにデータを追加＆返済金額が多少変化しても問題なく作業できるか試してみたいので返済のデータを増やしてみます。
この状態でマクロを実行してみると
問題なく元本との差額部分を利息としてデータの作成をすることができています。
まとめ
同じ銀行からの借入金が複数ある場合で返済金額に応じて違う仕訳を追加するExcelマクロを弥生会計にインポートする場合を想定して記事にしてみました。
やりたい作業を言語化できさえすればコードの作成はChatGPTを使えばいいので負担が激減しますね。
本当に助かります。
【本日の近況報告】
GAS（Google Apps Script）でスポット相談の問い合わせフォームに対して自動返信メールの機能を作成。
事務所HPの問い合わせフォームについては以前に問い合わせがあった場合に自動でメッセージが送信されるようにGASでプログラムしていたのですが、スポット相談については自動返信メールの設定をしていなかったのでGASでプログラムを作成しました。
Googleフォームで作成しているのでGASとの相性もよく、トリガーなどの設定も簡単で比較的すぐに作成することができました。
【本日の１曲】
Snoop Dogg/Another Part of Me (ft. Sting)
policeのMessage In A Bottleを下敷きにしてSnoop Doggという人が歌っています。恥ずかしながらSnoop Doggという人については全く知らないのですが、後ろで鳴っているのがpoliceなのでpoliceの曲だと思って聴いています。
