Excelに出納帳形式で入れた預金データを、仕訳に変換して集約するマクロ

Excelで、預金データを入力し、集約するには、Excelマクロを使うと便利です。

 

預金データを仕訳形式で入れる方法

今回の事例はネットバンクを使っていない場合を想定しています。

Excelで預金を入力する場合、仕訳の形式で入れておくと、
・会計ソフトに取り込む
・Excelで集計する
のどちらの場合にも便利です。

ただし、預金残高を確認できず、入力するのもめんどうで手間もかかります。

預金は残高を確認しながら入力した方が便利

預金は、このような出納帳形式（元帳形式）で入れた方が楽でしょう。

ただ、入力は楽ですが、
・会計ソフトに取り込む
・Excelで集計する
場合には不便です。

複数の口座があると、このように複数のシートにデータがちらばり、処理しにくくなります。
結果的に、「Excelを見ながら会計ソフトに入力」「Excelをプリントアウトして、紙を見ながら会計ソフトに入力」と二重の手間がかかってしまうのです。

出納帳形式→仕訳に変換→集約のしくみをExcelマクロで作る

そこで、出納帳形式で楽に入力しつつ、仕訳に変換し、1枚にシートに集約するしくみを使ってましょう。
（まあ、入力は楽ではありませんが）

①出納帳形式で、預金を入力します。

1枚にシートにつき、1つの口座を入れ、年度で1枚のシートにします。
月ごとにわけると、繰越残高の処理をしなければいけなくなりますし、シートが分散するからです。
Excelでは、ファイル（ブック）をできるだけ1つに、シートをできるだけ1つにするように心がけましょう。

②出納帳形式のものを仕訳に変換します。

出納帳の右側へ仕訳を作っていきましょう。
ただし、コピペしていては時間がかかります。

数式を入れて、元帳のデータを仕訳に変換するのです。

3行目には次のような数式が入っています。

日付は、=A3。単純にもってくるだけです。
借方は、IFを使って、もしC3（入金）が空欄だったら、○○/預金という仕訳になるため、セルB3の科目を入れます。そうでなかったら、預金/○○という仕訳になるため、預金名を入れます。
この場合は、H1に預金口座名を入れるようにしていますので、H1です。

ここを「普通預金」にしてもかまいません。
補助科目を使うのが通常ですが、口座名を直接科目として処理した方が楽です。

貸方も同様に考えます。

金額は、「入金」欄又は「支払」欄に数字が入ることになるので、それらを足しておきましょう。
どちらに入っていても数字を持ってこれます。

摘要は、そのまま持ってきます。

これをデータの数だけコピーすれば、預金出納帳を仕訳に変換できるのです。

今回は、わかりやすくするため、この部分は手作業でやると想定しましたが、この記事のマクロを組み込むのをおすすめします。
【関連記事】［Excelコンサル事例］Excelデータを弥生会計へインポートするためのマクロ | EX-IT
リンク

③マクロを書きます。

今回のマクロです。

Sub yokin()

    Dim w As Worksheet
    Dim Max_row1
    Dim Max_row2
    
'■いったんデータを消す
    Worksheets("data").Rows("2:1048576").Delete
    
 'すべてのシートで処理
    For Each w In Worksheets
    
 'ただし、対象のシートのみ処理
        If Right(w.Name, 1) = "s" Then
            '各シートでデータ数をカウントし、コピー
            Max_row1 = w.Range("a" & Rows.Count).End(xlUp).Row
            w.Range("i3", "m3").Copy w.Range("i4", "m" & Max_row1)
            w.Range("i3", "m" & Max_row1).Copy
            
            
            
            'シート「data」に値のみ貼り付け
            Max_row2 = Worksheets("data").Range("a" & Rows.Count).End(xlUp).Row
            Worksheets("data").Range("a" & Max_row2 + 1).PasteSpecial Paste:=xlPasteValues
        End If
    
    Next

End Sub

すべてのシートで処理する、for Eachを使うのですが、すべてのシートをコピーするわけではありません。
預金のシート名の一番右に、「s」をつけて、「s」ついたものだけを集計します。

    If Right(w.Name, 1) = "s" Then

	End if

の部分がその処理をしています。
Rightは、右から○番目の文字を取り出せる関数です。

サンプルで動きを確認してみてください。
EX-ITサンプル　預金入力→集約マクロ

プラスα

預金の処理を効率化するには、まず口座を減らすことが大事です。
手続きが大変ですが、毎月又は毎日やることですので、思い切って口座を減らしていきましょう。

そして、やはりネットバンクは便利です。
ネットバンクを使っていれば、ブラウザ（サイト）で明細を表示し、それをコピーすれば、今回のしくみが使えます。
ポイントは、マクロが動くように、各シートに仕訳をつくる列を統一することです。
今回は、I列からM列に仕訳がありましたが、もし、貼り付けたネットバンクのデータがY列まであれば、Z列から仕訳を作るようにします。
最大のデータにあわせるようにしましょう。

お客様、そして自分の経理でそのしくみを使っています。
【関連記事】ゆうちょ銀行のデータをExcelで加工して会計ソフトへ取り込む方法 | EX-IT
リンク

「ネットバンクのデータを連動できる！」というソフトが増えてきましたが、Excelを使った連動は10年前からこっそり使っていました。
今もExcelの方が楽な部分もあり、総合的に見るとExcelが便利なので、こちらを使っていることが多いです。

ネットバンクが心配という方はこちらの記事も参考にしてください。
【関連記事】三井住友銀行 SMBCダイレクトで不正送金。ネットバンクで不正送金の被害にあわないための7つの対策 | EX-IT
リンク

---

---

---

---

【編集後記】
去年買ったばかりの掃除機が故障・・。
本体が曲がっている部分があり、それが原因のようです。
修理に出さなきゃ・・と思う一方で、ルンバも気になっています。
ずっと前に買ったのは、壊れてしまったので。

【昨日の1日1新】
※詳細は→「1日1新」

近くの定食屋
オークリー調光タイプ