この記事は、あがぺいさんのブログ『ミールキットとExcel家計簿のあがぺいブログ』内で紹介されている「本気の家計簿」を参考に、Excelを使った複式家計簿を事業用に応用する帳簿「Excelの複式事業簿」を作るまでを撰述したものである。
あがぺいさんのブログ『ミールキットとExcel家計簿のあがぺいブログ』で紹介されている「本気の家計簿」を応用して個人事業用の帳簿をつけたい、と思い立ったことから、完全無料で「Excelの複式事業簿」を作っていく今回の連載。
今章ではいよいよ総勘定元帳のシートを作成していく。
仕訳帳と対を成す主要簿の一つであり、個人事業主には必須の帳簿を6章にしてようやく撰述。
なお「本気の家計簿」の作り方・記帳の仕方はここでは解説しないので、ぜひあがぺいさんのブログ『ミールキットとExcel家計簿のあがぺいブログ』をご参照いただきたい。
本記事は Windows版 Excel 2021 を基に解説しています。
Excel 365 や Mac版をご利用の場合、一部表示や機能に違いがある場合があります。
総勘定元帳の役割とは
総勘定元帳とは、仕訳帳に記録されたすべての取引の中から、「勘定科目ごと」に情報を整理して一覧化する帳簿のこと。
たとえば「現金」の動きを知りたいときは現金出納帳、「売上」を確認したいときは売上台帳が便利だが、それ以外の多くの勘定科目――たとえば「事務用品費」や「水道光熱費」など――の動きを把握するには、総勘定元帳が必要になる。
「Excelの複式事業簿」における仕訳帳シートでは、勘定科目による絞り込みや相手勘定科目の表示も可能で、簡易的な総勘定元帳としての利用もできる。
ただし、借方・貸方・残高の動きが一覧で把握しづらいため、別途「総勘定元帳」シートを用意し、月次・年次単位で勘定科目ごとの動きを記録・保存しておくのが望ましい。
「Excelの複式事業簿」総勘定元帳の作成手順
「Excelの複式事業簿」で総勘定元帳のシートを作成する手順を撰述していく。
なお、インボイスの対応／非対応どちらでのバージョンでも同じ手順となる。
- 画面下の［＋］をクリックして新しいシートを作成する
- シート名を「総勘定元帳」に変更し、一行目に以下の各見出しを追加する
- A列：年
- B列：日付
- C列：取引No
- D列：相手科目
- E列：摘要
- F列：借方金額
- G列：貸方金額
- H列：残高
- I列：備考
- H列の残高とI列の備考を範囲指定しテーブル化する
- Z1に評価対象年を入力し、Z2に勘定科目のリストを設定する
- Z2セルを選択＞［データ］タブ＞［データの入力規則］
- [入力値の種類：]を［日付］に＞［元の値：］に[=勘定科目]と入力＞［OK］
- 各セルに以下の数式を入力
- A2セル：A列からG列までを自動入力する数式
=LET(データ,shiwake,対象年,Z1,対象科目,Z2,年列,INDEX(データ,,1),日付列,INDEX(データ,,2),No列,INDEX(データ,,9),科目列,INDEX(データ,,5),摘要列,INDEX(データ,,7),金額列,INDEX(データ,,6),区分列,INDEX(データ,,8),相手列,INDEX(データ,,16),対象行,FILTER(データ,(年列=対象年)*(科目列=対象科目)),年出,INDEX(対象行,,1),日付出,INDEX(対象行,,2),No出,INDEX(対象行,,9),相手科目,INDEX(対象行,,16),摘要出,INDEX(対象行,,7),貸借出,INDEX(対象行,,8),金額出,INDEX(対象行,,6),借方金額,IF(貸借出=”借方”,ABS(金額出),””),貸方金額,IF(貸借出=”貸方”,ABS(金額出),””),CHOOSE({1,2,3,4,5,6,7},年出,日付出,No出,相手科目,IF(摘要出=””,””,摘要出),借方金額,貸方金額))
- H2セル：借方金額と貸方金額から差額を残高として表示する数式
=IF(AND(F2=””, G2=””), “”, SUM(F$2:F2) – SUM(G$2:G2))
- A2セル：A列からG列までを自動入力する数式
- そのままでは「#CALC!」エラーが表示される
- Z2のリストから勘定科目を選択すればデータが反映される
- 日付表示が正しくない場合は設定を変更する
- B列を右クリック＞［セルの書式設定］
- [分類：]を[日付]に＞［種類：］を[〇月〇日]の表示に＞［OK］
- 勘定科目シートの大枠の完成
- ①画面下の［＋］をクリックして新しいシートを作成する
- ②②シート名を「総勘定元帳」に変更し、一行目に画像のように各見出しを追加する
- ③③H列の残高とI列の備考を範囲指定しテーブル化する
- ④Z1に評価対象年を入力し、Z2に勘定科目のリストを設定する
Z2セルを選択＞［データ］タブ＞［データの入力規則］
[入力値の種類：]を［日付］に＞［元の値：］に[=勘定科目]と入力＞［OK］
- ⑤各セルに手順⑤の数式を入力
- ⑥そのままでは「#CALC!」エラーが表示される
Z2のリストから勘定科目を選択すればデータが反映される
- ⑦日付表示が正しくない場合は設定を変更する
B列を右クリック＞［セルの書式設定］
[分類：]を[日付]に＞［種類：］を[〇月〇日]の表示に＞［OK］
- ⑧勘定科目シートの大枠の完成
「Excelの複式事業簿」総勘定元帳シートの使い方
このセクションでは大枠が完成した「Excelの複式事業簿」の総勘定元帳シートの使い方について解説する。
残高は都度テーブルを拡張して反映させる
総勘定元帳もその他の一部の台帳と同じく、完全オートマチックにすべての項目が反映されるようにはできていない。
借方金額と貸方金額の、その時点の差額を表示する残高列に関しては、反映される取引が増える都度、そのテーブルを拡張していく必要がある。
一度テーブルを拡張した分に関しては、Z2セルの勘定科目リストを他の科目に変えたときやZ1の評価対象年を変更した際に、そのまま残ってしまう仕様となっているが、特に問題が発生することはない。
相手科目が複数の場合は一行にまとまってしまう
こちらも仕様となってしまう欠点の一つ。
仕訳帳シートで仕訳を記帳する際、相手科目が複数になった場合は、そのすべてが総勘定元帳シートの相手科目欄に表示されるようになっている。
こちらはExcel2021ではどうしても一つ一つの行に分けることが困難だったため、もし帳簿保存のために印刷やPDFファイルとして残しておく場合は、備考欄にその取引の内訳をメモした上で残しておくことを推奨する。
ちなみに備考欄に入力した文字に関しては、拡張したテーブルと同じく、Z2セルの勘定科目リストを変更したときにもそのまま残り続けてしまうので、注意が必要だ。
総勘定元帳シートで現金出納帳や預金出納帳はまかなえる？
これまでに、総勘定元帳シートの作成方法とその使い方を解説してきた。
ここで勘の鋭い方なら、こう思われたかもしれない。
このシート、以前に作成した現金出納帳（第5章-2）や預金出納帳（第5章-3）と、あまり変わらないのでは？
ということは、他の補助簿は作らなくても、総勘定元帳シートひとつで十分じゃん
――ある意味では、それは正しい。
実際、Z2セルで「現金」や「預金」などの科目を選べば、その科目の動きを一覧で表示することができ、出納帳としての代用は十分に可能である。
しかし、それでもなお、出納帳シートは別に作成しておくことを強く推奨している。その理由は、大きく分けて次の2点である。
理由①：帳簿保存義務の観点から、台帳は明確に分けておくべき
青色申告者には、仕訳帳や総勘定元帳といった主要簿に加え、必要な補助簿も帳簿として保存する義務がある。
たとえ「Z2セルで科目を切り替えれば他の帳簿も確認できる」という設計であっても、実態として帳簿が分かれていなければ、保存義務をきちんと果たしているとは言い難い。
データ上も、形式上も、それぞれの台帳を独立したシートとして分けておくほうが安心である。
理由②：科目ごとの流れを同時に確認したいときに便利
もうひとつの理由は、実務上の使いやすさだ。
たしかに、総勘定元帳シートで「現金」や「普通預金」に切り替えれば、出納帳のように使うことはできる。
だが、その都度切り替える必要があり、現金と預金の動きを同時に追いたい場合などは不便である。
たとえば、現金出納帳を確認しつつ、同時に預金出納帳で振替処理を確認したいとき。あるいは、売上がどのように入金されているかを出納帳と照らし合わせたいとき。シートを分けておけば、画面を並べて確認することができる。
台帳は目的別に分けて使うほうが賢明
総勘定元帳は、あくまで「勘定科目ごとの履歴を記録・集計するための帳簿」であり、出納帳は「日々のお金の出入りを時系列で把握する帳簿」だ。
目的が異なる以上、用途に応じてシートを分けておくことが、帳簿の運用としてもスマートである。
