コピペ可！Excelマクロによる総勘定元帳作成

会計ソフトを使わなくても、不動産投資家の不動産所得のように、毎月の仕訳がルーティンで、複雑な仕訳がない方は、Excelマクロで総勘定元帳を作成することができます。

日々の取引から確定申告までの流れは「①仕訳帳→②総勘定元帳→③残高試算表→④決算書→⑤申告書」です。

前後しますが、以下の流れで作成します。

1. ①仕訳帳からExcel関数で③残高試算表を作成
2. ①仕訳帳③残高資産表からExcelマクロで②総勘定元帳を作成

残高試算表を切り分けると、決算書が作成されます。

申告書のもとになる仕訳帳や総勘定元帳を自分で作成し、取引を把握しておくことは、節税をする上でも大切です。

Excelマクロによる総勘定元帳作成のメリット

1. Excelマクロを実行すると、一瞬で総勘定元帳が作成される
2. 会計ソフトのコストがかからない
3. 自分の好きなように総勘定元帳などをカスタマイズすることが可能
4. 簡易帳簿から複式簿記に変える手間がかからない

4について補足します。

簡易帳簿で青色申告特別控除10万円を受けている方は、事業的規模になると、基本的には複式簿記である総勘定元帳を作成しないと、青色申告特別控除55万円が受けられません。

不動産所得における事業的規模の判断基準は、5棟10室以上であり、アパート2棟で10室は超えてきそうです。

当初から総勘定元帳を作成しておくと、簡易帳簿から複式簿記に変える手間がかかりません。

Excelマクロによる総勘定元帳作成の事前準備

シート名を「仕訳帳」「試算表」「元帳様式」として、それぞれ作成しておきます。

仕訳帳の作成・入力

日々の取引を仕訳帳に入力していきます。
賃借料、管理手数料等の取引は、毎月ルーティンで、仕訳をコピーして貼り付けることができます。

見やすくするため、テーブル形式にしていますが、テーブル形式でなくてもかまいません。

留意点としては、1行ごとに仕訳を入力しているので、以下のような仕訳は分解する必要があります。

（例）建物1,000万円を購入して、頭金200万円を現金で支払い、残りはローンを組んだ。
建物1,000万円／現金　200万円　
　　　　　　　／借入金800万円
　　　　　　　↓
建物　800万円／現金　200万円
建物　200万円／借入金800万円

残高試算表の作成

科目、借方貸方判定、期首残高をあらかじめ入力しておきます。
科目は、仕訳帳で使った借方科目、貸方科目を列挙します。
借方貸方判定は、借方科目が１、貸方科目－1で、期末残高の計算の際に使います。

借方金額以降は、関数や計算式が入力されており、以下がCtrl+Shift+@で表示させたものになります。

借方金額、貸方金額には、SUMUIF関数を使っています。
SUMUIF関数は、検索条件に合う値の合計を出したい場合に使います。

SUMIF(検索範囲,検索条件,合計範囲)

仕訳帳の借方科目と貸方科目（検索範囲）から、残高試算表の科目（検索条件）を検索して、一致した場合に、仕訳帳の借方科目と貸方科目の金額（合計範囲）の合計を表示させています。

実際に、残高試算表の最初の科目である預金で確認してみます。

借方金額は、SUMIF(仕訳帳!B:B,A2,仕訳帳!D:D)になっています。

仕訳帳シート（仕訳帳!）のB列（B:B）の借方科目から、このシートの科目である預金（A2）を検索して、一致した場合に、仕訳帳シート（仕訳帳!）のD列（D:D）の金額の合計を表示させます。

貸方金額の式も同じ内容で、検索範囲を仕訳帳シート（仕訳帳!）のC列（C:C）の貸方科目に変えたものになります。

期末残高の計算式は「期首残高+借方貸方判定×借方金額－借方貸方判定×貸方金額」になります。

期末残高の計算式は、本来、科目の種類によって区別する必要がありますが、「借方金額－貸方金額」に借方貸方判定をかけることによって、Ｆ列のセルも他のセルと同様にコピーして貼り付けることができます。

最後に、以下の方法でチェックしています。

• 仕訳帳シートの金額の合計（D16）が、試算表シートの借方金額、貸方金額の合計（D13,E13）と一致するかを確認することによって、科目の拾い漏れがないか
• 試算表シートで、借方貸方判定に期末残高をかけて、合計（E13）が0になるかを確認することによって、科目の借方貸方判定に誤りがないか

総勘定元帳様式の作成

仕訳帳から転記される総勘定元帳のひな型を用意しておきます。

総勘定元帳作成のExcelマクロ全コード

ExcelマクロのVBAコードの書き込み方は、インターネットで検索するとたくさん出てくるので、参考にしてください。

VBAコードはコピーして、総勘定元帳の作成に使っていただいてもかまいません。

ただ、シート名やセル位置は、VBAコードとリンクしているので、次の章の解説も読み、VBAコードの内容を大まかに知っていただいた上で使っていただければと思います。

Sub 総勘定元帳作成()

Dim ws As Worksheet

Dim tbAccount As String

