【Excel会計③】コピペで完成！全自動で決算書を作る「魔法の数式」
※はじめに（E-E-A-T対策）
本連載で紹介するExcelの関数および動作環境は、Microsoft 365（最新版）を基準としています。Excel 2016以前のバージョンでは一部の関数（UNIQUE、FILTER等）が動作しない可能性があります。
【今回のゴール】
A1セルの「2026」という数字を変えるだけで、自動でその年のデータを集計し、前年からの繰越まで完了する「永久機関」を完成させます。
安心してください。難しい理屈はさておき、「指定された場所に数式をコピペ」すれば動くように設計しています。 （「うわ、作るの面倒くさそう…」と心が折れかけた読者への救済措置）
💡 「エクセルを作る時間がない！」という方へ
この連載ではエクセルでの自作方法を解説していますが、「正直、数式とか苦手…」「手っ取り早く終わらせたい」という方もいると思います。 「時間をお金で買いたい」 という方は、クラウド会計ソフト（マネーフォワードや弥生など）を使うのが一番の近道です。
- 銀行口座・クレカと連携して自動入力
- スマホでレシートを撮るだけで仕訳完了
- 確定申告書Bなどの提出書類も自動作成
目次
1. 準備：シート作成と「司令塔」の設定
まず、新しいシートを作成し、名前を PL&BS_2026 としてください。
（※この名前が重要です。来年はシートをコピーして PL&BS_2027 に変えるだけで動くようになります）
そして、A1セルに半角で 2026 と入力します。 たったこれだけですが、この数字がシステム全体の「司令塔」として機能します。
2. STEP 1：メイン集計表の構築（A列〜F列）
全ての計算の基礎となるメイン表を作ります。 1行目に見出し（勘定科目名、区分、期首残高、借方合計、貸方合計、期末残高）を作り、A列〜F列を選択して「テーブル化（Ctrl+T）」してください。
テーブルデザインタブで、テーブル名を tbl_PLBS_2026 に変更してください。
準備ができたら、3行目（データ1行目）に以下の数式をコピペしてください。
（1）A列・B列（Masterデータの読み込み）
マスタシートから科目情報を自動で引っ張ってきます。
（2）C列（期首残高：前年からの繰越）
ここが「永久機関」のキモです。 A1セル（2026）から1を引いた年、つまり「2025年のシート（PL&BS_2025）」を自動で見に行き、去年の期末残高を引っ張ってきます。また、元入金に関しては特殊なため、元入金にのみ反応するIF関数も盛り込み、前年からの引継ぎをスムーズにできるようにしています。元入金の計算は$H$3でやる想定で、関数を設定してます。
※1年目（前年シートがない場合）のエラー回避も入っています。
（3）D列・E列（借方・貸方集計）
INDIRECT関数を使い、Journalシート（tbl_Journal_2026）から、その科目の合計金額を集計します。
（4）F列（期末残高）
区分（資産・負債・売上・経費）ごとに、足し引きのルールを変えて計算させます。
3. STEP 2：最重要！「元入金」の例外処理と損益通算のための利息計算
元入金は「別の数式」が必要です。 個人事業主の会計では、翌年の開始日である1月1日の「元入金」は毎年変わります。 シートの余白（P列あたり）に「来年のための計算機」を作ります。
ただ、元入金の考え方は、僕も躓いた部分なので、第4回で詳しく説明します。 下図のようにH～K列はとりあえずあけておいてください。
4. STEP 3：レポートの自動生成（M列〜）
メイン表の右側（M列あたり）に、確定申告書に転記しやすい「きれいなPL表」を自動生成します。 最近のExcel（Office365等）なら、以下の数式を入れるだけで勝手に表が広がります（スピル機能）。
スピル機能は勝手に表が広がるため、項目数に応じて、行を作成しておかないと#スピルとエラーが出ます。例えば売上の勘定科目が３つ設定しているならM4～M6まではなにも数式や文字を入れないようにしてください。これに気づくまでに僕はすごい時間かかりました💦
1. 収益（売上）リスト
2. 費用（経費）リスト
5. 動かない時のチェックリスト
「数式をコピペしたのにエラーになる！」 そんな時は、以下の4つを確認してください。
-
□
テーブル名の変更忘れ Journalシートのテーブル名は
tbl_Journal_2026になっていますか？（Table1 等では動きません） -
□
A1セルの年号 PL&BSシートのA1セルは半角数字の
2026ですか？（2026年 はNG） -
□
列名の完全一致 Journal側の列名と数式内の [借方科目] は完全に同じですか？（スペースの有無に注意）
-
□
見出しの参照 PL&BSシートのA列見出しは
勘定科目になっていますか？
tbl_PLBS_20〇〇の方でドラックしすぎて＃VALUEが出ていることがあります。 必要以上のドラックのせいでエラーになってますので、必要量のみ行ってください。
6. まとめ
お疲れ様でした！ これでシステム構築は完了です。
- A列〜F列でデータを全自動集計
- K列以降できれいな決算書を表示
- A1セルを変えるだけで翌年へ自動繰越（INDIRECT関数の力）
この構成さえ作ってしまえば、あなたは毎年の「繰越処理」のストレスから完全に解放されます！ このシステムを使った「日々の運用（レシート入力など）」と「決算整理（減価償却など）」について解説します。 （「自力でやりたいけど、一から作るのは面倒」という層へのプレゼント）
【読者限定】完成版エクセルを無料プレゼント！
作成した「確定申告用エクセルファイル（完成版）」ですが、トラブル防止のため直接の配布は行っておりません。
ご希望の方には個別にファイルをお送りしますので、以下のいずれかの方法でご連絡ください！
※「エクセル希望」と一言メッセージをいただければ、すぐにお送りします。
▼ 参考資料
- [Microsoftサポート] INDIRECT 関数
- [Microsoftサポート] SUMIFS 関数
▼ あわせて読みたい
