会計ソフト不要!?エクセルを使って総勘定元帳(帳簿)を作る

公開日: 税務申告

事業をしている方ならば、一度は、

「エクセルを使って帳簿を作れるだろうか?」

と考えることと思います。

実際のところは「万人におすすめできるものではない」のですが、やろうと思えばできないことはありません。

例えば、次の動画の帳簿程度であれば、慣れれば数時間の作業で作ることができます。

※下記を再生しても音は出ませんので、音が出せない環境でもご安心ください。

そこで、今回は、上記動画で出てきた、

  • 仕訳帳から試算表を作る
  • 仕訳帳から総勘定元帳を作る

方法を紹介します。

エクセルでの帳簿記帳をおすすめしない理由

エクセルでの帳簿記帳をおすすめしない理由は、難易度が非常に高いからです。

というのは、会計・税務知識と、エクセルの知識の両方が要求されるのです

1.会計・税務知識

エクセルで帳簿を記帳するためには、次のようなことを理解している必要があります。

  • どういう帳簿が必要か?
  • 帳簿には、どのような項目が必要か?
  • どのような手順でそれぞれの帳簿を作成するのか?(=複数の帳簿間の関連性が理解できているか?)

要は、元々会計ソフトを使いこなしていて「会計ソフトというのはこういうデータを扱うものだ」というイメージがしっかりできている人でないと、エクセルで帳簿は作れません。

2.エクセルの知識

エクセルで帳簿を作成するためには、作成すべき帳簿をエクセルで作成できるだけの「エクセルの知識」が必須です。

特に、一番の難関が「総勘定元帳」です。

総勘定元帳は、関数だけで作ると、日常の使い勝手やメンテナンス性が非常に悪くなるため、マクロを使わざるをえません

結局、マクロを使えない人は、エクセルで帳簿は作れないということになります。

このように、エクセルで帳簿をつくろうと思うと、

  • 会計・税務の知識
  • VBAマクロなどの、エクセルの知識

の両方が必要になるため、かなりハードルが高いです。

それでもエクセルで帳簿が作りたい!

逆に言うと、上記1、2の両方の知識があれば、エクセルで帳簿を作れないことはありません。

そこで、この記事では、帳簿をエクセルで作る「アイデア」をお伝えしていこうと思います。

仕訳帳(入力フォーマット)を準備する

帳簿を作成する場合、最初に「仕訳帳」を作成することが一般的です。

そこで、その入力フォーマットを準備します。
実際に、データを入力すると次のような感じになります。

tyoubo_1_1

仕訳帳から試算表を作る

試算表を作るのは、それほど難しくはありません。

次の画像のように、科目別の借方・貸方合計をsumif関数で集計します(D列、E列)。
その後、期首残高との差引で期末残高を計算します(F列)。

tyoubo_1_2

試算表を作成する場合、

  • 借方科目(資産など) → 借方残高:プラス、貸方残高:マイナス
  • 貸方科目(負債など) → 借方残高:マイナス、貸方残高:プラス

というように、科目により符号を反転させて表示させるケースがあります。

今回も、その流儀で表示をさせるために、B列に「符号」という列を準備し、借方科目・貸方科目に応じて符号を反転させています。

それにあわせ、C列の期首残高も、

  • 資産などの科目 → 借方がプラス
  • 負債などの科目 → 貸方がプラス

となるように入力しています。

仕訳帳から総勘定元帳を作る

次に、仕訳帳から総勘定元帳を作っています。

総勘定元帳のイメージは、次のようなものです。

tyoubo_1_3

総勘定元帳は、各科目ごとに、

  1. 科目ごとの総勘定元帳シートを作成し、期首残高を転記
  2. 仕訳帳の各行ごとに、仕訳帳の「借方」「貸方」に該当科目があれば転記
  3. 期末残高を転記

を表示させて作ることができます。

先ほどの総勘定元帳と、上記の手順は次のように対応しています。

tyoubo_1_3_2

言葉で書くと簡単なのですが、使い勝手を考えると、細かいところでいろいろ気をつけないといけない点があります。

ソースコード

実際に私が作ったソースコードを次に載せておきます。

わかりやすさを優先するため、参照するセルを決めうちしているなど、汎用性はあまりありません。
その点、ご理解のうえ、ご使用ください。

Dim oWS As Worksheet
Dim oY As Long

Dim acName As String
Dim acFugou As Long
Dim acZan As Long

Sub create()
 Dim acY As Long
 Dim docY As Long
  
 Dim acRange As Range
 Dim docRange As Range
 
'既存シート削除
 On Error Resume Next
 Application.DisplayAlerts = False
 For Each acRange In Worksheets("試算表").Range("A3:A51")
  Worksheets(acRange.Value).Delete
 Next
 Application.DisplayAlerts = True
 On Error GoTo 0
 