Dim tbY As Long
Dim tbHantei As Long
Dim tbZan As Long
Dim i As Long
Dim joY As Long

Dim tbRange As Range
Dim joRange As Range

'既存シート削除
 Application.DisplayAlerts = False
  For Each ws In Worksheets
   If ws.Name <> "仕訳帳" And ws.Name <> "試算表" And ws.Name <> "元帳様式" Then
    ws.Delete
   End If
  Next
 Application.DisplayAlerts = True

'試算表読み込み
 For Each tbRange In Worksheets("試算表").Range("A2:A12")
  With Worksheets("試算表")
   tbY = tbRange.Row
   tbAccount = .Cells(tbY, 1)
   tbHantei = .Cells(tbY, 2)
   tbZan = .Cells(tbY, 3)
  End With

'総勘定元帳を作成
 Worksheets("元帳様式").Copy after:=Worksheets(Worksheets.Count)
 Set ws = ActiveSheet
 ws.Name = tbAccount
 ws.Cells(1, 1) = tbAccount
 ws.Cells(3, 5) = tbZan
 i = 4
 
 With ws
 
'仕訳帳の借方を総勘定元帳に出力
  For Each joRange In Worksheets("仕訳帳").Range("A2:A15")
   joY = joRange.Row
 
   If Worksheets("仕訳帳").Cells(joY, 2) = tbAccount Then
    .Cells(i, 1) = Worksheets("仕訳帳").Cells(joY, 1)
    .Cells(i, 2) = Worksheets("仕訳帳").Cells(joY, 3)
    .Cells(i, 3) = Worksheets("仕訳帳").Cells(joY, 4)
    .Cells(i, 4) = 0
    .Cells(i, 5) = .Cells(i - 1, 5) + .Cells(i, 3) * tbHantei - .Cells(i, 4) * tbHantei
    .Cells(i, 6) = Worksheets("仕訳帳").Cells(joY, 5)
    i = i + 1
   End If
 
'仕訳帳の貸方を総勘定元帳に出力
   If Worksheets("仕訳帳").Cells(joY, 3) = tbAccount Then
    .Cells(i, 1) = Worksheets("仕訳帳").Cells(joY, 1)
    .Cells(i, 2) = Worksheets("仕訳帳").Cells(joY, 2)
    .Cells(i, 3) = 0
    .Cells(i, 4) = Worksheets("仕訳帳").Cells(joY, 4)
    .Cells(i, 5) = .Cells(i - 1, 5) + .Cells(i, 3) * tbHantei - .Cells(i, 4) * tbHantei
    .Cells(i, 6) = Worksheets("仕訳帳").Cells(joY, 5)
    i = i + 1
   End If
 
  Next

'期末残高記載
   .Cells(i, 1) = ""
   .Cells(i, 2) = "期末残高"
   .Cells(i, 3) = 0
   .Cells(i, 4) = 0
   .Cells(i, 5) = ws.Cells(i - 1, 5) + ws.Cells(i, 3) * tbHantei - ws.Cells(i, 4) * tbHantei
   .Cells(i, 6) = ""
  
  End With

 Next

End Sub

総勘定元帳作成のExcelマクロ各コードの説明

変数の宣言

Dim ws As Worksheet

Dim tbAccount As String

Dim tbY As Long
Dim tbHantei As Long
Dim tbZan As Long
Dim i As Long
Dim joY As Long

Dim tbRange As Range
Dim joRange As Range

3～14行目で変数を宣言し、それ以降で処理を行います。

• 変数WSをWorksheet（ワークシート）
• 変数tbAccountをString（文字列）
• 変数tbY、tbHantei、tbZan、i、joYをLong（数値）
• 変数tbRange、joRangeをRange（セル範囲）

tbはTraial Balance（残高試算表）の略で試算表シート、joはJournal（仕訳帳）の略で仕訳帳シートで使っていることを意味します。

既存の総勘定元帳の削除

'既存シート削除
 Application.DisplayAlerts = False
  For Each ws In Worksheets
   If ws.Name <> "仕訳帳" And ws.Name <> "試算表" And ws.Name <> "元帳様式" Then
    ws.Delete
   End If
  Next
 Application.DisplayAlerts = True

16～23行目で既存の総勘定元帳シートを削除します。

If～EndIF文により、仕訳帳、試算表、元帳様式以外のシートがあれば、削除しています。

ForEach～Next文により、変数WSを全てのシートとして、全てのシートについて繰り返し処理しています。

シートを削除すると、確認メッセージが出るため、Application.DisplayAlerts = Falseで出ないようにしています。

Trueでもとに戻しておきます。

残高試算表の読込み

'試算表読み込み
 For Each tbRange In Worksheets("試算表").Range("A2:A12")
  With Worksheets("試算表")
   tbY = tbRange.Row
   tbAccount = .Cells(tbY, 1)
   tbHantei = .Cells(tbY, 2)
   tbZan = .Cells(tbY, 3)
  End With

25～32行目で残高試算表を読み込みます。

ForEach～Next文により、変数tbRangeを試算表シートのデータがあるA2～A12の範囲として、A2～A12について繰り返し処理しています。

