【第5回】税務調査も怖くない！Excelで「総勘定元帳」を全自動化する
※はじめに（E-E-A-T対策）
本連載で紹介するExcelの関数および動作環境は、Microsoft 365（最新版）またはExcel 2021以降を基準としています。古いバージョンではFILTER関数などが動作しないためご注意ください。
【これまでのあらすじ】
こんにちは、勤務医大家のほねぶとDrです。
今回が青色申告のためのエクセル講習、ついに最終回です！
前回までに、「仕訳帳（Journal）」に入力するだけで、「決算書（P/L・B/S）」が全自動で作られるシステムを構築しました。
しかし、「青色申告特別控除（65万円）」への道で、最後に立ちはだかる壁があります。それが「総勘定元帳（そうかんじょうもとちょう）」です。 これは税務調査が入った際に「見せて」と言われる最重要書類でもあります。
【今回のゴール】
Excelの最新関数を駆使して、「年号を変えるだけで、過去も未来も参照できる（タイムマシン機能付き）」の最強の元帳を作ります。
💡 「エクセルを作る時間がない！」という方へ
この連載ではエクセルでの自作方法を解説していますが、「正直、数式とか苦手…」「手っ取り早く終わらせたい」という方もいると思います。 「時間をお金で買いたい」 という方は、クラウド会計ソフト（マネーフォワードや弥生など）を使うのが一番の近道です。
- 銀行口座・クレカと連携して自動入力
- スマホでレシートを撮るだけで仕訳完了
- 確定申告書Bなどの提出書類も自動作成
目次
1. 目指すゴール：こんなシートを作ります
今回作る「Ledger（総勘定元帳）」シートの完成形はこれです。
- B1セル： 「2025」などの年号を入力
- B2セル： 「普通預金」などの勘定科目を入力
- 結果： A5行目以降に、その年の、その科目の取引データがズラッと自動表示される。しかも「残高」推移付き。
※最新のスピル機能（FILTER関数など）を使用するため、古いExcelでは動きません。
2. ステップ1：シートの準備
まず、新しいシートを作成し、シート名を「Ledger」とします。 以下の配置で枠組みを作ってください。
| セル | 入力内容 | 役割 |
|---|---|---|
| B1 | 2025 | 対象の年数（ここを変えるとデータが切り替わります） |
| B2 | 普通預金 | 見たい勘定科目 |
| A4 | 日付 | 見出し |
| B4 | 相手勘定 | 見出し |
| C4 | 摘要 | 見出し |
| D4 | 借方 | 見出し |
| E4 | 貸方 | 見出し |
| F4 | 残高 | 見出し |
※前提として、別シートに仕訳データが入ったテーブル（前回までに作成した tbl_Journal_2025 など）があるものとします。
3. ステップ2：魔法の数式を埋め込む
ここが核心です。 ExcelのINDIRECT関数とFILTER関数を組み合わせることで、「指定した年のテーブルを見に行き、指定した科目の行だけを引っ張ってくる」処理を実装します。 以下の数式を、それぞれのセルにコピペしてください。
A5セル（日付）
B5セル（相手勘定）
「自分が借方にいる時は貸方の科目を、自分が貸方にいる時は借方の科目を表示する」という処理をしています。
C5セル（摘要）
データがない場合に「0」と表示されるのを防ぐため、最後に &”” を付けています。
D5セル（借方金額）
※E5セル（貸方金額）は、上記の「借方」と「貸方」を入れ替えたものを使用します（長くなるため、配布テンプレートで確認してください！）。
4. ステップ3：最難関「残高推移」の自動計算
青色申告の要件として、総勘定元帳には「残高」の記載が必要です。 しかし、「資産（現金など）」は借方で増え、「負債（借入金など）」は貸方で増えるため、単純な足し算では計算できません。
そこで、[第3回]で作ったPL/BSシートからその科目の「区分（資産・負債など）」を読み取り、計算式を自動で切り替えるロジックを組みました。
▼ F5セルに入れて、下までオートフィル（コピー）
解説： 「期首残高（VLOOKUP）」＋「今の行までの借方累計」－「今の行までの貸方累計」を計算しています（負債の場合はプラスマイナス逆）。
5. 完成！これで何ができるのか？
このシートが1枚あれば、以下のような運用が可能になります。
調査官に「消耗品費の元帳を見せてください」と言われたら、B2セルを「消耗品費」に変えて印刷するだけ。秒で終わります。
「現金」を選択して、残高がマイナスになっていたら記帳漏れのサインです。
B1セルを「2025」に変えれば、一瞬で去年のデータに切り替わります。
6. まとめ：Excelで会計はもっと自由になる
市販ソフトは便利ですが、「ブラックボックス」になりがちです。 自分でシステムを構築すると、お金の動き（借方・貸方）が手に取るように理解できるようになります。何より、自分の事業規模やスタイルに合わせて、機能を無限に拡張できるのがExcelの醍醐味です。
そんな「ほねぶと流・資産管理システム」、ぜひ試してみてください。
*これは不動産所得が、消費税のかからないために簡便にできています。事業所得で消費税の計算が必要などより専門的な知識が必要な場合はうまく作動しない可能性もありますので、会計ソフトを利用することや専門家にご相談ください。
【読者限定】完成版エクセルを無料プレゼント！
作成した「確定申告用エクセルファイル（完成版）」ですが、トラブル防止のため直接の配布は行っておりません。
ご希望の方には個別にファイルをお送りしますので、以下のいずれかの方法でご連絡ください！
※「エクセル希望」と一言メッセージをいただければ、すぐにお送りします。
▼ 参考資料
- [Microsoftサポート] FILTER 関数
- [Microsoftサポート] LET 関数