'勘定科目リストを読み込み
 For Each acRange In Worksheets("試算表").Range("A3:A51")
  With Worksheets("試算表")
   acY = acRange.Row
   acName = .Cells(acY, 1)
   acFugou = .Cells(acY, 2)
   acZan = .Cells(acY, 3)
  End With
  
  If acName <> "" Then
   Set oWS = Nothing
   
'2.仕訳帳の各行の処理
   With Worksheets("取引")
    For Each docRange In .Range("A1:A50")
     docY = docRange.Row
     
     If .Cells(docY, 2) = acName Then
'仕訳の借方を総勘定元帳に出力
      Call write_row(.Cells(docY, 1), .Cells(docY, 3), _
                     .Cells(docY, 4), 0, .Cells(docY, 5))
     End If
    
     If .Cells(docY, 3) = acName Then
'仕訳の貸方を総勘定元帳に出力
      Call write_row(.Cells(docY, 1), .Cells(docY, 2), _
                     0, .Cells(docY, 4), .Cells(docY, 5))
     End If
    Next
    
'3.期末残高処理
    If Not oWS Is Nothing Or acZan <> 0 Then
     Call write_row("", "期末残高", 0, 0, "")
    End If
   End With
  End If
 Next
End Sub

Sub write_row(d, ac, dr, cr, text)
 If oWS Is Nothing Then
'1.期首残高を転記(+該当科目のワークシート作成)
  Worksheets("元帳ブランク").Copy after:=Worksheets(Worksheets.Count)
  Set oWS = ActiveSheet
  oWS.Name = acName
  oWS.Cells(1, 1) = acName
  oWS.Cells(3, 5) = acZan
  oY = 4
 End If
 
'2.期中仕訳 あるいは 3.期末残高の転記処理本体
 With oWS
  .Cells(oY, 1) = d
  .Cells(oY, 2) = ac
  .Cells(oY, 3) = dr
  .Cells(oY, 4) = cr
  .Cells(oY, 5) = .Cells(oY - 1, 5) + dr * acFugou - cr * acFugou
  .Cells(oY, 6) = text
 End With
 oY = oY + 1
End Sub

以下、重要そうな部分について解説をしていきます。

既存シートの削除

'既存シート削除
 On Error Resume Next
 Application.DisplayAlerts = False
 For Each acRange In Worksheets("試算表").Range("A3:A51")
  Worksheets(acRange.Value).Delete  
 Next
 Application.DisplayAlerts = True
 On Error GoTo 0

総勘定元帳作成時には、各科目ごとに「科目名のシート」を新規作成します。

ところが、シートを新規作成する場合に、同名のシートが存在しているとエラーが出てしまいます。

そのため、事前に「科目名のシート」を削除します。

次のように、試算表シートのA列(A3セル~A51セル)に勘定科目のリストが入っているので、

  1. A列の各セルごとに
  2. そのセルに入力されている値(=勘定科目)と同一のシート名があれば削除します
tyoubo_1_4

なお、シート削除前に、

  • シートを削除する際の確認メッセージが出力されないように「Application.DisplayAlerts=False」
  • 元々シートが存在していないときにエラーが生じないように「On Error Resume Next」

文を実行しておき、シート削除後に、

  • シートを削除する際の確認メッセージを出力するように「Application.DisplayAlerts=True」
  • エラーが生じたときは通常のエラー処理が行われるように「On Error Goto 0」

と元の状態に戻す文を入れています。

勘定科目ごとの処理

'勘定科目リストを読み込み
 For Each acRange In Worksheets("試算表").Range("A3:A51")
  With Worksheets("試算表")
   acY = acRange.Row
   acName = .Cells(acY, 1)
   acFugou = .Cells(acY, 2)
   acZan = .Cells(acY, 3)
  End With
  
  If acName <> "" Then
    (後述。総勘定元帳作成処理)
  End If
 Next

総勘定元帳を作るために勘定科目ごとの処理を行います。

先ほどと同じように、for each~nextで勘定科目リストを読み込みます。

あとは、

  • acNameに勘定科目名
  • acFugouに借方科目か貸方科目かの符号
  • aczanに期首残高

を入れて、総勘定元帳作成処理に入ります。

総勘定元帳作成処理(枠組み)

  If acName <> "" Then
   Set oWS = Nothing
   
'2.仕訳帳の各行の処理
   With Worksheets("取引")
    For Each docRange In .Range("A1:A50")
     docY = docRange.Row
     
     If .Cells(docY, 2) = acName Then
'仕訳の借方を総勘定元帳に出力
      Call write_row(.Cells(docY, 1), .Cells(docY, 3), _
                     .Cells(docY, 4), 0, .Cells(docY, 5))
     End If
    
     If .Cells(docY, 3) = acName Then
'仕訳の貸方を総勘定元帳に出力
      Call write_row(.Cells(docY, 1), .Cells(docY, 2), _
                     0, .Cells(docY, 4), .Cells(docY, 5))
     End If
    Next
    