81行目のNextからForEachに戻ってきて、現在の勘定科目がA2であれば、次の勘定科目のA3に移ります。

各変数に以下を代入しています。

• tbY→tbRangeの行数＝2～12
• tbAccount→試算表シートのセル(行数, 1)＝勘定科目
• tbHantei→試算表シートのセル(行数, 2)＝借方貸方判定
• tbZan→試算表シートのセル(行数, 3)＝期首残高

With～EndWith文により、 Cellsの前に入るシート名を省略しています。

総勘定元帳の作成

'総勘定元帳を作成
 Worksheets("元帳様式").Copy after:=Worksheets(Worksheets.Count)
 Set ws = ActiveSheet
 ws.Name = tbAccount
 ws.Cells(1, 1) = tbAccount
 ws.Cells(3, 5) = tbZan
 i = 4

34～40行で総勘定元帳シートを作成します。

元帳様式のシートをコピーして、最後のシート（Worksheets（Worksheets.Count））の後に総勘定元帳シートを作成します。

シート名をtbAccount（勘定科目）とし、セル(1,1)にtbAccount（勘定科目）、セル(3,5)にtbZan（期首残高）を入力しています。

変数iは次で使いますが、4を代入しているのは4行目から書き込んでいくためです。

仕訳帳から総勘定元帳への転記

'仕訳帳の借方を総勘定元帳に出力
  For Each joRange In Worksheets("仕訳帳").Range("A2:A15")
   joY = joRange.Row
 
   If Worksheets("仕訳帳").Cells(joY, 2) = tbAccount Then
    .Cells(i, 1) = Worksheets("仕訳帳").Cells(joY, 1)
    .Cells(i, 2) = Worksheets("仕訳帳").Cells(joY, 3)
    .Cells(i, 3) = Worksheets("仕訳帳").Cells(joY, 4)
    .Cells(i, 4) = 0
    .Cells(i, 5) = .Cells(i - 1, 5) + .Cells(i, 3) * tbHantei - .Cells(i, 4) * tbHantei
    .Cells(i, 6) = Worksheets("仕訳帳").Cells(joY, 5)
    i = i + 1
   End If
 
'仕訳帳の貸方を総勘定元帳に出力
   If Worksheets("仕訳帳").Cells(joY, 3) = tbAccount Then
    .Cells(i, 1) = Worksheets("仕訳帳").Cells(joY, 1)
    .Cells(i, 2) = Worksheets("仕訳帳").Cells(joY, 2)
    .Cells(i, 3) = 0
    .Cells(i, 4) = Worksheets("仕訳帳").Cells(joY, 4)
    .Cells(i, 5) = .Cells(i - 1, 5) + .Cells(i, 3) * tbHantei - .Cells(i, 4) * tbHantei
    .Cells(i, 6) = Worksheets("仕訳帳").Cells(joY, 5)
    i = i + 1
   End If
 
  Next

44～69行で仕訳帳の貸方・借方を総勘定元帳に転記しています。

ForEach～Next文により、変数joRangeを仕訳帳シートのデータがあるA2～A15の範囲として、A2～A15について繰り返し処理しています。

変数joYに joRangeの行数＝2～15を代入しています。

If～EndIF文により、仕訳帳シートのセル(行数,2)（借方科目）が、試算表シートのtbAccount（勘定科目）と一致した場合に、総勘定元帳シートの各セルに以下を入力しています。

• セル(4,1)→仕訳帳シートのセル(行数,1)＝年月日
• セル(4,2)→仕訳帳シートのセル(行数,3)＝貸方科目
• セル(4,3)→仕訳帳シートのセル(行数,4)＝金額
• セル(4,4)→0
• セル(4,5)→セル(3,5)＋セル(4,3)×tbHantei－セル(4,4)×tbHantei
• セル(4,6)→仕訳帳シートのセル(行数,5)＝摘要

セル(4,5)には残高の計算式を入力しています。

1行前の残高＋借方金額－貸方金額です。

勘定科目が貸方科目であれば、1行前の残高－借方金額＋貸方金額になるため、借方貸方判定の－1をかけています。

総勘定元帳シートの4行目に書き込みが終わったので、変数iに1を足し、次は5行目からになります。

仕訳帳シートの借方科目についても同じ要領になります。

総勘定元帳の期末残高の記載

'期末残高記載
   .Cells(i, 1) = ""
   .Cells(i, 2) = "期末残高"
   .Cells(i, 3) = 0
   .Cells(i, 4) = 0
   .Cells(i, 5) = ws.Cells(i - 1, 5) + ws.Cells(i, 3) * tbHantei - ws.Cells(i, 4) * tbHantei
   .Cells(i, 6) = ""

71～77行で、総勘定元帳の期末残高を記載しています。

期末残高を記載するために、総勘定元帳シートの各セルに以下を入力しています。

• セル(最終行,1)→（空欄）
• セル(最終行,2)→期末残高
• セル(最終行,3)→0
• セル(最終行,4)→0
• セル(最終行,5)→セル(最終行数－1,5)＋セル(最終行数,3)×tbHantei－セル(最終行数,4)×tbHantei
• セル(最終行,6)→（空欄）