Excelのみで経理をやる場合、総勘定元帳（元帳）が問題となります。
元帳作成には、マクロが必要です。
私が実際にやっている事例で解説します。
法人も個人も法律上、元帳が必要
必ず作らなければいけない「元帳」とは
法人も個人（個人事業、フリーランス）も、経理において最終的に、元帳（総勘定元帳）といわれるものが必要です。
これが青色申告の要件になっており、税務調査（税務署が数年に1回、実際に資料や領収書を見に来る）のときには、提示しなければいけません。
提示するのは、
・元帳
・領収書、レシート、請求書、契約書等
です。
決算書や税務申告書は提出していますので、税務署側が持っていますが、これらのものは、提出しません。
そのため、実際に見に来るしかないのです。
誤解が多いのは、伝票。
伝票は必ずしも必要ではありません。
領収書、レシートから、PCへ直接入力して大丈夫なのです。
会計ソフトは私にとっても使いにくく感じます。
従来型のソフト（PCにインストールするもの。弥生会計が代表的）であれ、クラウド型のソフトであれ、決定打がありません。
それでいて、お金はかかります。
Excelで経理をやりたいという方も多いはずです。
しかし、使い慣れたExcelに入力して、集計することは比較的簡単ですが、元帳を作るのは簡単ではありません。
元帳の具体的な事例
元帳とは、こんなものです。
たとえば、「現金で会議費を払った」という取引があります。
会議費/現金 340という仕訳です。
これを、現金側から見た「現金」の元帳へ転記し、
会議費の側から見た、「会議費」の元帳へ転記します。
元々は、手書きでこういった経理をやっていましたので、その間違いを防ぐためにこういった転記をする仕組みなのです。
（元帳の形式で入力することもできます。これが現金出納帳や預金出納帳です）
今は、ソフトでやりますので、自動的にこの転記をやってくれます。
Excelなら、この転記を1つずつ、自分でやらなければいけないのです。
Excelで普通に元帳を作るなら
Excelで元帳を作るなら、かなりの作業になります。
わかりやすいように、同じシートへ元帳を作ってみましょう。
仕訳のデータから、まず「現金」の元帳へ、日付、科目（会議費）、金額、摘要をコピーして貼り付け、残高を計算し、
次に相手科目の「会議費」の元帳へ、日付、科目（会議費）、金額、摘要をコピーして貼り付け、残高を計算します。
相手科目が「交際費」なら、交際費の元帳へコピペし、これをすべての科目でやらなければいけません。
Excelで作るならマクロが必須です。
※現実的には、Excelに入力し、集計して、経営判断にも使い、最終的に会計ソフトへ取り込んで、元帳を作るというのがおすすめです。
会計ソフトは元帳作成ソフトとしてだけ使うのです。
そうすれば、入力も集計も楽で、自分なりに使いやすくできるExcelの良さが最大限に活かせます。
クラウド会計ソフトのメリットである、ネットバンク取り込みも、Excelでできますし、長年私はこれをやっています。
【関連記事】預金データを会計ソフトに取り込む方法(基礎編) | EX-IT
リンク
Excelで、仕訳→集計の流れを作っておくのが前提
Excelに入力した仕訳データから元帳を作ることもできますが、問題もあります。
1つは、期首の残高。
B/S科目は、期首の残高を元帳に反映しなければいけません。
もう1つは、元帳作成科目の準備。
どの科目の元帳を作るか、どの順番で作るかの指定をしなければいけません。
こういった問題を解決するなら、Excelで仕訳→集計の流れを作っておく方が、元帳作成上も便利です。
【関連記事】Excel、マクロで試算表を作る方法ーピボットテーブル、VLOOKUPを活用ー | EX-IT
リンク
今回は、シンプルに1ヶ月分のみ集計し、消費税は免税である場合の事例でご紹介します。
入力は仕訳で行い（現金、預金を元帳形式で入力、変換することもできます）、
このような集計表です。
推移表を作ることを想定していますので、B/S、P/Lを縦に並べます。
ファイルの構成は、
・data
仕訳データ
・集計
集計表
です。
マクロでは、「集計」で科目名や期首残高を取得し、「data」よりデータを抽出して新しいブックへ元帳を作ります。
Excelマクロで元帳を作る方法
こんなマクロです。
事例に合わせて作っていますので、加工しないと使えません。
考え方を理解していただければと思います。
カーソルを置いたセルでマクロを実行すると、そのセルがある科目の元帳を作るものです。
一括で作るなら、このマクロを加工して、すべての科目で作るマクロにしましょう。
Sub leger()
Dim Wb As Workbook
Set Wb = ThisWorkbook
'カーソルがあるセルの行を取得
Dim r As Long
r = ActiveCell.Row
Dim kamoku As String
Dim Kishu As Long
Dim Frg As Long
'元帳を作る科目名、期首残高、フラグを取得
kamoku = Worksheets("集計").Cells(r, 2).Value
Kishu = Worksheets("集計").Cells(r, 3).Value
Frg = Worksheets("集計").Cells(r, 1).Value
'元帳作成科目を判定
If Frg = 0 Then
MsgBox ("該当するデータはありません")
Exit Sub
End If
'■借方を元帳へ
'新規ブック作成
Workbooks.Add
'シート名を科目に
ActiveSheet.Name = kamoku
'シートを変数にセット
Dim w As Worksheet
Set w = Worksheets(kamoku)
'元帳のフォーマットを作成
w.Range("a1").Value = "日付"
w.Range("b1").Value = "相手科目"
w.Range("c1").Value = "借方"
w.Range("d1").Value = "貸方"
w.Range("e1").Value = "残高"
w.Range("f1").Value = "摘要"
'フィルターで科目を抽出
Wb.Worksheets("data").Range("a1").AutoFilter Field:=2, Criteria1:=kamoku
Dim D_count As Long
D_count = Wb.Worksheets("data").Range("a" & Rows.Count).End(xlUp).Row
'抽出データがあるかどうか判定
If WorksheetFunction.Subtotal(3, Wb.Worksheets("data").Range("a:a")) > 1 Then
'日付
Wb.Worksheets("data").Range("a2", "a" & D_count).Copy
w.Range("a3").PasteSpecial xlPasteValues
'相手科目
Wb.Worksheets("data").Range("c2", "c" & D_count).Copy
w.Range("b3").PasteSpecial xlPasteValues
'借方金額
Wb.Worksheets("data").Range("d2", "d" & D_count).Copy
w.Range("c3").PasteSpecial xlPasteValues
'摘要
Wb.Worksheets("data").Range("e2", "e" & D_count).Copy
w.Range("f3").PasteSpecial xlPasteValues
End If
Wb.Worksheets("data").Range("a1").AutoFilter
'貸方を元帳へ
Wb.Worksheets("data").Range("a1").AutoFilter Field:=3, Criteria1:=kamoku
D_count = Wb.Worksheets("data").Range("a" & Rows.Count).End(xlUp).Row
'抽出データがあるかどうか判定
If WorksheetFunction.Subtotal(3, Wb.Worksheets("data").Range("a:a")) > 1 Then
'貼り付け位置を指定
Dim Paste As Long
If w.Range("a3").Value < > 0 Then
Paste = w.Range("a" & Rows.Count).End(xlUp).Row + 1
Else
Paste = 3
End If
'日付
Wb.Worksheets("data").Range("a2", "a" & D_count).Copy
w.Range("a" & Paste).PasteSpecial xlPasteValues
'相手科目
Wb.Worksheets("data").Range("b2", "b" & D_count).Copy
w.Range("b" & Paste).PasteSpecial xlPasteValues
'貸方金額
Wb.Worksheets("data").Range("d2", "d" & D_count).Copy
w.Range("d" & Paste).PasteSpecial xlPasteValues
'摘要
Wb.Worksheets("data").Range("e2", "e" & D_count).Copy
w.Range("f" & Paste).PasteSpecial xlPasteValues
End If
'dataのフィルター解除
Wb.Worksheets("data").Range("a1").AutoFilter
'元帳へ期首残高を入力
w.Range("e2").Value = Kishu
'元帳を日付順にソート
D_count = w.Range("a" & Rows.Count).End(xlUp).Row
Range("a3", "i" & D_count).Sort key1:=Range("a3"), order1:=xlAscending
'残高を計算
Dim i As Long
For i = 3 To D_count
If Frg = 1 Then
Range("e" & i).FormulaR1C1 = "=-rc[-1]+r[-1]c+rc[-2]"
Else
Range("e" & i).FormulaR1C1 = "=rc[-1]+r[-1]c-rc[-2]"
End If
Next
'元帳のフォーマットを調整
Columns("a:f").AutoFit
Columns("a:a").NumberFormatLocal = "yyyy/mm/dd"
Columns("c:e").NumberFormatLocal = "#,###"
Columns("a:f").Interior.ColorIndex = xlNone
'元帳をテーブルへ変換
ActiveSheet.ListObjects.Add(xlSrcRange, Range("a1", "f" & D_count), , xlYes).Name = kamoku
End Sub
ポイントを解説します。
期首の残高、科目、フラグ、フラグの取得
'カーソルがあるセルの行を取得
Dim r As Long
r = ActiveCell.Row
Dim kamoku As String
Dim Kishu As Long
Dim Frg As Long
'元帳を作る科目名、期首残高、フラグを取得
kamoku = Worksheets("集計").Cells(r, 2).Value
Kishu = Worksheets("集計").Cells(r, 3).Value
Frg = Worksheets("集計").Cells(r, 1).Value
セルがある位置で、科目、期首残高を取得します。
フラグとは、元帳で残高を計算するときに使うものです。
たとえば、現金なら、残高＋借方金額ー貸方金額=残高と計算しますが、売上高は、残高＋貸方金額ー借方金額=残高と計算します。
これを判断するフラグ（数値）です。
元帳のフォーマット作成
'新規ブック作成
Workbooks.Add
'シート名を科目に
ActiveSheet.Name = kamoku
'シートを変数にセット
Dim w As Worksheet
Set w = Worksheets(kamoku)
'元帳のフォーマットを作成
w.Range("a1").Value = "日付"
w.Range("b1").Value = "相手科目"
w.Range("c1").Value = "借方"
w.Range("d1").Value = "貸方"
w.Range("e1").Value = "残高"
w.Range("f1").Value = "摘要"
新規のブックを開き、シート名を科目名に変えて、元帳のフォーマットを作ります。
オートフィルターで、科目名を抽出
'フィルターで科目を抽出
Wb.Worksheets("data").Range("a1").AutoFilter Field:=2, Criteria1:=kamoku
Dim D_count As Long
D_count = Wb.Worksheets("data").Range("a" & Rows.Count).End(xlUp).Row
オートフィルターで、科目名を抽出し、そのデータ数を数えます。
「現金」の場合は、こうなります。
コピーして元帳へ貼り付け
'抽出データがあるかどうか判定
If WorksheetFunction.Subtotal(3, Wb.Worksheets("data").Range("a:a")) > 1 Then
'日付
Wb.Worksheets("data").Range("a2", "a" & D_count).Copy
w.Range("a3").PasteSpecial xlPasteValues
'相手科目
Wb.Worksheets("data").Range("c2", "c" & D_count).Copy
w.Range("b3").PasteSpecial xlPasteValues
'借方金額
Wb.Worksheets("data").Range("d2", "d" & D_count).Copy
w.Range("c3").PasteSpecial xlPasteValues
'摘要
Wb.Worksheets("data").Range("e2", "e" & D_count).Copy
w.Range("f3").PasteSpecial xlPasteValues
End If
Wb.Worksheets("data").Range("a1").AutoFilter
抽出したデータがある場合は、それをコピーして貼り付けます。
貼り付けは値のみ（書式なし）です。
このIF文を入れておかないと、データがない場合（現金入金がない場合）、元帳を正しく作れません。
貸方を貼り付け
'貸方を元帳へ
Wb.Worksheets("data").Range("a1").AutoFilter Field:=3, Criteria1:=kamoku
D_count = Wb.Worksheets("data").Range("a" & Rows.Count).End(xlUp).Row
'抽出データがあるかどうか判定
If WorksheetFunction.Subtotal(3, Wb.Worksheets("data").Range("a:a")) > 1 Then
'貼り付け位置を指定
Dim Paste As Long
If w.Range("a3").Value < > 0 Then
Paste = w.Range("a" & Rows.Count).End(xlUp).Row + 1
Else
Paste = 3
End If
'日付
Wb.Worksheets("data").Range("a2", "a" & D_count).Copy
w.Range("a" & Paste).PasteSpecial xlPasteValues
'相手科目
Wb.Worksheets("data").Range("b2", "b" & D_count).Copy
w.Range("b" & Paste).PasteSpecial xlPasteValues
'貸方金額
Wb.Worksheets("data").Range("d2", "d" & D_count).Copy
w.Range("d" & Paste).PasteSpecial xlPasteValues
'摘要
Wb.Worksheets("data").Range("e2", "e" & D_count).Copy
w.Range("f" & Paste).PasteSpecial xlPasteValues
End If
'dataのフィルター解除
Wb.Worksheets("data").Range("a1").AutoFilter
同様に、貸方でフィルターをかけて、データがあったらコピーして貼り付けます。
この場合、貼り付け位置に注意です。
借方の場合、貼り付け位置は必ずセルA3になります。
貸方の場合は、借方の貼り付けがないときは、セルA3、貼り付けがあるときは、データの最終行＋1の行に貼り付けなければいけません。
その処理がこの部分です。
'貼り付け位置を指定
Dim Paste As Long
If w.Range("a3").Value < > 0 Then
Paste = w.Range("a" & Rows.Count).End(xlUp).Row + 1
Else
Paste = 3
End If
フォーマットを整える
貼り付けたあと（値のみ貼り付けているのでこういう表示になります）、期首の残高を入れて、
日付順にソートして、残高を計算します。
計算式は、前述のとおり、フラグで場合分けをします。
'残高を計算
Dim i As Long
For i = 3 To D_count
If Frg = 1 Then
Range("e" & i).FormulaR1C1 = "=-rc[-1]+r[-1]c+rc[-2]"
Else
Range("e" & i).FormulaR1C1 = "=rc[-1]+r[-1]c-rc[-2]"
End If
Next
最後に元帳のフォーマットを調整して、テーブルにして完成です。
（ここはお好みでやる部分です。）
実際は、横幅を1枚におさめるようにし、PDFにして保存するマクロも入れています。
'元帳のフォーマットを調整
Columns("a:f").AutoFit
Columns("a:a").NumberFormatLocal = "yyyy/mm/dd"
Columns("c:e").NumberFormatLocal = "#,###"
Columns("a:f").Interior.ColorIndex = xlNone
'元帳をテーブルへ変換
ActiveSheet.ListObjects.Add(xlSrcRange, Range("a1", "f" & D_count), , xlYes).Name = kamoku
マクロをショートカットキーに設定して、選択した科目で元帳を出せるようにしておけば、残高のチェックにも使えます。
ただし、その元帳を修正しても、元のデータには反映されません。
もともとの仕訳データを修正する必要があります。
まとめ
一部のお客様を除き、Excelのこの仕組みを使っています。
自分の経理もこれです。
（会社分、フリーランス分、家計を仕訳で入力し、それぞれで集計しています。預金はネットバンクを加工しています）
消費税があると、さらに大変になりますが、しくみを作っておけば、自分好みの会計ソフトができあがります。
なお、科目の設定、カスタマイズの問題があるので、一般的に提供する予定は今のところありません。
一般的にかちっと作ってしまうと使いにくくなり、市販会計ソフトと同じになってしまうからです。
（それでもなんとかしようとは思っていますが）
税務顧問のお客様又は、Excelコンサルのお客様のみの提供とさせていただいております。
サンプルはこちらです。
EX-ITサンプル 元帳作成マクロ
昨日は、午後にExcelコンサル2件。
仕事で使っている事例をお持ちいただいて、どう勧説すればいいか、Excelをどう考えればいいかについてお伝えしました。
セミナーでは、一般的な事例で基礎作りをし、コンサルでは、マンツーマンの個別対応で学べるようにしています。
【昨日の1日1新】
※詳細は→「1日1新」
ゼノブレイド 3DS
中目黒フレッシュネス
■著書
『税理士のためのプログラミング -ChatGPTで知識ゼロから始める本-』
『すべてをがんばりすぎなくてもいい！顧問先の満足度を高める税理士業務の見極め方』
ひとり税理士のギモンに答える128問128答
【インボイス対応版】ひとり社長の経理の基本
「繁忙期」でもやりたいことを諦めない! 税理士のための業務効率化マニュアル
ひとり税理士の自宅仕事術
リモート経理完全マニュアル――小さな会社にお金を残す87のノウハウ
ひとり税理士のセーフティネットとリスクマネジメント
税理士のためのRPA入門～一歩踏み出せば変えられる！業務効率化の方法～
やってはいけないExcel――「やってはいけない」がわかると「Excelの正解」がわかる
AI時代のひとり税理士
新版 そのまま使える 経理&会計のためのExcel入門
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
【監修】十人十色の「ひとり税理士」という生き方