'3.期末残高処理
    If Not oWS Is Nothing Or acZan <> 0 Then
     Call write_row("", "期末残高", 0, 0, "")
    End If
   End With
  End If

総勘定元帳の作成部分です。
といっても、実際の出力はwrite_row関数で行っているので、上記は総勘定元帳を出力するための「枠組み」部分のみとなります。

上記ソースを見ると、一見不思議に感じるところがいくつかあるかもしれません。

期首残高を表示する処理がない

総勘定元帳を作成する場合には、

  1. 科目ごとの総勘定元帳シートを作成し、期首残高を転記
  2. 仕訳帳の各行ごとに、仕訳帳の「借方」「貸方」に該当科目があれば転記
  3. 期末残高を転記

という手順を踏みます。

ですから、直感的には、上記ソースの一番最初に「科目ごとの総勘定元帳シートを作成し、期首残高を転記する処理」が来ないといけないはずです。

でも、それがありません。

なぜかというと、必ずしも全ての科目のシートを作成するわけではないからです。

tyoubo_1_1

例えば、上記のように「仮払金」の仕訳がまったくなく、期首残高も0の場合には「仮払金」の総勘定元帳は作る必要がありません

当たり前ですよね?
こういう場合は、「仮払金」シートはあるだけ邪魔なので、できれば作らないで済ませたいわけです。

ところが「仮払金」シートを作る必要があるかどうかは、実際に仕訳データをすべて見て、仮払金に関する仕訳の有無を調べないとわかりません

そこで、今回は少し変則的なのですが「期首残高を表示する処理」はwrite_row関数の冒頭に持ってきています。

write_row関数は、その科目の取引がある場合にだけ呼び出されますので、その中で、必要に応じて期首残高を表示する処理を書いているのです。

変数oWSにNothingを指定している

変数oWSは「出力先の総勘定元帳シート」を示すオブジェクトを入れるための変数です。

例えば「仮払金」の総勘定元帳を「仮払金シート」に作成しようとしている場合には、「仮払金シート」を示すオブジェクトを、変数oWSに代入します。

通常の流れだと、変数oWSへの代入は、上記ソースの一番最初に行うことが一般的です。

ところが、今回は「Nothing」を代入しています。

というのは、先ほどと同じ理由で、write_row関数が呼び出されないことには、その科目に関する総勘定元帳を作るかどうかがわからないからです。

そこで、この変数には、先ほど出てきたwrite_row関数内部の「期首残高を表示する処理」の中で、適切なワークシートオブジェクトを代入しています。

期末残高の出力条件

期末残高の出力条件も若干複雑ですが、次のどちらかの条件を満たす場合に出力をするようにしています。

  • 期中取引が1件でもあった場合(=oWSがNothingで「ない」場合)
  • 期首残高が0でない場合

例えば、期中取引は全くなかったが期首残高が100だったという場合には、上記条件を満たすことになります。

総勘定元帳の出力処理(本体)

Sub write_row(d, ac, dr, cr, text)
 If oWS Is Nothing Then
'1.期首残高を転記(+該当科目のワークシート作成)
  Worksheets("元帳ブランク").Copy after:=Worksheets(Worksheets.Count)
  Set oWS = ActiveSheet
  oWS.Name = acName
  oWS.Cells(1, 1) = acName
  oWS.Cells(3, 5) = acZan
  oY = 4
 End If
 
'2.期中仕訳 あるいは 3.期末残高の転記処理本体
 With oWS
  .Cells(oY, 1) = d
  .Cells(oY, 2) = ac
  .Cells(oY, 3) = dr
  .Cells(oY, 4) = cr
  .Cells(oY, 5) = .Cells(oY - 1, 5) + dr * acFugou - cr * acFugou
  .Cells(oY, 6) = text
 End With
 oY = oY + 1
End Sub

このロジックが、総勘定元帳出力処理の本体です。

冒頭で「変数oWSがNothingかどうか」を調べています。

Nothingの場合には、この勘定科目についての「最初の出力処理」だということがわかりますので、

  • ワークシートを新たに作成(正確には、あらかじめ作成済みの雛形「元帳ブランクシート」からコピー)し
  • 期首残高などを出力します

その後の「2.期中仕訳 あるいは 3.期末残高の転記処理本体」では、基本的には、引数として受け取った情報をそのまま転記しています。

1つだけ複雑な計算式が入っていますが(赤字部分)、その部分では(借方科目か貸方科目かに応じて)残高を計算して出力しています。

計算式を見ていただければ感覚的に理解できると思うのですが、考え方としては、試算表を作成するときの考え方と全く一緒です。

ダウンロード

このページのように、帳簿を作ろうと思えば、帳簿を作れないこともありません。

今回作ったエクセルシートは、下記からダウンロードできるようにしておきますので、興味がある方は試してみてください。
総勘定元帳作成エクセルサンプルのダウンロードはこちら


  • twitter
  • facebook
  • はてなブックマーク

経理事務のためのエクセル基礎講座動画マニュアル無料配布中

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!


タグ