弥生会計等のインストール型の会計ソフトはCSVデータをインポートする機能がついていることが多いので、銀行の取引データを入力する場合は会計ソフトに直接手入力するよりも、インターネットバンキングのデータを会計ソフトにインポートできる形式に加工してインポートするほうが楽に、かつ早く会計データの作成が可能です。
この銀行取引を会計ソフトへ入力する際に、銀行取引データ１つに対して１つの仕訳データであればいいんですが、銀行取引データ１つに対して2つ以上の仕訳を入力しないといけない場合がたまにあります。
そんな時はマクロを使ってインポート用のデータを加工して会計ソフトへインポートしていますので、今回そのマクロを晒してみます。
ざっくりとしたマクロの流れとしては
- 仕訳を追加したい摘要を検索する
- 空白の行を追加する
- 追加した空白行に仕訳データを入力する
- もし仕訳を追加したい同じ摘要が複数あるならその他の摘要に対しても同様の処理をする
という流れです。
この記事について
弥生会計の仕訳日記帳形式でのインポートを前提としています。
振替伝票形式でのインポートには対応していませんので、振替伝票形式でインポートしたい場合にはマクロの記述を変更する必要があります。
インポート用のデータは「部門」について考慮していませんので、部門管理をしている顧問先データをインポートしたい場合には同様にマクロの記述を追加する必要があります。
マクロのコードに使用している関数やステートメントについての説明はネットにたくさん解説記事がありますので本記事では説明しません。（自分が完全に理解していないのでそもそも説明できません。）
弥生会計のCSVインポート機能を使用してインターネットバンキングのデータを取り込む際に指定した摘要がある仕訳データの下に新規の仕訳を1行追加挿入するマクロ
マネーフォワードなどのクラウド会計であれば一つの銀行取引に対して2つ以上の仕訳を登録できる機能があるんで便利なんですが、インストール型の会計ソフトにCSVデータをインポートする場合、当然そのような機能はないので自分でデータを作らないといけません。
ここでいう一つの銀行取引に対して2つ以上の仕訳を入力する必要があるケースとは例えばこんなケースです。
- 銀行借入の返済を行うときで元金と利息が同時に引き落とされるケース
- 司法書士、税理士、弁護士、社会保険労務士等の士業の報酬で源泉所得税を差し引いて支払うケース
- フリーランスに報酬を支払う際に源泉所得税を差し引いて支払うケース
- クレジット会社から手数料が差し引かれて入金があるケース
- 金融機関等に出資していて源泉所得税が差し引かれて配当金が振り込まれるケース等
こういったケースの取引が行われると銀行の取引データは１つなのですが、仕訳としては複数の仕訳を入力する必要がでてきます。
今回説明用にダミーで下記の弥生会計インポート用のデータを準備しました。
この準備したデータの「エアペイ入金」という取引が「手数料が差し引かれて入金される取引」とします。
ここで下記のマクロを実行すると「エアペイ入金」というデータの下に1行仕訳を追加します。
Sub yayoiadd()
'■【マクロの説明】
'弥生会計のインポート用データで摘要欄から指定したキーワードを検索して空白行を追加し、
'仕訳を1行追加挿入するマクロ
'■変数宣言
Dim targetrng As Range
Dim firstrng As Range
Dim findword As String
Dim targetrow As Long
findword = "エアペイ入金" 'ここに記載した摘要をQ列に記載された摘要欄から探す。
'■変数宣言（静的1次元配列）
Dim yayoiadd(24) As Variant
'■処理実行部分
Set targetrng = Range("q:q").Find(what:=findword, lookat:=xlWhole) 'Q列から「完全一致」で検索実行
If targetrng Is Nothing Then
MsgBox findword & "という摘要はありません。終了します。", vbInformation
exit sub
Else
Set firstrng = targetrng
targetrow = targetrng.Row
targetrng.Offset(1, 0).EntireRow.Insert
'■1行目の追加する仕訳の項目を設定する。使用しない項目はコメントアウトしておくこと。
yayoiadd(0) = 2000 '識別フラグ
yayoiadd(3) = targetrng.Offset(0, -13).Value '日付
yayoiadd(4) = "借方科目" '借方科目
yayoiadd(5) = "借方補助科目" '借方補助科目
yayoiadd(7) = "借方消費税区分" '借方消費税区分
yayoiadd(8) = 880 '借方金額
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) = "摘要" '摘要欄
yayoiadd(19) = 0
yayoiadd(24) = "no"
'■仕訳の追加
Range("a" & targetrow, "y" & targetrow).Offset(1, 0).Value = yayoiadd
End If
'■他の同じ摘要に対しても仕訳を追加する処理を繰り返す
Do
Set targetrng = Range("q:q").FindNext(targetrng)
If targetrng.Address = firstrng.Address Then
Exit Do
Else
targetrow = targetrng.Row
targetrng.Offset(1, 0).EntireRow.Insert
yayoiadd(3) = targetrng.Offset(0, -13).Value '日付のデータを再取得
'■仕訳の追加
Range("a" & targetrow, "y" & targetrow).Offset(1, 0).Value = yayoiadd
End If
Loop
MsgBox findword & "という摘要に対して仕訳の追加処理が完了しました。", vbInformation
End Sub
コードが長く感じるかもしれませんが、やってることは難しくはありません。
マクロを実行すると下記のようになります。わかりやすいように追加された仕訳を黄色でマークしています。
このコード自体はこのままでは使えないので、実務で使用する場合、特定の箇所のマクロを記述を変更する必要があります。
コードの記述を変更する箇所について
findword = ””に検索したい摘要を記載する
上記のコード欄のfindword=”エアペイ入金”の部分を変更すると検索するキーワードを変更することができるのでこの箇所を変更します。
具体的には下記の箇所になります。
例えば「エアペイ入金」を「甲税理士事務所」へ変更すると「甲税理士事務所」あるデータの下に仕訳が追加されます。
必要に応じて検索させたい摘要に変更してください。
配列の要素に弥生会計インポート用の項目（借方科目等）を設定する
上記のコード欄のyayoiadd(4)＝””からyayoiadd(16)=””の部分を変更すると追加する仕訳のデータを作成することができるので、ここに弥生会計でインポートするときの必要な項目を記載します。
具体的には下記の箇所になります。「■処理実行部分の下」へ少し進んだ下記の場所、
例えば追加したい仕訳が「支払手数料（課税仕入） ／ 売掛金（対象外） 550円」で摘要が「振込手数料」の場合にはコードを下記のように変更します。
'■1行目の追加する仕訳の項目を設定する。使用しない項目はコメントアウトしておくこと。
yayoiadd(0) = 2000 '識別フラグ
yayoiadd(3) = targetrng.Offset(0, -13).Value '日付
yayoiadd(4) = "支払手数料" '借方科目
yayoiadd(7) = "課税仕入込10%" '借方消費税区分
yayoiadd(8) = 550 '借方金額
yayoiadd(9) = Int(yayoiadd(8) * 10 / 110) '借方消費税額
yayoiadd(10) = "売掛金" '貸方科目
yayoiadd(13) = "対象外" '貸方消費税区分
yayoiadd(14) = yayoiadd(8) '貸方金額
yayoiadd(15) = Int(yayoiadd(8) * 10 / 110) '貸方消費税額
yayoiadd(16) = "振込手数料" '摘要欄
yayoiadd(19) = 0
yayoiadd(24) = "no"
'■仕訳の追加
Range("a" & targetrow, "y" & targetrow).Offset(1, 0).Value = yayoiadd要注意
摘要欄に検索ワードと同じ摘要を記載するとこのマクロはバグが発生してしまいますので絶対にしないでください。
この例の場合、yayoi add(16)＝””の箇所にyayoiadd(16)=”エアペイ入金”と記載するとマクロが無限に繰り返されてしまいますので気を付けてください。
どうしても同じ摘要にしたい場合は摘要欄の最後に空白スペースを入力しておくなどして対処しましょう。具体的にはyayoiadd(16)=”エアペイ入金 ” などと空白スペースをいれておけばよいです。
使用しない項目、例えば「借方補助科目」や「貸方補助科目」を使用しないデータを追加する場合にはコードを削除するか、コメントアウトしておきましょう。（今回は削除しています。）
なお、yayoiadd(9)、yayoiadd(14)、yayoiadd(15)の部分については連動しているので特に変更する必要はないです。
以上の変更をしたうえでマクロを実行（最初にマクロを実行する際にはF8キーで処理を少しづつ実行していきエラー、バグが発生しないかを確認するのをおススメします。）して問題なく自分が追加したい仕訳が追加されていればOKです。
配列を使用して追加する行の仕訳データを作成するとマクロの処理速度が速い
ここから先は特に読む必要がありませんが、ざっくりとマクロのコードの説明をしておきます。
「yayoiadd(24)」という配列を設定してそこに、追加する仕訳のデータを格納しています。
この部分です。
配列を使用せずに、「Value」プロパティを使用してセルに直接データを入力する方法で追加する仕訳データを作成してもいいんですが、マクロの処理速度がものすごく遅くなってしまうという問題が発生してしまったので、マクロの処理速度を早くするために配列を使用しています。
実際に配列を使用して上記のマクロを実行したら大体２～３秒で処理が完了しますが、「Value」プロパティを使用してマクロを実行すると確実に10秒以上はかかります。
Find関数とFindnext関数で指定した摘要全てに同じ処理を繰り返す
Find関数を使用して「findword」で設定したキーワードをExcelのQ列の摘要欄から「完全一致」形式で検索させています。こちらも必要に応じて「部分一致」形式へ変更してもよいかもしれません。
また、Findnext関数を使用することで「findword」で設定したキーワードがQ列の摘要欄に複数ある場合においても同様に仕訳を追加するように設定しています。
その際に追加する仕訳の日付だけ変更する必要があるので、配列の要素を再度設定しなおしています。
下記のコードの部分がそれです。
yayoiadd(3) = targetrng.Offset(0, -13).Value '日付のデータを再取得
Do Loopステートメントを使って検索指定した摘要全てに対して仕訳追加の処理が完了したらマクロを終了する
Do Loopステートメントを使用して、ExcelのQ列の検索ワードで指定した摘要欄のあるセルすべてについての処理が完了した場合にマクロを終了させます。
で、処理が終了したことがわかりやすくなるようにメッセージボックスで終了した旨のメッセージを表示させています。
まとめ
銀行のインターネットバンキングを会計ソフトにインポートした後で、会計ソフトの側で追加の仕訳を入力する作業は面倒くさいのでそんな時は今回のマクロを使うことで、そもそもインポートする際に仕訳を追加した状態で会計ソフトにインポートするので、後から仕訳を入力する必要がなくなりますし、仕訳を追加し忘れるというミスを避けることができると思います。
今回記載したマクロは実行テストを行ったうえで掲載していますが、コピペして使用する際にはご自身の責任のもとでおこなってください。掲載された内容によって生じた損害等の一切の責任を負いかねますので、ご了承ください。
【本日の近況報告】
漫画「テセウスの船」を一気読み。
ドラマが人気だったのは何となく知っていましたが、調べてみると漫画家・東元俊哉さんの作品ということが判明。
ヤングマガジンで連載していた時の別の作品が好きだったので、テセウスの船も読んでみました。
ヤングマガジン連載時はハードボイルドな作風が多かったと思うんですが、テセウスの船はハードボイルドとはまったく違いますがこれはこれで面白かったです。
個人的にはヤングマガジンでまた連載してほしい。。。
【本日の１曲】
Long Beach Dub Allstars/ Easy
なんと19年ぶりに新譜が出たそうです。その中の一曲。
ずっとやってたのか、それともいったん辞めてて復活したのかまったくわかりませんが、聴いてみたら曲の感じはイイ意味でまったく変わってないです。
ゆる～い、だら～っとした感じが昔のアルバムと変わってません！ロック、スカ、レゲェ、ダブありって感じでまったく変わってません！イイアルバムだと思います。
冷えたビールでも飲みながらボーっと聴いときたい。
