【かんたん、今すぐ使える！】エクセルマクロで総勘定元帳を作成

会計ソフトを使わなくても、エクセルのマクロで仕訳帳から総勘定元帳を作成することができます。

事前に、仕訳帳、残高試算表、総勘定元帳様式の3つのシートを用意しておきます。
そして、VBAコードを記載して、マクロを実行すると、総勘定元帳のシートが科目ごとに次々と作成されます。

総勘定元帳 Excel

【エクセルマクロが初めての方】VBAコードの記載の仕方
エクセルマクロで総勘定元帳を作成する理由
事前準備するエクセルシート
総勘定元帳作成のエクセルマクロVBAコード
総勘定元帳作成のエクセルマクロVBAコードの解説

【エクセルマクロが初めての方】VBAコードの記載の仕方

①エクセルの「開発」タブから「コード」→「Visual Basic」を選びます。

②VBA画面が出てくるので、「挿入」から「標準モジュール」を選ぶと、右側にＶBAコードが入力できるようになります。

③ＶBAコードを入力した後、マクロを実行するには、①のエクセルの「開発」タブから「コード」→「マクロ」を選ぶか、②のVBA画面の以下の「▶」を押すか、どちらかになります。

エクセルマクロで総勘定元帳を作成する理由

会計ソフトを使って、総勘定元帳を作成するには費用がかかります。
せっかくお金を払って会計ソフトを購入したのに、自分の仕様に合わず、使いにくいこともあります。
複雑な仕訳がないのであれば、エクセルでも総勘定元帳を作成することができます。

エクセル関数で総勘定元帳を作成するには手間がかかるので、やめた方がいいでしょう。
エクセルのマクロを使うと、仕訳帳から総勘定元帳を効率的に作成することができます。
仕訳帳にある科目の総勘定元帳を作成し、仕訳帳のデータを総勘定元帳に素早く転記させることができます。

エクセルのマクロは業務を効率化させる上で、欠かせないツールです。

事前準備するエクセルシート

仕訳帳

テーブル形式ですが、通常の形式でも大丈夫です。

残高試算表

事前に仕訳帳から残高試算表を作成しておきます。
シート名は「試算表」としています。

【誰にでも簡単にできる！】エクセルで残高試算表を作成

会計ソフトを使わなくても、エクセルで仕訳帳から残高試算表を作ることができます。残高試算表を切り分けると、貸借対照表と損益計算書ができあがります。エクセルで残高試算表を作る方法として、SUMIF関数で直接集計する方法と、ピボットテー

総勘定元帳の様式

仕訳帳から出力される総勘定元帳のひな型を作成しておきます。
シート名は「元帳様式」としています。

マクロを実行すると、以下のようなシートが作成されます。

総勘定元帳作成のエクセルマクロ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:A14")
  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:A23")
   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

総勘定元帳作成のエクセルマクロVBAコードの解説

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

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

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

既存の総勘定元帳シートの削除

16～23行目になります。

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

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

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

残高試算表の読み込み

25～32行目になります。

ForEach～Next文により、変数tbRangeを試算表シートのデータがあるA2～A14の範囲として、A2～A14について繰り返し処理しています。
81行目のNextからForEachに戻ってきて、現在の勘定科目がA2であれば、次の勘定科目のA3に移ります。

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

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

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

総勘定元帳シートの作成

34～40行になります。

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

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

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

仕訳帳の貸方・借方を総勘定元帳に転記

44～69行になります。

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

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

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行目からになります。

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

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

71～77行になります。

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

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